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

This article will explain in detail the sample analysis of MySQL transactions for everyone. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

ACID properties

Transaction processing is a management mechanism for MySQL operations that must be executed in batches. During the transaction process, unless all batch operations are executed correctly, any operation in the middle will be rolled back to the original safe state to ensure that no incorrect changes are made to the system data.

In the previous article we mentioned that after MySQL 5.5, the default storage engine was replaced from MyISAM to InnoDB. One of the important reasons for this is that InnoDB supports transactions. Let's use SHOW ENGINES to look at the descriptions of various storage engines in MySQL.

The four most important characteristics of a transaction are often referred to as ACID characteristics

Atomicity: A transaction is an indivisible smallest unit in which all operations either succeed or fail, with no intermediate states. Atomicity is mainly realized through the undo log in the transaction log. When a transaction modifies the database, InnoDB generates an undo log of the opposite operation according to the operation. For example, for the insert operation, a delete record will be generated. If the transaction fails to execute or rollback is called, the state before execution will be restored according to the contents of the undo log.

C - Consistency: The data is legal and consistent before and after the transaction is executed. Even if an exception occurs, the integrity constraints of the database will not be broken due to the exception, such as uniqueness constraints.

I - Isolation: Each transaction is independent of each other and is not affected by the execution of other transactions, and transactions are invisible to other transactions until committed. Isolation is defined by the level of isolation of transactions, with locking mechanisms for write isolation and MVCC for read isolation, as described in more detail below.

D-Durability: Changes made to data after a transaction is committed are durable and will not be lost even if the database is down, guaranteed by redo logs in the transaction log. Before the transaction is modified, the change information will be pre-written to the redo log. If the database is down, the records in the redo log will be read to recover the data after recovery.

transaction control syntax

MySQL transaction control has several important nodes, which are transaction initiation, commit, rollback and savepoint.

Start transaction represents the start of transaction execution, the statement is START TRANSACTION or BEGIN, commit transaction represents all updates in the transaction are written to the physical database of disk, the transaction is executed normally, the statement is COMMIT, if an exception needs to be rolled back, the statement is ROLLBACK. Note that once the transaction has committed, there is no rollback, so you need to execute rollback directly instead of commit when an exception is caught during code execution.

For example, A transfers 100 yuan to B:

//Execute normally, commit BEGIN; #Open transaction UPDATE account_balance SET balance = balance - 100.00 WHERE account_name ='A ';UPDATE account_balance SET balance = balance + 100.00 WHERE account_name ='B';COMMIT; #Submit transaction//Exception occurs, rollback BEGIN;#Open transaction UPDATE account_balance SET balance = balance - 100.00 WHERE account_name ='A ';UPDATE account_balance SET balance = balance + 100.00 WHERE account_name ='B';ROLLBACK; #Transaction rollback

In complex scenarios, sometimes we do not need to roll back the entire operation, but batch execution, rolling back to a certain node is good, equivalent to nesting several sub-transactions under a large transaction, in MySQL can use the reservation point SAVEPOINT to achieve.

BEGIN;insert into user_tbl (id) values (1) ;insert into user_tbl (id) values (2) ;ROLLBACK; #1, 2 neither write BEGIN;insert into user_tbl (id) values (1) ;SAVEPOINT s1;insert into user_tbl (id) values (2) ;ROLLBACK TO s1; #Rolls back to reservation point s1, so 1 writes successfully, 2 is rolled back, and the final result is 1RELEASE SAVEPOINT s1; #Releases reservation point

By the way, there are implicit transactions (autocommit) and explicit transactions (must be manually committed). MySQL defaults to implicit transactions, which are automatically committed and controlled by the autocommit parameter.

#VIEW VARIABLES SHOW LIKE 'autocommit';+--------------+------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-----+#Autocommit ON (default) SET autocommit = 1;#Autocommit OFF SET autocommit = 0;

In autocommit mode, if there are no open transactions displayed, then each statement is a transaction, and the system automatically performs a commit operation on each sql. Autocommit remains disabled after a transaction is started with BEGIN or START TRANSACTION, and reverts to its previous state after the transaction is ended with COMMIT or ROLLBACK.

There is another parameter completion_type for transactions, which defaults to 0 (NO_CHAIN)

#VIEW VARIABLES SHOW LIKE 'completion_type';+-----------------| Variable_name | Value |+-----------------+----------+| completion_type | NO_CHAIN |+-----------------+----------+

completion_type = 0: Default, new transactions will not be automatically opened after commit.

completion_type = 1: When commit is executed, it is equivalent to COMMIT AND CHAIN, which automatically opens a transaction with the same isolation level.

completion_type = 2: Commit is equivalent to COMMIT AND RELEASE, which automatically disconnects the server after committing the transaction.

transaction concurrency exception

In the actual production line environment, there may be large-scale concurrent requests. If the isolation level of transactions is not properly set, some exceptions may occur. The most common exceptions are Dirty Read, Phantom Read and Unrepeatable Read.

dirty reads

Dirty read refers to a transaction accessing uncommitted data from another transaction, as follows:

Suppose a has a value of 1 and transaction 2 changes a to 2 before the transaction is committed

At this point, transaction 1 reads a and reads a value of 2. Transaction 1 reads complete.

As a result, transaction 2 rolls back the modification (or uncommitted) to a, so that the value of a goes back to 1.

This results in the fact that a has a value of 1, but transaction 1 gets a result of 2, so transaction 1 reads dirty data and a dirty read occurs.

unrepeatable reading

Non-repeatable reading refers to the process of reading the same data multiple times in a transaction, the content of the data value has changed, resulting in no way to read the same value, describing the phenomenon of update/delete for the same piece of data, as follows:

Transaction 1 reads a, where a = 1

Transaction 2 modifies a to 2, commits successfully, and the change takes effect.

Transaction 1 reads a again, where a = 2

Transaction 1 reads the same value twice in the same transaction, but the content of the data value has changed, and an unrepeatable read occurs.

phantom reading

Magic read refers to a transaction read the same data multiple times in the process, the global data (such as the number of data rows) has changed, as if an illusion, describes the phenomenon of insert/delete for the whole table, the following process:

Transaction 1 reads the quantity for the first time and gets 10 pieces of data

At this point transaction 2 inserted a piece of data and successfully committed, the change takes effect, the data becomes 11

Transaction 1 reads the quantity again and gets 11 pieces of data. For transaction 1, there is one more piece inexplicably, as if an illusion has occurred. Phantom reading occurs.

Or in another scenario, for example, for fields with uniqueness constraints (such as id), the following process occurs:

To insert a record with id = 5 in transaction 1, query the database first, and find that there is no data with id = 5, so it can be inserted normally.

At this point transaction 2 inserts a piece of data id = 5.

When transaction 1 inserts id = 5, it is found that there is an error in uniqueness conflict. For transaction 1, it is like seeing a ghost. I have just checked if there is no such thing. Why is there another one at this time?

transaction isolation level

Serialized transaction processing is of course the most secure, but serial can not meet the needs of high concurrent access to the database, as a compromise, sometimes have to reduce the isolation standard of the database in exchange for the concurrency of transactions, through the sacrifice of correctness in a controllable range in exchange for efficiency, this trade-off through the isolation level of transactions to achieve.

The database has four transaction isolation levels, from low to high, Read uncommitted, read Committed, repeatable Read, serialized.

Read Uncommitted

Uncommitted content is allowed to be read. Queries at this level are not locked, so dirty reads, non-repeatable reads, and phantom reads are all possible.

Read Submitted Read Submitted

Only committed content is allowed to be read, and queries at this level will not have dirty reads, because dirty data is uncommitted data, so it will not be read, but non-repeatable reads and phantom reads may still occur.

Repeatable Read (MySQL default isolation level)

Row-level locking is used to ensure that the data obtained by two queries of a transaction under the same query conditions are consistent. Dirty reads and non-repeatable reads can be avoided, but there is no way to avoid phantom reads.

It is important to note that Innodb provides a stable view through MVCC under Repeatable Read, so the first scenario in the above phantom read exception does not occur under RR isolation level of Innodb, but the second scenario still occurs.

Serializable

Using table-level locks to ensure serialization of all transactions prevents all exceptions, but at the expense of concurrency.

The command to view isolation levels is

SHOW VARIABLES LIKE 'transaction_isolation';#OR SELECT @@global.tx_isolation, @@tx_isolation;

The second way is to view the isolation levels of the global and current sessions.

The command to set the isolation level is

#Set the isolation level of the current session to READ UNCOMMITTED SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #Set the global isolation level to READ UNCOMMITTED SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; About "MySQL transaction sample analysis" This article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please 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.