Concurrency in table without deadlocks

Question

Post reply

Concurrency in table without deadlocks

Cláudio Silva

SSC Eights!

Points: 986
More actions
June 24, 2010 at 5:22 am

#157035

Hi!
I have a problem caused by one SP that is executed simultaneosly dozens of times in the same milisecond and duration takes between 600 and 800ms most of the time and the same sp with the same charge in other times takes less than 100ms.
Important Detail: This sp execution only affect diferrents keys.
I'm assuming that SQL only locks that record and not lock or late the other executions, is this correct?
Can anyone help understant this situation?
Thanks in advance!
Cláudio Silva

Viewing 12 posts - 1 through 11 (of 11 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Deadlocks occur when two (or more) concurrent sessions hold locks on the same object (table, row etc.).

Typical scenario is something like this:

Session 1 Session 2

------------------------------------------------

UPDATE SomeTable

SET SomeColumn = 'a'

WHERE KeyColumn = 1

UPDATE SomeTable

SET SomeColumn = 'b'

WHERE KeyColumn = 2

UPDATE SomeTable

SET SomeColumn = 'c'

WHERE KeyColumn = 2

UPDATE SomeTable

SET SomeColumn = 'd'

WHERE KeyColumn = 1

To avoid deadlocks be sure to update objects always in the same order.

Does this apply to your procedure?

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Cláudio Silva (6/24/2010)
I have a problem caused by one SP that is executed simultaneosly dozens of times in the same milisecond and duration takes between 600 and 800ms most of the time and the same sp with the same charge in other times takes less than 100ms.

Could you expand on what problem you're having?

Important Detail: This sp execution only affect diferrents keys.
I'm assuming that SQL only locks that record and not lock or late the other executions, is this correct?

Not necessarily. Depends on the number of rows affected, indexes present, amount of concurrent queries and probably other things too. SQL can lock at row, page or table level.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 3

Gianluca Sartori (6/24/2010)
Deadlocks occur when two (or more) concurrent sessions hold locks on the same object (table, row etc.).
Typical scenario is something like this:
Session 1 Session 2
------------------------------------------------
UPDATE SomeTable
SET SomeColumn = 'a'
WHERE KeyColumn = 1
UPDATE SomeTable
SET SomeColumn = 'b'
WHERE KeyColumn = 2
UPDATE SomeTable
SET SomeColumn = 'c'
WHERE KeyColumn = 2
UPDATE SomeTable
SET SomeColumn = 'd'
WHERE KeyColumn = 1
To avoid deadlocks be sure to update objects always in the same order.
Does this apply to your procedure?

I don't have any deadlocks! This is not my problem! 🙂

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

I'm sorry. I thought you were experiencing deadlocks from the thread title.

So, what's your issue?

-- Gianluca Sartori

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 5

GilaMonster (6/24/2010)
Cláudio Silva (6/24/2010)
I have a problem caused by one SP that is executed simultaneosly dozens of times in the same milisecond and duration takes between 600 and 800ms most of the time and the same sp with the same charge in other times takes less than 100ms.
Could you expand on what problem you're having?

This sp in non-concurrencial (one/two requests per second) environment takes between 0 and 50ms which is great, but when enter in concorrencial environment (dozens of requests in the same milisecond) I can see two different worlds, one is the executions that takes between 600 and 800ms, the other is the same queries executed takes less than 100ms.

This demonstrate a big discrepancy.

GilaMonster (6/24/2010)
Cláudio Silva (6/24/2010)
Important Detail: This sp execution only affect diferrents keys.
I'm assuming that SQL only locks that record and not lock or late the other executions, is this correct?
Not necessarily. Depends on the number of rows affected, indexes present, amount of concurrent queries and probably other things too. SQL can lock at row, page or table level.

By the performance counters that I made I never have had page lock neither table lock, only row lock!

As I have menitoned before the sp execution only affect differents keys.

By other words imagine that I have 100 entities and which one have 4 object and each objet execute the same sp. Now, the second execution only will be made after first one done, the 3rd after the 2nd and so on.

So in my case I can't have more than 100 requests where which one of this requests is from a diffrent entity. My table have a key at the Entity ID which means (I think) that only locks one record and this record is only for that entity. It's why I was assuming that SQL only locks that record and not lock or late the other executions.

I hope you understand my problem.

Best Regards,

Cláudio Silva

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 6

Gianluca Sartori (6/24/2010)
I'm sorry. I thought you were experiencing deadlocks from the thread title.
So, what's your issue?

Thread title says "without" 🙂

Read my previous post I think now it's more clear

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Cláudio Silva (6/24/2010)
This sp in non-concurrencial (one/two requests per second) environment takes between 0 and 50ms which is great, but when enter in concorrencial environment (dozens of requests in the same milisecond) I can see two different worlds, one is the executions that takes between 600 and 800ms, the other is the same queries executed takes less than 100ms.

So you've got blocking. Make sure the queries are optimal, the indexes support them, monitor for common blocks and focus on queries involved.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 8

GilaMonster (6/24/2010)
Cláudio Silva (6/24/2010)
This sp in non-concurrencial (one/two requests per second) environment takes between 0 and 50ms which is great, but when enter in concorrencial environment (dozens of requests in the same milisecond) I can see two different worlds, one is the executions that takes between 600 and 800ms, the other is the same queries executed takes less than 100ms.
So you've got blocking. Make sure the queries are optimal, the indexes support them, monitor for common blocks and focus on queries involved.

I think in terms of query plan they are correct.

All the single queries into the SP are using it PK and when this isn't possible they are using "index seek"!

What am I missing? :ermm:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Have you monitored for blocking?

Have you looked for long-running queries?

Have you checked what the most common waits are on your system?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 10

GilaMonster (6/26/2010)
Have you monitored for blocking?

Are you talking about performance counters?

If yes, yes I have had monitorizing using some SQL counters like RowLocks and PageLocks.

There is a few RowLocks but no pagelocks.

GilaMonster (6/26/2010)
Have you looked for long-running queries?

Yes, I have created an table and I logged the executions times of each query inside the SP! Then I analyzed the results and I saw that some queries there aren't well-formed (not using idexes) so I made some corrections.

GilaMonster (6/26/2010)
Have you checked what the most common waits are on your system?

Sorry I don't understand the question. What you mean with "the most common waits"? Queries duration inside SP?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Cláudio Silva (6/26/2010)
GilaMonster (6/26/2010)
Have you monitored for blocking?
Are you talking about performance counters?

No. I'm talking about blocked queries. You can't tell if there's blocking by looking at the locks taken, you'd need to look at the locks waiting

sys.dm_exec_requests has a column blocking_session_id. If that's non-zero, it means that the query that connection is running is blocked (waiting for locks) by the session indicated in that column.

Have you looked for long-running queries?
Yes, I have created an table and I logged the executions times of each query inside the SP! Then I analyzed the results and I saw that some queries there aren't well-formed (not using idexes) so I made some corrections.

What about Profiler/server side trace? That'll catch anything running against the server, those stored procs or other stuff that's running concurrently.

Server-side trace is the best way to monitor performance, as it'll catch duration, reads, and CPU, whereas logging execution times can only get you duration.

Have you checked what the most common waits are on your system?

Sorry I don't understand the question. What you mean with "the most common waits"? Queries duration inside SP?[/quote]

No. I mean wait types. There are typically two states for executing queries, running or waiting for resources. Erratic duration often indicates blocking or other waits. It's an important thing to monitor when checking performance.

Start with this: http://msdn.microsoft.com/en-us/library/cc966413.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass