Interesting TRUE Story
As part of learning I get inspiration from the activities we do daily and see how it can be mapped to SQL Server. This in a way many say is an obsession for the subject I love – SQL Server. But I think it is part of how we laterally think. In the recent past, I have been following a number of sports and this quest is fundamental to make sure when my little girl asks me, I am ready with the answers. Till date it has been a fun ride and I have nothing to complain. Recently, I was watching a live World Chess Championship Tournament and it was intriguing. There was silence all around and I was intensely watching the match. My little girl was amused with this silence and let me be myself for close to 15 mins. She then tried to join me where in the match was about to end. She was watching me and TV with curiosity to why I was concentrating so much. The match actually ended in a draw and people were clapping hands and the players walked away. Seeing this, my daughter asked me to why there was no shouting or cheering from the audience (sorry for the football/cricket fever that was running for a while).
Then she asked an interesting question, why did the players walk away? They didn’t talk, fight or do anything but they just walked away, why? I told it was a stalemate game and none of the players won. So they decided to end it this way gracefully. That night I was thinking to how such simple things can get the curiosity of our children. It was a dead game and in SQL Server terms I would say a deadlock.
Back to Reality
This incident was in my mind for a while and I was planning to share it when the appropriate time comes. Unrelated, I got a simple enquiry from one of my blog readers. Can a table deadlock itself? Is that possible? Moreover, we are just doing inserts into this table as it is getting deadlocked, how is it possible? They were interestingly getting something like this in their environment and wanted to know how that is possible.
A web search can give you deadlock information generally where there are two objects that are in cyclic deadlock with each other, rarely do we get to see a single table deadlock. So I thought to write this blog to show how we can still get deadlocks using a single table. The sequence for this script would look like:
| Session 1 | Session 2 | |
| 1 | Create our table | |
| 2 | BeginTransactionInsert row1 | |
| 3 | BeginTransactionInsert Row2 | |
| 4 | Insert row2Will get blocked | |
| 5 | Insert Row 1Will get blocked and become deadlock | |
| 6 | Deadlock encountered, one of the session terminates | |
Now that we got the flow ready, let us put the same in code. Below is the script for your reference.
Script for Scenario
-- Session 1: Step 1. Create our table
USE tempdb
GO
CREATE TABLE deadlocks
(ID INT, Name CHAR(20), Company CHAR(50));
GO
CREATE UNIQUE CLUSTERED INDEX deadlock_ind ON deadlocks (ID, Name)
GO
-- ----------------------
-- Session 1: Step 2. Insert row 1
BEGIN TRAN
INSERT INTO deadlocks
VALUES (1, 'Pinal', 'SQLAuth')
-- ----------------------
-- Session 2: Step 3. Insert Row 2
BEGIN TRAN
INSERT INTO deadlocks
VALUES (10, 'SQLAuth', 'Blog')
-- ----------------------
-- Session 1: Step 4. Insert row 2
INSERT INTO deadlocks
VALUES (10, 'SQLAuth', 'Blog')
-- ----------------------
-- Session 2: Step 5. Insert Row 1
INSERT INTO deadlocks
VALUES (1, 'Pinal', 'SQLAuth')
Please take a note of the session above. We will alternate the way in which the insert statements are built. Once these steps are followed, the famous deadlock error message of 1205 is shown:
Msg 1205, Level 13, State 47, Line 7
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In the above example, we are just using insert statements to generate a deadlock situation. How many of you have ever encountered such deadlocks in your environments? Do let me know.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
19 Comments. Leave new
Interesting point. Thanks for sharing.
My observations and conclusions:
1. Default transaction isolation level for SQL server is Read Commited
2. In given example row level lock is playing important role.
3. If observed carefully 2nd Insert in first session transaction is tring to read record inserted by 2nd session transaction(due to uniqe clustered index created on table) and vice a versa and causing dead lock.
same situation can be observe if we drop index on table and sessions of transansactions are like below
/*Query 1
Session 1*/
BEGIN TRAN
INSERT INTO deadlocks
VALUES (1, ‘Pinal’, ‘SQLAuth’)
/*Query 2
Session 2 */
BEGIN TRAN
INSERT INTO deadlocks
VALUES (10, ‘SQLAuth’, ‘Blog’)
/*Query 3
session 1*/
Update deadlocks
set company=’SQLAuth’
where id=10
/*Query 4
Session 2*/
Update deadlocks
set company=’Blog’
where id=1
please correct me if I am going wrong any where.
Thank you.
You are correct. It is easy to show deadlock using update. But in the scenario, I was asked if deadlocks can happen only with Insert statements. So this was just an example for the same. What you have written is absolutely right too. Thanks for the note.
Thanks. Very useful information. Can you please elaborate, how we can avoid these scenarios in a single table insert. Eg. Going for UNIQUEIDENTIFIERS will solve this problem. going for sequences etc.
Sure, will try to blog them next. Thanks for the note.
It’s the most common kind of deadlock I see.
Thanks for sharing your thoughts Cody.
Was this ever solved?
I am having the same issue.
You need to look at deadlock graph and find if you have any index which is not useful.
I remember that in ORACLE concurrent inserts doesn’t block the table, even if in transaction, why does SQL Server Act that way?
Hi pinal,
I am getting exactly the same issue. Just inserting a single statement from multiple machines and getting deadlock. I don’t have any index on my table even I have removed primary key too. But still getting deadlocks.
I am using SQL server 2014 and inserting record by using a c# api.
Pls help.
Hi Pinal,
Thank you for your post, I have also the same situation which is explained by Deepti Bandre. How can I over come from the situation. Can I ask my application team to looking into the multiple calls they are using?
Please advice me
Thank you
Chakravarthy
Hi Pinal,
Can a insert/update with select at a same time on same table can cause a dead lock or any performance issue on the transactions ?
Thank you
Rahul Dhumale
This kind of deadlock is actually what brought me to this article. We are selecting and inserting in a stored procedure, and if it’s run in multiple sessions concurrently, we see the deadlock on a single table. Even when setting transaction isolation to READ UNCOMMITTED and using NOLOCK hint on all the SELECTs, we still see the deadlock. Is there anything that can be done in this case?
Hi Dave,
This is my query
insert into SA_PDICheckList (DealerCode,LocalPDIChkLstNo,FK_PDINum_vc,DNLocalCode_VC,SerialNo_VC,id,ChckPoint,Category,Recommendation,StatGuideLn,CreatedBy_VC)
values(?,(select ISNULL(MAX(LocalPDIChkLstNo)+1,1) from SA_PDICheckList),(select ISNULL(MAX(LocalPDIChkLstNo)+1,1) from SA_PDICheckList),?,?,?,?,?,?,?,?)
and giving error
2017-12-30 14:25:42,455 +0530 [default task-207] ERROR (AbstractLoggingWriter.java:71) – com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 207) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2017-12-30 14:25:42,455 +0530 [default task-207] ERROR (AbstractLoggingWriter.java:71) – at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
2017-12-30 14:25:42,455 +0530 [default task-207] ERROR (AbstractLoggingWriter.java:71) – at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
2017-12-30 14:25:42,455 +0530 [default task-207] ERROR (AbstractLoggingWriter.java:71) – at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:1277)
2017-12-30 14:25:42,455 +0530 [default task-207] ERROR (AbstractLoggingWriter.java:71) – at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:1191)
2017-12-30 14:25:42,455 +0530 [default task-207] ERROR (AbstractLoggingWriter.java:71) – at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4615)
I have Simple SP to Insert Single Record from Application.
When I executes it manually Response time is within 1 Sec.
But When we try to Test with multiple users Same Time it’s Getting Blocked/Deadlock it Self.(we try for 10 users only)
Table Has around 1 billions of rows without partitioning.
and it also have some Indexes
It’s Working Fine with Applock and tablock but responce time is around 30 sec. but My requirement response time is less 2 Sec.
OKTransaction (Process ID 178) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
You should also tell how to avoid these deadlocks.
What is the solution here? We cannot drop index, and at the same time we will need to insert from multiple transactions.
THIS IS THE STOREDPROCEDURE
DECLARE @GameID INT,@BET INT,@BETOPTIONID VARCHAR(10)
SET @GameID =1000
SET @BET =10
SET @BETOPTIONID =1020
DECLARE @STATUS INT,@BETCOUNT INT,@BETOPTIONID INT,@MAXBETCOUNT INT
BEGIN TRANS
SELECT @STATUS=STATUS FROM GAMESAVAIL WHERE GAMEID=@GameID
–CHECK FOR STATUS OF THE GAME
SELECT @BETCOUNT=BETCOUNT FROM BETOPTION WITH UPDLOCK WHERE BETOPTIONID=@BETOPTIONID
IF @BETCOUNT>=@MAXBETCOUNT
ROLLBACK
RETURN
UPDATE BETOPTION SET BETCOUNT=BETCOUNT+@BET WHERE BETOPTIONID=@BETOPTIONID
COMMIT TRANS
WILL THIS TRANSACTION HANDLE DEADLOCKS
NON CLUSTERED INDEX ON BETOPTIONID
THIS CODE WAS WORKING PERFECLTY IN SQLSERVER 2005 BUT NEXT VERSION FROM 2008 THIS CODE IS GIVING LOTS OF DEADLOCK
IN SQLSERVER 2005 NOT EVEN A SINGLE DEADLOCK HAPPEND.
THAT TOO THE DATABASE WAS A LOTTERY APPLICATION. SO MANY REQUESTING WILL THERE IN A SINLGE ROW
THANK YOU FOR ANY HELP YOU CAN OFFER.