Re: Deadlock problem / tablock
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 08/03/04
- Next message: Ian Boyd: "Re: Comprable technique to Yukon's "WITH", in 2000?"
- Previous message: Need more Zzzz: "Re: How can I do a "bulk delete"?"
- In reply to: jrg11201_at_newsgroup.nospam: "Deadlock problem / tablock"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 3 Aug 2004 13:00:48 -0500
On first glance, what I would do first would be to set DEADLOCK_PRIORITY LOW
before the background job. Then the background job will not ever cause a
foreground process to deadlock.
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "jrg11201@newsgroup.nospam" <jrg11201@newsgroup.nospam@discussions.microsoft.com> wrote in message news:DDA2F844-E4CF-4F41-8F3B-BB282201BAF9@microsoft.com... > I am getting deadlocks, and I'm baffled. One of the programs that is deadlocking is a background job that runs about once a minute. This is the only one that I have the ability to change. The job runs very quickly, less than 100ms, so I thought that I could stop the deadlocking problem by getting exclusive table locks on the 4 tables it updates before doing any updates, like this (table names changed): > > BEGIN TRANSACTION > if exists (select b.OrderID > FROM b (TABLOCKX, HOLDLOCK) > LEFT OUTER JOIN b (TABLOCKX, HOLDLOCK) ON b.OrderID =a.OrderID AND b.SOI_OrderItemID = a.SOI_OrderItemID > join c (TABLOCKX, HOLDLOCK) on c.SalesOrderID = a.OrderID > left outer join d (TABLOCKX, HOLDLOCK) on c.SalesOrderID = dt.SalesOrderID > WHERE (d.PostedSeq < c.LastUpdateSeq or d.PostedSeq is NULL) > ) > BEGIN > ... do some updates to the tables in the query above > END > COMMIT TRANSACTION > > I've seen two things that I don't understand. The first wierd thing was to see the job above livedlocked with another job, waiting on table b - locks were acquired on tables a, c and d, but the job was waiting for table b on a process that had an intent share table lock on it. My question here, is whether the query above could under any circumstances acquire locks on a, c and d and then sit there waiting for table b? Whenever I try to provoke this pattern by hand, I wait on table b before the locks are taken on the other tables; I thought that was standard behavior. > > The other thing is that the program listed above deadlocked this morning, which I don't understand, because it should get ex locks on all the tables it updates or inserts before it proceeds to do any updates. Unfortunately I have no more information on what caused the deadlock, I'll turn on SQL profiler today. > > Thanks for any insight, sorry this post was so long. > > JRG
- Next message: Ian Boyd: "Re: Comprable technique to Yukon's "WITH", in 2000?"
- Previous message: Need more Zzzz: "Re: How can I do a "bulk delete"?"
- In reply to: jrg11201_at_newsgroup.nospam: "Deadlock problem / tablock"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
