Database transaction characteristics and isolation Levels

1. Definition of a transaction

A database transaction is a logical unit in the execution of a database management system, and a limited sequence of database operations is completed. For example, a transaction is the following sequence of operations: "A account is remitted to a B account".

A. Read the balance from the a account.
B. Do a subtraction operation on the a account.
C. Write the results back to the a account.
D. Read the balance from the B account.
E. Do addition operations on B accounts.
F. Write the results back to the B account.

2. Characteristics of the transaction (ACID)

There are four characteristics of transactions, namely atomicity (Atomic), consistency (consistency), isolation (isolation), persistence (durability).

Atomicity (Atomic):

The operations contained in a transaction are considered to be a logical unit in which the operations in the logical unit are either all successful or all failed to roll back. The atomicity of a transaction is also reflected in the transaction's reading of the data, for example, the result of multiple reads of a transaction on the same data item must be the same.

Consistency (consistency):

Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution. For example, assuming that both user A and User B have a total of 5000, then no matter how much money is transferred between A and B, a couple of times, the sum of two users should be 5000 when the transaction ends, which is the consistency of the transaction.

Sometimes this consistency is guaranteed by the internal rules of the database, such as the type of data must be correct, the data value must be within the specified range, and so on.
Other times this consistency is guaranteed by the application, for example, * * The bank account balance cannot be negative in general, and credit card consumption cannot exceed the credit limit of the card. **

Isolation (Isolation):

Transactions allow multiple users to concurrently access the same data without destroying the correctness and integrity of the data.
At the same time, the modification of parallel transactions must be independent of the modifications of other parallel transactions.
The isolation of a transaction is generally controlled by the lock of the transaction.

Many times a database executes multiple transactions concurrently, and each transaction may need to modify and query multiple tables, while more query requests may be executed. The database needs to ensure that every transaction is not visible to other transactions until all its modifications have been completed.

In other words, you cannot let other transactions see the middle state of the transaction, for example, by transferring from bank account A to account B, you cannot allow other transactions (such as account inquiries) to see that the a account has been deducted from a but the B account has not increased the status of a.

Persistence (Durability):

After the transaction has ended, the result of the transaction must be cured, even if there are various exceptions to the system.

That is, once a transaction is committed, the change to the data in the database is permanent, even if the database system encounters a failure, it will not lose the commit transaction operation.

3. Isolation level of the database

In a multi-threaded concurrency environment, when multiple threads are opening data in a transactional operations database, the database system is able to isolate operations to ensure that each thread obtains the accuracy of the data. Otherwise, the following issues may occur:

• Dirty Read

Dirty reads refer to the data in another uncommitted transaction that is read in one transactional process.

When a transaction is modifying a data multiple times, and when many of the modifications in the transaction are not committed, a concurrent transaction accesses the data, resulting in inconsistent data from two transactions.

• Non-REPEATABLE READ

Non-repeatable reading means that in the case of a data in a database, multiple queries within a transaction scope return different data values, because at the query interval, the data is modified and committed by another transaction.

The difference between non-repeatable reads and dirty reads is that a dirty read is a transaction that reads dirty data that is not committed by another transaction, and non-repeatable reads read the data submitted by the previous transaction.

• Phantom Read (virtual Read)

Phantom reading is a phenomenon that occurs when a transaction is not executed independently. For example, a transaction T1 a data item from "1" to "2" for all rows in a table, and the transaction T2 inserts a row of data items into the table, and the value of this data item is "1" and is submitted to the database. The user of the operation transaction T1, if you look at the data you just modified, will find that there is another line that has not been modified, in fact, this line is added from the transaction T2, as if the illusion, this is the occurrence of Phantom read.

Both Phantom and non-repeatable reads read another transaction that has been committed (the dirty read is different), and the difference is that the non-repeatable read query is the same data item, and the Phantom read is for a batch of overall data.

Next look at the four isolation levels provided by MySQL:

1. Serializable (serialization): Can avoid dirty reading, non-repeatable reading, the occurrence of phantom reading
2. REPEATABLE READ (Repeatable Read): Can avoid dirty read, non-repeatable read occurrence
3. Read Committed (reading committed): prevents dirty reads from occurring
4. READ UNCOMMITTED (unread): lowest level, unavoidable

The top four levels are serializable, the lowest is read uncommitted, and the higher the level, the lower the execution efficiency. The default level for MySQL is read committed.

It is important to note that these four levels require that when a transaction is in a write operation, no other transaction is allowed to write, which can be achieved through an "exclusive write lock".

Implementing these four isolation levels requires different types of locks, this article only cares about transaction characteristics and isolation levels, and on how to implement isolation levels, will be explored in subsequent articles.

Database transaction characteristics and isolation Levels