SQL SERVER – How Can Deadlock Happen on Same Table?

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).

SQL SERVER - How Can Deadlock Happen on Same Table? silence

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 1Session 2
1Create our table
2BeginTransactionInsert row1
3BeginTransactionInsert Row2
4Insert row2Will get blocked
5Insert Row 1Will get blocked and become deadlock
6Deadlock 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)

Previous Post
Interview Question of the Week #005 – List All the Tables with Name Containing Specific String
Next Post
SQL SERVER – Script – When/Who did Auto Grow for the Database?

Related Posts

No results found.

19 Comments. Leave new

  • Phaneendra Babu Subnivis
    February 2, 2015 10:24 am

    Interesting point. Thanks for sharing.

    Reply
  • 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.

    Reply
    • 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.

      Reply
  • 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.

    Reply
  • It’s the most common kind of deadlock I see.

    Reply
  • Was this ever solved?

    I am having the same issue.

    Reply
    • You need to look at deadlock graph and find if you have any index which is not useful.

      Reply
  • Stefano Bozzoni
    May 4, 2016 6:01 pm

    I remember that in ORACLE concurrent inserts doesn’t block the table, even if in transaction, why does SQL Server Act that way?

    Reply
  • Rajesh Thakur
    May 26, 2016 8:23 am

    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.

    Reply
  • Chakri Lekkala
    May 27, 2016 10:45 pm

    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

    Reply
  • 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

    Reply
    • 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?

      Reply
  • 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)

    Reply
  • 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.

    Reply
  • You should also tell how to avoid these deadlocks.

    Reply
  • What is the solution here? We cannot drop index, and at the same time we will need to insert from multiple transactions.

    Reply
  • 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.

    Reply

Leave a Reply