ACID Properties of transactions

The four properties of a transaction acid is the atomicity of the transaction (atomicity), consistency (consistency), isolation (isolation), persistence (durability.

Atomic Sex (atomicity)
Atomicity means that a transaction is an indivisible unit of work, and that the operations in the transaction either occur or do not occur.

Consistency (consistency)
The transaction must transform the database from one consistency state to another consistent state.

Isolation (Isolation)
The isolation of a transaction is when multiple users access the database concurrently, the transaction that the database opens for each user is not interfered by the operational data of the other transaction, and the multiple concurrent transactions are isolated from each other.

Persistence (Durability)
Persistence means that once a transaction is committed, its changes to the data in the database are permanent and should not be affected even if the database fails.

Isolation level of the transaction

L When multiple threads open the data in the respective transaction operations database, the database system is responsible for isolating operations to ensure the accuracy of each thread in obtaining the data.

If you do not consider isolation, the following issues may be raised:

1. Dirty reading:

• refers to a transaction that reads data that is not committed by another transaction.

This is very dangerous, assuming a transfer to B 100 yuan, the corresponding SQL statement as follows

1.updateaccount set money=money+100 while name= ' B ';

2.updateaccount set money=money-100 while Name= ' a ';

When the 1th SQL finished, 2nd has not been implemented (a did not submit), if the B query their own account, you will find that they have more than 100 yuan of money. If a waits for B to go and then rolls back, B will lose 100 yuan.

2, not repeatable READ:

• read a row of data from a table in one transaction and read the results multiple times differently.

For example, the bank would like to query a account balance, the first query a account for 200 yuan, at this time a to the account to deposit 100 yuan and submitted, the bank then proceeded to a query, at this time a account for 300 yuan. The Bank two times query inconsistent, may be very confused, do not know which query is accurate.

• The difference between dirty reads is that dirty reads are dirty data that is not committed before a transaction is read, and that non-repeatable reads re-read the previous transaction's committed data.

Many people think that this situation is right, do not need to be confused, of course, the back of the prevail. We can consider such a situation, for example, the bank program needs to output the results of the query to the computer screen and write to the file, the result in a transaction for the output of the destination, the two query inconsistent, resulting in the file and screen results inconsistent, the bank staff do not know which is whichever.

3. Virtual reading (phantom reading)

• Refers to data that is inserted into another transaction within a transaction, resulting in inconsistent read and backward reading.

• such as C deposit 100 yuan has not been submitted, when the Bank makes statement statistics Account table All users of the total of 500 yuan, and then C submitted, then the bank to find accounts for more than 600 yuan, resulting in false reading will also make the bank at a loss, in the end is whichever.

Therefore, it is necessary to set the appropriate isolation level for a transaction under certain circumstances.

The isolation level of a transaction is set as follows:

Serializable: Can avoid dirty read, not repeatable read, the occurrence of virtual read. (serializable) (serialized)

REPEATABLE READ: Prevents dirty reads, non-repeatable reads from occurring. (Repeatable Read)

Read Committed: Prevents dirty reads from occurring (Read committed).

READ UNCOMMITTED: The lowest level, the above situation can not guarantee. (Read not submitted)

Set TRANSACTION isolation Level setting transaction isolation Levels

select@ @tx_isolation Query The current transaction isolation level

Using isolation levels correctly and understanding the four attributes of a transaction can help us better solve practical problems with our business.