sql server

how to handled deadlock in sql server? any sql example is great
LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:

Transaction A


 UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
 WAITFOR DELAY '00:00:05' -- Wait for 5 ms
 UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221


Transaction B


 UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221
 WAITFOR DELAY '00:00:05' -- Wait for 5 ms
 UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111


Here both the transactions will place deadlock .
Transaction A will place lock on Table Customer and Transaction B will place lock on Table Orders

So after Delay of 5 ms both will try to get the table release for update and hence Deadlock will occour.

Try catch block can be used to avoid DeadLock

Transaction A


      UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
      WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
      UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221

      PRINT 'Rollback Transaction'
      IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
            WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
            GOTO RETRY -- Go to Label RETRY

Hope this would help you
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't handle a deadlock. The SQL Server engine will handle it for you. It will chose one of the locked transactions and will kill it and that's why it's called deadlock.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ukerandiAuthor Commented:
ok  i will
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You will what?
Andrei FomitchevCommented:
1. execute sp_who2
2. BlkBy column will show the bloker. Deadlock = two SPIDs block each other.
3. Choose the SPID of the victim and: KILL SPID
Jason clarkDBA FreelancerCommented:
Deadlocks are severe problem causing instance since all the processes or the transactions can be troubled, if it occurs..for more read this article https://atdhebuja.wordpress.com/2015/08/25/difference-between-deadlock-and-blocking-in-sql-server/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.