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.
Senario One: Typical DeadLock
Followed the instruction int the comment text, you will get a dead lock. Session 1 hold X lock on id 100 and trying to get x lock on id 400 while session2 is holding X lock on id 400 and trying to get X lock on 100. Both sessions block each other.
use tempdb if OBJECT_ID('SimpleTable') is not null drop table SimpleTable create table SimpleTable(id int primary key, text1 char(2800)) go insert into SimpleTable values(100, replicate('a',2800)), (200, replicate('b',2800)), (300, replicate('c',2800)), (400, replicate('d',2800)) go --Open session 1 and run begin transaction update SimpleTable set text1='100' where id = 100 go --open sessio 2 and run begin transaction update SimpleTable set text1='400' where id = 400 go --Go back to session 1 and run, update SimpleTable set text1 = '400' where id = 400 --Go to session 2 and run update SimpleTable set text1 = '100' where id = 100
This is a data access(or lock) ordering issue which is common but some time hard to resolve. The code below will generate deadlock when there is no snapshot option enabled on the database.
-- Session 1 update a set a.text1 = b.text1 from SimpleTable1 a inner join SimpleTable2 b on a.id = b.id--Session --Session 2 update a set a.text1 = b.text1 from SimpleTable1 b inner join SimpleTable2 a on a.id = b.id
It’s necessary to avoid pattern like below
--Session 1 begin transaction exec proc1 exec proc2 commit --Sesssion 2 begin transaction exec proc2 exec proc1 commit
Senario Two: DeadLock Because of Foreign Key
In previous examples, you may say I don’t have any deadlock issues after turning Read Committed Sanpshot on. But in this case, you will not be so lucky since the locking behaviors will not be affected by the status of the snapshot at all — that is the foreign key verification. Whenever a referecing key is modified in the child table, SQL Server will check existance of the new key value in the parent table with Shared Lock. Whenever a referenced key value in the parent table is changed, SQL Server will check if there is any values in the child table referencing the old value in the parent table using Shared Lock. See the example below
create table Customer(CustomerID int primary key, Discount int default(0)) create table Invoice(InvoiceID int identity(1,1)primary key, CustomerID int references Customer(CustomerID), Amount numeric(9, 2)) create index CustomerID on Invoice(CustomerID) go insert into Customer (CustomerID) values (1) insert into Customer (CustomerID) values (2) insert into Customer (CustomerID) values (3) insert into Customer (CustomerID) values (4) insert into Customer (CustomerID) values (5) insert into Invoice (CustomerID, Amount) values (1, 1000) insert into Invoice (CustomerID, Amount) values (2, 2000) insert into Invoice (CustomerID, Amount) values (3, 3000) insert into Invoice (CustomerID, Amount) values (4, 4000) insert into Invoice (CustomerID, Amount) values (5, 5000) go --Session 1 begin tran update Customer set Discount = 30 where CustomerID = 1 go --Session 2 update Invoice set CustomerID = 1 where InvoiceID = 3 go --Session 1 update Invoice set Amount = Amount - 30 where CustomerID = 1 go
This is closer to real business case. One clerk gives a customer $30 discount and also deducts all invoice amount by the discount in one transaction and another clerk corrects an invoice in between. In this case, you will have to consider disabling the foreign key or remove the transaction control for clerk one.
Senario Three: Indexed View
create table Customer(CustomerID int primary key, CustomerName varchar(30)) create table Invoice(InvoiceID int primary key, CustomerID int, Amount int) go create view vCustomerInvoice with schemabinding as select c.CustomerID, c.CustomerName, i.InvoiceID, i.Amount from dbo.Invoice i inner join dbo.Customer c ON i.CustomerID = c.CustomerID go create unique clustered index PK_vCustomerInvoice on dbo.vCustomerInvoice(InvoiceID) go insert into Customer values(1, 'C1') insert into Customer values(2, 'C1') insert into Customer values(3, 'C1') insert into Customer values(4, 'C1') insert into Customer values(5, 'C1') insert into Invoice values(1,1,1) insert into Invoice values(2,2,2) insert into Invoice values(3,3,3) insert into Invoice values(4,4,4) insert into Invoice values(5,5,5) go --Session 1 begin tran update Invoice set Amount = Amount + 1 where InvoiceID = 1 go --Session 2 update Customer set CustomerName = 'Customer Change1' where CustomerID = 2 go --Session 1 update Invoice set Amount = Amount + 1 where InvoiceID = 2
If you are not aware of the existance of an index view behind those 2 tables, you will be really confused since those 2 sessions are operating on different tables. Here is why:
1. First command in Session 1 will place exclusive lock on Invoice table (InvoiceID = 1).T
2. The row in the index view then gets invalidated. This invalidation will place a shared lock on the linking row (which is customer 1)in customer table until the transaction complete.
3. Session 2 updates CustomerID = 2 in customer table which place exclusive lock on customer table.
4. The row(s) where CustomerID = 2 in the index view should be invalidated. The linking row in the Invoice table for CustomerID = 2 should have a Shared lock placed until transaction in Session 2 complete. Since there is no index Invoice(CustomerID) defined, SQL Server has to perform a scan on Invoice table. Since Session 1 is holding exclusive lock on CustomerID = 1 in Customer table, Session2 will have to wait until Session 1′s lock released.
5. Now Session 1 Updates Invoice table where InvoiceID = 2, this will require a Shared lock on Customer’s CustomerID = 2. However, An eXclusive lock has been hold by Session 2 on CustomerID = 2.
6. Then deadlock.
Solution for this is to create index Invoice(CustomerID). From here, we can draw a conclusion that you should have index on joining keys on the base tables of the indexed view.
Create index customer on Invoice(CustomerID)
Sometime deadlock is hard to resolve, see the picture on your right. I got this picture from Certified Master community. You should always try to avoid such situation from happening. Indexed views, triggers, foreign keys, lock hints, execution orders…, they can all possibly be the cause of deadlock. Shall we not using them at all? No. You need full an understanding of SQL Server locking behaviors. Tool posted previously and the interpretion of the lock resource can always help you to ensure what’s going on behind. Many articles on internet tell you how to avoid deadlocks. After reading this series, can you tell why they suggest that? What’s the limitation of each suggestion? Which one is incorrect?
Locks not only guarantee the data consistency but also help process data flow control. From next post, I will let you know how. Thanks for visiting and enjoy the reading.