sql server

how to handled deadlock in sql server? any sql example is great
LVL 10
ukerandiAsked:
Who is Participating?
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 GargBusiness Intelligence DeveloperCommented:
HI,

Transaction A

 BEGIN TRANSACTION

 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

 COMMIT TRANSACTION

Transaction B

 BEGIN TRANSACTION

 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

 COMMIT TRANSACTION

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

RETRY: -- Label RETRY
BEGIN TRANSACTION
BEGIN TRY

      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

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

Hope this would help you
0
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.
0

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You will what?
0
Andrei FomitchevCommented:
With SSMS
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
0
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/
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.