All posts in category SQLServerPedia Syndication

Deadlock due to Implicit Conversion

In this post I am going to demonstrate how Implicit conversion can lead to deadlock errors and how to troubleshoot and fix them.

Setup

For this demo, we will use a standard queue reader pattern, which reads a single row from a table, perform some operations based on it and finally update the row.

First we need to create the table and populate it.

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.icdl(SlNo VARCHAR(10) primary key clustered,
status varchar(12),
filler char(100))
GO

SET NOCOUNT ON

DECLARE @i int
SET @i = 1
WHILE @i < 1000
BEGIN
INSERT INTO dbo.icdl(SlNo, status,filler)
VALUES(RIGHT(REPLICATE(‘0′,10)+ CAST(@i AS VARCHAR(10)),10),’Pending’, ‘Abcd’)
SET @i = @i+1
END

Now run the below query simultaneously in 2 sessions. Couple of things to note about the script is that, it uses TOP 1 to get a single row. It also uses the hints (ROWLOCK, UPDLOCK,READPAST) which ensures that the script will not be blocked by another instance and it places a U lock at the key level.  Also the final UPDATE is performed using an equality predicate on the primary key.

DECLARE @SlNo NVARCHAR(10)

BEGIN TRAN

SELECT TOP 1 @SlNo = SlNo
FROM dbo.icdl WITH(UPDLOCK, ROWLOCK, READPAST)
WHERE Status = ‘Pending’
ORDER By SlNo

SELECT @slNo

WAITFOR DELAY ’00:00:05′

UPDATE dbo.icdl
SET Status = ‘Processed’
WHERE SlNo = @slNo

COMMIT TRAN

In five seconds, one of the session will fail with a deadlock error.

Analyze

As usual let’s build the deadlock table to analyze what is happening here.

Process Resource Mode State Command
process43b34c8 KEY: 26:72057594044678144 (fff536a51673) U WAIT UPDATE
process43b34c8 KEY: 26:72057594044678144 (c3d783fe2889) X GRANT UPDATE
process406f921 KEY: 26:72057594044678144 (c3d783fe2889) U WAIT UPDATE
process406f921 KEY: 26:72057594044678144 (fff536a51673) U GRANT SELECT

This tells us that the contention is on two individual keys of the same HOBT. Lets see which rows these keys belong to.

SELECT %%lockres%% AS LockResource, Slno
FROM dbo.icdl
WHERE %%lockres%% IN(‘(fff536a51673)’,'(c3d783fe2889)’)

LockResource Slno
(c3d783fe2889) 0000000001
(fff536a51673) 0000000002

Our script is designed to work on individual rows alone. We expect the SELECT statement to acquire a U lock on the row and the UPDATE statement to convert the U lock to X lock and finish the processing without requiring locks on any other rows.

But what actually happening here is Process 1 is waiting for an UPDATE lock on the second row (0000000002) and holding an EXCLUSIVE lock the first row (0000000001) . Whereas Process1 is holding an UPDATE lock on the second row (0000000002) , which is acquired during the SELECT phase, and is waiting for UPDATE lock on the first row (0000000001).

At this stage, we will need to look at the execution plan to see why the UPDATE command requires locks on rows belonging to another key value.

image

The execution plan reveals that there is an IMPLICIT CONVERSION on the Slno column and as a result, we are getting a Clustered Index scan even though we are using an equality predicate on the  Clustered index key column. If you look at the script, you can see that the column is defined as VARCHAR(10) whereas the variable is defined as NVARCHAR(10). As per the data type precedence rules,  when we compare a VARCHAR value to an NVARCHAR value, the VARCHAR value will be implicitly converted to NVARCHAR.

Fix

Once we know the real issue, its an easy fix. You can just change the data type of the variable to match the type of the table. If you are joining two tables with different data types, and you cannot change the type, it will be good idea to explicitly convert the value in the smaller table to match the type of the larger table to ensure efficient index utilization.

Note: The Queue reader pattern used in this post is also vulnerable to this deadlock scenario.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please leave a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on August 10, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , ,

Posted by thomasroji on August 10, 2012

https://readuncommitted.com/2012/08/10/deadlock-due-to-implicit-conversion/

DEADLOCK on SELECT due to UNORDERED PREFETCH

In a previous post, I have provided links to multiple articles that demonstrate how a SELECT statement involving a key lookup running under READ COMMITTED isolation level can deadlock with another session running an UPDATE statement.

In this post we will discuss about another case where a SELECT statement running under READ COMMITTED isolation level  can deadlock with another session due to the locking behavior.

Setup

Run the below script to create the test table and populate it.

IF DB_ID(‘DeadLockTest’) IS NULL

CREATE Database DeadLockTest

GO

USE DeadLockTest

GO

create table dbo.rcdl (id int identity(1,1) primary key clustered, id2 int, filler char(1000) default ‘abc’)

GO

set nocount on

declare @i int

set @i = 0

while @i < 1000

begin

insert dbo.rcdl(id2) values ( @i)

set @i = @i + 1

end

Go

create index ix_rcdl on dbo.rcdl(id2)

go

Now start another session (Session 1) and run the below command.

BEGIN TRAN

UPDATE dbo.rcdl

SET filler = ‘xyz’,id2=id

WHERE id = 24

WAITFOR DELAY ’00:00:05′

UPDATE dbo.rcdl

SET filler = ‘xyz’,id2=id

WHERE id = 1

ROLLBACK TRAN

Start one more session (session 2) and run the below query

SELECT filler FROM dbo.rcdl WHERE id2 < 25

In five seconds, the SELECT statement running in session 2 will fail with the below error.

Msg 1205, Level 13, State 51, Line 1

Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analyze

Let’s go ahead and populate the deadlock analysis table from the deadlock trace.

Process Resource Mode State Command
process593048 KEY: 26:72057594042056704 (1edefaa29392) S WAIT SELECT
process593048 KEY: 26:72057594042056704 (bb0d06c12baa) S GRANT SELECT
process13ee08 KEY: 26:72057594042056704 (bb0d06c12baa) X GRANT UPDATE
process13ee08 KEY: 26:72057594042056704 (1edefaa29392) X WAIT UPDATE

You can see that the update command is holding an X lock on one row and waiting for another X lock. The SELECT statement is also holding a shared (S) lock and waiting for another shared lock. But why the SELECT statement is holding a shared lock under READ COMMITTED isolation level? As stated in a previous post, SELECT statements should only hold the shared lock for the duration of processing the row. For a visual representation of the cocept of lock duration under different isolation levels, see Micheal J Swart’s (b |t) post “Visualizing Transaction Isolations For SQL Server”

But there are few exceptions to this rule. In certain cases, SQL server will keep the shared locks until the end of the transaction to ensure consistency. One of them involves “UNORDERED PREFETCH”, which happens to be the case here. If you look at the execution plan xml of the SELECT statement, you can see that our plan uses a Nested Loop Join with UNORDERED PREFETCH. Craig Freedman blogged about the scenarios where the shared locks are held until the end of the transaction here. He also wrote about UNORDERED PREFETCH in detail here.

Fix

You can use any of the below methods to resolve this issue.

  1. Combine the UPDATE statements to a single UPDATE statement.
  2. Cover the index by including the filler column
  3. Acquire non-compatible locks using a locking hint.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on July 13, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , , ,

Posted by thomasroji on July 13, 2012

https://readuncommitted.com/2012/07/13/deadlock-on-select-due-to-unordered-prefetch/

Multi-Victim Deadlocks

In some cases, to resolve a deadlock condition, the deadlock monitor may need to identify and rollback multiple transactions. The following demo shows one of such cases.

Setup

Run the below script to create the necessary tables

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO
CREATE TABLE TabA(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)
CREATE TABLE TabB(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)
CREATE TABLE TabC(id int IDENTITY(1,1), col1 char(100) DEFAULT ‘abc’)

INSERT INTO TabA DEFAULT VALUES
INSERT INTO TabB DEFAULT VALUES
INSERT INTO TabC DEFAULT VALUES

Open a new session(Session 1) and run the below script

SET DEADLOCK_PRIORITY HIGH
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabA A
INNER JOIN TabB B ON A.id = B.Id
INNER JOIN TabC C ON C.id = B.Id

Open another session(Session 2) and run the below script

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabA
UPDATE TabA SET Col1 = ‘xyz’

Open another session(Session 3) and run the below script

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabB
UPDATE TabB SET Col1 = ‘xyz’

Open one final session(Session 4) and run the below script

SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM TabC
UPDATE TabC SET Col1 = ‘xyz’

Session 2,3, and 4 will be blocked behind session 1 for the Exclusive lock. Now go back to Session 1 and execute the below script to force a deadlock condition.

SELECT * FROM TabA  A WITH(XLOCK)
INNER JOIN TabB B WITH(XLOCK)   ON A.id = B.Id
INNER JOIN TabC C WITH(XLOCK)  ON C.id = B.Id

ROLLBACK TRAN

In few seconds Sessions 2,3, and 4 will fail with the deadlock error.

Analyze

We are running the first session with a higher deadlock priority compared to other sessions. A deadlock victim is selected based the priority, log used, and  its ability to resolve all deadlock conditions. If we set the DEADLOCK PRIORITY to NORMAL for the first session, SQL server will choose it as the deadlock victim since choosing a single victim will resolve all deadlock conditions.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on July 7, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , ,

Posted by thomasroji on July 7, 2012

https://readuncommitted.com/2012/07/07/multi-victim-deadlocks/

Deadlocks Involving Lock Partitions

Lock Partitioning is a feature in SQL Server – which is automatically enabled for machines with more than 16 logical processors – aimed at reducing lock contention. To quote from the MSDN link on the topic,

For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources. Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. Only object locks can be partitioned.

When lock portioning is enabled NL, SCH-S, IS, IU, and IX lock modes are acquired on a single partition. All other lock modes must be acquired on all partitions.

One key thing to notice is that, when lock partitioning is enabled, Objects locks are acquired on lock partitions,not the object itself. For eg. If you run the below query on a server where lock partitioning is not enabled, you will get the below result. (irrelevant results omitted)

USE AdventureWorks
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN

SELECT * FROM Person.[Address]
WHERE AddressId = 1

SELECT * FROM Person.ContactType WITH(TABLOCKX)

SELECT OBJECT_NAME(resource_associated_entity_id) as objectName,resource_description, resource_lock_partition,request_mode  FROM sys.dm_tran_locks
WHERE resource_type = ‘OBJECT’

ROLLBACK TRAN

ObjectName resource_lock_partition request_mode
Address 0 IS
ContactType 0 X

If you run the same script on a machine on which lock partitioning is enabled, you will get the following results.

ObjectName resource_lock_partition request_mode
Address 5 IS
ContactType 15 X
ContactType 14 X
ContactType 13 X
ContactType 12 X
ContactType 11 X
ContactType 10 X
ContactType 9 X
ContactType 8 X
ContactType 7 X
ContactType 6 X
ContactType 5 X
ContactType 4 X
ContactType 3 X
ContactType 2 X
ContactType 1 X
ContactType 0 X

You can see that the IS lock is acquired only on one partition, whereas the X lock is acquired on all partitions. The actual number of partitions depend upon the number of available logical processors.

As the locking is done at the lock partition level, naturally these locks can participate in a deadlock and the resources listed under the resource list will be the lock partitions.

A deadlock can be identified as a lock portioning related deadlock from the below attributes.

1. The wait resource is “Object”

2. The wait resource identifier will be in the format “7:1797581442:12” where the last two digit indicates the lock partition.

3. In the resource list the value for sub resource is “FULL”

4. The resource list will be in the format “lockPartition=12”

5. If you execute the statement involved in the deadlock in a query and inspect resource_lock_partition column in sys.dm_tran_locks, you will see a non-zero value.

The following demo will illustrate a deadlock on lock partitions.

Setup

SQL server automatically enables lock partitioning on machines with more than 16 logical processors. If you don’t have a machine with 16 cores, you can use the (undocumented and unsupported) startup parameter –Pn.

I’ve used –P16 to simulate 16 processors.

When lock partitioning is enabled, you will see the following message in the SQL server log.

Lock partitioning is enabled.  This is an informational message only. No user action is required.

Once you verify that lock partitioning is enabled, run the below script to create the table for our test.

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.lpdl(id int identity(1,1) primary key clustered, filler char(100) default ‘abc’)
GO
SET NOCOUNT ON
INSERT INTO dbo.lpdl DEFAULT VALUES
GO 100

Now open a new session (session 1) and run the below query.

BEGIN TRAN

UPDATE dbo.lpdl
SET filler = ‘xyz’
WHERE id = 1

WAITFOR DELAY ’00:00:05′

SELECT * FROM dbo.lpdl WITH(TABLOCKX)

ROLLBACK TRAN

Open another session (Session 2) and run the below query.

BEGIN TRAN

UPDATE dbo.lpdl
SET filler = ‘xyz’
WHERE id = 1

WAITFOR DELAY ’00:00:05′

SELECT * FROM dbo.lpdl WITH(TABLOCKX)

ROLLBACK TRAN

One of the sessions will fail with the deadlock error. You have probably noticed that we are forcing a conversion deadlock here by first acquiring a compatible IX lock and the trying to acquire a non-compatible X lock on the same table.

Analyze

To analyze this deadlock we will go ahead and create our deadlock table.

Process Resource Mode State Command
process4ee8508 OBJECT: 26:242099903:2 X WAIT SELECT
process4ee8508 OBJECT: 26:242099903:0 X GRANT SELECT
process7c4988 OBJECT: 26:242099903:0 X WAIT SELECT
process7c4988 OBJECT: 26:242099903:2 IX GRANT UPDATE

The lock resource reported in the deadlock trace will be in the format OBJECT:DBID:OBJECTID:LOCKPARTITION. AS we can see here  process process7c4988 is holding an IX lock on partition 2. Since IX lock is acquired only on the local partition, we can assume that partition 2 is local to  process7c4988. This process now requires an X lock on partition 0. When a process requires any locks other than locks that can be acquired on the local partition, it must access the locks in a sequential order starting from partition 0. But since process process4ee8508 is already holding an X lock on partition 0, process process7c4988 is now blocked.

Meanwhile process4ee8508 is holding an X lock (converted its own IX lock on the partition to X lock) on partition 0 and it needs to acquire the X lock on the remaining 15 partitions in that order. When it reaches partition 2, it will be blocked since the other thread is holding an incompatible lock and a deadlock ensues.

Regardless of whether lock portioning was enabled or not, the above script will cause a deadlock.  But there are extremely rare scenarios where the lock partitioning feature will cause a deadlock whereas the normal locking behavior will only cause blocking. I will talk about that in the next post.

Fix

The key to avoid this kind of deadlock is to avoid object level locks. These kind of deadlocks are pretty rare. I’ve only found one reference of deadlock involving lock partitions in MSDN forums.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

1 Comment
by thomasroji on July 7, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , ,

Posted by thomasroji on July 7, 2012

https://readuncommitted.com/2012/07/07/deadlocks-involving-lock-partitions/

Deadlocks involving Select Into

In fact there is nothing special about a deadlock involving a SELCT INTO statement except for an interesting ROLLBACK behavior. Follow the below demo to find out.

Setup

Run the below script to create the necessary tables.

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.sidl1(id int IDENTITY(1,1) PRIMARY KEY, filler char(100) default ‘abc’)
GO
CREATE TABLE dbo.sidl2(id int IDENTITY(1,1) PRIMARY KEY, filler char(100) default ‘abc’)
GO

INSERT INTO dbo.sidl1 DEFAULT VALUES

INSERT INTO dbo.sidl2 DEFAULT VALUES

Now open a new session (session 1) and execute the below statements

SET DEADLOCK_PRIORITY HIGH
BEGIN TRAN
UPDATE  dbo.sidl2
SET Filler = ‘xyz’
WHERE id = 1

Open another session (session 2) and execute the below SELECT INTO statement.

SELECT a.*
INTO dbo.sidl3
FROM dbo.sidl1 a
INNER JOIN dbo.sidl2 b
ON a.id = B.id

This session will be blocked since Session one is already holding a non-compatible lock on table sidl2. Now go back to session 1 and execute the below statement to force a deadlock condition.

UPDATE  dbo.sidl1
SET Filler = ‘xyz’
WHERE id = 1

ROLLBACK TRAN

The Session running the SELECT INTO statement (Session 1) will fail with a deadlock error since we are running the first session with a higher deadlock priority.

If you try to rerun the SELECT INTO statement, you will get the below error.

Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘sidl3’ in the database.

Analyze

You can see that even though the transaction failed with a deadlock error, the table creation is not rolled back. This is due to the fact that when you execute a SELECT INTO statement, the table creation and population are treated as two different transactions. Read this post to learn more about inspecting this behavior.

Fix

Place the SELECT INTO in an explicit user transaction

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on July 6, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , , ,

Posted by thomasroji on July 6, 2012

https://readuncommitted.com/2012/07/06/deadlocks-involving-select-into/

Deadlock Due to Foreign Key Constraints

Another common deadlock pattern is a deadlock involving foreign key validations. This can happen in any of the following scenarios.

1. An Insert parent-Insert child operation deadlocking with another thread performing the same operation

2. A Delete child-Delete parent operation deadlocking with another thread performing the same operation

3. An Insert parent-Insert child operation deadlocking with a Delete parent-Delete child operation.

SQLCat team published a whitepaper on handling this type of deadlocks. The whitepaper discusses various techniques to avoid this kind of deadlocks. Follow this link to read the whitepaper Eliminating Deadlocks Caused By Foreign Keys with Large Transactions

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on July 6, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , ,

Posted by thomasroji on July 6, 2012

https://readuncommitted.com/2012/07/06/deadlock-due-to-foreign-key-constraints/

Conversion Deadlock

This post discusses another ‘classic’ deadlock.

Setup

This deadlock involves just one resource and two processes, each holding a compatible lock and trying for a non-compatible lock. We will use a more restrictive REPEATABLE READ ISOLATION level for this demo. You can see that unlike the cyclic deadlock example, both the sessions are running the same script here.

Run the below script to create the table and test data.

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

USE DeadLockTest
GO

CREATE TABLE dbo.codl(id int identity(1,1) primary key clustered, col1 char(10) default ‘abc’)

INSERT INTO dbo.codl DEFAULT VALUES

Now run the below script simultaneously in two sessions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM dbo.codl WHERE ID = 1
WAITFOR DELAY ’00:00:05′
UPDATE dbo.codl
SET col1 = ‘xyz’ WHERE id = 1
ROLLBACK TRAN

One of the sessions will fail with the deadlock error.

Analyze

If you prepare the deadlock analysis table from the trace data, it will look like the following.

Process Resource Mode State Command
process592e08 KEY: 26:72057594040745984 (8194443284a0) X WAIT UPDATE
process592e08 KEY: 26:72057594040745984 (8194443284a0) S GRANT SELECT
process579048 KEY: 26:72057594040745984 (8194443284a0) X WAIT UPDATE
process579048 KEY: 26:72057594040745984 (8194443284a0) S GRANT SELECT

You can see that only a single resource is involved(specifically a single key) and both the sessions are holding a compatible Shared lock on it and both are trying to convert it into an exclusive lock.

Fix

You won’t see this deadlock happening in READ COMMITTED isolation level since the lock duration of a shared lock in READ COMMITTED is only during the time the row is being processed, with some exceptions where the lock is held until the statement is completed. Obviously in REPEATABE READ isolation level, the locks are held until the end of the transaction. To fix this deadlock in REPEATABLE READ isolation level you will need to acquire a non-compatible lock in the beginning by using a UPDLOCK hint in the SELECT statement.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on July 6, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , ,

Posted by thomasroji on July 6, 2012

https://readuncommitted.com/2012/07/06/conversion-deadlock/

Cyclic deadlock

We will start with a classic deadlock scenario. The cyclic deadlocks are probably the most common and easily fixable deadlock.

Setup

This deadlock requires a minimum two resources and two process. For simplicity we will use two different tables.

Run the below script to setup the tables.

IF DB_ID(‘DeadLockTest’) IS NULL

CREATE Database DeadLockTest

GO

USE DeadLockTest

GO

CREATE TABLE Cdl1(id int identity(1,1), col1 char(10) default ‘abc’)

CREATE TABLE Cdl2(id int identity(1,1), col1 char(10) default ‘abc’)

GO

INSERT INTO Cdl1 DEFAULT VALUES

INSERT INTO Cdl2 DEFAULT VALUES

 

Now the run the below script in a new session (Session 1)

BEGIN TRAN

–update the first table

UPDATE Cdl1 SET col1= ‘xyz’

–Wait for a second for the other session to catchup

WAITFOR DELAY ’00:00:5′

–update the second table

UPDATE Cdl2 SET col1= ‘xyz’

ROLLBACK TRAN

And the below script in session 2

BEGIN TRAN

–update the first table

UPDATE Cdl2 SET col1= ‘xyz’

–Waitfor a second for the other session to catchup

WAITFOR DELAY ’00:00:5′

–update the second table

UPDATE Cdl1 SET col1= ‘xyz’

ROLLBACK TRAN

One of the session will fail with the error
Msg 1205, Level 13, State 45, Line 13

Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analyze

By analyzing the trace using your favorite method (I had enabled trace flag 1222), you will be able to arrive at the following table (The actual value of the process and resource will vary)

Process Resource Mode State Command Object
process579288 RID: 26:1:2671:0 U WAIT UPDATE DeadLockTest.dbo.Cdl1
process579288 RID: 26:1:2673:0 X GRANT UPDATE DeadLockTest.dbo.Cdl1
process13fdc8 RID: 26:1:2673:0 U WAIT UPDATE DeadLockTest.dbo.Cdl1
process13fdc8 RID: 26:1:2671:0 X GRANT UPDATE DeadLockTest.dbo.Cdl1

 

As you can see, both the processes are holding and waiting for incompatible locks. If you analyze the batch you can see that the tables are accessed in a different order.

Fix

This one is an easy fix. If you change the script to access the tables in the same order the deadlock will go away.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

1 Comment
by thomasroji on July 6, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged ,

Posted by thomasroji on July 6, 2012

https://readuncommitted.com/2012/07/06/cyclic-deadlock/

Deadlock due to Parallelism

 

Do you think a simple query like the below one can deadlock with another instance running the same query?

SELECT @id = id FROM dbo.midl WITH(UPDLOCK)
WHERE id2 = @id

Read on to find out.

Setup

Since this post is about deadlock due to parallelism, make sure that parallelism is enabled on your server.

Run the below script to create the table.

IF DB_ID(‘DeadLockTest’) IS NULL
CREATE Database DeadLockTest
GO

CREATE TABLE dbo.midl(id int identity(1,1) primary key clustered, id2 int, filler char(100) default ‘abc’ )

SET NOCOUNT ON

DECLARE @i int
SET @i = 1
WHILE @i < 60000
BEGIN
INSERT INTO dbo.midl DEFAULT VALUES
SET @i = @i+1
END

UPDATE dbo.midl SET id2 = id+1

Now run the following query in multiple sessions or use a tool like SQLQueryStress developed by Adam Machanic (b | t) which can be used for executing a query in multiple sessions.

SET NOCOUNT ON

DECLARE @id int
WHILE 1=1
BEGIN
SET @id = (RAND() * 1000) + 1
SELECT @id = id FROM dbo.midl WITH(UPDLOCK)
WHERE id2 = @id
END

In my tests with five concurrent sessions, three of them deadlocked under a minute.

The queries failed with the error

Msg 1205, Level 13, State 51, Line 6

Transaction (Process ID 60) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Analyze

If you had paid close attention to the error message, it says the deadlock happened on lock | communication buffer resources, which is a hint about the actual issue here. If you pay close attention to the resource list in the deadlock trace you can see the exchange operators appearing in the waiter list.

exchangeEvent id=Pipe8ac26500 WaitType=e_waitPipeGetRow nodeId=1

Deadlocks involving parallelism always involve thread or lock resources. So we can proceed in our conventional route and prepare our deadlock table.

I had simplified the below table to include only two process instead of all four participants in the deadlock.

Process Resource Mode State Command Object
process579948 KEY: 26:72057594039500800 (8194443284a0) U WAIT SELECT indexname=PK__midl__X
process579948 KEY: 26:72057594039500800 (1524a5133a9d) U GRANT SELECT indexname=PK__midl__X
process81e714c8 KEY: 26:72057594039500800 (1524a5133a9d) U WAIT SELECT indexname=PK__midl__X
process81e714c8 KEY: 26:72057594039500800 (8194443284a0) U GRANT SELECT indexname=PK__midl__X

 

This only tells us that the contention is on the PK. We need to see what keys are being actually involved. We can get this using the undocumented %%lockres%% function.

By running the below query,

SELECT id, %%lockres%% as LockResource
FROM dbo.midl
WHERE %%lockres%% IN(‘(8194443284a0)’,'(1524a5133a9d)’)

Id LockResource
1 (8194443284a0)
12709 (1524a5133a9d)

 

Now we can see that, process81e714c8 is holding a lock on row with id 1 and is waiting for a lock on id 12709. This is OK since it is a normal blocking scenario. But process579948 is holding a lock on row with id 12709 and waiting for a lock on id 1. If we look at the execution plan, we can see why.

 

image

When a task is executed using a parallel operator, the input is divided into a number of parallel buckets and each parallel thread works on its own subset of data.

Fix

This is again an easy fix. You have probably noticed that the id2 column does not have an index. If you create a non-clustered index on the id2 column, the deadlock will disappear.

PS: If you did not get the deadlock error, try adding few more thousands of rows and/or few more concurrent processes running the same query.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

 

Leave a comment
by thomasroji on July 6, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged , ,

Posted by thomasroji on July 6, 2012

https://readuncommitted.com/2012/07/06/deadlock-due-to-parallelism/

Introduction to deadlocks

Anybody with little experience with SQL Server must have seen few deadlock errors and probably understood why it happens. So a definition may not be necessary. Books online defines deadlock as “A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock”

SQL server automatically detects deadlock conditions and choose one of the processes as the victim and rollback that transaction based on the DEADLOCK PRIORITY and log used. There are few exceptions to this rule for victim selection and in some cases more than one process may selected as a victim. We will discuss about this in detail in a later post.

A deadlock can occur on Locks, Worker Threads, Memory, Parallel query resources and MARS resources. Our discussions in this series will be around handling Lock related deadlocks.

A deadlock is not a bug, except for very few cases which we will discuss later. A deadlock is a natural side effect of a busy system and are not completely avoidable. But with careful database design, proper indexing and by handling deadlock errors, the possibility of deadlocks and the impact of it can be greatly reduced. Some of the deadlock scenarios we are going to discuss in the coming posts require use of additional locking hints or the use of sp_getAppLock

Deadlock Properties

  • If a lock can be described as a unit of contention, a deadlock involves at least two units of contention.
  • At least two processes will be involved in a deadlock.
  • Each of the processes involved in a deadlock will be – directly or indirectly – holding a lock and will be waiting for another.
  • The Pending requests are not compatible with the existing locks.

Deadlock Categories

Cyclic deadlock vs Conversion Deadlocks : Deadlocks are generally categorized as cyclic deadlocks or conversion deadlocks.

A cyclic deadlock happens when two processes accesses two resource in the opposite order. A cyclic deadlock involves two or more resources.

Following is a slightly modified version of the deadlock example from BOL

  1. Transaction A acquires an exclusive lock on row 1.
  1. Transaction B acquires an exclusive lock on row 2.
  1. Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the exclusive lock it has on row 2.
  1. Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the exclusive lock it has on row 1.

A conversion deadlock happens when two processes holding compatible locks try to Convert the lock into non-compatible mode. A conversion deadlock involves one or more resources.

If we modify the above example to show a conversion deadlock , it will look like

  1. Transaction A acquires a shared lock on row 1.
  1. Transaction B acquires a shared lock on row 1.
  1. Transaction A now requests an exclusive lock on row1, and is blocked until transaction B finishes and releases the share lock it has on row 1.
  1. Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Multi – Statement vs Single Statement deadlocks : Another way I would like to classify deadlocks are based on how many statements are involved in the deadlock. Simple deadlock scenarios involve multiple statements from multiple batches holding and waiting for conflicting locks. These deadlocks are relatively easy to troubleshoot. There are cases where the exact same statement executed by different processes ending up in a deadlock. We will see examples of both the cases in the coming posts.

Deadlocks and Isolation Levels

Deadlocks can happen in any isolation levels. The probability of deadlocks are higher in more restrictive isolation levels like SERIALIZABLE compared to less restrictive isolation levels like READ UNCOMMITTED. In the context of locks, Isolation levels only controls the duration and mode of Shared locks. Regardless of the isolation level, exclusive locks and update locks will be held until the end of the transaction.

In the next post I’ll introduce you to the resources I use for troubleshooting deadlocks.

This post is part of the series SQL Server deadlocks and live locks – Common Patterns. If you have questions, please post a comment here or reach me at @sqlindian

Leave a comment
by thomasroji on July 6, 2012  •  Permalink
Posted in Deadlocks, Locks, SQLServerPedia Syndication
Tagged

Posted by thomasroji on July 6, 2012

https://readuncommitted.com/2012/07/06/introduction-to-deadlocks/

« Older Posts