SQL Server deadlock example

Pubblicato il: 25 agosto 2015
sul canale di: kudvenkat

145,266

862

Text version of the video
http://csharp-video-tutorials.blogspo...

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
/ @aarvikitchen5572

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic

When can a deadlock occur
In a database, a deadlock occurs when two or more processes have a resource locked, and each process requests a lock on the resource that another process has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock.

When deadlocks occur, SQL Server will choose one of processes as the deadlock victim and rollback that process, so the other process can move forward. The transaction that is chosen as the deadlock victim will produce the following error.
Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

SQL script to create the tables and populate them with test data
Create table TableA
(
Id int identity primary key,
Name nvarchar(50)
)
Go

Insert into TableA values ('Mark')
Go

Create table TableB
(
Id int identity primary key,
Name nvarchar(50)
)
Go

Insert into TableB values ('Mary')
Go

The following 2 transactions will result in a dead lock. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window execute Transaction 2 code.

-- Transaction 1
Begin Tran
Update TableA Set Name = 'Mark Transaction 1' where Id = 1

-- From Transaction 2 window execute the first update statement

Update TableB Set Name = 'Mary Transaction 1' where Id = 1

-- From Transaction 2 window execute the second update statement
Commit Transaction

-- Transaction 2
Begin Tran
Update TableB Set Name = 'Mark Transaction 2' where Id = 1

-- From Transaction 1 window execute the second update statement

Update TableA Set Name = 'Mary Transaction 2' where Id = 1

-- After a few seconds notice that one of the transactions complete
-- successfully while the other transaction is made the deadlock victim
Commit Transaction

Next Video : We will discuss the criteria SQL Server uses to choose a deadlock victim

In questa pagina del sito puoi guardare il video online SQL Server deadlock example della durata di ore minuti seconda in buona qualità , che l'utente ha caricato kudvenkat 25 agosto 2015, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 145,266 volte e gli è piaciuto 862 spettatori. Buona visione!

132