Multiple processes accessing the same table. some to write others to read / Lock

Question

Multiple processes accessing the same table. some to write others to read / Lock

river1

SSC-Insane

Points: 24309
More actions
May 6, 2024 at 12:02 pm

#4418984

Dears,
Hope this message finds you well
We have a log table which is used at same time by some processed to read and others to write. This is causing deadlock
What can we do to secure that we end up with deadlocks?
Shall we shift the isolation level on the read queries or something like that?
thanks a lot for a guidance
Best regards,

Pedro

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Simply reading from a table that is being written to should not cause a deadlock. Are you sure that the read queries are causing the deadlocks?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

can you post a deadlock xml so we can look at it.

as well as the code involved in writing-reading that log table

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Are you getting deadlocks, with a deadlock victim, errors and all that? Or, are you seeing excessive blocking and long wait times on queries, which is absolutely not the same thing as a deadlock?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

river1 wrote:

Dears,
Hope this message finds you well
We have a log table which is used at same time by some processed to read and others to write. This is causing deadlock
What can we do to secure that we end up with deadlocks?
Shall we shift the isolation level on the read queries or something like that?
thanks a lot for a guidance
Best regards,
Pedro

Lots of good replies here for you to follow up on.

Do you understand the difference between locks\blocks and deadlocks?

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

"Ya can't make an omelette without breaking just a few eggs" 😉