patternsqlModerate
Safe to use WITH (NOLOCK) in this case?
Viewed 0 times
thiscasewithsafenolockuse
Problem
Scenario
I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an exclusive lock and conflicting with the shared lock of the SELECT.
Details
Use Case
In the case of a concurrent SELECT and DELETE, it might look like this (drawn in MS Paint, because I try to be professional...):
Edit: By "trigger" above I don't mean an actual db trigger, but just application behavior.
Research
I poked around here on the DBA Stack Exchange and found that I could
I'm new to WITH (NOLOCK) so here's what I've learned from this helpful site:
the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the same data, to finish its processing.
Justifications
These quotes are from the same link. Under each one I've described my thinking in concluding that the behavior won't affect me.
In general, using explicit table hints frequently is considered as a bad practice that you should generally avoid. For the NOLOCK table hint specifically, reading uncommitted data that could be rolled back after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted data read, so that the data you read could be different, or never even have existed.
Dirty Read: I don't think I need to care about this because the SELECT is not actually invalid because of the dirty read. Any DELETE that caused a d
I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an exclusive lock and conflicting with the shared lock of the SELECT.
Details
- SQL Server 14.00.3281.6.v1
- Running on AWS RDS
Use Case
- My app can trigger a SELECT in a variety of ways.
- If the app triggers a DELETE, it will (always) then trigger a SELECT to retrieve the results that reflect the effects of the DELETE.
In the case of a concurrent SELECT and DELETE, it might look like this (drawn in MS Paint, because I try to be professional...):
Edit: By "trigger" above I don't mean an actual db trigger, but just application behavior.
Research
I poked around here on the DBA Stack Exchange and found that I could
SELECT myTable WITH (NOLOCK) which would prevent the shared lock. I'm considering using this, but I know there are lots of caveats and gotchas so I want to validate my decision or replace it if necessary.I'm new to WITH (NOLOCK) so here's what I've learned from this helpful site:
the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the same data, to finish its processing.
Justifications
These quotes are from the same link. Under each one I've described my thinking in concluding that the behavior won't affect me.
In general, using explicit table hints frequently is considered as a bad practice that you should generally avoid. For the NOLOCK table hint specifically, reading uncommitted data that could be rolled back after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted data read, so that the data you read could be different, or never even have existed.
Dirty Read: I don't think I need to care about this because the SELECT is not actually invalid because of the dirty read. Any DELETE that caused a d
Solution
I'm not a DBA, but a software dev with a few years of DB experience. I'm only "textbook-level" familiar with the inner workings of locking, pages, hints,
Then you should use SNAPSHOT isolation, or set your database to READ COMMITTED SNAPSHOT, because it's fundamentally simpler to write correct, scalable, deadlock-free code.
It's a common misconception that NOLOCK/ReadUncommited relaxes the concurrency model in a predictable way. IE that it simply allows you to read data in a state that might eventually be rolled back. This is not the case. Rows sometimes need to move around when they are changed, and a NOLOCK query might miss such rows or read them multiple times. Or a NOLOCK query might read a non-clustered index and the underlying table when one was updated but the other was not. Both of these can cause results that are just wrong.
The cost of READ COMMITTED SNAPSHOT/SNAPSHOT is that rows need a little extra bookkeeping. There's an additional 14 byte field added to updated and deleted rows to point to the previous row version, and the row versions are stored either in TempDb or in the user database. But there's a performance benefit too. Your workload can scale more easily, as there is less locking contention, and sessions are able to run more concurrently.
Then you should use SNAPSHOT isolation, or set your database to READ COMMITTED SNAPSHOT, because it's fundamentally simpler to write correct, scalable, deadlock-free code.
It's a common misconception that NOLOCK/ReadUncommited relaxes the concurrency model in a predictable way. IE that it simply allows you to read data in a state that might eventually be rolled back. This is not the case. Rows sometimes need to move around when they are changed, and a NOLOCK query might miss such rows or read them multiple times. Or a NOLOCK query might read a non-clustered index and the underlying table when one was updated but the other was not. Both of these can cause results that are just wrong.
The cost of READ COMMITTED SNAPSHOT/SNAPSHOT is that rows need a little extra bookkeeping. There's an additional 14 byte field added to updated and deleted rows to point to the previous row version, and the row versions are stored either in TempDb or in the user database. But there's a performance benefit too. Your workload can scale more easily, as there is less locking contention, and sessions are able to run more concurrently.
Context
StackExchange Database Administrators Q#281560, answer score: 17
Revisions (0)
No revisions yet.