MySQL locks and transaction isolation levels (introduction)-Mysql Tutorial-php.cn

In today's Internet, you cannot develop a large-scale multi-player APP without a database. How to ensure that everyone can read and write with high concurrency has always been a difficult architectural problem. First of all, high concurrency is eliminated, and the most commonly used method to ensure consistent reading and writing is transaction, and the key point of implementing a transaction is locking. mechanism.

MySQL locks and transaction isolation levels (introduction)

Today we will introduce the principle and implementation of how the InnoDB storage engine implements the lock mechanism under high concurrency to meet consistent reading and writing.

Lock

The locking mechanism of the database is a key feature that distinguishes it from the file system. Used to manage concurrent access to shared resources. InnoDB uses locking mechanisms in many places, such as operating data tables, LRU page lists, and data rows in the buffer pool. In order to ensure consistency and integrity, a locking mechanism is required.

For different databases, the design and implementation of the lock mechanism are completely different:

● MyISAM engine: Table lock design, concurrent reading is no problem, but concurrent writing performance is poor.

●Microsoft SQL Server: Supports optimistic concurrency and pessimistic concurrency. Optimistic concurrency supports row-level locks. Maintaining locks is expensive. When the number of row locks exceeds the threshold, it will be upgraded to table locks.

●InnoDB engine: supports row locks and provides consistent non-locking reads. Row locks have no additional overhead and no performance degradation.

●Oracle: Very similar to the InnoDB engine.

Two types of locks: lock and latch

In the database, both lock and latch can be called locks, but there is a big difference.

Latch is generally called a latch, which is used to ensure the correctness of critical resource operations by concurrent threads. The object is a memory data structure. The lock time is required to be very short and deadlock will not be detected. In the InnoDB engine, it is divided into mutex (mutex) and rwlock (read-write lock).

Lock is used to lock objects in the database, such as tables, pages, and rows. The target is a transaction. It is released after commit/rollback and deadlock will be detected. Divided into row locks, table locks, and intent locks.

The locks we have below refer to lock type locks.

Four lock types

InnoDB supports four locks:

● Shared lock (S Lock): allows transactions Read a row of data

●Exclusive lock (X Lock): Allows a transaction to delete or update a row of data

●Intention S Lock: A transaction wants to obtain certain rows in a table Shared lock

●Intention X Lock: The transaction wants to obtain the exclusive lock of certain rows in a table

When transaction T1 acquires the shared lock of row The row data will not be changed, so transaction T2 can also directly obtain the shared lock of row r, which is called lock compatible.

When transaction T3 wants to obtain the exclusive lock of row r to modify data, it needs to wait for T1/T2 to release the row shared lock. This is called lock incompatibility.

S locks and X locks are both row locks, while IS locks and IX locks are intention locks and belong to table locks. Intention locks are designed to reveal the type of lock that will be requested for the next row within a transaction, that is, to lock at the finer granularity of a table lock. Since InnoDB supports table locks, intent locks will not block any requests except full table scans.

Lock compatibility:

##ISIXS XISCompatibleCompatibleCompatibleNot CompatibleIXCompatibleCompatibleIncompatibleIncompatibleSCompatibleNot compatibleCompatibleNot compatibleXIncompatibleIncompatibleIncompatibleIncompatible

Storage Three tables of transaction and lock information

We can view the information of the current lock request in the transaction section through the

show engine innodb status command.

Starting from InnoDB1.0, INNODB_TRX (transaction table), INNODB_LOCKS (lock table), INNODB_LOCK_WAITS (lock wait table) have been added under the INFORMATION_SCHEMA architecture. Through these three tables, we can monitor the current situation in real time. Transactions and analyze possible table problems.

The definitions of the three tables are:

##trx_idtrx_statetrx_startedtrx_requested_lock_idtrx_wait_startedtrx_weighttrx_mysql_thread_idtrx_query

INNODB_TRX
The unique transaction ID within the InnoDB storage engine
The status of the current transaction
The start time of the transaction
The lock IDC waiting for the transaction, when the status is not LOCK WAIT, it is NULL
The time the transaction waits to start
The weight of the transaction reflects the number of rows modified and locked by a transaction. When rollback is required, select the transaction with the smallest value for rollback
The thread ID of MySQL, the result displayed by show processlist
SQL statement for transaction operation

`INNODB_LOCKS`
lock_id	lockID
lock_trx_id	Transaction ID
lock_mode	Lock mode
lock_type	Type of lock, table lock or row lock
lock_table	Table to be locked
lock_index	Locked index
lock_space	Space id of the lock object
lock_page	The number of transaction-locked pages, NULL when the table is locked
lock_rec	The number of transaction-locked rows, NULL when the table is locked
lock_data	The primary key value of the transaction lock record, which is NULL when the table is locked

##requesting_trx_idrequesting_lock_idblocking_trx_idblocking_lock_idThrough

# #INNODB_LOCK_WAITS
Transaction ID for applying for lock resources
Apply The ID of the lock
The blocking transaction ID
The blocking lock ID

INNODB_TRX

we can see all transactions, whether the transaction is blocked, and what is the blocked lock ID. Afterwards, view all lock information through INNODB_LOCKS
. After that, you can view the waiting information and blocking relationship of the lock through INNODB_LOCK_WAITS
. Through these three tables, you can view the transaction and lock status more clearly, and you can also perform joint queries. In the following scenarios, we will show the contents of these three tables.

Isolation levelFirst of all, let’s talk about the four transaction isolation levels of the database:

● READ UNCOMMITTED (0 ): Browsing access level, there are dirty reads, non-repeatable reads, and phantom reads

●　READ COMMITTED (1): Cursor stability level, there are non-repeatable reads and phantom reads

● REPEATABLE READ（ 2): There is phantom reading

● SERIALIZABLE (3): Isolation level, ensuring transaction security, but completely serial and low performance

These four transaction isolation levels are specified by the SQL standard. The default isolation level of InnoDB is REAPEATABLE READ, but unlike other databases, it also uses the Next-Key-Lock lock algorithm, which can avoid the occurrence of phantom reads, so it can fully meet the transaction isolation requirements, that is, it can achieve SERIALIZABLE Isolation level.

The lower the isolation level, the fewer locks requested by the transaction or the shorter the lock holding time, so the default isolation level of most databases is READ COMMITED. However, relevant analysis also points out that the performance overhead of isolation levels is almost the same, so users do not need to adjust the isolation level to improve performance.

Commands to view and modify the transaction isolation level:

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

Copy after login

In the example, the transaction isolation level of this session is modified. If you need to modify the global parameters, you can replace session with global. If you want to make permanent changes, you need to modify the configuration file:

[mysqld]
transaction-isolation = READ-COMMITED

Copy after login

At the transaction isolation level of SERIALIZABLE, InnoDB will automatically add LOCK IN SHARE MODE after each SELECT statement to add a shared lock to the read operation. , so consistent non-locking reads are no longer supported.

Since InnoDB can reach SERIALIZABLE at the REPEATABLE READ isolation level, it is generally not necessary to use the highest isolation level.

Consistent non-locking read and multi-version concurrency control Consistent non-locking read (consistent non-locking read) refers to InnoDB’s multi-row Version control (Multi Version Concurrency Control, MVCC) method to read the row data in the database at the current execution time.

That is, if the read row is undergoing a change operation, the read will not wait for the release of the row lock, but will read a snapshot data of the row. A snapshot refers to a historical data of the row, which is completed through the undo operation. This method greatly improves the concurrency of the database, which is also the default setting of InnoDB.

A snapshot is a historical version of the current row, but there may be multiple versions. The row data has multiple snapshot data. This technology becomes row multi-version technology, and the resulting concurrency control is called multi-snapshot. Version Concurrency Control (MVCC). InnoDB uses non-locking consistent reads in the READ COMMITED and REPEATABLE READ isolation levels, but the quick data definitions used in these two isolation levels are different:

● READ COMMITED: always read The latest snapshot

● REPEATABLE READ: Always read the row data version at the beginning of the transaction

We execute an example:

Consistent non-locking readTime12345select * from z where a = 3 ;7select * from z where a = 3;##8

在这个例子中我们可以清晰的看到0、1、2三种隔离级别的区别：

#在事务开始前我们可以分别调整为0、1、2三种隔离级别，来查看不同的输出
mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from z where a = 3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update z set b=2 where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# A会话：T1事务，如果此时隔离级别是READ-UNCOMMITTED，因为此刻事务2可能会回滚，所以出现了脏读
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-UNCOMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# B会话：T2事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# A会话：T1事务，如果此时隔离级别是READ-COMMITTED，因为数据和事务开始时读取的出现了不一致，因此称为不可重复读，能够读到其他事务的结果，违反了事务的隔离性
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    2 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务，如果此时隔离级别是大于READ-COMMITTED的更高级别
mysql> select * from z where a=3;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
+---+------+
1 row in set (0.00 sec)

# A会话：T1事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Copy after login

一致性锁定读和SERIALIZABLE隔离

在默认的REPEATABLE READ隔离级别时，InnoDB使用的是一致性非锁定读。但有时我们也需要显示的指定使用一致性锁定读来保证读取操作时对数据进行加锁达到一致性。这要求数据库支持锁定读加锁语句：

　●　select ... for update: 读取时对行记录加X锁

　●　select ... lock in share mode：读取时对行记录加一个S锁

这两种锁必须在一个事务中，当事务提交后锁也就释放了，因此务必加上BEGIN, START TRANSACTION或者SET AUTOCOMMIT=0。

我们在前面隔离级别时也说过SERIALIZABLE隔离级别会对读操作自动加上LOCK IN SHARE MODE指令来加上一个共享锁，因此不再支持一致性的非锁定读。这也是隔离级别3的一大特性。

总结

由于锁的概念非常重要，这里先讲了锁的概念、锁的类型、锁的信息查看、事务的隔离级别和区别，后面我们会继续说锁的算法、锁的三种问题和幻读、死锁和锁升级。

推荐学习：MySQL教程


Session A	Session B
BEGIN
select * from z where a = 3;
	##BEGIN
	update z set b=2 where a=3;
		##6
##COMMIT;
		COMMIT;

The above is the detailed content of MySQL locks and transaction isolation levels (introduction). For more information, please follow other related articles on the PHP Chinese website!