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

This article mainly explains "the concept of MySQL transaction and the introduction of transaction isolation level". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the concept of MySQL transaction and the introduction of transaction isolation level.

Let's first briefly introduce the concept of transaction in MySQL, which refers to the combination of SQL statements used to implement a certain behavior, either a single SQL statement or multiple statements. Like threads, MySQL servers allow multiple transactions to execute concurrently to update some data. For security, MySQL provides four isolation levels to limit interference between different transactions.

The four isolation levels are as follows (from low to high):

Read uncommitted (read unsubmitted)

Read committed (read submitted)

Repeatable read (repeatable)

Serializable (serialization)

Next, we will demonstrate separately in this order.

* * preparatory work * *

Since there is only one root user on my local server, in order to demonstrate later, I need to create a new user testB and grant the appropriate permissions. For convenience, the transactions opened by root and testB below are called An and B.

1. Read uncommitted (read unsubmitted)

Log in to the user root and testB respectively, and take the table t_ss as the experimental table. Through the query, you can see that the table t_ss is empty.

Set the isolation level for root and testB to read uncommitted (read uncommitted), respectively.

Then open transactions An and B in root and testB, respectively, and insert a piece of data into table t_ss in B. At this time, the transaction in B has not yet been committed (commit). If we look up the table in A, we can see that even if the transaction in B has not been committed, the data inserted by transaction B can be queried in A.

At this point, everyone should be able to understand the meaning of read uncommitted (read uncommitted). Generally speaking, it is that even if transaction B does not have commit, other transactions can read the updated data of transaction B, that is, it can read the data that has not been committed by transaction. This is the lowest level of transaction isolation.

2. Read committed (read submitted)

Set the transaction isolation level for root and testB to read committed (read committed).

Also open two transactions, and insert another piece of data in B, at this time, B transaction is not committed, when querying in A, it is found that the newly inserted data of B is not queried.

After commit transaction B, A successfully reads the data inserted by B.

To sum up, at the read committed (read committed) level, other transactions can only read data committed by transaction B, but not if it is not committed. This is the default isolation level for most databases.

3. Repeatable read (repeatable)

Set the transaction isolation level to repeatable read (repeatable readable) and open the transaction separately.

A looks up the table first, there are two pieces of data, then B inserts one piece of data and commit,A checks the table again, the result is still two pieces of data, but the result of looking up the table in B is three pieces of data. (the arrow indicates the order in which the command is executed)

That is, when the database is in repeatable read (repeatable readable), the data read before and after the same transaction must be consistent, regardless of whether transaction B inserts the data or not. It is the default isolation level for MySQL.

4. Serializable (serialization)

Set the isolation level of root to Serializable (serialization) to start the transaction. When the table t_ss is queried in transaction An and there is no commit, transaction B waits until it reaches the specified time with an error and a timeout.

Transaction B can insert data successfully only after transaction A commits.

Serializable (serialization) is the highest and most demanding transaction isolation level, in which case other transactions must wait for the current transaction commit to execute.

At this point, I believe you have a deeper understanding of "the concept of MySQL transaction and the introduction of transaction isolation level". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.