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

This article is about how to achieve isolation in Mysql. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's follow the editor to have a look.

Mysql things

MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to that person, such as mailboxes, articles, etc., so that these database operation statements constitute a transaction!

In MySQL, only databases or tables that use the Innodb database engine support transactions. The native MyISAM engine does not support transactions.

Transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed.

Transactions are used to manage insert,update,delete statements.

Generally speaking, a transaction must satisfy four conditions (ACID): atomicity (Atomicity, or indivisibility), consistency (Consistency), isolation (Isolation, also known as independence), and persistence (Durability).

Atomicity: all operations in a transaction are either completed or not completed, and do not end at some point in the middle. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed.

Consistency: the integrity of the database is not compromised before the transaction starts and after the transaction ends. This means that the data written must fully comply with all the preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the scheduled work.

Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent data inconsistencies caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read commit (read committed), repeatable read (repeatable read) and serialization (Serializable).

Persistence: after the transaction is completed, the modification of the data is permanent, even if the system failure will not be lost.

Object control sentence

Things are used to manage updates, deletions, and insert statements. The commonly used object control statements are:

BEGIN explicitly starts a transaction

COMMIT commits the transaction and makes all changes made to the database permanent

ROLLBACK rollback ends the user's transaction and undoes any uncommitted changes that are in progress

SAVEPOINT identifier,SAVEPOINT allows you to create a SavePoint in a transaction, and there can be multiple SAVEPOINT in a transaction

RELEASE SAVEPOINT identifier deletes a SavePoint for a transaction, and executing the statement throws an exception when there is no SavePoint specified

ROLLBACK TO identifier rolls back the transaction to the marked point

SET TRANSACTION is used to set the isolation level of the transaction. The isolation levels of transactions provided by the InnoDB storage engine are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

Commonly used transaction processing statements:

BEGIN starts a transaction

ROLLBACK transaction rollback

COMMIT transaction confirmation

SET AUTOCOMMIT=0 forbids automatic submission

SET AUTOCOMMIT=1 enables auto-submission

Note here that AUTOCOMMIT is set to 0 or 1 according to the needs of your project.

Isolation of things

Going back to the question at the beginning of the article, there is a key with two results in the table, which is initially estimated to be the problem of isolation of things. The above briefly introduces things, as well as the four categories of thing isolation, which are described in detail here.

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".

Dirty reading: dirty reading means that when a transaction is accessing the data and has made changes to the data that have not yet been committed to the database, another transaction accesses the data and then uses the data

Unrepeatable: refers to reading the same data multiple times within a transaction. Before the transaction finishes, another transaction accesses the same data. Then, between the two reads in the first transaction, the data read by the first transaction may not be the same because of the modification of the second transaction. This happens that the data read twice in a transaction is different, so it is said to be unrepeatable. For example, an editor reads the same document twice, but the author rewrites the document between reads. When the editor reads the document for the second time, the document has changed. The original read cannot be repeated. This problem can be avoided if editors can read the document only after the author has finished writing it.

Illusion: a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies the data in a table, which involves all rows of data in the table. At the same time, the second transaction also modifies the data in the table, which inserts a row of new data into the table. In that case, later, the user who operates the first transaction will find that there are still rows of data in the table that have not been modified, as if there were hallucinations. For example, an editor changes a document submitted by the author, but when the production department merges its changes into the main copy of the document, it finds that the author has added unedited new material to the document. This problem can be avoided if no one can add new material to the document until the editors and production departments have finished working on the original document.

Before talking about the level of isolation, it is important to know that the tighter the isolation, the less efficient it will be. Therefore, most of the time, we need to find a balance between the two. SQL standard transaction isolation levels include read uncommitted (read uncommitted), read commit (read committed), repeatable read (repeatable read), and serialization (serializable):

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 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: 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.

Therefore, query the transaction isolation type of the miswritten table:

Mysql > SELECT @ @ tx_isolation tableName

The result is:

READ-COMMITTED

As you can see, the isolation type of the table is read commit, that is, the change needs to be committed before it can be seen by something else. The problem that causes this situation is that both results are written into the table. When a key in one of the results is written and not submitted, the second result is also inserted into the table directly from the condition of whether it can be inserted or updated without a query, resulting in repetitive insertion. The solution is to write the table of the two results in order, finish the first one, and then write the second, so that this problem does not occur.

Summary

1. The characteristics of transactions: atomicity, consistency, isolation, persistence

2. Problems that may occur when multiple transactions are executed at the same time: dirty reading, unrepeatable reading, and phantom reading.

3. Transaction isolation level: read uncommitted, read commit, repeatable read, serialization

4. The difference of different transaction isolation levels:

Read uncommitted: changes made by a transaction can be seen by other transactions before it is committed

Read commit: after a transaction commits, its changes can be seen by other transactions

Repeatable: the data seen during the execution of a transaction is consistent. Uncommitted changes are not visible to other transactions

Serialization: a read-write lock is added to a record. In the event of a conflict, the later accessed transaction must wait for the previous transaction to complete before it can continue execution.

5. Configuration method: startup parameter transaction-isolation

6. Implementation of transaction isolation: each record will record a rollback operation when it is updated. There can be multiple versions of the same record in the system, which is the multi-version concurrency control (MVCC) of the database.

7, transaction start mode: first, explicitly start the transaction statement, begin or start transaction, commit commit, roll back rollback; II, set autocommit=0, this command will turn off the automatic commit of this thread. In this way, as soon as a select statement is executed, the transaction starts and does not commit automatically until commit or rollback is actively executed or disconnected

8. If you consider one more interaction, you can use the commit work and chain syntax. Explicitly start the transaction with begin in the case of autocommit=1, and commit the transaction if commit is executed. If commit work and chain is executed, commit the transaction and automatically start the next transaction

The above is how to achieve thing isolation in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.