One of my friends finds this issue in the systems he is managing. In my locking and blocking series, I mentioned that when a record is being modified it always acquires IX lock on the object, IX lock on the page, and X lock on the record. In rare case, this may also hold IX lock on another object. This is cased by sp_rename.
Locking and Blocking
Locking and Blocking (12) – Counting Semaphore
Counting semaphores restricts number of concurrent sessions from accessing the same resource. It’s useful when code blocks perform well when number of concurrent users is under a certain number. For instance, SQL Server merge replication implements semaphores to limit number of subscribers to access publication at the same time.
Locking and Blocking (11) – Process Control and Customized Locking Hierarchies
Building lock based process control is to avoid conflicting processes from running at same time. A very common question DBAs undergo is that Task A can be launched when Task B(s) are not running. Many times I heard that “I will have to disable jobs, wait until they are finished, start another job, wait until it finishes, and then enable the jobs were disabled previously.”. The cycle of this process some time is just very few minutes but mostly it’s half to few hours which is occasionally scheduled in the midnight. The work like this is no longer to be fun to DBAs. It would be nice if something can be implemented in the process which it can detect, wait, and/or safely stop or interrupt running business operations, start its own process after all business processes stopped, and then resume the business processes automatically…This is possible in SQL Server. SQL Server application locks can even bring more value for such matters.
Locking and Blocking (10) – Binary Semaphore
Semaphore is an abstract for controlling access by multiple threads (sessions) to a common resource. Binary semaphore, it can be called Mutex, or Lock/Unlock, allows only one thread access a resource at a time. Back old days, in C++ programming, critical section object is used for this purpose. In C#, keyword lock(obj){code block}
and Monitor class implements binary semaphores. Once a object is locked by one thread, other threads who need to acquire the lock on the object will have to wait until the lock on the object is released by thread previously held. SQL Server is an multi-threading application. It uses semaphores internally to control the access to the record, for instance, while you are modify a record, an exclusive lock is placed on the record and released when modification finishes. This is a binary semaphore. In T-SQL, you can implement binary semaphore by locking row(s) and application locks.
Locking and Blocking (9) – Live Locks and Deadlocks
Deadlock can happen at latch level, memory, MARS, parallel query, etc. In this post, I am only going to talk about deadlocks at lock level. Resource A and B, when Session 1 locks Resource A and tries to get a lock on Resource B which has a incompatible lock held by Session2, Session 1 will wait until Session 2 releases locks on Resource B. This is called live lock. A live lock will be eventually resolved when the blocking session releases the lock or requesting lock gets timeout. If Session 2 requests a incompatible lock on Resource A at this moment, deadlock happens, because Session 1 will be waiting for the released lock on Resource B and Session 2 will be waiting for the released lock on resource A. Both sessions are deadly locked and blocked. I am going to give you few deadlock scenarios and solutions below.
Locking and Blocking (7) – Transaction Isolation Levels
In my first post of the series, I have mentioned that SQL Servers before version 2008 implemented transaction isolation levels by using locking only. In the newer version of SQL Server, this mechanism is NOT changed. Snapshots related isolation levels are the functionalities added to the existing framework. In general, transaction isolation levels controls the reading,SELECT
, behaviour of SQL Server. I am going to explain it from locking perspective.
Locking and Blocking (6) – NoLock
Nolock table hint tells SQL Server engine to not place locks to rows and/or pages while reading them. With this hint enabled, query will not be blocked by any row level modification. It’s equivalent to READUNCOMMITTED table hint in which it allows query return uncommitted data. Many people probably already know that schema stability lock will be applied to the table being access with this table hint, but it’s not always the trueth. In some cases, it will apply Shared lock to the HOBT — partition.
Locking and Blocking (5) – Lock Resources in Extended Events
Three columns resource_0, resource_1, and resource_2, returned from Extended Events which monitors the locking behavior,lock_acquired and lock_released, are always puzzling people. Actually, information of it already exposed in page 264, book Microsoft SQL Server 2008 Internals, wirtten by Kalen Delaney. In my first post of this series, I’ve also talked little bit about it. I decided to write a function to help people translate them into a human readable form. This function now can recognize lock resource for Object, HOBT, Page, Extent, RID, and KEY. for others, such as DATABASE and FILE, the formats are very straight forward, I did not include them in the function.
Locking and Blocking (4) – How It Works
Behind locking, there must be a queuing mechenism implemented in the base framework. In C#, if multiple threads are running the same code below, when the first thread successfully acquired a lock on syncObject and continues the code within the brace, the second thread will be waiting for the lock on syncObj to be released. If the third thread comes in, it will wait until the second thread finishes the execution of the code in the brace bracket.
lock(syncObj) { //You code here }
Whenever you use locks like the code above, a FIFO (first-in-first-out) queue is implicitly created internally; every requests will be queued until the previous request dequeued and finishes its work. SQL Server locking system has such concept as well but it’s more sophisticated. In my this example, lock(){}
is equivalent to applying exclusive lock to a resource(syncObj above) in SQL Server.
Locking and Blocking (3) – Update and Exclusive Locks
Update and Exclusive locks mostly are used for Data Manipulating Language delete and update. Fisrt of all, let’s have a quick glance below:
use tempdb set nocount on if OBJECT_ID('abc') is not null drop table abc create table abc(id int) insert into abc values(1),(2),(3) begin transaction delete abc where id = 1 exec sp_lock @@spid rollback transaction /* spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- -------------------------------- -------- ------ 52 2 1365579903 0 RID 1:172:0 X GRANT 52 2 1365579903 0 PAG 1:172 IX GRANT 52 2 1365579903 0 TAB IX GRANT 52 1 439672614 0 TAB IS GRANT */
From result, you can ignore the line 17, the IS lock on that line on master database (dbid = 1) is look up values in the master database, like RID, PAG, TAB. While data being deleted, there’s on IX (Intent Exclusive) lock granted to the table (TAB), IX lock on the page where the records located, and X (eXclusive) lock on the row. After transaction is rolledback or committed, the lock on those resources will be release. Seem like there is nothing to the Update lock. That’s true if you use this tool or sys.dm_trans_locks to check the locking behaviour. Your will see more locks by using below.