MySQL Advanced (2)-Locks and Transactions

MySQL Advanced (2)-Locks and Transactions

Article Directory

Java and big data development learning points (continuous update...)

1. MySQL lock mechanism**

1.1 Locks and their application scenarios

MySQL locks are classified according to data operation types: read locks (shared locks), write locks (exclusive locks) [read-read shared, read-write, write-write mutual exclusion]
according to the granularity of data operations are divided into: row locks and table locks

Types of locks and suitable scenarios

  • Table lock : suitable for multiple reading scenarios, prefer MyISAM storage engine, low overhead, fast locking, large lock granularity, and low concurrency.
  • Row locks : suitable for transaction scenarios, prefer the InnoDB storage engine, with high overhead, slow locking, small lock granularity, and high concurrency.

Read blocking write, write blocking read

Read blocking write : When a session adds a read lock to a table, other sessions can also read the data in this table, but the write data requests of other tables will be blocked . In addition, the current table cannot perform operations on other tables before releasing the read lock of this table, and cannot perform data write operations on the table that is read-locked by itself.

Read blocking and write : When a session adds a write lock to a table, then other sessions are blocked from reading and writing data in this table . This session can read and write this table.


Manually lock a row to implement a transaction


select * from test where id=8 for update;

--do your job


The case where row locks are upgraded to table locks

In the case of no index or index failure (for example, the query of the varchar field in SQL is not quoted, but the automatic type conversion is performed by the internal optimization of MySQL, but the index is invalid), the row lock is upgraded to the table lock. Secret and terrifying situation

Gap lock problem

When MySQL performs range condition operation on data instead of equivalent condition operation, InnoDB will lock the existing data index items that meet the conditions. Records that do not exist in the existing range will also be locked, and other sessions will be blocked for new operations on this non-existent record row. This locking mechanism is called gap lock. However, gap locks can be used to prevent phantom reads from occurring under the repeatable read isolation level, so MySQL will not occur under the repeatable read isolation level. (Equivalent query does not need to say phantom reading at all, range query uses gap lock to prevent phantom reading)

1.2 Transaction and MySQL isolation level

1.2.1 ACID properties of transactions and transactions***

MySQL transaction is a logical processing unit composed of a set of SQL statements, with four attributes ACID of transaction:

  • Atomic : A transaction is an atomic operation, which either succeeds or fails.
  • Consistency (Consistent) : When the transaction starts and completes, the data remains in a consistent state. The result of reading a piece of data in all transactions is consistent.
  • Isolation (Isolation) : The system that supports transactions provides a certain degree of isolation to ensure that transactions can be completed without interference in a concurrent environment and are invisible to other transactions.
  • Durable : After the transaction is completed, there is a persistence mechanism for the data to ensure that the data can be recovered even if the system fails.

1.2.2 Problems with transactions in concurrent scenarios**

  • Update loss : When multiple transactions modify the same data row, they do not know the existence of each other, causing data loss on one side.
  • Dirty read : Transaction A reads transaction BModified and not yet submittedAt this time, transaction B rolls back, and the data read by A is invalid and does not conform to consistency.
  • Non-repeatability : Transaction A reads a row of data many times, but during the period, transaction B modifies and commits the data of this row. The data read twice is inconsistent, which does not meet the isolation.
  • Phantom read : Transaction A reads the new data of transaction B , which does not meet the isolation.

1.2.3 MySQL transaction isolation level

Isolation levelRead data consistencyDirty readNon-repeatablePhantom reading
Uncommitted read (read uncommitted)The lowest level, which can only guarantee that no physical damage data is readYesYesYes
Has been submitted to read (read committed)Statement levelnoYesYes
Repeatable readDefault level, transaction levelnonoYes
SerializableHighest level, transaction levelnonono

That is, under the default configuration of MySQL, there is a problem of phantom reading. The isolation level and concurrency are negatively related. The higher the isolation level, the more it tends to execute serially, which contradicts the concurrency capability. Therefore, the corresponding isolation level is adopted according to the requirements of the business scenario.

1.2.4 MVCC protocol

MVCC works under two isolation levels: repeatable read and read committed .

The realization of locking has locking synchronization and CAS mechanism, MVCC is similar to CAS mechanism. Under the InnoDB engine, with the above two isolation levels, MySQL does not add row locks to read and write data, but uses a multi-version control method. In this way, read and write operations will not be blocked, but additional storage space is required to implement it, and the space-for-time method is used.

MVCC is stored in two hidden behind each row of columns used to store the version number two: create a version number and delete the version number . And every transaction has a unique incremental version number when it starts . And the operation is carried out in the form of a snapshot of the original data row .

In 插入operation: The creation version number of the record is the transaction version number.

During 更新operation: first mark the old record as deleted, and the delete version number is the transaction version number, and then insert a new record whose creation version number is also the transaction version number.

During 删除operation: use the transaction version number as the delete version number.

As can be seen from the above description, 查询only the records that meet the following two conditions can be queried by the transaction:

InnoDB only finds data rows whose version is earlier than the current transaction version. This ensures that the rows read by the transaction either already existed before the transaction started, or were inserted or modified by the transaction itself.

The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that the rows read by the transaction are not deleted before the transaction starts.


MVCC's transaction rollback is to Undo日志link up a snapshot of each data row, and roll back the transaction through the Undo log.

Two, MySQL master-slave replication*

Master-slave replication

MySQL master-slave replication is divided into three steps (MySQL replication is asynchronous and serial):

  1. The master writes the operation into the binary log. The recording process is called a binary log event.
  2. The slave copies the binary log events of the master to its relay log (relay log).
  3. The slave redo the events in the relay log and apply the changes to its own database.
Canal extracts incremental data from MySQL based on the same principle, disguising itself as a slave.