Update lock (U) – this lock is similar to an exclusive lock but is designed to be more flexible in a way. In addition, a shared lock will allow write operations, but no DDL changes will be allowed However, a shared lock can be imposed by several transactions at the same time over the same page or row and in that way several transactions can share the ability for data reading since the reading process itself will not affect anyhow the actual page or row data. Shared lock (S) – this lock type, when imposed, will reserve a page or row to be available only for reading, which means that any other transaction will be prevented to modify the locked record as long as the lock is active. This practically means that only one exclusive lock can be imposed to a page or row, and once imposed no other lock can be imposed on locked resources An exclusive lock can be imposed to a page or row only if there is no other shared or exclusive lock imposed already on the target. The exclusive lock will be imposed by the transaction when it wants to modify the page or row data, which is in the case of DML statements DELETE, INSERT and UPDATE. Lock mode considers various lock types that can be applied to a resource that has to be locked:Įxclusive lock (X) – This lock type, when imposed, will ensure that a page or row will be reserved exclusively for the transaction that imposed the exclusive lock, as long as the transaction holds the lock. SQL Server locks can be specified via the lock modes and lock granularity Translated into the SQL Server language, this means that when a transaction imposes the lock on an object, all other transactions that require the access to that object will be forced to wait until the lock is released and that wait will be registered with the adequate wait type Once the lock is released by committing the changes or by rolling back changes to initial state, other transactions will be allowed to make required data changes. While objects are locked, SQL Server will prevent other transactions from making any change of data stored in objects affected by the imposed lock. SQL Server locking is the essential part of the isolation requirement and it serves to lock the objects affected by a transaction.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |