MySQL Transaction Definition of Transaction

A Transaction is a unit of program execution that accesses and updates a database; A transaction may contain one or more SQL statements, all of which are executed or none of which are executed.

Transaction processing has a wide range of applications in a variety of management systems, such as personnel management systems, many synchronous database operations most need to use transaction processing. For example, in the personnel management system, you delete a personnel, you need to delete the basic information of personnel, also want to delete and the personnel related information, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

Delete the SQL statement
delete from userinfo where ~~~
delete from mail where ~~
delete from article where~~
~ ~
If there is no transaction, and in the process of your deletion, suppose something goes wrong and only the first sentence is executed, then the consequences are unimaginable!

But with transactions. If a deletion fails, rollback is all you need to do to undo the deletion.

Generally speaking, in the business level application, must consider the transaction processing!

MySQL supports transaction storage engines such as InnoDB and NDB Cluster, among which InnoDB is the most widely used. Other storage engines do not support transactions, such as MyIsam, Memory, etc.

Transaction characteristics

  • A minimum non-subdividable unit of work; Usually one transaction corresponds to a complete business (such as a bank account transfer business, which is a minimum unit of work)

  • A complete business requires a batch of DML(INSERT, Update, DELETE) statements combined to complete

  • Transactions are only related to DML statements, or DML statements have transactions. This is related to the business logic, and the number of DML statements varies depending on the business logic

First, let’s clarify the relevant knowledge involved in the transaction:

Transactions should have ACID characteristics

ACID is a Durable, Durable, Durable, Durable, Durable, Durable, Durable, Durable, Durable, Durable, Durable, Durable, Durable.

In general,
Transactions must meet four conditions (ACID)
  • Atomicity (Autmic): A transaction is the smallest unit and cannot be divided. That is, the statements that make up a transaction form a logical unit, and no part of it can be executed. Transactions in the execution, to do “all or nothing!” That is to say, the department is not allowed to share the implementation. Rollback will eliminate database impacts even if a transaction cannot complete due to a failure.

    For example, in a bank transfer process, the transfer amount must be deducted from one account at the same time and added to the other account, and it is not reasonable to change only one account.

    Atomicity is guaranteed by Undo log. Undo Log saves the previous record of each change, allowing you to roll back in the event of an error.

  • Consistency: The database is consistent before and after a transaction is executed. Transactions require that all DML statements must either succeed or fail at the same time. Transaction operations should make the database transition from one consistent state to another!

    For example: online shopping, you only let the goods out of the warehouse, and let the goods into the shopper’s shopping basket to constitute a transaction!

    For example, in a bank transfer, either the transfer amount is transferred from one account to another, or both accounts remain the same, nothing else.

    Consistency is guaranteed by atomicity, isolation, and persistence

  • Isolation: One transaction has no effect on the other. That is, transaction A and transaction B are isolated. If multiple transactions are executed concurrently, they should behave as if they were executed independently! This means that it is impossible for any transaction to see a transaction in an incomplete state.

    Isolation is the pursuit of mutual non-interference between concurrent transactions.

    For example, during a bank transfer, another transfer transaction can only wait until the transfer transaction is committed.

    Isolation is guaranteed by MVCC(Multi-Version Concurrency Control) and Lock(Lock mechanism)

  • “Durability”: The effects of transactions can be permanently saved. Conversely, transactions should be able to withstand all failures, including server, process, communication, media, and so on. A successfully executed transaction is persistent to the database and should be able to recover even if the database fails! Persistence is a guarantee of a transaction, a sign of the end of a transaction (data in memory persists to a disk file).

    For example, during a bank transfer, the state of the account must be preserved after the transfer.

    Persistence is guaranteed by the Redo Log. The Redo Log is written to the B+ tree only when the Redo Log is written successfully. If the Redo Log is disconnected before the Redo Log is written, the Redo Log can be restored

InnoDB stores two types of transaction logs:

  • Redo log: Used to ensure transaction persistence

  • Undo Log: The foundation of transaction atomicity and isolation

The undo log is the key to atomicity. It is the ability to undo all successfully executed SQL statements when a transaction is rolled back.

InnoDB rolls back using undo log:

  • InnoDB generates an undo log when a transaction changes the database.

  • If the transaction fails or rollback is called, and the transaction needs to be rolled back, you can use the information in the undo log to rollback the data to what it was before the change.

An undo log is a logical log that records information about SQL execution.

When a rollback occurs, InnoDB will do the reverse of what it did before based on the undo log:

  • For each INSERT, a delete is performed on the rollback.

  • For each DELETE, an INSERT is performed on the rollback.

  • For each update, a reverse update is performed to change the data back.

Take an update operation for example: When a transaction performs an update, the undo log it generates contains information about the primary keys of the rows that were modified (so that it knows which rows were modified), which columns were modified, and the values of those columns before and after the change. This information can be used when rolling back to restore the data to the pre-update state.

Background to the redo log

InnoDB is the storage engine for MySQL, and the data is stored on disk, but if you need disk I/O every time you read and write the data, the efficiency is very low.

To do this, InnoDB provides a Buffer Pool, which contains the mapping of some of the data pages on the disk as a Buffer to access the database:

  • When data is read from the database, it is first read from the Buffer Pool. If it is not in the Buffer Pool, it is read from disk and put into the Buffer Pool.

  • When data is written to the database, it is first written to the Buffer Pool, and the modified data from the Buffer Pool is periodically flushed to disk (a process known as grubbing).

The use of the Buffer Pool has greatly improved the efficiency of reading and writing data, but it also brings a new problem: if MySQL goes down and the modified data in the Buffer Pool has not been flushed to disk, the data will be lost and the transaction persistence will not be guaranteed.

The redo log was introduced to solve this problem: when data is modified, in addition to the data in the Buffer Pool, the operation is recorded in the Redo log. When a transaction is committed, the fsync interface is invoked to flush the redo log.

If the MySQL database is down, the redo log can be read during the restart to restore the database.

The redo log uses a write-ahead logging (WAL). All changes are written to the log and then updated to the Buffer Pool. This ensures that data is not lost due to MySQL outage, thus meeting persistence requirements.

Since the redo log also writes logs to disk at transaction commit time, why is it faster than writing modified data from the Buffer Pool directly to disk (i.e. brushing dirty)?

There are two main reasons as follows:

  • Brushwork is a random I/O operation, because the data is changed randomly, but writing the redo log is an append operation, which is a sequential I/O operation.

  • The default Page size is 16KB. Every small change on a Page is written to the entire Page. The redo log contains only the part that really needs to be written, and invalid IO is greatly reduced.

Redo log and binlog

As we know, there are also binary logs in MySQL that can also record write operations and be used for data recovery, but they are fundamentally different.

Different effects:

  • The redo log is used for crash recovery to ensure that MySQL outage does not affect persistence.

  • Binlogs are used for point-in-time recovery to ensure that the server can recover data based on the point in time. Binlogs are also used for master/slave replication.

Different levels:

  • The Redo log is implemented by the InnoDB storage engine,

  • Binlog is implemented in the server layer of MySQL and supports InnoDB and other storage engines.

Different contents:

  • The redo log is a physical log that contains disk-based pages.

  • Binlog is a logical log containing rows of SQL.

Different timing of writing:

  • The redo log can be written at multiple times. As mentioned earlier, fsync is called to flush the redo log when a transaction commits; This is the default policy and can be changed by changing the innodb_flush_LOG_AT_trx_COMMIT parameter, but the persistence of the transaction is not guaranteed.

In addition to transaction commit, there are other times to flush the redo log. For example, master threads flush the redo log once per second.

  • Binlog is written at transaction commit time.

There are four levels of transaction isolation

Among the four characteristics of ACID, the most difficult to understand is not consistency, but transaction isolation. Database experts have developed four levels of transaction isolation for transaction isolation. The four transaction isolation levels are designed to solve the problems caused by high concurrency (dirty read, non-repeatable read, magic read).

Concurrency consistency problem

  • Dirty read: for example, if there are two concurrent transactions on the same database record, transaction A can read uncommitted data from transaction B. For example, if transaction B operated on the database but did not commit the transaction, A read the data for which transaction B did not commit. This is the embodiment of dirty reading.

  • Non-repeatable reads: For example, when transaction A reads the same record multiple times in the same transaction, transaction B modifs the data being read by transaction A and commits the transaction, but transaction A reads the data committed by transaction B, resulting in data inconsistency between the two reads.

  • Magic read: Transaction A sets all data in the data table to 100, and transaction B inserts A data with A value of 200 and commits the transaction. After transaction A completes the modification and commits the transaction, it finds another data with A value of less than 100. This is a form of hallucination.

“Dirty reads”, “non-repeatable reads”, and “magic reads” are actually database read consistency problems, which must be solved by the database to provide a certain transaction isolation mechanism.

Transaction Isolation level

  • Uncommitted read (RU), when a transaction is not committed, its changes can be seen by other transactions.

    Why dirty reads occur: RU’s principle is to lock the rows of each update statement, not the entire transaction, so dirty reads occur. RC and RR lock the entire transaction.

  • Committed read (RC). After a transaction commits, its changes are not seen by other transactions.

    RC generates a new Read View each time it executes the SQL statement. It reads a different View each time. RR transactions use the same Read View from beginning to end.

  • Repeatable read (RR), the data seen during the execution of a transaction, is always the same as the data seen at the start of the transaction. Of course, at the repeatable read isolation level, uncommitted changes are also invisible to other transactions.

    By default, the MVCC mechanism (Consistent Non-locked read) ensures that the RR level is correctly isolated and is not locked. Select XXXX for update (exclusive lock); Select XXXX lock in share mode. By using locks, you can avoid phantom reads at the RR level. Of course, the default MVCC reads can also avoid phantom reads.

  • Serializable, where all transactions are executed one after the other to avoid phantom reads. For lock-based concurrency control databases, serializable requires that a range lock be acquired when executing a range query. If the database is not lock-based concurrency control, If a transaction that violates serial operation is detected, the transaction needs to be rolled back.

    As the name implies, for the same line of records, “write” will add “write lock”, “read” will add “read lock”. When a read/write lock conflict occurs, the subsequent transaction must wait for the previous transaction to complete before continuing.

The more stringent the transaction isolation level, the more computer performance is consumed and the less efficient it is.

The stricter the transaction isolation of the database, the smaller the concurrency side effects, but also the greater the cost, because transaction isolation is essentially “serialization” of transactions to a certain extent, which is obviously contradictory to “concurrency.” At the same time, different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to “non-repeatable reads” and “magic reads” and may be more concerned with the ability to concurrently access data.

The default data isolation level used by Mysql is REPEATABLE READ. MySql REPEATABLE READ level does not cause magic reads.

The REPEATABLE READ level can cause magic reads. However, Mysql uses THE MVCC mechanism to ensure no magic reads when default SELECT is used.

You can also use locks. When using locks, such as for Update (X lock), lock in share mode (S lock), MySql will use next-key lock to ensure that magic reads do not occur.

The former is called snapshot read and the latter is called current read.

Mysql uses REPEATABLE READ reason by default

Before Mysql 5.0, binlog only supported STATEMENT format! The Read Commited isolation level is buggy, so Mysql uses Repeatable Read as the default isolation level!

What’s the bug? Read “What transaction Isolation level should mysql choose in Internet Projects”

Why not use Serializable by default? This isolation level is usually used when using mysql’s distributed transaction function, because every read is locked and snapshot reads fail.

Master-slave replication, what replication is it based on? Is based on binlog replication!

How many formats are there for binlog?

  1. Statement: Records the modification of SQL statements

  2. Row: Records changes to the actual data in each row

  3. Mixed: Indicates a mixture of statement and row modes

A sign that a transaction is open? A sign of the end of a transaction?

Enable flag: The execution of any DML statement (INSERT, UPDATE, DELETE) indicates that the transaction is enabled

End flag (commit or rollback) :

Commit: Successful completion, synchronizing all DML statement operation history with the underlying disk data at one time

Rollback: The end of the failure will clear all DML statement operation history

Things and database underlying data:

The DML statement does not change the underlying data during the process of the transaction, but records the historical operation and completes the record in memory. Only at the end of the transaction, and successfully, is the data in the underlying hard drive file modified.

What is a transaction and its controls?

Transaction consists of a series of task operations. These operations, which may be create update delete and so on, are represented as a specific result. Either it works or it doesn’t.

There are four types of control over transaction. One is commit, which means commit. One is rollback, or callback. The second is set Transaction, which gives the transaction a name. The other is save point. This control is used to set a point to go back and forth.

MVCC parsing

MVCC is a multi-version Concurrency Control protocol.

Consistent non-locked reads

A consistent nonlocking read means that the InnoDB storage engine reads the rows in the database at the current execution time by means of multiple version control (MVCC). If the row is in the middle of a DELETE or UPDATE operation, the read operation will not wait for the lock to be released, as with the XS lock. Instead, it will read a snapshot.

At transaction isolation levels RC and RR, InnoDB storage engines use non-locked consistent reads. However, the definition of snapshot data is different. At the RC level, the latest snapshot data of the locked row is always read in inconsistent reads. At the RR level, for snapshot data, inconsistent reads always read the version of row data at the beginning of the transaction.

As you can see, steps 1 and 2 are very easy to understand, but after inserting A new piece of data in transaction B in step 3, transaction A is still not found in step 4, which takes advantage of the MVCC features. After transaction B commits, the output of the step 5 query is different at the RC and RR isolation levels. The reason for this, as mentioned in another blog post, is that they create the ReadView at different times.

But the weird thing is that at step 6, transaction A updates A record that it can’t see, and then the query is able to retrieve it. This is confusing to a lot of people. Invisible doesn’t mean the record doesn’t exist, it just makes use of the visibility judgment to ignore it. After the update is successful, transaction A logs the Undo log of this record, and in the subsequent query, because it can see its change and this visibility judgment, it can query out naturally. There are a number of terms that need to be read in depth: Talk about the ACID properties of MySQL InnoDB storage engine transactions

RR uses the MVCC to solve dirty read, non-repeatable read, and phantom read problems.

MVCC is a variant of row-level locking that avoids locking in normal read situations and is therefore less expensive. Implementations vary, but generally non-blocking reads are implemented, with writes locking only necessary lines.

Characteristics of MVCC: At the same time, different transactions may read different data (that is, multiple versions)

The biggest advantage of MVCC is that read is not locked, so read and write do not conflict, and concurrency performance is good. InnoDB implements MVCC, where multiple versions of data can coexist, relying mainly on hidden columns of data (also known as token bits) and undo logs.

Hidden columns of data include the version number of the row’s data, the deletion time, the pointer to the undo log, and so on.

When reading data, MySQL can determine whether to roll back by hiding columns and find the undo log needed to roll back, thus implementing MVCC; The detail format for hidden columns is no longer expanded.

Consistency lock read

As mentioned earlier, under the default isolation level RR, InnoDB storage engines use consistent non-locked reads for SELECT operations. However, in some cases, users need to explicitly lock database reads to ensure the consistency of the data logic. InnoDB storage engine supports two consistent locking read operations for SELECT statements.

  • The SELECT… FOR UPDATE (X lock)


Dirty read

When transaction A reads Zhangsan’s balance at time T3, it finds that the data has been modified by other transactions and the status is uncommitted.

After reading the latest data, transaction A rolls back the data according to the undo log of the data to obtain the data before transaction B, thus avoiding dirty reads.

Unrepeatable read

When transaction A reads data for the first time on the T2 node, the version number of the data is recorded (the version number of the data is recorded in rows), assuming that the version number is 1; When transaction B commits, the version number of the row is increased, assuming version number 2.

When transaction A reads the data again on T5, it finds that the version number (2) of the data is greater than the version number (1) recorded during the first read. Therefore, the transaction will perform A rollback operation according to the undo log to obtain the data with the version number of 1, thus realizing repeatable read.

Phantom read

InnoDB’s RR avoids phantom reading through next-keylock mechanism.

Next – Keylock is a row lock, implementation equivalent to record lock + gap lock(gap lock); The feature is that it locks not only the record itself (record Lock), but also a range (Gap Lock).

Of course, we are talking about unlocked reads here: the next-key lock does not actually lock the data, but merely adds tags (such as the version number of the data) to the read data. Let’s call this the class next-key Lock mechanism for accuracy.

This will read 0 again at time T5

Summary: In summary, InnoDB implements RR, with locking mechanism, hidden columns of data, undo log and next-key lock class, to achieve a degree of isolation, can meet the needs of most scenarios.

However, it should be noted that although RR avoids phantom reading problems, it is not Serializable and cannot guarantee complete isolation.

MYSQL handles transactions in two main ways

Use begin,rollback,commit

  • Begin Starts a transaction
  • Rollback Rollback of transactions
  • Commit transaction validation

Change the auto-commit mode of mysql

MYSQL’s default transaction is auto-commit, which means you submit a QUERY and it executes it! That is, a transaction is opened and committed as soon as a DML statement is executed.

We can implement transactions by setting autoCOMMIT.

  • Set autocommit=0 Disables automatic commit

  • Set autocommit=1 Enable autocommit

Note that when you use set autoCOMMIT =0, all subsequent SQL transactions will be processed as transactions until you use either commit confirmation or rollback. Note that when you end this transaction, you also start a new transaction! The first method treats only the current one as a transaction!
Personally, I recommend using the first method!
Only INNODB and BDB tables in MYSQL support transaction processing! Other types are not supported! (Remember!)

Let’s take a look at some of the problems that can be dealt with in transactions:

Let’s take a look at the background of the problem: there is only one book (database number: 123) left to buy online, and two users make purchase requests for the book almost simultaneously. Let’s take a look at the process:

Before going into details, let’s look at the definition of the data table:


create table book ( book_id unsigned int(10) not null auto_increment, book_name varchar(100) not null, Book_number int(10) not null, book_price float(5, 2) not null, primary key (book_id) ) type = innodb; #engine = innodb


For user A, who moves slightly faster than user B, the purchase process triggers an action that looks something like this:


1. SELECT book_number FROM book WHERE book_id = 123;

If book_number is greater than zero, confirm the purchase and update book_number

2. UPDATE book SET book_number = book_number – 1 WHERE book_id = 123;

Acquisition success


For user B, his action is slightly slower than user A’s, and his purchase process triggers the same action as user A’s:


1. SELECT book_number FROM book WHERE book_id = 123;

At this point, a has just finished the first step and has not yet finished the second step, so book_number must be greater than zero

2. UPDATE book SET book_number = book_number – 1 WHERE book_id = 123;

Acquisition success


On the surface, it seems that both a and B’s operation succeeded. They both bought the book, but there was only one copy in stock. How could they both succeed? If you look at the contents of the table book_number, it has become -1, which of course is not allowed. (In fact, declaring such a column type should have an unsigned property so that it cannot be negative.)

Now that the problem statement is clear, let’s take a look at how to use transactions to solve this problem. Open the MySQL manual, and you can see that using transactions to protect your SQL execution is actually very simple. It is basically three statements: Start, commit, and rollback.


  • COMMIT: COMMIT can COMMIT the current transaction, making the change permanent

  • ROLLBACK: ROLLBACK can ROLLBACK the current transaction and cancel its changes

In addition, SET the AUTOCOMMIT = {0} | 1 can enable or disable the default AUTOCOMMIT mode, for the current connection.


Does that guarantee that our SQL statements are correct simply by wrapping them in transaction statements? For example:



SELECT book_number FROM book WHERE book_id = 123;

// …

UPDATE book SET book_number = book_number – 1 WHERE book_id = 123;



The answer is no. In this way, the problem still cannot be avoided. If you want to avoid such a situation, the actual situation should be as follows:



SELECT book_number FROM book WHERE book_id = 123 FOR UPDATE ;

// …

UPDATE book SET book_number = book_number – 1 WHERE book_id = 123;



If the transaction is not completely finished, then other transactions will use SELECT… A FOR UPDATE request is placed in a wait state until the previous transaction is complete, thus avoiding this problem. Note that if your other transactions are using SELECT statements without FOR UPDATE, you will not have this protection.

MySQL Transaction Operations

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # Create data table
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
Empty set(0.01 SEC) mysql > begin;# Start transactionQuery OK, 0 rows affected (0.00 SEC) mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01sec) mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 SEC) mysql> commit;# Commit transactionQuery OK, 0 rows affected (0.01sec) mysql> select * from runoob_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rowsin set(0.01 SEC) mysql > begin;# Start transaction
Query OK, 0 rows affected (0.00 sec)
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
mysql> rollback;   # the rollback
Query OK, 0 rows affected (0.00 sec)
mysql>   select * from runoob_transaction_test;   # Data not inserted because of rollback
| id   |
| 5    |
| 6    |
2 rows in set (0.01 sec)Copy the code

PHP + MySQL transaction

<? php$dbhost = 'localhost:3306'; // Mysql server host address$dbuser = 'root'; // Mysql user name$dbpass = '123456'; // Mysql user name and password$conn = mysqli_connect($dbhost.$dbuser.$dbpass);
if(! $conn )
    die('Connection failed:' . mysqli_error($conn)); } mysqli_query();$conn."set names utf8");
mysqli_select_db( $conn.'RUNOOB' );
mysqli_query($conn."SET AUTOCOMMIT=0"); Mysqli_begin_transaction (mysqli_begin_transaction)$conn); // Start the transaction definitionif(! mysqli_query($conn."insert into runoob_transaction_test (id) values(8)"))
    mysqli_query($conn."ROLLBACK"); // Determine if the execution failed to roll back}if(! mysqli_query($conn."insert into runoob_transaction_test (id) values(9)"))
    mysqli_query($conn."ROLLBACK"); } mysqli_commit();$conn); Mysqli_close (mysqli_close)$conn); ? >Copy the code

SQL > use AdoDB to manipulate MySQL transactions in PHP

In practical LAMP applications, PHP generally uses AdoDB to operate MySQL. The code for AdoDB is given below for your convenience.


// …

$adodb -> startTrans ();

// In fact, the query that getOne calls can also be placed directly in rowLock, just to demonstrate the effect is more obvious. $adodb -> rowLock ( ‘book’ , ‘book_id = 123’ ); $bookNumber = $adodb -> getOne ( “SELECT book_number FROM book WHERE book_id = 123” ); $adodb -> execute ( “UPDATE book SET book_number = book_number – 1 WHERE book_id = 123” ); $adodb -> completeTrans (); / /… ? >


$adodb->getOne(); $adodb->getOne(); $adodb->getOne(); $adodb->getOne(); Not all databases use the &”FOR UPDATE&” syntax FOR row locking. Sybase uses the &”HOLDLOCK&” syntax FOR row locking. Therefore, to keep your database abstraction layer portable, I recommend that you use rowLock FOR row locking. As for portability, I’ll leave it to AdoDB, well, that’s a bit of a stretch, so that’s it for today.


The attached:

AdoDB has a setTransactionMode() method that sets the transaction isolation level as follows:

SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode(“SERIALIZABLE”); $db->BeginTrans(); $db->Execute(…) ; $db->Execute(…) ; $db->CommiTrans();

$db->SetTransactionMode(“”); // restore to default $db->StartTrans(); $db->Execute(…) ; $db->Execute(…) ; $db->CompleteTrans();

Supported values to pass in:

* READ UNCOMMITTED (allows dirty reads, but fastest)

* READ COMMITTED (default postgres, mssql and oci8)

* REPEATABLE READ (default mysql)

* SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as ‘SNAPSHOT’ for mssql or ‘READ ONLY’ for oci8/postgres.

Zhou Junjun’s personal website will be updated at the source site. Please let me know if there is anything wrong with this article. Thank you very much!

Reference links:

MySQL — Transaction (Transaction)…

Three knowledge of MySql index, lock, affairs

The interviewer asks you: MYSQL affairs and isolation level, how to answer

Talk about MySQL lock

Is utter MySQL principle affairs and the realization of the ACID properties

MySQL transaction