Sql server® 2012 t-sql fundamentals

 

CHAPTER 9

 

Transactions and Concurrency

 

 301

As for reading data, the defaults are different for on-premises SQL Server installations and 

SQL Database. In an on-premises SQL Server installation, the default isolation level is called READ 

COMMITTED. In this isolation, when you try to read data, by default your transaction requests a 

shared lock on the data resource and releases the lock as soon as the read statement is done with 

that resource. This lock mode is called “shared” because multiple transactions can hold shared 

locks on the same data resource simultaneously. Although you cannot change the lock mode and 

duration required when you are modifying data, you can control the way locking is handled when 

you are reading data by changing your isolation level. As mentioned, I will elaborate on this later 

in this chapter.

In SQL Database, the default isolation level is called READ COMMITTED SNAPSHOT. Instead of 

relying on locking, this isolation relies on a row-versioning technology. Under this isolation level, 

readers do not require shared locks, and therefore they never wait; they rely on the row-version-

ing technology to provide the expected isolation. In practical terms, this means that under the 

READ COMMITTED isolation level, if a transaction modifies rows, until the transaction completes, 

another trans action can’t read the same rows. This approach to concurrency control is known as 

the pessimistic concurrency approach. Under the READ COMMITTED SNAPSHOT isolation level, if 

a transaction modifies rows, another transaction trying to read the data will get the last commit-

ted state of the rows that was available when the statement started. This approach to concurrency 

control is known as the optimistic concurrency approach.

This lock interaction between transactions is known as lock compatibility. Table 9-1 shows the lock 

compatibility of exclusive and shared locks (when you are working with an isolation level that generates 

these locks). The columns represent granted lock modes, and the rows represent requested lock modes.