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

This article mainly introduces the concept of "MYSQL transaction and isolation level". In daily operation, I believe many people have doubts about MYSQL transaction and the concept of isolation level. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "MYSQL transaction and isolation level concept". Next, please follow the editor to study!

The concept of transaction

A transaction is a set of atomic SQL query statements that can also be thought of as a unit of work. If the database engine can successfully apply all query statements to the database, it will execute all queries, and if any query statement cannot be executed because of a crash or other reason, then none of the statements will be executed. That is, all or none of the statements within the transaction are executed.

Example: move the qualified records in table b to table a (similar statements have been tested at the command prompt)

Double-click the code 1 2 3 4 5start transaction;// to start a transaction insert into a select from b where with the start transaction statement; / / insert the eligible row records in the b table into the a table delete from b where...; / / delete the eligible row records in the b table commit; / rollback / / commit statement commits the entire transaction, permanently modifies the data, rollback statement rolls back the entire transaction, cancels the modifications made / / if the insert operation fails, then the delete operation will not be performed

ACID test

ACID: atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability). An effective transaction processing system must meet the relevant standards.

a. Atomicity: a transaction must be treated as a separate internal "indivisible" unit of work to ensure that the entire transaction is either fully executed or rolled back. When a transaction is atomic, it is never partially executed, either completely or not at all.

b. Consistency: the database always transitions from one consistency state to another. In the above example, consistency ensures that even if the insert or delete operation fails, the database will not have more or lost records. Because the final transaction is not committed at all, any data changes made during the transaction will not affect the contents of the database.

c. Isolation: the results of one transaction are not visible to other transactions until they are completed. In the above example, when the database has finished executing the insert statement and has not yet executed the delete statement, if another client's access to the database is also running at the same time, it will still be recorded in the b table as qualified.

d. Persistence: once a transaction commits, the data changes made by the transaction will be permanent.

This additional security measure in transaction processing causes the database server to do more extra work. In general, a database that supports ACID requires stronger CPU processing power, more memory and more disk space than databases that do not support this feature. This is the advantage of choosing the MYSQL storage engine architecture. Users can select the corresponding storage engine according to whether the application needs transaction processing or not. If the user does not need real transaction processing for some types of data queries, he can choose a non-transactional storage engine to implement the query to achieve higher processing performance. (the storage engine of our database is innoDB, which supports transaction processing, while the default storage engine of mysql is MyISAM engine, which does not support transactions.)

Isolation level

The SQL standard defines four types of isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not. Low-level isolation levels generally support higher concurrent processing and have lower system overhead.

READ UNCOMMITTED (read uncommitted)

At the READ UNCOMMITTED isolation level, all transactions can "see" the execution results of uncommitted transactions. At this level, a lot of problems may arise. This isolation level is rarely used in practical applications because its sexual performance is not much better than other levels, while other levels have other advantages. Read uncommitted data, also known as "Dirty Read"

READ COMMITTED (read submission)

The default isolation level for most database systems is READ COMMITTED (but this is not the default for MYSQL). It satisfies the previous simple definition of isolation: at the beginning of a transaction, you can only "see" the changes made by the committed transaction, and any data changes made by a transaction from the beginning to the commit are not visible unless it has been committed. This isolation level also supports so-called "unrepeatable reads (Nonrepeatable Read)". This means that the user runs the same statement twice and sees different results.

REPEATABLE READ (reread)

The REPEATABLE READ isolation level solves the problem caused by the READ UNCOMMITTED isolation level. It ensures that multiple instances of the same transaction "see the same" data rows when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the 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, another transaction inserts a new row in that range. when the user reads the range of data rows, they will find new "phantom" rows. InnoDB and Falcon storage engines solve the problem of phantom reading through multi-version concurrency control mechanism. REPEATABLE READ is the default transaction isolation level for MYSQL. Both the InnoDB and Falcon storage engines follow this setting.

SERIALIZABLE (serializable)

SERIALIZABLE is the highest level of isolation, which solves the problem of phantom reading by forcing transaction exclusion so that it is not possible to conflict with each other. In short, SERIALIZABLE is locking each row of data read. At this level, it can lead to a large number of timeouts and lock competition. It is rare to see users choose this isolation level.

Note: non-repeatable reading and phantom reading: the focus of non-repeatable reading is modification (the same query conditions, the data you have read, read again and find that the values are different); the focus of phantom reading is to add or delete (the same query conditions, the number of records read out for the first time and the second time is not the same)

View the transaction isolation level at the InnoDB system level:

Double-click the code and select 1mysql > SELECT @ @ global.tx_isolation

View the transaction isolation level at the InnoDB session level:

Double-click the code and select 1mysql > SELECT @ @ tx_isolation

Transactions in MySQL

AUTOCOMMIT (autocommit)

The default mode of operation for MySQL is AUTOCOMMIT mode. This means that unless a transaction is started explicitly, it will automatically execute each query as a separate transaction. In the current connection, AUTOCOMMIT mode can be enabled and disabled through variable settings.

Double-click the code to select all 1 2mysql > SHOW VARIABLES LIKE 'AUTOCOMMIT'; / / higher versions of mysql support mysql > select @ @ autocommit; / / mysql5.0 support

If you set AUTOCOMMIT=0, the user will remain in a transaction until the user executes a COMMIT or ROLLBACK statement, after which MYSQL will immediately start a new transaction.

Mixed use of storage engines in transactions

In a transaction, if a mixture of transactional and non-transactional tables is used, the result will be normal if the transaction goes well. However, if the transaction needs to be rolled back, changes made on the non-transactional table cannot be canceled. This will cause the database to be in a state of data inconsistency, in which it is difficult to recover the data and the transaction becomes pending.

Implicit and explicit locking

InnoDB uses a two-phase locking protocol (Two-Phase Locking Protocol two-stage locking protocol). A transaction can acquire locks at any time during execution, but they can be released only after a COMMIT or ROLLBACK statement has been executed. It releases all locks at the same time. The locking mechanisms described above are implicit locking. InnoDB automatically handles locks based on the user's isolation level. However, InnoDB also supports display locking.

At this point, the study of "the concept of MYSQL transaction and isolation level" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.