Shulou(Shulou.com)06/01 Report--

The following content mainly brings you the definition and analysis of MySQL transaction isolation level. the knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Four isolation levels of MySQL transaction basic elements of transaction (ACID) atomicity (Atomicity): after the transaction starts, all operations are either done or not done, and cannot be stuck in the middle. When an error occurs during the execution of the transaction, it is rolled back to the state before the start of the transaction, and all operations are as if they had not occurred. In other words, affairs are an indivisible whole, just like atoms learned in chemistry, are the basic units of matter. Consistency: the integrity constraints of the database are not broken before and after the transaction begins. For example, if A transfers money to B, it is impossible for A to deduct the money, but B does not receive it. Isolation: only one transaction is allowed to request the same data at a time, and there is no interference between different transactions. For example, An is withdrawing money from a bank card, and B cannot transfer money to this card until the withdrawal process of An is over. Durability: after the transaction completes, all updates to the database made by the transaction are saved to the database and cannot be rolled back. 2 concurrency of transactions

Dirty reading: transaction A reads the data updated by transaction B, and then B rolls back the operation, then the data read by An is unrepeatable: transaction A reads the same data many times, and transaction B updates and commits the data during transaction A's multiple readings, resulting in inconsistent results when transaction A reads the same data many times. Illusion: system administrator A changed the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserted a record of specific scores at this time. When system administrator A finished the change, he found that there was a record that had not been changed, as if there were hallucinations, which is called hallucination.

Summary: non-repeatable reading and phantom reading are easy to be confused, non-repeatable reading focuses on modification, and phantom reading focuses on adding or deleting. To solve the problem of unrepeatable reading, you only need to lock the rows that meet the conditions, and to solve the phantom reading, you need to lock the table.

3 four isolation levels of transactions: Dirty Read, non-repeatable read, NonRepeatable Read, Phantom Read, uncommitted, Read uncommitted, Read committed, Read committed, Repeatable read, Repeatable read, Serializable, Read Uncommitted: dirty reading is allowed That is, it is possible to read data commit reads (Read Committed) that have been modified by uncommitted transactions in other sessions: only committed data can be read. Most databases such as Oracle default to this level (non-repeatable) repeatable (Repeated Read): repeatable. All queries within the same transaction are consistent at the beginning of the transaction, the default level of InnoDB. In the SQL standard, this isolation level eliminates non-repeatable reads, but there are phantom read serial reads (Serializable): fully serialized reads, each read requires a table-level shared lock, and reads and writes block each other.

The SQL standard defines four isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not.

Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

According to the SQL:1992 transaction isolation level, InnoDB is REPEATABLE READ by default.

MySQL/InnoDB provides all four transaction isolation levels described by the SQL standard.

4 set default isolation level 4.1 query global and session transaction isolation level: specify the isolation level when SELECT @ @ global.tx_isolation;SELECT @ @ session.tx_isolation;SELECT @ @ tx_isolation;4.2 starts (temporarily effective)

Specify the isolation level with the-- transaction-isolation {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE} option when starting the mysql service on the command line.

4.3 profile addition (effective each time you restart)

Add the following settings in the [mysqld] section of the configuration my.cnf file:

Transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE} 4.4 client command line

Users can use SET TRANSACTION statements to change the isolation level for a single session or for all new connections. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Note: the default behavior (without session and global) is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point (except for those that do not exist). You need SUPER permission to do this. Use the SESSION keyword to set the default transaction level for future transactions executed on the current connection. Any client is free to change the session isolation level (even in the middle of the transaction) or set the isolation level for the next transaction.

5 level 1: Read Uncommitted (read uncommitted)

(1) all transactions can see the execution results of other uncommitted transactions

(2) this isolation level is rarely used in practical applications, because its performance is not much better than other levels.

(3) the problem caused by this level is dirty reading (Dirty Read): uncommitted data was read.

# first, modify the isolation level set tx_isolation='READ-UNCOMMITTED';select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | READ-UNCOMMITTED | +-+ # transaction A: start a transaction start transaction;select * from tx +-+-+ | id | num | +-+-+ | 1 | 1 | 2 | 2 | 3 | 3 | +-+-# transaction B: also starts a transaction (then the two transactions cross) and executes the update statement in transaction B without committing start transaction;update tx set num=10 where id=1;select * from tx +-+-+ | id | num | +-+-+ | 1 | 10 | 2 | 2 | 3 | 3 | +-+-# transaction A: can transaction A see the updated data at this time? select * from tx +-+-+ | id | num | +-+-+ | 1 | 10 |-- > you can see it! It means that we have read the data that transaction B has not yet committed | 2 | 2 | | 3 | +-+-+ # transaction B: transaction B is rolled back, but rollback;select * from tx is still uncommitted. +-+-+ | id | num | +-+-+ | 1 | 1 | 2 | 2 | 3 | 3 | +-+ # transaction A: the data seen in transaction An is also the data not committed by B select * from tx +-+-+ | id | num | +-+-+ | 1 | 1 |-- > dirty reading means that I am in this transaction (in A). Although transaction B is not committed, I can see any data changes in it! | | 2 | 2 | 3 | 3 | +-+-+ 6 level 2: Read Committed (read submission) |

(1) this is the default isolation level for most database systems (but not the default for MySQL)

(2) it satisfies the simple definition of isolation: a transaction can only see the changes made by the committed transaction.

(3) the problem with this isolation level is unrepeatable reading (Nonrepeatable Read): unrepeatable reading means that we may see different results when we execute exactly the same select statement in the same transaction.

|-- > the reasons for this may be as follows: (1) A crossed transaction has a new commit, resulting in a change in data; (2) when a database is operated by multiple instances, other instances of the same transaction may have a new commit during the processing of the instance.

# first modify the isolation level set tx_isolation='read-committed';select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | READ-COMMITTED | +-+ # transaction A: start a transaction start transaction;select * from tx +-+-+ | id | num | +-+-+ | 1 | 1 | 2 | 2 | 3 | 3 | +-+-# transaction B: also starts a transaction (so two transactions cross) to update data in this transaction without committing start transaction;update tx set num=10 where id=1;select * from tx +-+-+ | id | num | +-+-+ | 1 | 10 | 2 | 2 | 3 | 3 | +-+-# transaction A: can we see the data change in transaction An at this time? select * from tx -> +-+-+ | | id | num | | +-+-+ | | 1 | 1 |-- > cannot be seen! | | 2 | 2 | 3 | 3 | | +-+-+ |-- > the same select statement, but the result is different | # transaction B: what if transaction B is committed? | commit | # transaction A: | select * from tx | -> +-+-+ | id | num | +-+-+ | 1 | 10 |-- > because transaction B has been committed, we see a data change in A | 2 | 2 | | 3 | +-+-+ 7 level 3: Repeatable Read (readable)

(1) this is the default transaction isolation level for MySQL

(2) it ensures that when multiple instances of the same transaction read data concurrently, they will see the same data row.

(3) there may be a problem at this level-Phantom Read: when the user reads a range of data rows, another transaction inserts a new row in the range, and when the user reads the range of data rows, they will find a new "phantom" row.

(4) InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanism.

# first, change the isolation level set tx_isolation='repeatable-read';select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ # transaction A: start a transaction start transaction;select * from tx +-+-+ | id | num | +-+-+ | 1 | 1 | 2 | 2 | 3 | 3 | +-+-# transaction B: start a new transaction (so the two transactions intersect) update the data in transaction B and commit start transaction;update tx set num=10 where id=1;select * from tx +-+-+ | id | num | +-+-+ | 1 | 10 | 2 | 2 | 3 | 3 | +-+-+ commit;# transaction A: can A see the data change even though transaction B has been committed? Select * from tx;+-+-+ | id | num | +-+-+ | 1 | 1 |-- > still cannot be seen! (this level 2 is different, which also means that level 3 solves the problem of non-repeatable reading) | 2 | 2 | 3 | 3 | +-+-# transaction A: only when transaction An also commits can it see the data change commit;select * from tx +-+-+ | id | num | +-+-+ | 1 | 10 | 2 | 2 | 3 | 3 | +-+-+ 8 level 4: Serializable (serializable)

(1) this is the highest level of isolation

(2) it solves the problem of illusory reading by forcing the ordering of transactions to make it impossible for them to conflict with each other. In short, it adds a shared lock to each read row of data

(3) at this level, it may lead to a large number of timeouts and lock competition.

# first modify the isolation sector set tx_isolation='serializable';select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | SERIALIZABLE | +-+ # transaction A: start a new transaction start transaction;# transaction B: before A has no commit, this cross-transaction cannot change the start transaction of the data Insert tx values ('4cmpl. 4'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionupdate tx set num=10 where id=1

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

For the above definition and analysis of MySQL transaction isolation level, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.