Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working with SQL Server 2012 Express Edition and I getting this error:

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

I've added these two commands to show more information about it:

DBCC TRACEON(1204, -1)
DBCC TRACEON(1222, -1)

With these two commands I get this log. But I don't know how to find where the problem is.

Executing sp_lock I have identified that the problem is on table TRZIC.dbo.CODES and it has a PAG type.

On this table I'm updating rows and inserting new ones at the same time.

Any advice? How can I solve this deadlock?

share|improve this question
    
Can you show your code? Usually it's a simple fix. –  Aaron Bertrand Jun 19 at 15:03

1 Answer 1

I bet your code does something like this logic:

IF EXISTS (SELECT 1 FROM dbo.table WHERE key = @key)
BEGIN
  UPDATE dbo.table SET foo = @bar WHERE key = @key;
END
ELSE
BEGIN
  INSERT dbo.table(key, foo) VALUES(@key, @bar);
END

If so, just do this:

UPDATE dbo.table SET foo = @bar WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(key, foo) VALUES(@key, @bar);
END

That meaningless first SELECT is just a wasted seek (or scan if you are not identifying row(s) by something that can seek), and can often be the cause of deadlocks when you have many users simultaneously running this same piece of code.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.