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

This article will explain what MySQL affairs are for you in detail. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

MySQL transactions and transaction isolation levels

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 have to delete the basic information of the person, as well as the information related to that person, such as mailboxes, articles, etc., these database operation statements constitute a transaction (recommended course: MySQL tutorial)

Only databases or tables that use the Innodb database engine in MySQL support transactions

Transaction processing 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 meet four conditions: atomicity, consistency, isolation, and persistence

Atomicity: all operations in a transaction, either all or none, will not end in the middle. An error in the execution of a transaction will be rolled back to the state before the start of the transaction

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 serialization 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 reading uncommitted content, reading committed content, repeatable readable and serializable

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

Under the default setting of the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is executed immediately after the SQL statement is executed. So to explicitly start a transaction, you need to use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable autocommit using the current painting

Transaction control statement:

BEGIN or START TRANSACTION; explicitly open a transaction

COMMIT; can also use COMMIT WORK, which is equivalent. COMMIT commits the transaction and makes all changes made to the database permanent

ROLLBACK; can also use ROLLBACK WORK, which is equivalent. A rollback ends the user's transaction and reverses any uncommitted changes in progress

SAVEPOINT identifier;SAVEPOINT allows you to create a SavePoint in a transaction, and a transaction can have multiple SAVEPOINT

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

ROLLBACK TO identified; rolls back the transaction to the marked point

SET TRANSACTION; is used to set the isolation level of the transaction. The InnoDB storage engine provides transaction isolation levels such as READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ and SERIALIZABLE

There are two main methods of MySQL transaction processing:

Using BEGIN,ROLLBACK,COMMIT to realize

BEGIN starts a transaction

ROLLBACK transaction rollback

COMMIT transaction confirmation

Directly SET to change the auto-commit mode of MySQL:

SET AUTOCOMMIT=0 forbids automatic submission

SET AUTOCOMMIT=1 enables auto-submission

One of the four characteristics of transactions: isolation

There is a certain isolation between transaction An and transaction B.

Read uncommited read not submitted

At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications. Reading uncommitted data is called dirty data.

Read COMMIT

The default isolation level for most database systems (but not MySQL). A transaction can only see the changes that have been submitted to the firm. It avoids dirty reading, but it still has the problems of unrepeatable reading and phantom reading.

Repeatable read

The default level of MySQL; ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. It avoids dirty reading and unrepeatable reading, but it leads to another problem: phantom reading. Phantom reading means that when a user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, the user will find new phantom rows. InnoDB and Falcon storage engine solve this problem through multi-version concurrency control (MVCC) mechanism.

The MVCC mechanism is used under the repeatable isolation level, and the select operation does not update the version number, but is a snapshot read (historical version); insert,update and delete update the version number, which is the current read (current version)

Serializable

The highest isolation level solves the problem of phantom reading by forcing the ordering of transactions so that they cannot conflict with each other. In short, it adds a shared lock to each read row of data. At this level, it can lead to a lot of timeouts and lock competition.

Set transaction isolation level

Set in the my.cnf file

-READ-UNCOMMITTED- READ-COMMITED- REPEATABLE-READ- SERIALIZABLE* for example [mysqlId] transaction-isolation = READ-COMMITTED

Set the isolation level dynamically through the command

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL where isolation-level can be:-READ UNCOMMITTED- READ COMMITTED- REPEATABLE READ- SERIALIZABLEGLOBAL | SESSION indicates the scope of transaction isolation level: GLOBAL: valid for all sessions SESSION: valid for current session transaction concurrency problem

Dirty read: transaction A reads the data updated by transaction B, and then B rolls back the operation, then the data read by An is dirty data.

Can not be read repeatedly: transaction A reads the same data many times, and transaction B updates the data and does not commit during the process of transaction A reading many times. As a result, transaction A reads the same data many times, resulting in inconsistent results.

Phantom reading: the number of result data read before and after is inconsistent. This is because transaction B inserts or deletes the table during multiple reads of transaction A.

So much for sharing about what MySQL transactions are. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.