Locking in SQL Server 2012

The default transaction level in sql server 2012 is READ COMITTED. That transaction level uses a pessimistic approach meaning readers can block writers, writers can block writers and writers can block readers.
Is it basically the opposite approach of optimistic approaches like providing row versions i.e. snapshot isolation level.

However in this session we want to focus on how SQL Server maintains consistency in a multi-user environment. The answer is simple: locks.

But how are locks actually handled. Well there is a bunch of information about locking.

First there are locks and latches. Most locks are being held until the end of transactions.
Latches are being held until the specific operation is over. Locks are used to maintain logic consistency. Latches are used to maintain physical consistency.
We will focu ....

continue reading