asked
Sql server Deadlock behaviour
Hi I had an sp in my sql server Create sp Updaterecord as begin try Update Table return 0 end try begin catch return 1 end catch so the sp basically updates table if successful return 0 else one My question here is if the table updation inside the sp is becoming victim of deadlock will the sp execution proceed further and return 0 even though table updation is not done or will it stop there where the deadlock has occurred
If there are 10 queries all want tom access the same resource so how sql server handle deadlock here . that is whether nine of the queries will be marked as deadlock victim and rolled back or only one of them will be marked as victim
It's pretty abnormal to see more than two, but it is possible to have multiple sessions involved in a deadlock, yes. And one is chosen to win. Most of the time, all you're going to see is normal blocking, not deadlocking. Deadlocking requires not simply the desire to access one resource, but the existing exclusive ownership of a second resource that is needed by both sessions.
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
I suspect that it wouldn't return a value at all, assuming that it was the victim. You would instead see a 1205 error thrown: *After a deadlock is detected, the Database Engine ends a deadlock by choosing one of the threads as a deadlock victim. The Database Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Rolling back the transaction for the deadlock victim releases all locks held by the transaction. This allows the transactions of the other threads to become unblocked and continue. The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.*
http://technet.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx
You can put a [TRY/CATCH][1] block into the procedure in order to deal with a deadlock. Otherwise, no, you won't see the return value. [1]:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
16 People are following this question.
