This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Database session A tries to update some data that is already locked by database session B. What happens to session A?
The session A will actually be placed in what’s called a lock wait state, and session A will be stopped from making further progress with any SQL transaction that it’s performing. Another way of saying this is that session A will be “stalled” until session B releases the lock on that data.
What is lock contention?
One problem that occurs with having locks is that locks can cause what’s known as contention.
In the best case, lock contention means that some user processes run slower because a session is waiting for a lock. In the worst case, having sessions compete for locks can make sessions stall for an indefinite period of time.
What is Locking Hints option?
Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. The hints specify the type of locking or row versioning the instance of the SQL Server Database Engine uses for the table data.
Does Locking hints override the current transaction isolation level?
Yes, The Locking hints override the current transaction isolation level for the session.
What is WITH (NOLOCK)?
It is equivalent to READUNCOMMITTED isolation level and you can SELECT table dirty data which are not yet committed.
What is WITH (NOWAIT)?
It instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table.
What is WITH (ROWLOCK)?
It specifies that row locks are taken when page or table locks are ordinarily taken.
What is WITH (TABLOCK)?
Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed.
What is WITH (UPDLOCK)?
It specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level.
What is the use of sys.dm_tran_locks?
You can query the sys.dm_tran_locks dynamic management view to obtain information about the current state of locking in an instance of the Database Engine.
What is a Lock Escalation?
A query might have a resource locked for “shared” and escalate it to “exclusive” to perform an update. SQL Server does Lock Escalation in order to reduce memory overhead, by converting several fine-grained low level locks to coarse-grained high level locks. Page Locks are also escalated to Table Locks.
What is the Spinlock?
It controls access to a lock resource, such as a row or a table.
Without lock partitioning, one spinlock manages all lock requests for a single lock resource.
To reduce contention on a single lock resource, lock partitioning splits a single lock resource into multiple lock resources to distribute the load across multiple spinlocks.
How READ_COMMITTED internally implemented row level locks?
When SQL Server executes a statement at the read committed isolation level, it acquires short lived share locks on a row by row basis. The duration of these share locks is just long enough to read and process each row; the server generally releases each lock before proceeding to the next row.
Within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation and assuming row-level locking, how long are locks held or retained for?
Answer: Within either of these isolation levels, locks are held for the duration of the transaction, unlike within the READ_COMMITTED isolation level.
When Lock escalation is triggered?
When a single Transact-SQL statement acquires at least 5,000 locks on a single table or index.
When the number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.
If locks cannot be escalated because of lock conflicts, the Database Engine periodically triggers lock escalation at every 1,250 new locks acquired.
Name as many of the lockable resources of the SQL Server.
- RID (single row on a heap)
- KEY (single row (or range) on an index)
- PAGE
- EXTENT
- HOBT (heap or b-tree)
- TABLE (entire table, all data and indexes)
- FILE
- APPLICATION
- METADATA
- ALLOCATION_UNIT
- DATABASE
What is the least restrictive and the most restrictive type of lock?
The least restrictive type of lock is a shared lock. The most restrictive type of lock is a schema-modification.
