[Database Transactions and locks]: a thorough understanding of database transactions

Note: This article is reproduced from http://www.hollischuang.com/archives/898

Transaction

Business (Transaction), generally refers to the things to do or do. In computer terminology, it refers to a program execution unit (unit) that accesses and possibly updates various data items in a database. In computer terminology, transactions are usually referred to as database transactions.

Concept

A database transaction typically contains a sequence of operations that read or write to the database. Its existence consists of the following two purposes:

1. Provides a way for database operations to recover from a failure to a normal state, while providing a way for the database to remain consistent even in an abnormal state.
2. When multiple applications access the database concurrently, an isolation method can be provided between these applications to prevent interference with each other's operations.

When a transaction is submitted to the DBMS (database management system), the DBMS needs to ensure that all operations in the transaction are completed successfully and that the results are persisted in the database, and that if none of the operations in the transaction are completed successfully, all operations in the transaction need to be rolled back to the state before the transaction is executed (or fully executed). , or none), while the transaction has no effect on the execution of the database or other transactions, and all transactions appear to be running independently.

But in reality, the risk of failure is high. During the execution of a database transaction, it is possible to encounter a transaction failure, a database system/operating system failure, or even a failure of the storage media. This requires the DBMS to perform a recovery operation on a failed transaction, restoring its database state to a consistent state (the consistent state of the data is guaranteed). To achieve the ability to restore the database state to a consistent state, the DBMS typically needs to maintain the transaction log to track all operations in the transaction that affect database data.

Characteristics

Not an arbitrary sequence of operations on a database is a database transaction. A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties.

Atomicity (atomicity): A transaction is executed as a whole, and the operations contained therein are either completely executed or not executed.
Consistency (consistency): a transaction should ensure that the state of the database transitions from one consistent state to another consistent state. The meaning of a consistent state is that the data in the database should satisfy the integrity constraints.
Isolation (Isolation): When multiple transactions are executing concurrently, the execution of one transaction should not affect the execution of other transactions.
Persistence (Durability): Once a transaction is committed, his modifications to the database should be persisted in the database.

Example

An example of how to use a common "a account to remit money to a B account" shows how to guarantee the accuracy and completeness of data through database transactions. Familiarity with relational database transactions requires 6 actions from account A to account B:

1. Read the balance from the A account (500).
2, to a account to do subtraction operation (500-100).
3. Write the results back to the A account (400).
4. Read the balance from the B account (500).
5, to the B account to do the addition operation (500+100).
6. Write the results back to the B account (600).

Atomic nature:

Ensure that 1-6 of all processes are either executed or not executed. Once a problem occurs during the execution of a step, a rollback operation is required. If the B account is suddenly unavailable (such as being logged out) at the fifth step, all previous operations should be rolled back to the state before the transaction was executed.

Consistency

Before the transfer, the accounts of A and B have 500+500=1000 dollars. After the transfer, the accounts of A and B have 400+600=1000 yuan. That is, the state of the data changes from one state to another after the transaction operation has been performed. At the same time, consistency can guarantee that the account balance will not become negative.

Isolation of

In the whole process of a to B transfer, as long as the transaction has not been submitted (commit), query A and B accounts, the amount of money in two accounts will not change.
If a transfer to B at the same time, there is another transaction executed C to B transfer operations, then when the two transactions are finished, b account of the money should be a to B money plus C to B money plus their original money.

Durability

Once the transfer is successful (transaction submission), the money inside the two accounts will change really (the data will be written to the database for persistent saving)!

Atomicity and Isolation lines

Consistency and atomicity are closely related, atomic destruction can lead to inconsistent database, the consistency of data is not all related to atomicity.
For example, in the fifth step, the addition of the B account is only added 50 yuan. The process can be atomic, but there is a problem with data consistency.

Therefore, the atomicity and consistency of the transaction are indispensable.

[Database Transactions and locks]: a thorough understanding of database transactions