Difference between blocking and deadlocking

deadlock vs blocking sql server
In this video we will discuss the difference between blocking and deadlocking. This is one of the common SQL Server interview question. Let us understand the difference with an example.
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 KZbin channel. Hope you can help.
/ @aarvikitchen5572
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
Blocking : Occurs if a transaction tries to acquire an incompatible lock on a resource that another transaction has already locked. The blocked transaction remains blocked until the blocking transaction releases the lock.
Example : 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. Notice that Transaction 2 is blocked by Transaction 1. Transaction 2 is allowed to move forward only when Transaction 1 completes.
--Transaction 1
Begin Tran
Update TableA set Name='Mark Transaction 1' where Id = 1
Waitfor Delay '00:00:10'
Commit Transaction
--Transaction 2
Begin Tran
Update TableA set Name='Mark Transaction 2' where Id = 1
Commit Transaction
Deadlock : Occurs when two or more transactions have a resource locked, and each transaction requests a lock on the resource that another transaction has already locked. Neither of the transactions here can move forward, as each one is waiting for the other to release the lock. So in this case, SQL Server intervenes and ends the deadlock by cancelling one of the transactions, so the other transaction can move forward.
Example : 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. Notice that there is a deadlock between Transaction 1 and Transaction 2.
-- 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
Link for all dot net and sql server video tutorial playlists
kzbin.info...
Link for slides, code samples and text version of the video
csharp-video-tutorials.blogspo...

[@ahmadganteng7435]

This tutorial is gold.. I learn new thing each day thanks to you..

[@sprasain1]

This Channel deservers 10 Million subscribers ! 👋👋👋

[@androidsavior]

as always, best instructor ever, helped me saving my life and my career

[@amkytube5242]

the best SQL teacher ever!

[@Adinasa2]

Awesome presentation

[@IAmKdizzle]

amazing!! very CLEARLY explained, best tutorial online

[@pramodmaurya6610]

After seeing you video ,1st time I am able to understand booking and deadlock concept completely with practical example.thanks alot..🌹

[@avinash23525]

Very nice explanation of dead lock with very simple example. Thank you so much !

[@SamairaJain11]

I just love this series. It is very informative and cleared all basic concepts

[@kidzTube7]

Very nicely explained the concept of locks with simple examples - ZiaKhan

[@muraleedharancholiyampadat8285]

Excellent tutorial! Thank you

[@nanditadebnath1181]

Thnak you so much for such clear explanations.

[@souvikghosh5668]

Venkat, this is something really great! Keep doing such videos. Subscribed to your channel now.

[@1cualtemoch970]

You are amazing! Greetings from the US

[@samuely6188]

You very genius my friend Thank you so much

[@Callum29D]

Great example and very clear

[@assaeinstein1089]

THANKS...I AM QUICK LEARNED...

[@eddieNoureddine]

Thank you so much , I really appreciate every single second of your time spent on making up videos! I almost watched all of your videos , thank you again , I have a question : what is the purpose of having two primary keys in a table ?!

[@letrung1083]

Thanks. Great explain.

[@nishasaravana4447]

Thanks for clearing my doubts. It is awasome video and informative

[@ariefahmedshaik729]

you presentation is awsome

[@rajababuraja7650]

Amazing explanation

[@vin2629]

You are awesome man !!

[@muhammadahsan7113]

Best videos

[@HammadKhanYT]

Perfect tutorial!

[@gykg3202]

Thank youuu!

[@raqibulAlam-su3jo]

Thanks

[@saikirankyasa1836]

Nice explanation

[@s.e.kuzhali7014]

Thank you, Sir !! Your presentations are really helpful for those who are learning SQL.Thanks a lot !!. Here I am having a query as deadlocking occurs when we are trying to process the same record in a table by two different transactions and I mean, the lock occurs on the record level and not on the Table level.

[@kamleshnakade2277]

Awesome explanation of dead lock with very simple example. I have a small question : How SQL Server decides that which transaction to kill in case of deadlock?

[@devexpost8508]

Kudvenkat, Thanks. Well done. One minor note on the Deadlock example... Given your initial diagram showing Mark in Table A, and Mary in Table B, the Deadlocking victim transaction in the right window has its Names reversed: the update to Table B should be Name = "MARY Transaction 2" rather than Mark, and vice versa for the second one. Best regards.

[@zameer218]

Superb and crystal clear explanation. Kudos to Mr. Venkat. what a marvellous job you are doing Sir. Thanks once again. I have question here, does the sql server end one of the transactions based on which executed first?

[@stephennguyen8052]

SQL Server decides which one is deadlock victim based on which one costs least for it to rollback. You can also set deadlock priority too so even if a transaction costs more to be rolled back, it can take priority and the other will be deadlock victim isntead

[@razimkhan2067]

Dear sir Kudvinkat what happend if two user request on same time to a database table , which one request will full fill first... as they both send request on same time , ...

[@KA09MYSURU]

Hi, can you help to write query to find previous quarter sales report ?

[@NeenaNano]

Can you explain nolock n rowlock

[@nirvikar23]

Please tell me about table level locking and row level locking

[@q8sian]

can I trace the deadlock error to let the user to try again to save the change?

[@hina2931]

Are you a DBA, do you offer classes?

[@shivangicial6801]

In SQL server every DML is auto commit then how come the lock on the table without committing?

[@ashishchinna9201]

If you use those commands inside a Transaction block, you need to end the Transaction by 'Commit Transaction' or 'Rollback Transaction'. So here the Tables will be locked until you Commit or Rollback.

[@IbrahiimMohammed]

create table events( event_type integer not null, value integer not null, time timestamp not null, unique (event_type ,time) ); Have inserted few values like insert into events values (2, 5, '2015-05-09 12:42:00'), (4, -42, '2015-05-09 13:19:57'), (2, 2, '2015-05-09 14:48:39'), (2, 7, '2015-05-09 13:54:39'), (3, 16, '2015-05-09 13:19:57'), (3, 20, '2015-05-09 15:01:09') I want to write a query that for each event_type that has been registered more than once returns the difference between the latest and the second latest value . Given the above data, the output should be like event_type value 2 -5 3 4

[@IbrahiimMohammed]

3 years later :D open for improvments CREATE VIEW [max_date] AS SELECT event_type, max(time) as time, value FROM events group by event_type having count(event_type) >1 order by time desc; select event_type, value from ( select event_type, value, max(time) from( Select E1.event_type, ([max_date].value - E1.value) as value, E1.time From events E1, [max_date] Where [max_date].event_type = E1.event_type and [max_date].time > E1.time ) group by event_type )

[@cuteiffatfatima]

Dear sir First of all "May Allah grant you a long life to help people" i am a big fan of you. I asked a question in interview is below please solve this. I have 3 different questions but now i am asking only 1. ID Name Grade Marks 1 Rajesh 9 450 2 Rakesh 8 510 3 Manoj 9 485 4 Vikram 10 392 5 Aarif 8 465 6 Sunil 10 425 Display Name,Grade and maximum marks in each Grade. i am waiting for response

[@gagandeepsingh6126]

+Farhan Ahmed select name,grade,max(marks) from table group by grade.

[@prakashtharla4740]

Hi frnd, Query:- Name,Grade,max(marks) from tablename group by Name,Grade