Locks
Locking is a part of SQL Server operations. When a row is read by a query a lock will be placed on that row to prevent any modifications which reading. If we issue a update statement on a single row a lock will be placed on that row to prevent reading the data which updating. Different types of locks issued by SQL Server DB engine to determine how the resources can be accessed by concurrent transactions.
Types of Locks
Shared Lock-- SQL Server applied shared lock on all read operations. Any number of shared locks can be held on a particular resource on the same time. As soon as the select statment completes reading the shared lock will be released from the resource.
Update Lock -- Update Lock, In other words it is a combination of share and exclusive locks. It is to prevent the deadlock scenario. An update lock will be held when user trying to modify the data. If an update command is issued first thing what it will do is, to update the row first it needs to find out the row which needs to be updated, till that time it places a shared lock initially after that it will convert that into exclusive lock to prevent other users needs to modify the record at same time. These update locks are not only used for update operations, DB engine uses update locks all time when a search operation needs to perform prior update or delete commands.
Exclusive Lock -- This lock is applied when any insert / update / delete operations are performed and to ensure no multiple updates will happen on the same resource at the same time. Exclusive locks are not compatible with any other locks. If an exclusive lock is placed on a resource the other transactions needs to wait until the current transaction is completes.
Intent Lock -- Intent locks are used to setup lock hierarchy. It protect placing a shared (S) or exclusive (X) lock on a resource lower in the lock hierachy. There are three type of Intent locks Intent Shared (IS), Intent Exclusive (IX) and shared intent exclusive (SIX).
Schema Lock -- Schema locks are applied while performing DDL operations on tables, views and such as adding a column to a table, or dropping a table etc., There are two types of schema locks
a) Schema Modification Lock (Sch-M) -- While performing any DDL operation like altering a table it wont allow other transactions to be performed until the lock is released to prevent concurrent access to table structure.
b) Schema Stability Locks -- The DB engine applies schema stability locks when compiling or executing queries, and it doesn't block any transaction locks like (shared / exclusive or any other) locks. If it is refer an index at that time it places a schema lock because to ensure some other process should not delete that one.
Bulk Update Locks -- The DB Engine uses bulk update locks (BU) when bulk copying data into tables using bcp utility. This will be applied only when TABLOCK hint is specified at bcp utility. It allows other users to perform bulk copy data into same table in parallel and preventing accessing data by users that are not performing bulk load operation.
Key Range Locks -- It protects a range of rows read by a query while using a serializable isolation level.
And also ensures to prevent other transactions to insert rows (phantom inserts and phantom deletes).
Blocking
If a process say Transaction1 placed a lock on a table (ie., on a complete table or a set of rows) in the same time Transaction2 is trying to modify the data which is hold by Transaction1 then blocking occurs. Because the Transaction2 needs to wait until Transaction1 completes it work and release the lock. Blocking often happens on heavy OLTP systems.
Deadlocking
When two processes say transaction1 and transaction2 have locks on seperate objects, for example transaction1 is locked objectA and transaction2 is locked objectB and transaction2 is trying to aquire lock on objectA and transaction1 is trying to acquire lock on ObjectB then deadlock occurs.
SQL Server will automatically detect and resolves deadlocks by using an internal process called LOCK_MONITOR, when a deadlock occurs then sql server will choose a process which will take less amount to time to abort or rollback then it aborts that process, like this deadlock will be resolved. There are seveal ways to identify using profiler deadlock graph, dmv like sys.dm_tran_locks etc.,
No comments: