Concurrency in SQL Server

Note: this article is published on 07/14/2024.

Concurrency

In computer science, concurrency is the ability of different parts or units of a program, algorithm, or problem to be executed out-of-order or in partial order, without affecting the outcome.

Transaction

A transaction is a collection of operations that performs a single logical function in a database application. Multiple transactions execute at the same time can cause the concurrency issue.

Transaction Properties

A database management system (DBMS) is considered a relational database management system (RDBMS) if it follows the transactional properties, ACID, a set of properties of database transactions intended to guarantee data validity..

A: Atomicity
- Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely or fails completely:
C: Consistency
- Consistency ensures that a transaction can only bring the database from one consistent state to another, preserving database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
I: Isolation
- Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
D: Durability
- Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash)

Concurrency Problem Types

The concurrency problem mostly arises when both the users try to write the same data, or when one is writing and the other is reading. Apart from this logic, there are some common types of concurrency problems:

Dirty Reads
- This problem occurs when another process reads the changed, but uncommitted data. For instance, if one process has changed data but not committed it yet, another process is able to read the same data. This leads to the inconsistent state for the reader.
Lost Updates
- This problem occurs when two processes try to manipulate the same data simultaneously. This problem can lead to data loss, or the second process might overwrite the first processs change.
Non-repeatable Reads
- This problem occurs when one process is reading the data, and another process is writing the data. In non-repeatable reads, the first process reading the value might get two different values, as the changed data is read a second time because the second process changes the data.
Phantom Reads
- If two same queries executed by two users show different output, then it would be a Phantom Read problem. For instance, If user A select a query to read some data, at the same time the user B insert some new data but the user A only get able to read the old data at the first attempt, but when user A re-query the same statement then he/she gets a different set of data.

Solve Concurrency Problems

SQL Server provides 5 different levels of transaction isolation to overcome these Concurrency problems. These 5 isolation levels work on two major concurrency models:

Pessimistic model -
- Readers can block writers, and
- Writers can block readers.
Optimistic model -
- Readers cannot block writers, and
- Writers cannot block readers, but
- Writer can block another writer.

Note that readers are users are performing the SELECT operations. Writers are users are performing INSERT, ALTER, UPDATE, S.E.T. operations.

5 Isolation Level

In a SQL server, a query can have five different isolation levels:

Read Uncommitted --- pessimistic
- One transaction is allowed to read the data that is about to be changed by the commit of another process.
  - Allows the dirty read problem.
Read Committed --- pessimistic --- This option is the SQL Server default.
- Only allowed to read data that is committed,
  - Eliminates the dirty read problem.
    - In reading data if there are concurrent transactions that can delete or write data, the read is blocked until other work is complete.
Repeatable Read --- pessimistic
- All Transactions but Insert has to wait till another transaction's update or read query is complete.
  - Eliminates the Non-Repeatable Read problem, but
  - Phantom data exists
Serializable --- pessimistic
- Prevent the Phantom Read problem.
  - The highest level of isolation in the pessimistic model.
  - Can ask any transaction to wait until the current transaction completes.
Snapshot --- optimistic
- Snapshot follows the optimistic model of concurrency, and this level of isolation takes a snapshot of the current data and uses it as a copy for the different transactions. Here each transaction has its copy of data, so if a user tries to perform a transaction like an update or insert, it asks him to re-verify all the operation before the process gets started executing.

These levels are ordered in terms of the separation of work by two different processes, from minimal separation to maximal.

Conclusion

Concurrency occurs when two transactions operate currently on the same set of data.
- Concurrency can then lead to data inconsistency and abnormal behaviour for a transaction.
RDBMS transactions have four properties: ACID, that ensure consistency of work.
- Atmocity
- Consistency
- Isolation
- Durability
Four major common concurrency problems
- 4 problems
  - Dirty Read
  - lost data
  - unrepeatable
  - Phantom data
- that can be solved by using two models of concurrency:
  - pessimistic and
  - optimistic.
- with five types of isolation levels.
  - Read Uncommitted --- pessimistic
  - Read Committed --- pessimistic
  - Repeatable Read --- pessimistic
  - Serializable --- pessimistic
  - Snapshot --- optimistic

References: