March 13, 2013 at 8:04 am
Dear All
I have read that sql takes care of dead locks on its own. But when i run following query through SSMS in 2 differnt query sessions it keeps on going
USE AdventureWorks
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 20
Am i doing something wrong. I am using sql server 2008 R2
Regards
March 13, 2013 at 8:16 am
That wont cause a deadlock, that will cause blocking.
You need to do things in a different order, eg 1 query update t1 then t2, other query update t2 then t1
CREATE TABLE t1 (i int);
CREATE TABLE t2 (i int);
INSERT t1 SELECT 1;
INSERT t2 SELECT 9;
/* in one window enter: */
BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
WAITFOR DELAY '00:00:20'
UPDATE t2 SET i = 99 WHERE i = 9
COMMIT
/* in a second window (another transaction) enter: */
BEGIN TRAN
UPDATE t2 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:20'
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT
March 13, 2013 at 9:10 am
Thanks for u quick reply.
I would like to know how do i find if system is blocking? Is there a query by which i can find out that blocking is happning?
March 13, 2013 at 9:12 am
select * from sys.dm_exec_requests where blocking_session <> 0
March 13, 2013 at 9:29 am
now when tried to test the command given by you, I ran the same quires mentione by me in my first post and it come out on its own with following message
(1 row(s) affected)
Msg 1205, Level 13, State 51, Line 5
Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Surprising. Why it did not happen when i tried first few times?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy