Four characteristics of database transactions acid

This article tells the four characteristics (ACID) of transactions in the database, and will explain the isolation level of the transaction in detail.

If a database claims to support transactional operations, then the database must have the following four features:

⑴ atomicity (atomicity)

Atomicity means that all operations contained in a transaction are either all successful or fail back, so the operation of a transaction must be fully applied to the database if it succeeds, and it cannot have any effect on the database if the operation fails. In order to achieve atomicity, it is necessary to log all update operations to the data, if a part of the operation has been successful, but subsequent operations, due to power outages/system crashes/Other hardware and software errors can not continue, through the backtracking log, has performed a successful operation revoked, so as to achieve " All operations failed "purpose.

⑵ 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, and no intermediate state is allowed.

Example:

Take the transfer, assuming that user A has 1000 dollars, User B has 2000 dollars, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum of money should also be 3000, this is the consistency of the transaction.

Transaction inconsistency: (1) User A to User B to 500 dollars, at this time user a account minus 500 yuan, the remaining 500 yuan, but at this time suddenly the system crashes, User B did not have time to add 500, the database appeared inconsistent state.

(2) Transaction 1 needs to transfer 100 yuan to account a: first read the value of account A, and then add 500 to this value. However, between these two operations, another transaction 2 modifies the value of account A, adding $500 to it. So the final result should be an increase of 1000 yuan. In fact, after transaction 1 was finalized, account a only increased by $500, because the modification of transaction 2 was overwritten by transaction 1. This makes the data inconsistent after the transfer of three accounts.

In fact, atomicity, isolation, and persistence are all designed to ensure consistency of database data.

⑶ Isolation (isolation)

　　isolation is when multiple users concurrently access the database , such as when the same table operation, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

To achieve this effect: for any two concurrent transactions T1 and T2, in the case of transaction T1, the T2 either ends before the T1 starts, or starts after the T1 ends, so that every transaction does not feel that another transaction is executing concurrently.

The isolated database on transactions provides a variety of isolation levels, which are described later.

⑷ Persistence (durability)

Persistence refers to the fact that once a transaction is committed, changes to the data in the database are permanent, even if the database system encounters a failure, and the commit transaction is not lost.

For example, when we use the JDBC operation database, after committing the transaction method, prompt the user transaction operation completes, when our program execution completes until sees the prompt, can determine the transaction and the correct commit, even if this time the database has the problem, also must have our transaction complete execution, Doing so will cause us to see that the transaction is complete, but the database failed to perform a transaction because of a failure.

The above-mentioned four characteristics of the transaction (called acid), now focus on the isolation of the transaction, when multiple threads are open transaction operations database data, the database system to be able to isolate operations to ensure the accuracy of each thread to obtain data, in the introduction of the various isolation levels provided by the database, Let's take a look at some of the problems that can occur if you don't consider the isolation of a transaction:

1. Dirty reading

Dirty reads refer to the concurrency process in which data from another uncommitted transaction is read in one transaction.

When a transaction is modifying a data multiple times, and the changes in the transaction are not committed, then a concurrent transaction accesses the data, resulting in inconsistent data from two transactions. For example, user a transfers $100 to User B, and the corresponding SQL command is as follows:

Update account set money=money+100 where name= ' B ';  (At this point A Notice b) Update account set money=money-100 where name= ' A ';

When only the first SQL is executed, a informs B to view the account, B discovers that the money has been paid (that is, a dirty read occurs), and then regardless of whether the second SQL is executed, as long as the transaction is not committed, all operations will be rolled back, and when B sees the account again later, the money does not actually go.

2. Non-repeatable reading

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 the query interval is modified and committed by another transaction.

For example, when a transaction T1 reads a certain data, and the transaction T2 modifies the data immediately and commits the transaction to the database, the transaction T1 reads the data again to get different results, and non-repeatable reads occur.

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

In some cases, non-repeatable reading is not a problem, for example, we query a data multiple times and of course the results are the result of the final query. However, in other cases, problems can occur, for example, the same data A and B in turn, the query may be different, A and B may play up ...

3, Phantom read

Phantom reading is a phenomenon that occurs when a transaction is not executed independently. For example, transaction T1 queries how many records are in the entire table , and the transaction T2 inserts a row of data into the table. And the user of the operation transaction T1 to see how many rows of data in the whole table, will find a row of data, in fact, this line is the transaction T2 added, as if the illusion, this is the occurrence of Phantom read.

　　The difference between Phantom reads and non-repeatable reads is:

• The key to non-repeatable reading is the modification (update), which operates on a row of data and requires a lock line. The same condition, you read the data, read it again and found that the value is different.

• The focus of Phantom reading is to add or delete (delete), to manipulate the entire table, and to lock the table. The same conditions, the 1th and 2nd readings of the number of records are not the same

　　

Now look at the four isolation levels that the MySQL database provides to us:

①serializable (serialization): Can avoid dirty reading, non-repeatable reading, the occurrence of phantom reading.

②repeatable Read (Repeatable Read): Can avoid dirty read, non-repeatable read occurrence.

③read committed (Read Committed): Can avoid the occurrence of dirty reading.

④read UNCOMMITTED (READ UNCOMMITTED): lowest level, no case is guaranteed.

The top four isolation levels are the serializable level, the lowest is the read uncommitted level, and the higher the level, the lower the efficiency of execution. A level like serializable is a lock table (similar to a lock in Java Multi-threading) so that other threads can only wait outside the lock, so what isolation level to choose should be based on the actual situation. The default isolation level in the MySQL database is repeatable read (repeatable read).

In the MySQL database, the above four isolation levels are supported, the default is repeatable read (repeatable read), and in the Oracle database, only the serializable (serialization) level and Read Committed (Read Committed) levels are supported. The default is the Read committed level.

Reference documents:

Four characteristics of database transactions and the isolation level of transactionsHow is the atomicity and consistency of database transactions implemented?

Four characteristics of database transactions acid