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

Most people do not understand the knowledge points of this "how to understand MySQL transaction" article, so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to understand MySQL transaction" article.

A transaction refers to a logical set of operations in which the units that make up the set of operations either succeed or fail.

Four characteristics of transactions (ACID)

Atomicity (Atomicity): a thing is an indivisible unit, either all or none at all

Consistency: the data is in a legal state before and after the transaction is executed

Isolation: after a transaction is executed, the modification of the data is persistent and will not be affected by other operations or failures

Durability: multiple transactions executed concurrently without interfering with each other.

What if an operation of a thing as a whole (action A, B), action A, finished, action B, halfway through, and the execution process goes wrong? There is a rollback mechanism, and a log records these operations, recording the values before and after the data modification.

Dirty reading

Transaction An is modifying the data (but not committing), and transaction B reads the data here, and the operation read by transaction B is called a dirty read. Solution: lock the write operation. When transaction An is writing data, transaction B cannot read it.

Non-repeatable

Transaction A modifies the data and commits the data "name=L", and then transaction B reads the data, but at this time A feels that the modified data is not satisfied with "name=H" and continues to modify and commit, and transaction B reads it again and finds that, ah, the data is different. This is the problem of unrepeatable reading. Solution: while locking the write operation, the read operation is also locked. when transaction An is writing, transaction B cannot be read, and thing B is not allowed to write when transaction B is reading.

Illusory reading

Although the write and read operations have been locked, there will be a situation in which transaction A cannot modify the name while transaction B is reading, but transaction A can write another age. When transaction B reads the data again, it finds that there is an extra piece of data. Solution: can only be strictly serialized. (lowest concurrency and lowest efficiency, but the highest reliability of data)

It's easy to confuse unrepeatable and phantom readings here. In fact, you only need to understand that unrepeatable reading is to modify the data, the number of data remains unchanged; illusory reading is to add or delete data, the content of the data remains unchanged, and the number of data changes.

Isolation level of MySQL

Read unsubmitted (read-uncommitted): dirty reading, non-repeatable reading, non-repeatable reading (read-committed): unrepeatable reading, repeatable reading (repeatable-read): serialization of phantom reading (serializable): solve these three problems

The higher the isolation level of the transaction, the better, but the higher the isolation level of the transaction, the lower the concurrency and the lower the efficiency, the higher the reliability of the data.

Starting with MySQL8, query the isolation level with SELECT@@GLOBAL.transaction_isolation,@@transaction_isolation; (here is MySQL5)

Here we can see that both the global isolation level and the current session isolation level of MySQL are REPEATABLE-READ (repeatable). Different databases have different default isolation levels, and we can modify it ourselves.

You can modify the isolation level by using the following command (it is recommended that you modify the current session isolation level instead of the global isolation level):

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Note that if you only modify the isolation level of the current session, after changing the session, the isolation level will return to the default isolation level, so when we test, we can change the isolation level of the current session.

The above is about the content of this article on "how to understand MySQL affairs". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more related knowledge, please pay attention to 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.