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

Today, the editor shares with you what are the isolation levels of mysql transactions. I believe many people do not know much about them. In order to let you know more about the isolation levels of mysql transactions, I have summarized the following contents for you. Let's look down together. I'm sure you'll get something.

Isolation level of the transaction

In the database operation, in order to effectively ensure the correctness of concurrent reading data, the transaction isolation level is proposed.

The raising of a question

The database is to be shared and accessed by the majority of customers, so the following uncertainties are likely to occur in the process of database operation.

Update lost

Both transactions update a row of data at the same time, and one transaction's update to the data overwrites the other transaction's update to the data. This is because the system does not perform any locking operations, so concurrent transactions are not isolated.

Dirty reading

One transaction reads the result of an uncommitted data operation from another transaction. This is quite dangerous because it is likely that all operations will be rolled back.

Non-repeatable

Unrepeatable read (Non-repeatable Reads): a transaction reads the same row twice but gets different results.

This includes the following:

(1) Virtual reading: after transaction T1 reads some data, transaction T2 modifies it, and when transaction T1 reads the data again, it gets a different value from the previous one.

(2) Phantom Reads: the transaction makes two queries during the operation, and the result of the second query contains data that does not appear in the first query or is missing from the first query (the SQL statements of the two queries are not required here). This is because another transaction inserts data during the two queries.

Solution

In order to avoid the above situations, four transaction isolation levels are defined in the standard SQL specification, and different isolation levels handle transactions differently.

Unauthorized read

Also known as read uncommitted (Read Uncommitted): dirty reads are allowed, but updates are not allowed to be lost. If one transaction has already started writing data, the other transaction does not allow simultaneous write operations, but allows other transactions to read this row of data. This isolation level can be achieved through an exclusive write lock.

Authorized read

Also known as read commit (Read Committed): allows non-repeatable reads, but does not allow dirty reads. This can be achieved through "instant shared read locks" and "exclusive write locks". Transactions that read data allow other transactions to continue to access the row's data, but uncommitted write transactions will prevent other transactions from accessing the row.

Repeatable read (Repeatable Read)

Repeatable reads (Repeatable Read): disable non-repeatable and dirty reads, but sometimes phantom read data. This can be achieved through "shared read locks" and "exclusive write locks". Transactions that read data will prohibit writing transactions (but read transactions are allowed), and write transactions will prohibit any other transactions.

Serialization (Serializable)

Serializable: provides strict transaction isolation. It requires transaction serialization execution, and transactions can only be executed one after another, not concurrently. Transaction serialization cannot be achieved through row-level locks alone, and other mechanisms must be used to ensure that newly inserted data is not accessed by the transaction that has just performed the query operation.

The higher the isolation level, the better the integrity and consistency of the data, but the greater the impact on concurrency performance. For most applications, priority can be given to setting the isolation level of the database system to Read Committed. It can avoid dirty reading and has good concurrency performance. Although it can lead to concurrency problems such as unrepeatable reads, phantom reads, and second-class missing updates, in individual cases where such problems may occur, the application can use pessimistic or optimistic locks.

These are the details of the isolation level of mysql transactions, and are there any gains after reading them? If you want to know more about it, welcome to the industry information!

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.