Locks and Dead Lock in SQL Server

Locks and Dead Lock in SQL Server 2008:


SQL Server uses locking and blocking mechanism to maintain data consistency in multiuser environment. Lock Manager is the SQL Server Internal governing body to handle locking scenarios like:

1. Determine Appropriate lock to be acquire/Release on resource

2. How much time will retain the lock

3. Decide when processes are allowed to modify data so that reads should be consistent

4. I. When SQL Server started with lock set to 0,lock manager allocate 2 percent of the memory allocated for SQL Server for an initial pool of lock structure(SQL 2000)

II. When SQL Server started with lock set to 0,lock manager allocate appropriate percent of the memory allocated for SQL Server for an initial pool of l 2500 lock structure(SQL 2005) as soon as lock pool is exhausted lock manager request for additional memory

III. The dynamic lock pool does not acquire more than 60 percent of the memory allocated to the Database Engine. After the lock pool has reached 60 percent of the memory acquired by an instance of the Database Engine, or no more memory is available on the computer, further requests for locks generate an error

5. Resolve Dead locks

6. compatibility between locks



SQL Server has seven different locking modes and three different lock types:

Locking modes are:

1. Shared locks(S): placed on resource for read operation and compatible for other shared and update lock except exclusive lock. When repeatable read or locking hint is used then shared lock is retained till duration of the transaction otherwise shared lock released as soon as read is completed

Equation: S on S = OK, S on U = OK, S on X = WAIT



2. Update Lock(U): placed on resource where shared lock is required and need to upgrade to exclusive lock. one transaction can acquire update lock on resource once the modification is required on resource update lock upgrade to exclusive lock

Equation: U on S = OK, U on U = WAIT, U on X = WAIT



3. Exclusive Lock(X): placed on resource for data modification and not compatible with any other type of lock .only nolock hint and read uncommitted isolation level overrides exclusive lock functionality.

Equation: X on S = WAIT, X on U = WAIT, X on X = WAIT



4. Intent(IS,IX,SIX):placed on resource to improve performance and locking efficiency by placing at high level i.e. table level



5. Schema(Sch-M,Sch-S):Sch-M is placed on resource while schema modification like while adding column to a table and Sch-S are placed on object/resource while query are being compiled or executed.

Equation: Sch-M Block all other operations until lock is released

Sch-S Locks are not compatible with Sch-M Locks







6. Bulk Update(BU):Placed on resource for Bulk insert and update of the data, these locks allow multiple bulk insert threads to access table but does not allow other processes to access the table,BU locks is enable by using TABLOCK hint or by using sp_tableoption for table lock for bulk load table option.



7. Key Range: Placed on the range of rows to protect against phantom insertion/deletion in a record set accessed by transaction, these locks are used by transaction using SERILIZABLE transaction isolation level.





These three different lock mode can be acquire at Row, Page, Table level and scope

Up to session, transaction, and cursor.



Dead Lock: when two transactions are holding resources and both of them require before completion.

Once the dead lock situation is detected lock manager make one of the transactions as victim for rollback and raise error 1205 which is captured/handled properly.