CODEWITHSUNDEEP
sqlsql-server-2008
TBohnen.jnr
TBohnen.jnr

Reputation: 5119

Deadlock on query that is executed simultaneously

I've got a stored procedure that does the following (Simplified):

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 

DECLARE @intNo int
SET @intNo = (SELECT MAX(intNo) + 1 FROM tbl)
INSERT INTO tbl(intNo)
Values (@intNo)

SELECT intNo  
      FROM tbl
      WHERE (intBatchNumber = @intNo - 1)  

COMMIT TRANSACTION

My issue is that when two or more users execute this at the same time I am getting deadlocks. Now as I understand it the moment I do my first select in the proc this should create a lock in tbl. If the second procedure is then called while the first procedure is still executing it should wait for it to complete right?

At the moment this is causing a deadlock, any ideas?

Upvotes: 3

Views: 504

Answers (2)

gbn
gbn

Reputation: 432180

Make it simpler so you have one statement and no transaction

--BEGIN TRANSACTION  not needed

INSERT INTO tbl(intNo)
OUTPUT INSERTED.intNo
SELECT MAX(intNo) + 1 FROM tbl WITH (TABLOCK)

--COMMIT TRANSACTION not needed

Although, why aren't you using IDENTITY...?

Upvotes: 1

Andomar
Andomar

Reputation: 238048

The insert query requires a different lock than the select. The lock for select blocks a second insert, but it does not block a second select. So both queries can start with the select, but they both block on the other's insert.

You can solve this by asking the first query to lock the entire table:

SET @intNo = (SELECT MAX(intNo) + 1 FROM tbl with (tablockx))
                                             ^^^^^^^^^^^^^^^

This will make the second transaction's select wait for the complete first transaction to finish.

Upvotes: 4

Related Questions