As you already know, data in a database is generally shared between many user application programs. The situation in which several user application programs read and write the same data at the same time is called concurrency. Thus, each DBMS must have some kind of control mechanism to solve concurrency problems. A high level of concurrency is possible in a database system that can manage many active user applications without them interfering with each other. Conversely, a database system in which different active applications interfere with each other supports a low level of concurrency.
This chapter begins by describing the two concurrency control models that the Database Engine supports. The next section explains how concurrency problems can be solved using transactions. This discussion includes an introduction to the four properties of transactions, known as ACID properties, an overview of the Transact-SQL statements related to transactions, and an introduction to transaction logs. The third major section addresses locking and the three general lock properties: lock modes, lock resources, and lock duration. Deadlock, an important problem that can arise as a consequence of locking, is also introduced.
The behavior of transactions depends on the selected isolation level. The five isolation levels are introduced, including whether each belongs to the pessimistic or the optimistic concurrency model. The differences between existing isolation levels and their practical meaning will be explained too.
The end of the chapter introduces row versioning, which is how the Database Engine implements the optimistic concurrency model. The two isolation levels related to this model—SNAPSHOT and READ COMMITTED SNAPSHOT—are discussed, as well as use of the tempdb system database as a version store.
Concurrency Models
The Database Engine supports two different concurrency models:
- Pessimistic concurrency
- Optimistic concurrency
Pessimistic concurrency uses locks to block access to data that is used by another process at the same time. In other words, a database system that uses pessimistic concurrency assumes that a conflict between two or more processes can occur at any time and therefore locks resources (row, page, table), as they are required, for the duration of a transaction. As you will see in the section “Locking,” pessimistic concurrency issues shared locks on data being read so that no other process can modify that data. Also, pessimistic concurrency issues exclusive locks for data being modified so that no other processes can read or modify that data.
Optimistic concurrency works on the assumption that a transaction is unlikely to modify data that another transaction is modifying at the same time. The Database Engine supports optimistic concurrency so that older versions of data rows are saved, and any process that reads the same data uses the row version that was active when it started reading data. For that reason, a process that modifies the data can do so without any limitation, because all other processes that read the same data access the saved versions of the data. The only conflict scenario occurs when two or more write operations use the same data. In that case, the system displays an error so that the client application can handle it.
Note – The notion of optimistic concurrency is generally defined in a broader sense. Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely, and allows transactions to execute without using locks. Only when a user is attempting to change data are resources checked to determine if any conflicts have occurred. If a conflict occurs, the application must be restarted.
