i have few snapshot transactions, now i need to put nolock/read uncommit for perticular table

Question

hi,

I have few snapshot transactions , now my admin has informed me that put no lock in all tables in snapshot transactions, as it was blocking locking deadlocking with others. so I suggested to put read uncommited instead of snapshot when u are putting no lock in all tables of the transaction.

q1) snapshote takes lot of resources , so if u are putting nolock in all tables of the snapshot transaction then it is better to put read uncommitted is my stmt correct?

q2) or as the snapshot says that it does not clash with other transactions then should we look in other directions like resource consumption?

q3) putting nolock means read uncommited data from the table, does it means same when it is put in side the snapshot transaction?

your sincerely

Answer 1

If your admin suggests that you should use NOLOCK in your queries, your admin should get the sack! NOLOCK very rarely has any place in application code. With NOLOCK, this can happen:

1. You are reading uncommitted and inconsistent data. Take for instance a transfer from one account to another, and you are computing the sum for all accounts, and you read as the withdrawal has been booked, but not the deposit. You get the wrong result!
2. You may fail to read committed data, because it was moved due to page splits. When you have NOLOCK/READUNCOMMITTED, SQL Server scans tables in a way where such misses can happens.
3. For the same reason, you may read the same data twice.
4. The query can die with error 601.

NOLOCK is great for diagnostic queries where you just want to know how far an large transaction has progressed. But application code? No.

Snapshot transactions, on the other hand, gives you very consistent result. Because of the semantics of snapshot isolation, the result may be outdated, which can be a problem for validations. Also, with snapshot transactions, you are not very prone to cause deadlocks. Long-running snapshot transactions can result in the version store in tempdb to grow, though.

So either you are admin is completely confused, or your are describing your situation inaccurately.

Answer 2

The option WITH (NOLOCK) are also called "dirty reads"; guess why.

See https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

=> dirty reads.

Answer 3

snapshot read committed takes no locks (makes use of row versioning). if you are getting locks, then your query is not using snapshot isolation (or is an update). if your database has the READ_COMMITTED_SNAPSHOT option on, queries default to snapshot isolation read committed. if its not enabled, be sure to set snapshot isolation on your queries.