Contents
What is the benefit of intent locks?
Intent locks serve two purposes: To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level. To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.
What is intent locking?
An intent lock is the lock the database server (lock manager) places on a higher granularity object when a lower granularity object needs to be locked.
What are the consequences of acquiring lock at row-level?
So long as no deadlock situation is detected, a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released. This means it is a bad idea for applications to hold transactions open for long periods of time (e.g., while waiting for user input).
What are the type of intent locking?
The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Used when an operation dependent on the schema of a table is executing.
Shared locks support read integrity. They ensure that a record is not in the process of being updated during a read-only request. Shared locks can also be used to prevent updates of a record between the time that a record is read and the next syncpoint.
Can two transaction acquire exclusive lock on same time?
Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.
What are the two modes of locking?
Two types of locks are utilized by the basic protocol: Shared and Exclusive locks. Refinements of the basic protocol may utilize more lock types. Using locks that block processes, 2PL may be subject to deadlocks that result from the mutual blocking of two or more transactions.