Four characteristics of database transactions and the isolation level of transactions (MySQL)

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 the operations contained in a transaction are either all successful or fail back, which is the same concept as the previous two blogs about transactions, 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.

⑵ 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.

Take the transfer, assume that user A and User B both the sum of money together is 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction two users of the sum should be 5000, this is the consistency of the transaction.

⑶ 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 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 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 yuan to User B, corresponding to the SQL command 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 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 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 obtain different results, sending a non-repeatable read.

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, virtual reading (phantom reading)

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 already been committed (this is different from the dirty Read), and the difference is that the non-repeatable read query is the same data item, and the Phantom Read is for a whole group of data (such as the number of data).

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.

View the isolation level of the current transaction in the MySQL database:

    SELECT @ @tx_isolation;

Set the isolation level of the transaction in the MySQL database:

    Set  [Glogal | session]  TRANSACTION ISOLATION level isolation class name;    Set tx_isolation= ' isolation level name; '

Example 1: View the isolation level of the current transaction:

　　

Example 2: Set the isolation level of a transaction to the READ UNCOMMITTED level:

　　

Or:

　　

Remember: Setting the isolation level of the database must be before opening the transaction!

If you are using JDBC to set the isolation level for a transaction on a database, it should be before you call the Setautocommit (false) method of the Connection object. You can set the isolation level of the current link by calling the settransactionisolation of the Connection object, and the field of the connection object, as with the parameter level:

　　

To set the isolation level part of the code in JDBC:

　　

PostScript: The isolation level setting is only valid for the current link. For a MySQL command window, a window is equivalent to a link, and the isolation level set by the current window is only valid for transactions in the current window; for the JDBC operations database, a Connection object is equivalent to a link, The isolation level set for the connection object is only valid for the connection object, regardless of the other linked connection objects.

Four characteristics of database transactions and the isolation level of transactions (MySQL)