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. Durability – requires that committed data must be stored using method that will preserve all data in correct state and available to a user, even in case of a failure.Isolation – requires that a transaction that is still running and did not commit all data yet, must stay isolated from all other transactions.Consistency – requires that a transaction must create a valid state of new data, or it must roll back all data to the state that existed before the transaction was executed.Atomicity – requires that a transaction that involves two or more discrete parts of information must commit all parts or none.To understand better the locking in SQL Server, it is important to understand that locking is designed to ensure the integrity of the data in the database, as it forces every SQL Server transaction to pass the ACID test.ĪCID test consists of 4 requirements that every transaction have to pass successfully: A lock as an in-memory structure is 96 bytes in size. Essentially, locks are in-memory structures which have owners, types, and the hash of the resource that it should protect. Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency.
0 kommentar(er)
