Menu
I'm making a self project in C#.Net, by using a 3-tier app, I wan't to make my sql statements transactional, these statements are used in several stored procedures, I've been using the WITH(NOLOCK) approach in order to query those tables that have been used for inserts or updates during the transaction, and on the C# side I've been using TransactionScope, but I recently read that using WITH(NOLOCK) is not recommended because it can lead to phantom reads or using dirty and inconsistent data. My question is, in order to use data that has been inserted or updated during a transaction, when it comes to a select, What is the best approach in terms of transactional operations?, whether it is database side or business code side.
241
Copy/paste for posterity: The NOLOCK hint has been deprecated in favor of READ COMMITTED SNAPSHOT (RCSI). Starting with SQL Server 2022, these hints will no longer be honored, and the transaction will operate under default isolation level (RCSI, if enabled, or READ COMMITTED if not).
The WITH (NOLOCK) table hint is a good idea when the system uses explicit transactions heavily, which blocks the data reading very frequently. The WITH (NOLOCK) table hint is used when working with systems that accept out of sync data, such as the reporting systems.
So the first thing to say is that with (nolock), is the equivalent of setting the database isolation level to "read uncommitted".
The NOLOCK and READUNCOMMITED hints should only be used with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error. You will see this error. The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.
Have you considered snapshot isolation? It provides perfect read consistency and does not take locks on data at all.
SI is standard on many RDBMSes and default on. Not sure why SQL Server people are so hesitant to use it. The drawbacks are mild, yet you need to research them.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
