Transactions and locks for databases

Transaction

is a series of operations performed as a logical unit, a logical work cell must have four properties, called ACID (atomicity, consistency, isolation, and persistence) properties.

1, A (atomicity) atomicity

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

2, C (consistency) consistency

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B树索引或双向链表）都必须是正确的。

3, I (isolation) Isolation

并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。

4, D (durability) Persistence

事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

Lock

Locking is a mechanism that the Microsoft SQL Server database engine uses to synchronize access to the same block of data by multiple users at the same time.

Before a transaction obtains a dependency on the current state of a block of data, such as by reading or modifying data, it must protect itself from changes made to the same data by other transactions. The transaction achieves this by requesting a block of data. Locks have multiple modes, such as shared or exclusive

1. Dirty Reading

The data that a transaction reads is data that is being processed by another transaction. Another transaction may be rolled back, causing the data read by the first transaction to be incorrect.

2. Non-repeatable reading

The data is read two times in one transaction, but another transaction has changed the data involved in the first transaction, causing the first transaction to read into the old data.

3. Phantom Reading

Phantom reading refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

4, Update lost

When multiple transactions read one data at a time, one transaction successfully processed the data and was written back to the original value by another transaction, resulting in the loss of the first transaction update.

Transaction ISOLATION Level:

READ UNCOMMITTED Read committed REPEATABLE read serializable

Lock mode:

1. Shared lock

A shared lock (S-Lock) allows concurrent transactions to read (SELECT) resources under closed concurrency control. For more information, see Types of concurrency control. When there is a shared lock (S lock) on the resource, no other transaction can modify the data. As soon as the read operation is complete, the shared lock (S lock) on the resource is freed, unless the transaction isolation level is set to repeatable read or higher, or a shared lock (S lock) is reserved with a lock hint for the duration of the transaction.

2. Update lock (U lock)

Update locks in a hybrid of shared and exclusive locks. An update lock means that when an update is made, a shared lock may be converted to an exclusive lock after the scan has completed qualifying data.

There are two steps in this:

1) When the scan gets the where condition. This section is an update query, and this is an update lock.

2) If a write update is performed. The lock is now upgraded to an exclusive lock. Otherwise, the lock is turned into a shared lock.

Update locks can prevent common deadlocks.

3. Exclusive lock

An exclusive lock (X Lock) prevents concurrent transactions from accessing resources. An exclusive lock is not compatible with any other lock. When an exclusive lock (x Lock) is used, no other transaction can modify the data, and the read operation is performed only when the NOLOCK hint or uncommitted read isolation level is used.

How to avoid deadlocks

(1). Access the object in the same order. (Note: avoid loops) (2). Avoid user interaction in the transaction. (Note: Less time to hold resources, less lock-up competition) (3). Keep the transaction short and in a batch. (Note: Same (2), reduce the time of holding resources) (4). Use a lower isolation level. (Note: Use a lower isolation level (for example, read-committed) to hold shared locks more quickly than with higher isolation levels (such as serializable), reducing lock contention) (5). Use row versioning-based isolation levels: 2005 for transactions that support snapshot transaction isolation and specify read_committed isolation level using line versioning, you can minimize the chance of deadlock between read and write operations:

Transactions and locks for databases