SQL Server deadlocks

Member 11154151

I'm dealing with a strange (for me) situation in SQL Server. I'm having a process that locks the same table twice. If the same time that the first process is locking the table a second process tries to lock the same table a deadlock happens immediately. Is this normal? I wrote a script and run it twice from different connections. Here is the script:

begin tran myTran
create table anyTable (anyColumn varchar(10), primary key (anyColumn))

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
update anyTable set anyColumn = anyColumn --remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

drop table anyTable

commit tran myTran

Kornfeld Eliyahu Peter

Are you executing this exact code twice?

Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.

Member 11154151

Not this exact code twice. But similar yes. The only difference is the three lines on top which are commented for the process I would like to be the deadlock victim. These lines: print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim) update anyTable set anyColumn = anyColumn --remove for the second process (victim) print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim) The only alternative that I can think of is to check if the table is locked by my SPID before try to lock it for the second time. Thanks for your posts. If can anyone come with any idea I would appreciate it.

Kornfeld Eliyahu Peter

It was much better two post the code you have problem with - including the way you are running it... The reason I say this, is that I took your code and immediately saw that it can not be run twice as every instance will try to create a table, and that can be a serious problem... Other thing is the transaction - do not create a transaction if you need not (and from the sample you had here you need not, as you are updating the very same table twice)...

Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.

Member 11154151

You are right. The second process should run something like this: begin tran myTran declare @exec datetime, @i int, @m varchar(2), @x int set @exec = getdate() set @m = substring(convert(varchar, @exec, 120), 15, 2) set @x = convert(int, @m) + 1 while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0 begin print 'Waiting. Time = ' + convert(varchar(40), @exec) set @exec = getdate() end print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate()) update anyTable set anyColumn = anyColumn print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate()) commit tran myTran But the reason that I lock the same table twice is much more complicated that you think. This is not a simple process. The two locks are coming from different updates. I have just tried to make it simple. Thanks any way.

Member 11154151

The situation is much more complex than I thougth it was. The deadlock happened between the primary key of the table and the index of the same table. I really do not know why. A workaround that I found is to use SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK) insted of UPDATE EDEFADDERS SET ID = ID. Here is the scripts that you need to reproduced it. (I believe complete and correct!!!) Script to create the table:

CREATE TABLE EDEFADDERS(
id varchar(32) NOT NULL,
adderkind int NOT NULL,
description varchar(30) NOT NULL,
formula text NOT NULL,
transkind int NOT NULL,
addedset varchar(255) NULL,
removedset varchar(255) NULL,
adderdecs int NOT NULL,
PRIMARY KEY CLUSTERED (id ASC)
)

CREATE NONCLUSTERED INDEX EDEFADDERS_I1 ON EDEFADDERS (adderkind ASC)

INSERT INTO EDEFADDERS VALUES ('088162994983574692C347CE326582BD', 0, 'Πληρ. Μετρητοίς', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('0DCE2805DF7E4B4C875649128C16A14C', 0, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('1072EB880FABF8459B99EC38058B6CA1', 2, 'Εισπράξεις Γραμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('142F006F40F0DA4C80ACEA477E2B5662', 0, 'Εκπτώσεις Χονδρ.', '"Αξία Εκπτωσης" + "Πιστωτικό Εκπτωσης" * "Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('1D4E3250AFD52343A1680A4387BA1490', 0, 'Εισπρ. Μετρητοίς', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('22A60A4EC35EC14E8AD82B97A5906572', 2, 'Εισπράξεις Επιτ.', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('24B3B31AD780124B888C5D935966F679', 3, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 2, 49, , 0)
INSERT INTO EDEFADDERS VALUES ('273FED73284E5E4689BAA7F913173086', 0, 'Εκπτ. Ειδών Χ.', '"Αξία Εκπτωσης Είδους"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('3034E6BD63AF114A8DEB61B442C8D873', 0, 'Πωλήσεις Π.Υ. Λ.', '"Καθαρή Αξία"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('349B9E68AE2D104689B6E2E67C618A8E', 0, 'Εκπτώσεις Π.Υ.Χ.', '"Αξία Εκπτωσης"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('3708771B165CD74E81323BCBDE14CEC7', 2, 'Εισπράξεις Μετρ.',

Richard Deeming

Member 11154151 wrote:

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0 begin print 'Waiting. Time = ' + convert(varchar(40), @exec) set @exec = getdate() end

Rather than sitting in a tight loop waiting for a particular time, it would be better to use WAITFOR[^]. That will let SQL free up the processor to do other things, rather than constantly querying the current time and shouting "are we nearly there yet?" until the target time arrives.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Member 11154151

The answer to my question was given to me by someone from Microsoft. The reason why this deadlock happens is that the optimiser is choosing the second index for this SQL: Update anyTable set anyColumn = anyColumn mak So, it puts a lock in order to do the update and then SQL server is putting an exclusive lock to the primary key for the update and release the first lock of the second index. At this time the second process is putting a lock for the second index and waits for the 1st process to finish in order to get an exclusive lock for the primary key. The 1st process now is trying to reupdate the same table and to put a lock for the second index but this is already locked by the 2nd process (who waits the 1st). So, the deadlock is inevitable.