Introduction A couple of years a go I wrote an article about monitoring the version store. With SQL Server 2017 a new DMV has been added to make this easier.
optimistic locking
Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB. The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced. The downside is that the resources required for data modifications are increased, …
In a previous article (here) I described how the isolation level read committed works, with some examples. There is also an optimistic version of this – read committed snapshot, which uses row versions. The behaviour of read committed and read committed snapshot is similar and repeating the examples used previously will show the similarities and …
The previous articles on isolation levels were using pessimistic locking. Two further isolation levels exist that use optimistic locking and are row-versioning based. Snapshot Isolation With snapshot isolation level no locks are placed upon the data when it is read and transactions that write data don’t block snapshot isolation transactions from reading data. Instead, when …
Notes on SQL 