A problem of concurrency on a small table

Question

A problem of concurrency on a small table

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

It could be that the sessions performing updates on this table as part of a larger batch transaction. Often times programming will begin a transaction, update, and then do some other potentially time consuming stuff like querying other tables before committing the transaction. Active transactions will hold locks until committed.

So, take a look at the stored procedure and confirm that SQL operations contained within BEGIN TRAN / COMMIT TRAN blocks are kept to a minimum. However, you did say that this is a 3rd party developed application, so it could be you have no control over that.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

One thing you kind of have control over is indexing. If a FK'd table is being updated and that is locking your other table and the update is doing a table scan on a large table for a few rows where it could be doing an index seek for those few rows it could be a magic bullet to create the missing NC index to facilitate that seek. I note that this will almost certainly void your supportability with the third party product. Have a script to drop said index before you call for support or apply a patch, then recreate it when done. I have done this numerous times for clients over the years to great effect (and even sometimes get the stupid third party to integrate the index(es) into their product). YMMV and your risk aversion come into play here of course!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

paolobianchi (7/13/2016)
Hi all,
i have a problem on an application.
Details:
Table AAA, about 500 records, not growing
Many users (about 100) make a read from this table, many times (about 120 times for minute)
Others users write on this table, about 10 times for minute
Example:
SELECT A_FIELD FROM AAA WHERE B_FIELD = 'xxx' (about 50/60 times for minute)
UPDATE AAA SET A_FIELD=A_FIELD+1 WHERE B_FIELD = 'xxx' (about 10 times for minute)
A_FIELD is a Decimal(10,0), B_FIELD is a Char(100)
Normally, every select and every update take 1/2 ms, MAX 10ms
But sometimes (every 10 minutes, sometimes), the UPDATE take 10, 20 seconds!
A problem of concurrency, not?
What kinds of controls or modification i can make to resolve this problem?
(i cannot modify the application, i can only work on db setting, index, etc.)
(which other information you need to suggest something? I can say tha snapshot isolation situation of db is the following:
namesnapshot_isolation_statesnapshot_isolation_state_descis_read_committed_snapshot_onrecovery_modelrecovery_model_desccollation_name
master1ON03SIMPLELatin1_General_CI_AS
tempdb0OFF03SIMPLELatin1_General_CI_AS
model0OFF03SIMPLELatin1_General_CI_AS
msdb1ON03SIMPLELatin1_General_CI_AS
db_name1ON13SIMPLELatin1_General_CI_AS
need other?
Thank you very much!
Paolo

There's a possible super easy fix for all of this but I need one piece of information to know for sure (although it seems obvious it is). Is this table being used to determine what the next ID from primary key columns for multiple tables will be? If so, I'll tell you that your current method is prone to deadlocks and a wealth of other problems that can easily be fixed (like I did for a company suffering an average of 640 deadlocks per day with occasional spikes to 4000 in an 8 hour period).

I just need to know if my guess about the table being a kind of NextID table is correct or not. Or, perhaps, it's a warning counter but of a similar nature where you want to always return the new value of the "AutoNum" after you've done an increment?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paolobianchi SSC-Addicted Points: 493 More actions · Answer 4

Eric M Russell (7/13/2016)
It could be that the sessions performing updates on this table as part of a larger batch transaction. Often times programming will begin a transaction, update, and then do some other potentially time consuming stuff like querying other tables before committing the transaction. Active transactions will hold locks until committed.
So, take a look at the stored procedure and confirm that SQL operations contained within BEGIN TRAN / COMMIT TRAN blocks are kept to a minimum. However, you did say that this is a 3rd party developed application, so it could be you have no control over that.

Yes, i'm almost sure of this now...

I think:

begin tran

select .... from cpwarn

.. some other interesting stuff ...

.. many MANY other interesting stuff ...

update cpwarn ...

commit

So sometime the update remain blocked because table is locked...

Investigating 😉

Thank you

Paolo

paolobianchi SSC-Addicted Points: 493 More actions · Answer 5

TheSQLGuru (7/13/2016)
One thing you kind of have control over is indexing. If a FK'd table is being updated and that is locking your other table and the update is doing a table scan on a large table for a few rows where it could be doing an index seek for those few rows it could be a magic bullet to create the missing NC index to facilitate that seek. I note that this will almost certainly void your supportability with the third party product. Have a script to drop said index before you call for support or apply a patch, then recreate it when done. I have done this numerous times for clients over the years to great effect (and even sometimes get the stupid third party to integrate the index(es) into their product). YMMV and your risk aversion come into play here of course!

That's interesting... Investigating 😉

paolobianchi SSC-Addicted Points: 493 More actions · Answer 6

Jeff Moden (7/13/2016)
paolobianchi (7/13/2016)
Hi all,
i have a problem on an application.
Details:
Table AAA, about 500 records, not growing
Many users (about 100) make a read from this table, many times (about 120 times for minute)
Others users write on this table, about 10 times for minute
Example:
SELECT A_FIELD FROM AAA WHERE B_FIELD = 'xxx' (about 50/60 times for minute)
UPDATE AAA SET A_FIELD=A_FIELD+1 WHERE B_FIELD = 'xxx' (about 10 times for minute)
A_FIELD is a Decimal(10,0), B_FIELD is a Char(100)
Normally, every select and every update take 1/2 ms, MAX 10ms
But sometimes (every 10 minutes, sometimes), the UPDATE take 10, 20 seconds!
A problem of concurrency, not?
What kinds of controls or modification i can make to resolve this problem?
(i cannot modify the application, i can only work on db setting, index, etc.)
(which other information you need to suggest something? I can say tha snapshot isolation situation of db is the following:
namesnapshot_isolation_statesnapshot_isolation_state_descis_read_committed_snapshot_onrecovery_modelrecovery_model_desccollation_name
master1ON03SIMPLELatin1_General_CI_AS
tempdb0OFF03SIMPLELatin1_General_CI_AS
model0OFF03SIMPLELatin1_General_CI_AS
msdb1ON03SIMPLELatin1_General_CI_AS
db_name1ON13SIMPLELatin1_General_CI_AS
need other?
Thank you very much!
Paolo
There's a possible super easy fix for all of this but I need one piece of information to know for sure (although it seems obvious it is). Is this table being used to determine what the next ID from primary key columns for multiple tables will be? If so, I'll tell you that your current method is prone to deadlocks and a wealth of other problems that can easily be fixed (like I did for a company suffering an average of 640 deadlocks per day with occasional spikes to 4000 in an 8 hour period).
I just need to know if my guess about the table being a kind of NextID table is correct or not. Or, perhaps, it's a warning counter but of a similar nature where you want to always return the new value of the "AutoNum" after you've done an increment?

Hello,

table contains "logical keys" for the application.

In another table, a field contains a progressive number (like Order number), and in cpwarn he contains actual numer. So when he must write a record in the other tables (orders), he read next progressive order number and use it, and update table of progressive numbers (cpwarn)

I know that this kind of gestion is "dangerous" but the software works in this mode 🙂

Suggestion?

Without modifing software??

Tnx

Paolo

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Paolo

We haven't seen Jeff's solution yet, but my guess is that's going to be the way to go if you don't want to modify the software. If you do have the opportunity to change the code a bit, or perhaps if you're thinking of designing something similar in future, you might consider a sequence object - this sort of situation is exactly what they're for.

John