Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chetanb3
Yak Posting Veteran
52 Posts |
 Posted - 2008-07-03 : 02:22:01
|
| CREATE TABLE DBO.QUEUE ( QUEUEID INT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY, SOMEACTION VARCHAR(100)) -------------------------------------DECLARE @queueid INT BEGIN TRAN TRAN1 SELECT TOP 1 @queueid = QUEUEID FROM DBO.QUEUE WITH (updlock, readpast) PRINT 'processing queueid # ' + CAST(@queueid AS VARCHAR) -- account for delay in processing time WAITFOR DELAY '00:00:10' DELETE FROM DBO.QUEUE WHERE QUEUEID = @queueid COMMIT----------------------------------Hi all,I am using above query to process Queue in my application.when i execute above query in 2 differnet session, one of them does in deadlock and terminate.i don't know why..?Please help.. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-04 : 00:43:11
|
| Because select statement holds lock. Why use update lock in select statement? |
 |
|
|
chetanb3
Yak Posting Veteran
52 Posts |
Posted - 2008-07-07 : 06:58:47
|
| because my table works as QUEUE and when to parallel statement occures it will process same row this i don't want.to reproduce run query by removing "WITH (updlock, readpast) "DECLARE @queueid INT BEGIN TRAN TRAN1 SELECT TOP 1 @queueid = QUEUEID FROM DBO.QUEUE --WITH (updlock, readpast) PRINT 'processing queueid # ' + CAST(@queueid AS VARCHAR) -- account for delay in processing time WAITFOR DELAY '00:00:10' DELETE FROM DBO.QUEUE WHERE QUEUEID = @queueid COMMIT |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-07 : 23:44:16
|
| You may take look at snapshot isolation. |
|
|
|
|
|
|
Dead lock Problem