Four characteristics of database transactions and the isolation level of transactions

1. Four characteristics of transactions in the database (ACID)

⑴ atomicity (atomicity) atomicity means that all operations contained in a transaction are either all successful or fail back, which is the same concept as the previous two blogs that describe a transaction, 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.　　⑵ Conformance (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 operating the same table, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions between the isolation of 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, a change to the data in the database is permanent, even if the database system encounters a failure and does not lose the commit transaction. 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 there is a problem with the database at this time, we have to complete our transaction completely, otherwise it will cause us to see that the transaction is completed, but the database does not perform a transaction because of a failure.

2. Inconsistent data issues:

1, dirty read dirty reads the data in another uncommitted transaction that is read in a single transaction 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 to User B, corresponding to the SQL command update accountSetmoney=money+ - whereName=' B '; (At this point a Notice b) Update accountSetMoney=money- - whereName=' 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 read refers to a transaction scoped multiple queries but returns different data values in the case of a data in the database, which is modified and committed by another transaction at the query interval.　　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) is a phenomenon that occurs when a transaction is not executed independently. For example, a transaction T1 a data item for all rows in a table from the "1"Modify to"2, the transaction T2 inserts a row of data items into the table, and the value of this data item is "1"and submit it 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). 

3. mysql four isolation levels

①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 you usually 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, with the default Read committed level.

Resources:

Http://www.cnblogs.com/fjdingsd/p/5273008.html

http://www.zhihu.com/question/23989904

Http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

Http://www.cnblogs.com/xdp-gacl/p/3984001.html

　　

　

Four characteristics of database transactions and the isolation level of transactions