April 17, 2017 at 3:57 am
The way I see it, is that this table is a single source of resource contention. It has only 1 record, that is updated for every transaction (8 different types). NOTHING happens without an update to this one record in this one table.
If this is a vendor app, get them to fix it.
If this is your app, you have a lot of work ahead of you. I would STRONGLY suggest removing the dependency on this table, and allowing each table (Invoice, CreditNote, DebitNote, SalesOrder, SoCrNote, SoDbNote, DelNote, Rma) create its own key.
April 17, 2017 at 4:07 am
hi Des
its a vendor app, on old software they probably have fixed in a latest version, but we cant move to the latest version..
we will be changing ERP next year, but i guess i am looking at issues until then ?
mal
April 17, 2017 at 10:08 am
First time I've ever seen a single-resource deadlock. Interesting.
This can be fixed if you can change the code for the UPDATE statement. Can you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2017 at 10:07 am
hi Gail
no, ill not be able to change the code unfortunatly
mal
April 18, 2017 at 10:12 am
Then I'm afraid you'll have to live with the deadlocks.
For anyone curious, what's happening is the following:
Session 1 (the update) takes a U lock on a key. U locks are compatible with S, but not other U and not with X
Session 2 (the api_cursor) takes an S lock on the same key. S locks are compatible with each other and with U locks, but not X.
Then, session 1 tries to convert the U lock to X to perform the update. It's blocked. Session 2 holds an incompatible lock.
Session 2 now tries to take an U lock on the same key. It's blocked, session 1's U lock is incompatible.
We now have a deadlock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2017 at 2:33 am
hi Gail
if i was able to change the code what would you recommend (on the off change i can get the mod made)
thanks for help, its definity been a learning experience!
mal
April 19, 2017 at 3:15 am
Add a hint to the UPDATE statement WITH (XLOCK)
That means that the update goes for an exclusive lock straight off, not update lock first (which is done for performance reasons). Since there's no U lock to convert to X, there's no deadlock. The update just gets blocked instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2017 at 3:31 am
GilaMonster - Wednesday, April 19, 2017 3:15 AMAdd a hint to the UPDATE statement WITH (XLOCK)
That means that the update goes for an exclusive lock straight off, not update lock first (which is done for performance reasons). Since there's no U lock to convert to X, there's no deadlock. The update just gets blocked instead.
thanks Gail, lets see how i progress with the ERP company, ill post my results!
April 19, 2017 at 4:18 am
A bit a long shot, but you could try creating an unique constraint on the PK as well.
ALTER TABLE dbo.SalBranch
ADD CONSTRAINT UQ_SalBranch_Branch UNIQUE (Branch);
April 19, 2017 at 4:46 am
Ken McKelvey - Wednesday, April 19, 2017 4:18 AMA bit a long shot, but you could try creating an unique constraint on the PK as well.
Why? The Branch column is already forced unique, it's the primary key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2017 at 5:02 am
I was thinking of foreign key lookups in a high transaction environment.
If the PK on the lookup table is the clustered index then a lot of deadlocks can occur. The ideal solution is to find another column to put the CI on but having a non-clustered index on the PK, as well as the clustered index, also seems to work. (I suppose the lookup table could also be made into a heap if UPDATEs are rare.)
My basic idea was to try and con SQL server into putting the S locks on the non-clustered index and the U locks and X locks on the clustered index.
April 19, 2017 at 1:37 pm
This is kind of like an office with 100 employees and 1 bathroom stall to share between them; it's a very time wasting and contentious bottleneck. But of course you already know that, and you didn't code it.
Since this table is used to manage incrementing primary keys (ie: it must be serialized first in / first out and the subsequent request must read+update the previous request's update), whatever you do, you definitely don't want to loosen up on the isolation level. So, I'd go with Gail's recommendation of adding table locking hint to the UPDATE.
You want to tighten this operation up as much as possible. In addition to above reccomendation, also experiment with leveraging OUTPUT clause to both update and acquire the new ID in one statement. This would be preferable to doing update and select separately within a batch transaction, or (even worse) doing update and select separately outside a transaction.
DECLARE @NextInvoice INT;
UPDATE SalBranch
WITH (TABLOCK)
SET NextInvoice = NextInvoice + 1
OUTPUT INSERTED.NextInvoice INTO @NextInvoice
WHERE Branch = 'AA';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2017 at 1:45 pm
Eric M Russell - Wednesday, April 19, 2017 1:37 PMThis is kind of like an office with 100 employees and 1 bathroom stall to share between them; it's a very time wasting and contentious bottleneck. But you already know that, and you didn't code it.Since this table is used to manage incrementing primary keys (ie: it must be serialized one request at a time, and the current request must read+update the previous request's update), whatever you do, you definitely don't want to loosen up on the isolation level. So, I'd go with Gail's recommendation of adding table locking hint to the UPDATE.
You want to tighten this operation up as much as possible. In addition to above reccomendation, also experiment with leveraging OUTPUT clause to both update and acquire the new ID in one statement. This would be preferable to doing update and select separately within a batch transaction, or (even worse) doing update and select separately outside a transaction.
DECLARE @NextInvoice INT;UPDATE SalBranch
WITH (TABLOCK)
SET NextInvoice = NextInvoice + 1
OUTPUT INSERTED.NextInvoice INTO @NextInvoice
WHERE Branch = 'AA';
The OUTPUT requires a table/temp table/table variable to inset the value into.
DECLARE @Results TABLE (NextInvoice INT);
UPDATE #SalBranch WITH (TABLOCK)
SET NextInvoice = NextInvoice + 1
OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
WHERE Branch = 'AA';
SELECT NextInvoice
FROM @Results;
Since this is a single update, the Quirky Update method will probably be a better solution.
DECLARE @NextInvoice INT;
UPDATE SalBranch WITH (XLOCK)
SET @NextInvoice = NextInvoice = NextInvoice + 1
WHERE Branch = 'AA';
April 19, 2017 at 2:16 pm
DesNorton - Wednesday, April 19, 2017 1:45 PMThe OUTPUT requires a table/temp table/table variable to inset the value into.
DECLARE @Results TABLE (NextInvoice INT);UPDATE #SalBranch WITH (TABLOCK)
SET NextInvoice = NextInvoice + 1
OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
WHERE Branch = 'AA';SELECT NextInvoice
FROM @Results;Since this is a single update, the Quirky Update method will probably be a better solution.
DECLARE @NextInvoice INT;UPDATE SalBranch WITH (XLOCK)
SET @NextInvoice = NextInvoice = NextInvoice + 1
WHERE Branch = 'AA';
Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2017 at 2:29 pm
Eric M Russell - Wednesday, April 19, 2017 2:16 PMDesNorton - Wednesday, April 19, 2017 1:45 PMThe OUTPUT requires a table/temp table/table variable to inset the value into.
DECLARE @Results TABLE (NextInvoice INT);UPDATE #SalBranch WITH (TABLOCK)
SET NextInvoice = NextInvoice + 1
OUTPUT INSERTED.NextInvoice INTO @Results(NextInvoice)
WHERE Branch = 'AA';SELECT NextInvoice
FROM @Results;Since this is a single update, the Quirky Update method will probably be a better solution.
DECLARE @NextInvoice INT;UPDATE SalBranch WITH (XLOCK)
SET @NextInvoice = NextInvoice = NextInvoice + 1
WHERE Branch = 'AA';Thanks, DesNorton. I didn't know until now that inline variable assignment within an UPDATE statement like that was possible.
A couple of nice articles on the use of the Quirky Update method for Running totals
Jeff Moden: Solving the Running Total and Ordinal Rank Problems
Aaron Bertrand: Best approaches for running totals
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply