Type of Locks
Shared Locks - SQL Server applies shared locks for all read operations. If a shared lock is placed on a data page the other transactions can also acquire lock on the same page even when the first transaction is not completed that means any number of shared locks on a resource can be placed by concurrent sessions. No other transactions can modify the data (or place exclusive lock) until all shared locks. The shared lock gets released as soon as the read operation is completed. For example (select * from persons), will place a shared lock on the first page in the persons table when query starts, after data on the first page is read the lock on the page is released. It doesn’t hold the lock until the statement completes or until the end of its transaction.
Update Locks - SQL Server applies update locks on resource (page / row) that a transaction is would like to modify. If a transaction tries to update a row, first step it needs to read that row from the page (then it places a shared lock) on that page / row, A resource (page / row) that has update lock on it can still have shared locks from other concurrent sessions to prevent deadlocks. If a transaction initially with shared lock needs to converted to Exclusive (X) lock on the resource to modify the record and to prevent other users from modifying the same record, it may lead to deadlocks in the environment where multiple transactions are trying to update same record at the same time. Update locks will prevent this kind of situation, this update lock will allow shared locks on the same page / row but it doesn’t allow exclusive locks, this is an internal lock help to avoid deadlocks. If the other transactions need to change same resource then no shared locks are exists then update lock are promoted to exclusive locks.
In general read operations acquired shared locks and write operations acquired exclusive locks.
Exclusive Lock – SQL Server applies exclusive lock to prevent access to that resource by other transactions, when an Exclusive (X) lock placed on a resource then no other transaction will read / modify the data.
Intent Locks (I) - SQL Server uses intent locks to establish locking hierarchy. For example if a transaction placed an exclusive (X) lock on a row, then SQL Server places an Intent (I) lock on the table / page itself. The intent lock prevents other transaction from modifying or dropping the table / page while my session is reading that row. Intent locks improves locking performance by allowing SQL Server to examine locks at the table level to determine the locks held on the table, rather than searching through multiple locks at the page or row level within the table. There are three types of Intent Locks, Intent Shared (IS), Intent Exclusive (IX), and shared with intent exclusive (SIX). The IS lock indicates the process holds shared locks on the lower-level resources (page / row), The IX lock indicates the process hold exclusive locks on the lower level resources. The SIX lock has occurs on a special circumstances that a transaction is holding shared lock on a resource, and later in the transaction an IX lock is needed. In that case IS lock is converted to SIX.
Schema Locks - SQL Server uses schema locks to maintain structural integrity of SQL Server tables. There are two types of Schema Locks, Schema Modification Locks (Sch-M) and Schema Stability (Sch-S), SQL Server places Schema Modification Locks during a DDL operation is performed such as Alter Table, Create Table, and during that time it prevents concurrent access to the table until the transaction is completed or committed.
Schema Stability locks are placed while doing read operations, these are similar to shared locks on the object, it won’t block normal processing including write operations, but it will block other DDL operations on that resource. This means no other process will modify the schema of the object such as dropping an index, or altering a stored procedure or table, while other processes is referencing the object.
Bulk Update Lock (BU) - These are special types of locks used only when bulk copying data using bcp utility or the BULK Insert command. Bulk Update allows multiple threads to bulk load data concurrently into same table however it blocks other processes which is not doing Bulk Update.
select resource_type, request_session_id, db_name(resource_database_id) DatabaseName, request_mode, resource_description from sys.dm_tran_locks where resource_database_id>4
Shared Locks - SQL Server applies shared locks for all read operations. If a shared lock is placed on a data page the other transactions can also acquire lock on the same page even when the first transaction is not completed that means any number of shared locks on a resource can be placed by concurrent sessions. No other transactions can modify the data (or place exclusive lock) until all shared locks. The shared lock gets released as soon as the read operation is completed. For example (select * from persons), will place a shared lock on the first page in the persons table when query starts, after data on the first page is read the lock on the page is released. It doesn’t hold the lock until the statement completes or until the end of its transaction.
Update Locks - SQL Server applies update locks on resource (page / row) that a transaction is would like to modify. If a transaction tries to update a row, first step it needs to read that row from the page (then it places a shared lock) on that page / row, A resource (page / row) that has update lock on it can still have shared locks from other concurrent sessions to prevent deadlocks. If a transaction initially with shared lock needs to converted to Exclusive (X) lock on the resource to modify the record and to prevent other users from modifying the same record, it may lead to deadlocks in the environment where multiple transactions are trying to update same record at the same time. Update locks will prevent this kind of situation, this update lock will allow shared locks on the same page / row but it doesn’t allow exclusive locks, this is an internal lock help to avoid deadlocks. If the other transactions need to change same resource then no shared locks are exists then update lock are promoted to exclusive locks.
In general read operations acquired shared locks and write operations acquired exclusive locks.
Exclusive Lock – SQL Server applies exclusive lock to prevent access to that resource by other transactions, when an Exclusive (X) lock placed on a resource then no other transaction will read / modify the data.
Intent Locks (I) - SQL Server uses intent locks to establish locking hierarchy. For example if a transaction placed an exclusive (X) lock on a row, then SQL Server places an Intent (I) lock on the table / page itself. The intent lock prevents other transaction from modifying or dropping the table / page while my session is reading that row. Intent locks improves locking performance by allowing SQL Server to examine locks at the table level to determine the locks held on the table, rather than searching through multiple locks at the page or row level within the table. There are three types of Intent Locks, Intent Shared (IS), Intent Exclusive (IX), and shared with intent exclusive (SIX). The IS lock indicates the process holds shared locks on the lower-level resources (page / row), The IX lock indicates the process hold exclusive locks on the lower level resources. The SIX lock has occurs on a special circumstances that a transaction is holding shared lock on a resource, and later in the transaction an IX lock is needed. In that case IS lock is converted to SIX.
Schema Locks - SQL Server uses schema locks to maintain structural integrity of SQL Server tables. There are two types of Schema Locks, Schema Modification Locks (Sch-M) and Schema Stability (Sch-S), SQL Server places Schema Modification Locks during a DDL operation is performed such as Alter Table, Create Table, and during that time it prevents concurrent access to the table until the transaction is completed or committed.
Schema Stability locks are placed while doing read operations, these are similar to shared locks on the object, it won’t block normal processing including write operations, but it will block other DDL operations on that resource. This means no other process will modify the schema of the object such as dropping an index, or altering a stored procedure or table, while other processes is referencing the object.
Bulk Update Lock (BU) - These are special types of locks used only when bulk copying data using bcp utility or the BULK Insert command. Bulk Update allows multiple threads to bulk load data concurrently into same table however it blocks other processes which is not doing Bulk Update.
select resource_type, request_session_id, db_name(resource_database_id) DatabaseName, request_mode, resource_description from sys.dm_tran_locks where resource_database_id>4
No comments: