Notes on MySQL basic knowledge (transaction)

Time:2020-3-31

PS: the knowledge content and materials collated in this article are all from the column “SQL must know must know” of geek time

The InnoDB engine of MySQL supports transactions, and MyISAM does not support transactions;

Transaction Basics

Four characteristics of transaction: Acid


  1. AThat is, atomicity. The concept of atom is indivisible. It can be understood as the basic unit of matter and the basic unit of data processing operations. In other words, it is either completely executed or not executed at all;
  2. C, consistency. Consistency means that the database will change from the original consistent state to another consistent state after transaction operation. That is to say, when the transaction is committed, or when the transaction is rolled back, the integrity constraint of the database cannot be broken;
  3. IIsolation. It means that each transaction is independent of each other and will not be affected by the execution of other transactions. In other words, a transaction is invisible to other transactions before it is committed;
  4. DIs durability. The modification of data after transaction submission is persistent. Even in case of system failure, such as system crash or storage medium failure, the modification of data is still effective. Because when the transaction is completed, the database log will be updated. At this time, the system can recover to the last successful update state through the log.

Persistence is guaranteed by transaction logs. The log includes rollback log and redo log. When we modify the data through transactions, we will first record the change information of the database to the redo log, and then modify the corresponding rows in the database. The advantage of doing this is that even if the database system crashes, the redo logs that have not been updated to the database system can be found and executed again after the database restarts, so that the transactions are durable.

Common operation statements of transactions


  1. Start transaction or begin is used to open a transaction explicitly.
  2. Commit: commit the transaction. When a transaction is committed, the changes to the database are permanent.
  3. Rollback or rollback to [savepoint], which means to rollback a transaction. This means undoing all uncommitted changes in progress or rolling back the transaction to a savepoint.
  4. Savepoint: create a savepoint in the transaction to facilitate subsequent rollback of the savepoint. There can be multiple savepoints in a transaction.
  5. Release savepoint: delete a savepoint.
  6. Set transaction to set the isolation level of the transaction.

There are two ways to use transactions, implicit and explicit. Implicit transactions are actually auto commit. Oracle does not automatically commit by default. It requires handwritten commit commands, while MySQL automatically commits by default. Of course, we can configure MySQL parameters:

The effect of completion’u type parameter on transaction in MySQL

  • Completion? Type = 0, which is the default. That is to say, when we execute commit, we will commit the transaction. When we execute the next transaction, we need to use start transaction or begin to start it.
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
Insert into test select 'off';
COMMIT;
Insert into test select 'Zhang Fei';
Insert into test select 'Zhang Fei';
ROLLBACK;
SELECT * FROM test;

Run result (1 row of data):
Notes on MySQL basic knowledge (transaction)

  • Completion_type = 1. In this case, when we commit a transaction, it is equivalent to executing a command chain, that is, to open a chain transaction, that is, when we commit a transaction, we will open a transaction with the same isolation level (the isolation level will be described in the next section).
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
Insert into test select 'off';
COMMIT;
Insert into test select 'Zhang Fei';
Insert into test select 'Zhang Fei';
ROLLBACK;
SELECT * FROM test;

Run results (2 rows of data):
Notes on MySQL basic knowledge (transaction)

  • Completion_type = 2, in this case, commit = commit and release, that is, when we commit, we will automatically disconnect from the server.

MySQL transaction isolation

Notes on MySQL basic knowledge (transaction)

  • The exceptions that can be solved by isolation level are shown in the following table:

Notes on MySQL basic knowledge (transaction)

Characteristics of three kinds of abnormal conditions:
1. Dirty read: read the uncommitted data of other transactions. (focusing on uncommitted data)
2. Non repeatable reading: when reading a certain data, it is found that the results of the two readings are different, that is, the same content is not read. This is because there are other transactions that modify or delete this data at the same time. (focus on data modification, update or delete)
3. Phantom reading: transaction a obtains n pieces of data according to the condition query, but at this time transaction B changes or adds M pieces of data that meet the query condition of transaction a, so that when transaction a queries again, N + M pieces of data will be found, resulting in phantom reading. (focus on data addition, insert)

The lower the isolation level, the greater the system throughput (concurrency), but also the greater the probability of abnormal problems. In the actual use process, we often need to weigh and choose between performance and correctness. There is no perfect solution, only fit or not.

The simulation of abnormal conditions will not be recorded

The implementation of MySQL transaction isolation level

Locks in MySQL


The implementation of isolation level is achieved by locking. In fact, locking is to ensure the consistency of data. When multiple threads access a certain data concurrently, especially for some sensitive data (such as order, amount, etc.), we need to ensure that at most one thread accesses this data at any time to ensure the integrity and consistency of data.

  • Optimistic lock

Optimistic locks are mostly implemented based on the data version recording mechanism, which generally adds a “version” field to the database table. When reading data, read out this version number together, and then add one to this version number when updating. At this time, the version data of submitted data will be compared with the current version information recorded in the database table. If the version number of submitted data is greater than the current version number of the database table, it will be updated. Otherwise, it will be considered as overdue data.

  • Pessimistic lock

Pessimistic lock depends on the lock mechanism provided by database. The shared lock and exclusive lock in MySQL are pessimistic locks. The addition, deletion and modification of database will add exclusive locks by default, while the query will not add any locks.

  • Shared lock (read lock, s lock)

Shared lock refers to sharing the same lock for a resource for multiple different transactions. If a shared lock is added to a resource, the resource can be read by itself. Other people can also read the resource (or add a shared lock, that is, the shared lock shares multiple memories), but it cannot be modified. If you want to modify it, you must wait until all the shared locks are released. Syntax: select * from table lock in share mode;

  • Exclusive lock (write lock, X lock)

Exclusive lock refers to that there can only be one lock for the same resource for multiple different transactions. When a resource is locked, it can be added, deleted, or modified by itself. Other people cannot lock it, let alone add, delete, or modify it. Syntax: select * from table for update.

  • Row lock

Row lock is to lock a row of data. The operation object is a row in the data table (shared lock and exclusive lock may be row lock or table lock, depending on the range of data lock, a row or the whole table). Mvcc technology is widely used, but it can’t be used in MyISAM. Row level locking is implemented by MySQL storage engine instead of MySQL server. However, row level locking has high overhead and high concurrency.

There are three ways to lock InnoDB rows:
1. Record lock: lock a single line record;
2. Gap lock: lock a range (the gap between indexes), but not the record itself, to prevent unreal reading;
3. Next-key lock: lock a range, including the record itself, equivalent to gap lock + record lock, which can prevent unreal reading

  • Watch lock

Table lock is to lock a table. The operation object is data table. Most of the lock policies of MySQL are supported (MySQL InnoDB is common), which is the one with the lowest system overhead but the lowest concurrency. If transaction t adds a read lock to the whole table, other transactions are readable and not writable. If it adds a write lock, other transactions cannot be added, deleted or modified.

  • Intent lock

Intent lock is simply to give a larger level of space to indicate whether the lock has been applied. For example, if we add a lock to a row of data, the database will automatically add an intention lock to a larger level of space, such as a data page or a data table, to tell other people that the data page or a data table has been locked, so that when other people want to acquire the lock of the data table, they only need to know whether someone has acquired the intention lock of the data table Yes, it does not need to record one by one to determine whether there is a lock.

Mvcc of MySQL (multi version concurrent control)


  1. Through mvcc, read and write can not block each other, that is, read does not block write, write does not block read, which can improve the concurrent processing capacity of transactions.
  2. Reduce the probability of deadlock. This is because mvcc adopts the optimistic locking method. It does not need to lock when reading data. For write operations, it only locks the necessary rows.
  3. Solve the problem of consistent reading. Consistent read is also called snapshot read. When we query the snapshot of a database at a certain time point, we can only see the result of transaction commit update before this time point, but not the result of transaction commit update after this time point.

Snapshot read and current read

  • Snapshot reading

Simple select without lock belongs to snapshot read:

SELECT * FROM table WHERE ...
  • Current reading

Current reading is to read the latest data, not the historical version of the data. Locked select, or add, delete, or modify data will be read currently:

SELECT * FROM table LOCK IN SHARE MODE;
SELECT * FROM table FOR UPDATE;
INSERT INTO table values ...;
DELETE FROM table WHERE ...;
UPDATE table SET ...;

The core of mvcc: undo log (MV) + read view (CC)

The data of mvcc in InnoDB includesTransaction version numberHidden columns in row recordsandUndo Log

  • Transaction version number

Every time a transaction is opened, we will get a transaction ID (transaction version number) from the database. The transaction ID is self growing. Through the ID size, we can determine the time sequence of transactions.

  • Hidden columns in row records
  1. DB row ID: the hidden row ID used to generate the default clustered index. If we do not specify the clustered index when creating the data table, InnoDB will use this hidden ID to create the clustered index. Clustering index can improve the efficiency of data search.
  2. DB? TRX? ID: the transaction ID of the data, which is the last transaction ID to insert or update the data.
  3. DB? Roll? PTR: rollback pointer, that is, undo log information pointing to this record.

Notes on MySQL basic knowledge (transaction)

  • Undo Log

InnoDB saves the row record snapshot in undo log. We can find them in the rollback segment, as shown in the following figure:

Notes on MySQL basic knowledge (transaction)

It can be seen from the figure that the rollback pointer concatenates all the snapshot records of the data row through the linked list structure. The record of each snapshot saves the current DB ﹐ TRX ﹐ ID, which is also the transaction ID of the data operated at that time point. In this way, if we want to find the historical snapshot, we can find it by traversing the rollback pointer.

  • Read View

In mvcc mechanism, multiple transactions updating the same row record will generate multiple historical snapshots, which are saved in undo log. If a transaction wants to query this row record, which version of row record should be read? Now we need to use read view, which helps us solve the row visibility problem. Read view saves all active (uncommitted) transaction lists when the current transaction is opened. From another perspective, you can understand that read view saves other transaction ID lists that should not be seen by this transaction.

Read view has several important properties:

  1. TRX? IDS, the collection of transaction IDS currently active by the system.
  2. Low limit ID, the largest transaction ID in an active transaction.
  3. Up limit ID, the smallest transaction ID in an active transaction.
  4. Create the transaction ID of the read view.

As shown in the figure, TRX ID is the collection of TRX2, trx3, trx5 and trx8. The maximum active transaction ID (low limit ID) is trx8, and the minimum active transaction ID (up limit ID) is TRX2.

Notes on MySQL basic knowledge (transaction)

Suppose that the current transaction creator “TRX” ID wants to read a row record whose transaction ID is TRX “ID, the following situations will occur:

  1. If TRX ﹤ ID < up ﹤ limit ﹤ ID, that is to say, this row record has been committed before these active transactions are created, then this row record is visible to this transaction.
  2. If TRX? ID > active maximum transaction ID (low? Limit? ID), this means that the row record is not visible to the current transaction until these active transactions are created.
  3. If up limit ID < TRX ID < low limit ID, the transaction TRX ID where the row record is located may still be active when the transaction creator TRX ID is created, so we need to traverse in the TRX IDS set. If TRX ID exists in the TRX IDS set, we need to prove that the transaction TRX ID is still active and invisible. Otherwise, if the TRX ID does not exist in the TRX IDS set, the transaction TRX ID has been committed and the row record is visible.

When querying a record, use multi version concurrency control technology to find the corresponding record:

  1. First, get the version number of the transaction itself, that is, the transaction ID (creator_trx_id);
  2. Use creator? TRX? ID to get read view;
  3. Query the data and compare it with the transaction version number in read view;
  4. If the read view rule is not met, you need to take a historical snapshot from undo log;
  5. Finally, the data that meets the rules is returned.

In InnoDB, mvcc reads data through undo log + read view. Undo log saves historical snapshots, while read view rules help us judge whether the current version of data is visible.

  • When the isolation level is read commit, each select query in a transaction will get a read view. As shown in the table:

Notes on MySQL basic knowledge (transaction)

Under the isolation level of read submitted, the same query statement will get read view again. At this time, if read view is different, there may be non repeatable or unreal reading.

  • When the isolation level is repeatable, non repeatable reading is avoided. This is because a transaction only gets a read view once when it is selected for the first time, and all subsequent selections reuse the read view, as shown in the following table:

Notes on MySQL basic knowledge (transaction)

InnoDB’s solution to unreal reading: next-key lock + mvcc

  • Even if mvcc mode is adopted, unreal reading will occur when the read has been submitted.

If we open transaction a and transaction B at the same time, we first query a certain condition range in transaction a, use exclusive lock when reading, add a piece of data meeting the condition range in transaction B, and commit, and then we query a condition range in transaction a If you query the data in the range of the condition again, you will find that there is more data in the result set that meets the condition, so there is unreal reading.The reason for unreal reading is that InnoDB only uses record locking when the read has been submitted.

Notes on MySQL basic knowledge (transaction)

  • When the isolation level is repeatable, InnoDB will use the next key lock mechanism to help us solve the problem of unreal reading.

We can see that when we want to insert player Alex Allen (2.16 meters tall), transaction B will time out and cannot insert the data. This is because the next key lock will lock the range with height > 2.08, so the data matching this range cannot be inserted. Then transaction a re queries the condition range, and there will be no unreal reading.

Notes on MySQL basic knowledge (transaction)