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

Today, the editor gives you a brief introduction about the transaction isolation of MySQL. Many people don't know much about it. Today, in order to let you know more about the transaction isolation of MySQL, I summarized the following contents for you. Let's look down together. I'm sure you'll get something.

Introduction of transactions

A transaction is a set of atomic sql queries, or a separate unit of work. In short, all statements within a transaction either succeed or fail.

In Mysql, transaction support is implemented at the engine level, but not all Mysql engines support transactions. For example, MyISAM engines do not support transactions, which is one of the important reasons why MyISAM is replaced by InnoDB.

When it comes to transactions, we are sure to think of ACID:

Atomicity (Atomicity)

Consistency (Consistency)

Isolation (Isolation)

Persistence (Durability)

Isolation level

When multiple transactions in the database are executed at the same time, problems such as dirty reads, non-repeatable reads, and phantom reads may occur, because there is the concept of transaction isolation level.

The SQL standard is defining four isolation levels:

READ UNCOMMITTED (read not submitted)

Changes in a transaction, even if they have not been committed, are visible to other transactions. Transactions can read uncommitted data, also known as Dirty Read.

READ COMMITTED (submit for read)

After one transaction is committed, the changes made can be seen by other transactions. This level is also called non-repeatable, because if you execute the same query twice in a transaction, you may get different results.

REPEATABLE READ (repeatable)

During the execution of a transaction, it is always consistent with the data that the transaction sees when it starts. Of course, at this level, uncommitted data changes are not visible to other transactions.

SERIALIZABLE (serializable)

For the same row of records, both write and read will be locked. When there is a read-write lock conflict, the later accessed transaction must wait for the completion of the previous transaction before it can continue execution, which will lead to a large number of timeout and lock contention problems.

In implementation, a view will be created in the database, and the logic of the view will prevail when accessing it.

At the repeatable isolation level, this view is created when the transaction is started and is used throughout the transaction.

At the isolation level of read commit, this view is created when the sql statement starts to execute.

Under the read uncommitted isolation level, the latest value on the record is returned directly, without the concept of view.

Under the serialized isolation level, parallel access is avoided directly by locking.

The way to configure is to set the startup parameter transaction-isolation to the desired isolation level.

View the current settings:

Mysql > show variables like 'transaction_isolation' +-+-+ | Variable_name | Value | +-+-+ | transaction_isolation | REPEATABLE-READ | +- -+-+ 1 row in set (0.00 sec)

In short, existence is reasonable, different isolation levels are suitable for different scenarios, and we should decide according to the business scenario.

Implementation of transaction isolation

In Mysql, the update of each record also records a rollback operation, and the latest value on the record can get the value of the previous state through the rollback operation.

The system automatically determines that the rollback log will be deleted when there are no more transactions to roll back the log.

Why not recommend long transactions:

A long transaction means that there will be an old transaction view in the system, and because these transactions can access any data in the database at any time, the rollback records that may be used in the database must be retained before the transaction is committed. this takes up a lot of storage space. At the same time, long transactions also take up lock resources, which may also drag down the entire library.

The way in which the transaction starts

Explicitly start the transaction statement, begin or start transaction, commit is commit, rollback with rollback.

Set autocommit = 0, this command turns off the autocommit of the thread, which means that if you execute a select statement, the transaction starts and does not commit automatically until you actively execute commit or rollback, or disconnect.

Personal advice is to explicitly start the transaction in the first way to avoid the occurrence of long-term transactions.

In the case of set autocommit = 1, transactions that are explicitly started with begin commit transactions if commit is executed. If commit work and chain is executed, the transaction is committed and the next transaction is started automatically, which also saves the overhead of executing the begin statement again.

Query long transactions:

The following statement queries for transactions with a duration of more than 60s

Mysql > select * from information_schema.innodb_trx where TIME_TO_SEC (timediff (now (), trx_started)) > 60 sec

To sum up, we use as few long transactions as possible in the development process, and if it is unavoidable, ensure that the logical log space is large enough and support the growth of dynamic log space. Monitor Innodb_ trx table and find long transaction alarm.

The above is a brief introduction to the transaction isolation of MySQL, of course, the detailed use of the above differences still have to be used by everyone to understand. If you want to know more, welcome to 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.