Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dead lock not terminated Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 8:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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
Post #1430405
Posted Wednesday, March 13, 2013 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1430417
Posted Wednesday, March 13, 2013 9:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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?
Post #1430459
Posted Wednesday, March 13, 2013 9:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
select * from sys.dm_exec_requests where blocking_session <> 0



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1430461
Posted Wednesday, March 13, 2013 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
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?
Post #1430477
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse