Here is a typical UPDATE T-SQL we are having deadlock issues with. Just two users attempting to save data that is not related to each other, causes a deadlock. I suspect some things but I am not a DBA and know enough SQL as a programmer to get me by. Any help would be appreciated, I have done a lot of reading on deadlock issues but am still unsure.
The deadlock is a KEY type for a TRANSACTION that is X- exclusive.
WULaborHoursId is the primary key but LastChanged is not part of any index. Would this cause a table scan? Can a Timestamp that changes be part of the primary key? This does not sound like a good idea. Would creating an index on the LastChanged Timestamp eliminate a table scan?
This SELECT right after the UPDATE is something new to me that was introduced by my new boss and is part of some Framework they used in previous developments. I am not used to performing a SELECT right after an UPDATE and it seems it would share the transaction scope of the UPDATE which may not be desirable?
Here is the UPDATE stored proc:
Though your description doesn't say it I assume that when you SELECT the row for review/edit you are retrieving the LastChanged value. And then passing that value into the UPDATE procedure.
Your code, as it is, shouldn't cause any deadlocks. I suspect that you must be doing two things from your calling code whether that is from your application or from another sql process:
one is setting a transaction isolation level to something more restrictive than the default "read committed". The other thing is I suspect you are creating an explicit transaction. I can create a deadlock with some test code only under those two conditions using a WAITFOR 5 second delay between selecting the LastChanged value and execing the Update proc then running the same code from two different windows.
Here was may sample calling code:
answered Oct 21, 2009 at 12:02 PM