Deadlocks. - 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 2008 Forums

Transact-SQL (2008)

Deadlocks.

Author

Topic

imagremlin
Starting Member

1 Post

Posted - 2011-02-08 : 00:33:47

Hello.

I'm experiencing deadlocks that I can't explain. It appears to be the same statement, on the same store procedure, deadlocking itself. I know that it is possible to write self locking statements, but not with such a simple statement as a delete. Here is the trace:

2011-02-07 17:17:28.48 spid32s deadlock-list

2011-02-07 17:17:28.48 spid32s deadlock victim=process5c1ebc8

2011-02-07 17:17:28.48 spid32s process-list

2011-02-07 17:17:28.48 spid32s process id=process5c1ebc8 taskpriority=0 logused=852 waitresource=OBJECT: 30:325576198:0 waittime=5001 ownerId=66192656 transactionname=user_transaction lasttranstarted=2011-02-07T17:17:21.763 XDES=0x1d8417950 lockMode=SIX schedulerid=19 kpid=39636 status=suspended spid=165 sbid=0 ecid=0 priority=0 trancount=3 lastbatchstarted=2011-02-07T17:17:21.763 lastbatchcompleted=2011-02-07T17:17:21.763 clientapp=.Net SqlClient Data Provider hostname=UPVEBFCA002 hostpid=1272 loginname=DETNSW\App_Pool_Web isolationlevel=serializable (4) xactid=66192656 currentdb=30 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128024

2011-02-07 17:17:28.48 spid32s executionStack

2011-02-07 17:17:28.48 spid32s frame procname=AggregationDB.dbo.Workspace_InsertForUsers line=136 stmtstart=12316 stmtend=12526 sqlhandle=0x03001e006263a04cf805a4004c9e00000100000000000000

2011-02-07 17:17:28.48 spid32s DELETE FROM WorkspaceForUser

2011-02-07 17:17:28.48 spid32s WHERE

2011-02-07 17:17:28.48 spid32s WorkspaceId = @ID

2011-02-07 17:17:28.48 spid32s inputbuf

2011-02-07 17:17:28.48 spid32s Proc [Database Id = 30 Object Id = 1285579618]

2011-02-07 17:17:28.48 spid32s process id=process469c508 taskpriority=0 logused=852 waitresource=OBJECT: 30:325576198:0 waittime=5001 ownerId=66192567 transactionname=user_transaction lasttranstarted=2011-02-07T17:17:21.450 XDES=0x1d79b4e80 lockMode=SIX schedulerid=6 kpid=11252 status=suspended spid=133 sbid=0 ecid=0 priority=0 trancount=3 lastbatchstarted=2011-02-07T17:17:21.453 lastbatchcompleted=2011-02-07T17:17:21.450 clientapp=.Net SqlClient Data Provider hostname=UPVEBFCA002 hostpid=1272 loginname=DETNSW\App_Pool_Web isolationlevel=serializable (4) xactid=66192567 currentdb=30 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128024

2011-02-07 17:17:28.48 spid32s executionStack

2011-02-07 17:17:28.48 spid32s frame procname=AggregationDB.dbo.Workspace_InsertForUsers line=136 stmtstart=12316 stmtend=12526 sqlhandle=0x03001e006263a04cf805a4004c9e00000100000000000000

2011-02-07 17:17:28.48 spid32s DELETE FROM WorkspaceForUser

2011-02-07 17:17:28.48 spid32s WHERE

2011-02-07 17:17:28.48 spid32s WorkspaceId = @ID

2011-02-07 17:17:28.48 spid32s inputbuf

2011-02-07 17:17:28.48 spid32s Proc [Database Id = 30 Object Id = 1285579618]

2011-02-07 17:17:28.48 spid32s resource-list

2011-02-07 17:17:28.49 spid32s objectlock lockPartition=0 objid=325576198 subresource=FULL dbid=30 objectname=AggregationDB.dbo.WorkspaceForUser id=lock148a69380 mode=S associatedObjectId=325576198

2011-02-07 17:17:28.49 spid32s owner-list

2011-02-07 17:17:28.49 spid32s owner id=process469c508 mode=S

2011-02-07 17:17:28.49 spid32s waiter-list

2011-02-07 17:17:28.49 spid32s waiter id=process5c1ebc8 mode=SIX requestType=convert

2011-02-07 17:17:28.49 spid32s objectlock lockPartition=0 objid=325576198 subresource=FULL dbid=30 objectname=AggregationDB.dbo.WorkspaceForUser id=lock148a69380 mode=S associatedObjectId=325576198

2011-02-07 17:17:28.49 spid32s owner-list

2011-02-07 17:17:28.49 spid32s owner id=process5c1ebc8 mode=S

2011-02-07 17:17:28.49 spid32s waiter-list

2011-02-07 17:17:28.49 spid32s waiter id=process469c508 mode=SIX requestType=convert

These delete statements are part of a larger sproc. Can anyone make sense out of this?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-08 : 06:03:06

Well a delete may not be such a simple statement! Triggers, cascades, check constraints, foreign keys on other tables, etc can all make it a really complex operation.

Does your delete statement get a good index hit? -- WorkspaceID is the primary key?

Can you post the text of the stored proc?

I take it if you run the delete outside the sp it works fine?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources