Shulou(Shulou.com)05/31 Report--

Editor to share with you the isolation level of MySQL transactions and examples of dirty reading, phantom reading, and unrepeatable reading. I believe most people don't know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Recommended (free): mysql video tutorial

Isolation of transactions

MySQL is a client / server architecture software, for the same server, there can be several clients connected to it, each client connected to the server, it can be called a Session. Each client can issue a request statement to the server in its own session, and a request statement may be part of a transaction, that is, for the server, multiple transactions may be processed at the same time. When multiple transactions on the database are executed at the same time, the problems of Dirty Read, Non-Repeatable Read and Phantom Read may occur. In order to solve these problems, there is the concept of "isolation level".

In theory, when a transaction accesses a certain data, other transactions should be queued, and when the transaction commits, other transactions can continue to access the data. But in general, the tighter the isolation, the less efficient it will be. So most of the time, we have to find a balance between isolation and efficiency.

Problems encountered in concurrent execution of transactions

Dirty Read: dirty reading is when one transaction reads data modified by another uncommitted transaction.

If there is a balance of 100 in Xiao Wang's account, there are two transactions to access Xiao Wang's account.

Session A session Bbegin

Update xxx set balance = balance+50 where client_no = 'Xiao Wang customer account'; begin

Select balance from xxx where client_no = 'Xiao Wang customer number'

(if it reaches 150, it means a dirty reading has occurred.) rollback;commit

As above, session An and session B each opened a transaction. Session A first added 50 to Xiao Wang's account balance. At this time, account B queried Xiao Wang's account balance of 150, and then session A rolled back. The 150 queried by session B becomes an incorrect dirty data.

Unrepeatable read (Non-Repeatable Read): unrepeatable read means that the same data set is read many times in the same transaction, but the result is different. Non-repeatable reading occurs because the data queried during multiple searches has been modified by other transactions.

Look at the following two session requests.

Session A session Bbegin

Select balance from xxx where client_no = 'Xiao Wang customer number'

(read that the balance is 100) begin

Update xxx set balance = balance+50 where client_no = 'Xiao Wang customer number'

Commit;select balance from xxx where client_no = 'Xiao Wang customer number'

(if you read 150, it means that non-repeatable reading has occurred.)

Commit

In the same transaction of session A, the results of the two same queries are different, which means that unrepeatable reads have occurred.

Phantom Read: when a transaction reads records in a certain range, another transaction inserts a new record in that range, and when the previous transaction reads the records in that range again, it will read the data that has not been read before.

If only Xiao Wang has a balance of 100 in the account table, take a look at the following two session requests.

Session A session Bbegin

Select name from xxx where balance = 100

(read that name is' Xiao Wang') begin

Insert into xxx (client_no,name,balance) values ('Xiao Zhang customer number', 'Xiao Zhang', 100)

Commit;select name from xxx where balance = 100

(if you read 'Xiao Wang' and 'Xiao Zhang', it means that a phantom reading has occurred.)

Commit

Session A transaction in the second query, found the first query did not find the name 'Xiao Zhang', which means that there is a phantom reading.

Four isolation levels established by the SQL standard

The ISO and ANIS SQL standards define four transaction isolation levels: read uncommitted (read uncommitted), read commit (read committed), repeatable read (repeatable read), and serialization (serializable).

Let's take a look at the meaning of these four levels of isolation.

Read uncommitted: when a transaction is not committed, its changes can be seen by other transactions.

Read commit: after a transaction commits, its changes will not be seen by other transactions.

Repeatable: the data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, under the repeatable readable isolation level, uncommitted changes are not visible to other transactions.

Serialization: as the name implies, for the same row of records, "write" adds "write lock" and "read" adds "read lock". When there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

According to the SQL standard, concurrent transactions can have different severity problems according to different isolation levels, as follows:

(√ means it can happen; x means it can't happen)

Isolation level dirty read non-repeatable read uncommitted (read uncommitted) √√√ read commit (read committed) × √√ repeatable read (repeatable read) × × √ serialization (serializable) × ×

MySQL support for four isolation levels

Although the ISO and ANIS SQL standards define four transaction isolation levels, not all database vendors follow these standards. For example, Oracle databases do not support read uncommitted (read uncommitted) and repeatable read (repeatable read) transaction isolation levels.

The MySQL InnoDB storage engine supports four isolation levels, but unlike those defined in the SQL standard, the InnoDB storage engine uses the Next-Key Lock lock algorithm under the default repeatable read transaction isolation level to avoid phantom reads. In other words, the InnoDB storage engine can fully guarantee the isolation requirements of transactions under the transaction isolation level of repeatable read, that is, it meets the requirements of serializable isolation level in the SQL standard.

How to set the isolation level of a transaction

In the InnoDB storage engine, you can use the following command to set the transaction isolation level for the global or current session:

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

To set the isolation level of the current session to read commit, you can use the following statement:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

If you want to set the default isolation level for transactions when the MySQL database starts, you need to change the value of transaction-isolation in the configuration file. For example, if we specify transaction-isolation = READ COMMITTED before startup, the default isolation level for transactions changes from the original REPEATABLE READ to READ COMMITTED.

To view the transaction isolation level of the current session, you can use the following statement:

SELECT @ @ transaction_isolation

To view the global transaction isolation level, you can use the following statement:

SELECT @ @ global.transaction_isolation

Note: transaction_isolation was introduced in the version of MySQL 5.7.20 to replace tx_isolation. If you are using a previous version of MySQL, please replace the transaction_isolation used above with tx_isolation.

Isolation of transactions

MySQL is a client / server architecture software, for the same server, there can be several clients connected to it, each client connected to the server, it can be called a Session. Each client can issue a request statement to the server in its own session, and a request statement may be part of a transaction, that is, for the server, multiple transactions may be processed at the same time. When multiple transactions on the database are executed at the same time, the problems of Dirty Read, Non-Repeatable Read and Phantom Read may occur. In order to solve these problems, there is the concept of "isolation level".

In theory, when a transaction accesses a certain data, other transactions should be queued, and when the transaction commits, other transactions can continue to access the data. But in general, the tighter the isolation, the less efficient it will be. So most of the time, we have to find a balance between isolation and efficiency.

Problems encountered in concurrent execution of transactions

Dirty Read: dirty reading is when one transaction reads data modified by another uncommitted transaction.

If there is a balance of 100 in Xiao Wang's account, there are two transactions to access Xiao Wang's account.

Session A session Bbegin

Update xxx set balance = balance+50 where client_no = 'Xiao Wang customer account'; begin

Select balance from xxx where client_no = 'Xiao Wang customer number'

(if it reaches 150, it means a dirty reading has occurred.) rollback;commit

As above, session An and session B each opened a transaction. Session A first added 50 to Xiao Wang's account balance. At this time, account B queried Xiao Wang's account balance of 150, and then session A rolled back. The 150 queried by session B becomes an incorrect dirty data.

Unrepeatable read (Non-Repeatable Read): unrepeatable read means that the same data set is read many times in the same transaction, but the result is different. Non-repeatable reading occurs because the data queried during multiple searches has been modified by other transactions.

Look at the following two session requests.

Session A session Bbegin

Select balance from xxx where client_no = 'Xiao Wang customer number'

(read that the balance is 100) begin

Update xxx set balance = balance+50 where client_no = 'Xiao Wang customer number'

Commit;select balance from xxx where client_no = 'Xiao Wang customer number'

(if you read 150, it means that non-repeatable reading has occurred.)

Commit

In the same transaction of session A, the results of the two same queries are different, which means that unrepeatable reads have occurred.

Phantom Read: when a transaction reads records in a certain range, another transaction inserts a new record in that range, and when the previous transaction reads the records in that range again, it will read the data that has not been read before.

If only Xiao Wang has a balance of 100 in the account table, take a look at the following two session requests.

Session A session Bbegin

Select name from xxx where balance = 100

(read that name is' Xiao Wang') begin

Insert into xxx (client_no,name,balance) values ('Xiao Zhang customer number', 'Xiao Zhang', 100)

Commit;select name from xxx where balance = 100

(if you read 'Xiao Wang' and 'Xiao Zhang', it means that a phantom reading has occurred.)

Commit

Session A transaction in the second query, found the first query did not find the name 'Xiao Zhang', which means that there is a phantom reading.

Four isolation levels established by the SQL standard

The ISO and ANIS SQL standards define four transaction isolation levels: read uncommitted (read uncommitted), read commit (read committed), repeatable read (repeatable read), and serialization (serializable).

Let's take a look at the meaning of these four levels of isolation.

Read uncommitted: when a transaction is not committed, its changes can be seen by other transactions.

Read commit: after a transaction commits, its changes will not be seen by other transactions.

Repeatable: the data seen during the execution of a transaction is always the same as the data seen when the transaction is started. Of course, under the repeatable readable isolation level, uncommitted changes are not visible to other transactions.

Serialization: as the name implies, for the same row of records, "write" adds "write lock" and "read" adds "read lock". When there is a read-write lock conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

According to the SQL standard, concurrent transactions can have different severity problems according to different isolation levels, as follows:

(√ means it can happen; x means it can't happen)

Isolation level dirty read non-repeatable read uncommitted (read uncommitted) √√√ read commit (read committed) × √√ repeatable read (repeatable read) × × √ serialization (serializable) × ×

MySQL support for four isolation levels

Although the ISO and ANIS SQL standards define four transaction isolation levels, not all database vendors follow these standards. For example, Oracle databases do not support read uncommitted (read uncommitted) and repeatable read (repeatable read) transaction isolation levels.

The MySQL InnoDB storage engine supports four isolation levels, but unlike those defined in the SQL standard, the InnoDB storage engine uses the Next-Key Lock lock algorithm under the default repeatable read transaction isolation level to avoid phantom reads. In other words, the InnoDB storage engine can fully guarantee the isolation requirements of transactions under the transaction isolation level of repeatable read, that is, it meets the requirements of serializable isolation level in the SQL standard.

How to set the isolation level of a transaction

In the InnoDB storage engine, you can use the following command to set the transaction isolation level for the global or current session:

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

To set the isolation level of the current session to read commit, you can use the following statement:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

If you want to set the default isolation level for transactions when the MySQL database starts, you need to change the value of transaction-isolation in the configuration file. For example, if we specify transaction-isolation = READ COMMITTED before startup, the default isolation level for transactions changes from the original REPEATABLE READ to READ COMMITTED.

To view the transaction isolation level of the current session, you can use the following statement:

SELECT @ @ transaction_isolation

To view the global transaction isolation level, you can use the following statement:

SELECT @ @ global.transaction_isolation

Note: transaction_isolation was introduced in the version of MySQL 5.7.20 to replace tx_isolation. If you are using a previous version of MySQL, please replace the transaction_isolation used above with tx_isolation.

These are all the contents of the article "MySQL transaction isolation levels and examples of dirty reading, phantom reading, and unrepeatable reading". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.