Notes on SQL

Random articles from a puzzled DBA

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, …

Continue reading

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 …

Continue reading

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 …

Continue reading