Problem with deadlock! - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with deadlock!

Author

Topic

Emanuele Garuglieri
Starting Member

3 Posts

Posted - 2007-02-08 : 11:26:43

Hi to everyone!
I don't know if this is the right position for post this topic, i hope !

I have a strange deadlock problem with the follow statement, anyone can help me?


 Query failed:
[3864] 2007-02-01T10:33:37,422 > SELECT TOP 128 "id", "sid", "domain", "topic", "status", "assignment", "priority", CAST("creation_date"-{ts '1970-01-01 00:00:00.000'} AS DOUBLE PRECISION)*86400.0, CAST("on_enter_date"-{ts '1970-01-01 00:00:00.000'} AS DOUBLE PRECISION)*86400.0, CAST("on_exit_date"-{ts '1970-01-01 00:00:00.000'} AS DOUBLE PRECISION)*86400.0, CAST("change_status_date"-{ts '1970-01-01 00:00:00.000'} AS DOUBLE PRECISION)*86400.0, "tries", "successful", "suspend_state", "abort_state", "annotation" FROM "flows_operationselector_view" WHERE "domain" = 'Mam.DataManager' AND "topic" = 'operation' AND "status" = 'OPENED' AND "suspend_state" <> 2 AND "abort_state" <> 2 AND "assignment" = 'video_line' AND "priority" <= 10 AND ( ("tries"=0) OR (dbo.ftries("on_exit_date","tries") < CURRENT_TIMESTAMP) ) ORDER BY priority,creation_date
SQLSTATE: 40001
Native error code: 1205
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Nelle risorse lock della transazione (ID di processo 59) si è verificato un blocco critico (deadlock) con un altro processo e la transazione è stata scelta come vittima. Rieseguirla.

How can i have a deadlock with a simply SELECT?? Excuse me if the info message is in italian.
Thanks
Emanuele Garuglieri

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-08 : 14:52:02

The select will take shared locks.
Say and update is locking an index entry, your select runs and takes a shared lock on the data page, the update then wants to update that page and gets blocked, the select then tries to get a shared lock on the index and we have a deadlock.
Can also happen because the updaet and select get data page or index entry locks in a different order. You can alleviate the problem by taking a table lock but you should always assume that deadlocks are possible in any system.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-08 : 17:00:17

perhaps you expected a simple select to read uncommitted by default? read uncommitted (dirty read) is not the default.

doing a dirty read will avoid this type of deadlock but can cause all sorts of other problems, such as reading invalid data, or the read failing because of data movement (page splits, etc).

best to fix the deadlock as Nigel suggests.

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources