Deadlock:update when table is in a subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Deadlock:update when table is in a subquery?

Author

Topic

rjackson4791
Starting Member

1 Post

Posted - 2009-12-31 : 17:30:27

I'm troublshooting some deadlock issues we have going on. All indication points to the following query but I'm actually not sure how valid it is.

The orginial Dev is updating a table and using that table as a subquery for the where clause seems a bit fishy to me. All thoughts on will be helpful.

Thanks.

		UPDATE Queue
		SET LastProcessedBy = @OwnerName,
			ReadyForProcessing = dateadd(ss, @VisibilityTimeoutSeconds, @Now),
			@ID = Queue.ID,
			ProcessingAttempts = ProcessingAttempts + 1		
		WHERE ID =
			(SELECT TOP 1 ID
			FROM Queue
			WHERE ReadyForProcessing < @Now
			AND OKToProcess = 1
			ORDER BY ProcessingAttempts, ReadyForProcessing)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-31 : 17:36:19

You've only given one side of the possible deadlock, so that's not enough information to assist yet. There are two sides, the victim and the owner. To capture both, you'll need to enable the deadlock trace flag. You can do this for the current session via DBCC TRACEON(1222,-1). For all future sessions after a restart, you'll need to add the traceflag to the startup parameter of SQL Server in SQL Server Configuration Manager.

By using the trace flags, the deadlock output will get put into the Error Log. Once you've captured both sides of the deadlock, post the queries here.

You should consider altering your database to use the READ_COMMITTED_SNAPSHOT isolation level instead of the default which is READ_COMMITTED. By using READ_COMMITTED_SNAPSHOT, it stops reads from blocking writes which means that reads can no longer be involved in a deadlock. You'll still encounter deadlocks when using READ_COMMITTED_SNAPSHOT if the deadlocks involve two writes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources