Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Sql Server transaction interview questions answers

Transaction interview questions answers  

In this article I will share some frequently asked transaction interview questions and answers in SQL server.
1. What is transaction?

Transactions provide a mechanism for grouping a series of database changes into one logical operation. After changes are made to the database, these changes can be committed or canceled as a single unit.

2. What is save transaction and save point?

Creates points within groups of transactions in which to ROLLBACK.Save points offer a mechanism to roll back portions of transactions. A user can decide and set a savepoint, within a transaction. 

The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.

In Detail… http://codechef4u.com/post/2015/04/13/save-transaction-and-savepoint

3. Explain Transaction Isolation levels in SQL Server?

To ensure quality of your data in RDBMS transaction isolation level is important.
There are five isolation levels - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED (snapshot), REPEATABLE READ and SERIALIZATION.

4. What is read committed?

This is the default isolation level and means selects will only return committed data. This prevents dirty reads.

For more info ….http://www.codechef4u.com/post/2015/04/05/read-committed

5. What is read UNCMMITED?

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

 For more info … http://www.codechef4u.com/post/2015/04/05/read-uncommitted

6. What is snapshot?

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

7. What is REPEATABLE READ?

This is the default isolation level and means selects will only return committed data. This prevents dirty reads.

For more info ….http://www.codechef4u.com/post/2015/04/05/read-committed

8. What is SERIALIZABLE?

 This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads.

For more info ….http://www.codechef4u.com/post/2015/04/07/serializable

9. What are Different Types of Locks?

Following are the list of locks used in sql server:

SHARED : 

SQL Server uses shared locks for all read operations. For example a select statement.

UPDATE :

This lock applied on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

EXCLUSIVE :

 Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

INTENT: 

Used to establish a lock hierarchy. The different types of intent locks are: intent shared, intent exclusive, and shared with intent exclusive.

SCHEMA: 

Used when an operation dependent on the schema of a table is executing. The different types of schema locks are: schema modification and schema stability.

BULK UPDATE :

This lock is applied when there is a bulk copying of data and the TABLOCK is applied.

KEY RANGE : 

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

10. What are Pessimistic Lock and Optimistic Lock?

Pessimistic:

In pessimistic locking a record or page is locked immediately when the lock is requested.

The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time

Optimistic:

In an optimistic lock the record or page is only locked when the changes made to that record are updated. 

11. What is Dirty Read?

Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and data is changing in the data set before the end of the transaction, thus getting you incorrect or wrong data.

12. What is Difference between Commit and Rollback when Used in Transactions?

Commit:

Commit marks the end of a successful implicit or explicit transaction. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.

Rollback (MSDN details):

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

13. What is NOLOCK?

When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.

WITH (NOLOCK)

Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. 

14. What is the Difference between Update Lock and Exclusive Lock?

Exclusive lock ensures that multiple updates cannot be made to the same resource at the same time.
Update lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

15. What is explicit mode in SQL Server?

An explicit transaction is one in which you explicitly define both the start and end of the transaction.

16. What is implicit mode in SQL Server?

When a connection is operating in implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back.

17. What is autocommit mode in SQL Server?

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.

18. What are “Unrepeatable reads”?

Non repeatable read is a situation where a session finds itself in when it perform multiple read.

It is possible to perform the query in the same transaction more than one and show different result.

19. What are “Phantom rows”?

Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.

20. What are “Lost Updates”?

Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.

21. What are “Lock” hints?

Lock hints are used to customize the locking behavor of SQL Server from 3 main perspectives, granularity, mode, and duration. Available hints are HoldLock, NoLock, NoWait, PagLock, ReadCommtted, ReadCommittedLock, ReadPast, ReadUncommitted, RepeatableRead, RowLock, Serializable, TabLock, TabLockX, UpdLock, and XLock. A full understanding of them is important before using them.

Reference … http://www.sqlnotes.info/tag/lock-hints/

22. What is a “Deadlock”?

This is situation where both transactions in a deadlock will wait forever unless the deadlock is broken by an external process.

A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward.

23. What are the steps you can take to avoid “Deadlocks”?

Some Suggestions:

1. Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation.

2. Ensure the database is normalized properly because bad database design can increase the number of deadlocks to occur inside database.

3. Avoid cursors if possible .

4. Implement proper locking or avoid it.

5. Consider using the NOLOCK hint where possible

6. Determine whether a transaction can run at a lower isolation level.

24. What is Concurrency?

 

25. How can we solve concurrency problems?

27. What kind of problems occurs if we do not implement proper locking strategy?

1. Deadlock issue.
2. Concurrency problems.


Invalid entry,please enter valid data.

Loading