SQL Server deadlock analysis and prevention

sql server deadlock analysis
read sql server deadlock log
Read and analyze sql server deadlock log

Text version of the video

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.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss how to read and analyze sql server deadlock information captured in the error log, so we can understand what's causing the deadlocks and take appropriate actions to prevent or minimize the occurrence of deadlocks. This is continuation to Part 80. Please watch Part 80 from SQL Server tutorial before proceeding.

The deadlock information in the error log has three sections
1. Deadlock Victim - Contains the ID of the process that was selected as the deadlock victim and killed by SQL Server.
2. Process List - Contains the list of the processes that participated in the deadlock.
3. Resource List - Contains the list of the resources (database objects) owned by the processes involved in the deadlock.

Process List : The process list has lot of items. Here are some of them that are particularly useful in understanding what caused the deadlock.
loginname : The loginname associated with the process
isolationlevel : What isolation level is used
procname: The stored procedure name
Inputbuf: The code the process is executing when the deadlock occured

Resource List : Some of the items in the resource list that are particularly useful in understanding what caused the deadlock.
objectname : Fully qualified name of the resource involved in the deadlock
owner-list : Contains (owner id) the id of the owning process and the lock mode it has acquired on the resource. lock mode determines how the resource can be accessed by concurrent transactions. S for Shared lock, U for Update lock, X for Exclusive lock etc.
waiter-list : Contains (waiter id) the id of the process that wants to acquire a lock on the resource and the lock mode it is requesting.

To prevent the deadlock that we have in our case, we need to ensure that database objects (Table A & Table B) are accessed in the same order every time.

[krzysztofs]

For U for all your job you have done. Great videos !

[ST-hjhr]

You are a life saver, respected sir! Thanks so much for sharing these deadlock related videos.

[anilgarlapati]

Perfect and CLEARLY explained as always... Thank you Venkat

[rehanshah]

Explanation was so clear and useful. It is the information I was actually looking for.

[raqibul]

Thanks a lot for Ur nice tutorial.Go ahead and give some advance level sql tutorial.There are so many tutorial for beginner level but not advance level.Thanks again.

[shubhamvishwakarma]

your all videos are perfect to under stand SQL server

[aarunvitha_vlog]

Great stuff Sir. I do like to watch your training videos.

[MyLubimishBabyLove]

Thank you, but what to do when there is a deadlocks on different tables at update statistics at always on?

[RevngeFold]

I am sitting with this exact same situation, except the deadlock transactions are two select statements trying to read from the same table... I don't understand why a select would require an exclusive lock on a table...

[abdullahmohammad]

I have a question regarding the second transaction, does it start execution after transaction 1 gets committed?

[kalamani]

Can a deadlock victim be a select into temp table from view? because for us deadlock transaction 1. is to get a view and insert to temp table, 2. is to a update the table. and victim is shown as process id for view!!

[nishadece]

If both Sp name are the same and giving different parameters, its causing deadlock.How can I resolve that admin?

[manavjain]

Can you please make project oriented tutorials on sql..