softright.blogg.se - Sql server deadlock prevention

#Sql server deadlock prevention how to

Sometimes a deadlock can be solved by adding indexing, as it allows the database to lock individual records rather than the whole table, so you reduce contention and the possibility of things getting jammed up. Cursors are notoriously inefficient, avoid them if at all possible. Where I've seen the most issues occur is where you saw yours, inside a cursor.

#Sql server deadlock prevention how to

This would prevent the deadlocking between code block A and code block Bįrom a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. Not forgetting to unlock A and B when done with them To prevent this condition, you can do something like the followingĬode Block A (psuedo code) Lock Shared Resource ZĬode Block B (pseudo code) Lock Shared Resource Z This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Prevention is more difficult, essentially you have to look out for the following:Ĭode Block 1 locks resource A, then resource B, in that order.Ĭode Block 2 locks resource B, then resource A, in that order. See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on. Write deadlock info to the SQL Server error log: You can cause SQL to write deadlocks to the error log with trace flags 12. Tracking deadlocks is the easier of the two:īy default, deadlocks are not written in the error log.

Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?.

Is the missing primary key the problem?.

I know it isn't a well defined conclusion, that is why I'm posting here. Updates apparently take longer without a primary key than with primary key. I felt happy and came back to my project, and did some research to found out the reason for that deadlock.Īpparently, it was a circular wait condition that caused the deadlock. I added a primary key and the problem was solved. I later found the developer who scripted database for deployment messed-up. I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a primary key! luckily it was an identity column. This is the first time I've seen this error and didn't know how to track and fix it effectively.

I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a cursor. Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error.