Sample Header Ad - 728x90

Perform SELECT without Locking Table but don't allow Dirty Reads

1 vote
0 answers
4011 views
Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM Table1 on the default SQL Server isolation level, the query above will block other queries inserting into Table1, However SELECT Col1, Col2 FROM Table1 WITH (NOLOCK) Will mean other queries are able inserting records. The downside of this is that the query above is susceptible to dirty reads. Is there a way to get the "good" part of no lock behaviour (no locking) but not the "bad" (dirty reads) and if not, why is this?
Asked by SE1986 (2182 rep)
Apr 4, 2019, 01:17 PM