MySQL Review the past and learn the new--Locks in Innodb storage engine

X Exclusive lock

##S ​​Shared lock

Exclusive lock

##Conflict

##Compatibility

2##, lock extension

InnodbThe storage engine supports multi-granularity locking, which allows row-level locks and table-level locks to exist at the same time. In order to support locking operations at different granularities, the InnoDB storage engine supports an additional locking method, which is intention locking. Intention locks are table-level locks designed primarily to reveal the type of lock that will be requested for the next row within a transaction. It is also divided into two types:

? Intention shared lock (IS Lock), the transaction wants to obtain certain rows in a table shared lock.

? Intention exclusive lock (IX Lock), the transaction wants to obtain exclusive locks on certain rows in a table.

Since InnoDB supports row-level locks, intention locks do not The assembly blocks any request except for the full table scan. Shared locks, exclusive locks, intention shared locks, and intention exclusive locks all have mutually compatible /mutually exclusive relationships, which can be represented by a compatibility matrix(y means compatible, n means incompatible ), as shown below:

          Analysis: The mutual compatibility relationship between X and S As described in step1, the relationships between IX and IS are all compatible, which is also easy to understand. , because they are only "intentional" and are still in the YY stage, and have not really done anything, so they are compatible;

The rest The ones are X and IX, X and IS, S and IX, S and IS Okay, we can deduce these four sets of relationships from the relationship between X and S.

Simply put: X and IX’s =X Relationship with X. why? Because after the transaction acquires the IX lock, it has the right to acquire the X lock. If X is compatible with IX, both transactions will obtain XLock situation, this is contradictory to what we know X and X are mutually exclusive, so X and IX can only have a mutually exclusive relationship. The remaining three sets of relationships are similar and can be derived in the same way.

3, simulated lock scene

Before InnoDB Plugin, we could only pass SHOW FULL PROCESSLIS and SHOW ENGINE INNODB STATUS to view the current database request, and then determine the lock situation in the transaction. In the new version of InnoDB Plugin, 3 is added to the information_schema library. Table, INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WAITS. Through this 3 table, you can more easily monitor current transactions and analyze possible lock problems. If the database is running normally, these 3 tables will be empty without any records.

3.1, open transaction t1, t2, Simulate lock

Open 2session windows and open 2 transactions t1 and t2.

Open the transaction in the first windowt1Perform a lock operation, is as follows t1Transaction window interface:

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  开始执行锁定操作
mysql> select * from test.t1 where a<5 for update;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
| 2 | a  |    |
| 3 | r5 | r3 |
| 4 | r4 | r5 |
+---+----+----+
4 rows in set (0.00 sec)

# #

mysql>

这个时候，事务t1已经锁定了表t1的所有a<5的数据行，然后去第二个窗口开启第二个事务t2，如下，会看到update语句一直在等待事务t1释放锁资源，过了几秒后，会有报错信息，如下t2事务窗口界面：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test.t1 set b='t2' where a=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

3.2，通过3个系统表来查看锁信息

l 1，INNODB_TRX表

先看下表的重要字段以及记录的信息

a) trx_id：innodb存储引擎内部事务唯一的事务id。

b)                                              trx_state: The status of the current transaction.

c)           trx_started: The time when the transaction started.

d)                         trx_requested_lock_id: Waiting for the transaction lock id, such as The state of trx_state is LOCK WAIT, then this value represents the id# that occupied the lock resource before the current transaction. ##, if trx_state is not LOCK WAIT, this value is null .

e)           trx_wait_started: The time the transaction is waiting to start.

f)                trx_weight: The weight of a transaction reflects the number of rows modified and locked by a transaction. In the innodb storage engine, when a deadlock occurs and requires rollback, the innodb storage engine will select the transaction with the smallest value Perform rollback.

g) trx_mysql_thread_id：正在运行的mysql中的线程id，show full processlist显示的记录中的thread_id。

h) trx_query：事务运行的sql语句，在实际中发现，有时会显示为null值，当为null的时候，就是t2事务中等待锁超时直接报错（ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction）后，trx_query就显示为null值

……

因为前面模拟了事务锁场景，开启了t1和t2事务，现在去查看这个表信息，会有2条记录如下：

mysql> select * from INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 3015646
                 trx_state: LOCK WAIT
               trx_started: 2014-10-07 18:29:39
     trx_requested_lock_id: 3015646:797:3:2
          trx_wait_started: 2014-10-07 18:29:39
                trx_weight: 2
       trx_mysql_thread_id: 18
                 trx_query: update test.t1 set b='t2' where a=1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 3015645
                 trx_state: RUNNING
               trx_started: 2014-10-07 18:29:15
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 17
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 4
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
 
mysql>

This only records some currently running transactions, such as transaction t2is runningtrx_query: update test.t1 set b=' t2' sql statement where a=1, t1 is executed first, so it is trx_state: RUNNINGThe resource applied for first is always running, and t2 after run is trx_state: LOCK WAIThas been waiting for t1 to release resources after execution. However, we cannot carefully judge some details of the lock. We need to look at the INNODB_LOCKS table data.

l 2，INNODB_LOCKSTable

##a)                                                     The id and the locked space id number, page number, row numberb)       lock_trx_id

: Lock transaction id. c)                                  lock_mode

: The lock mode.

d) lock_type：锁的类型，表锁还是行锁

e) lock_table：要加锁的表。

f) lock_index：锁的索引。

g) lock_space：innodb存储引擎表空间的id号码

h) lock_page：被锁住的页的数量，如果是表锁，则为null值。

i) lock_rec：被锁住的行的数量，如果表锁，则为null值。

j) lock_data：被锁住的行的主键值，如果表锁，则为null值。

mysql> select * from INNODB_LOCKS\G
*************************** 1. row ***************************
    lock_id: 3015646:797:3:2
lock_trx_id: 3015646
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: PRIMARY
 lock_space: 797
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 3015645:797:3:2
lock_trx_id: 3015645
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`t1`
 lock_index: PRIMARY
 lock_space: 797
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set (0.00 sec)
 
mysql>

Here we can see the current lock information, 2 transactions are locked, see the same datalock_space: 797, lock_page: 3, lock_rec: 2 can be concluded that transaction t1 and transaction t2 access the same innodb data block, and then pass lock_dataField informationlock_data: 1, you can see that the locked data rows are all data records with the primary key of 1, visible 2 transactions t1 and t2 both applied for the same resources, so will be locked and the transaction is waiting.

Transactions t1 and can also be seen through the lock_mode: X value t2All applications are for exclusive locks.

          PS: When executing a range query update, the value of this lock_data is not completely accurate. When we run a range update, lock_data only returns the primary key value of the first row found id; additionally if The current resource is locked. At the same time, the buffer pool page is replaced due to the capacity of the InnoDB storage engine buffer pool. Check againINNODB_LOCKS table, this lock_data will display an unNULL value, which means InnoDBThe storage engine will not search again from the disk.

l 3,INNODB_LOCK_WAITSTable

When the transaction volume is relatively small, we can visually check it. When the transaction volume is very large and lock waiting often occurs, we can use at this time. The INNODB_LOCK_WAITS table reflects the current lock waiting situation more intuitively:

INNODB_LOCK_WAITSThe main fields of the table are as follows:

1) requesting_trx_id：申请锁资源的事务id。

2) requested_lock_id：申请的锁的id。

3) blocking_trx_id：阻塞的事务id。

4) blocking_lock_id：阻塞的锁的id。

去看下当前锁等待信息，如下所示：

mysql> select * from INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 3015646
requested_lock_id: 3015646:797:3:2
  blocking_trx_id: 3015645
 blocking_lock_id: 3015645:797:3:2
1 row in set (0.00 sec)
mysql>

这里我们可以看到事务t1(3015646)申请了锁资源，而事务t2(3015645)则阻塞了事务t1的申请。我们管理其他表，得到更直观的详细信息，如下所示：

mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query,  it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2 WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_thread | waiting_query                       | blocking_trx_id | blocking_thread | blocking_query |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
| 3015647        |             18 | update test.t1 set b='t2' where a>2 | 3015645         |              17 | NULL           |
+----------------+----------------+-------------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
 
mysql>

4, consistent non-locking read operation

4.1，CNRPrinciple Analysis

Consistent nonlocking read (consistent nonlocking read, referred to as CNR) refers to the InnoDB storage engine that reads the current execution through row multi-versioning (multi versioning) Data running in time database. If the read row is executing delete, update operations, then the read operation will not wait for the row to be locked. Release, on the contrary, InnoDB The storage engine will read a snapshot data of the row, as shown in the following figure:

X Lock release, snapshot data refers to the previous version of the data of the row, this is achieved through the Undo segment, and Undo is used to roll back in the transaction Data, so the snapshot itself has no additional overhead. In addition, reading the snapshot does not require locking because there is no need to modify historical data.

InnoDB storage engine default settings, this is the default read In this way, reading will not occupy and wait for the lock on the table. However, reading methods are different under different transaction isolation levels. Not every transaction isolation level is consistent reading. Similarly, even if they both use consistent reads, the definition of snapshot data is different.

快照数据其实就是当前数据之前的历史版本，可能有多个版本。如上图所示，一个行可能不止有一个快照数据。我们称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

在Read Committed和Repeatable Read模式下，innodb存储引擎使用默认的非锁定一致读。在Read Committed隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据；而在Repeatable Read隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

4.2，CNR实例

开启2个Session A和B。

Session  A：
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from t1 where a=1;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
+---+----+----+
1 row in set (0.00 sec)
 
mysql>

Session A中事务已经开始，读取了a=1的数据，但是还没有结束事务，这时我们再开启一个Session B，以此模拟并发的情况，然后对Session B做如下操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update t1 set a=111 where a=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>

Session 中将a=1的行修改为a=111，但是事务同样没有提交，这样a=1的行其实加了一个X锁。这时如果再在Session A中读取a=1的数据，根据innodb存储引擎的特性，在Read Committed和Repeatable Read事务隔离级别下，会使用非锁定的一致性读。回到Session A，节着上次未提交的事务，执行select * from t1 where a=1;的操作，显示的数据应该都是原来的数据：

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

因为当前a=1的数据被修改了1次，所以只有一个版本的数据，接着我们在Session B中commit上次的事务。如：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql>

Session B提交事务后，这时再在Session A中运行select * from t1 where a=1;的sql语句，在READ-COMMITTED和REPEATABLE-READ事务隔离级别下，得到的结果就会不一样，对于READ-COMMITTED模事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行的最新一个快照(fresh snapshot)。因此在这个例子中，因为Session B已经commit了事务，所以在READ-COMMITTED事务隔离级别下会得到如下结果，查询a=1就是为null记录，因为a=1的已经被commit成了a=111，但是如果查询a=111的记录则会被查到，如下所示：

mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
 
mysql> select * from t1 where a=1;
Empty set (0.00 sec)
 
mysql> select * from t1 where a=111;
+-----+----+----+
| a   | b  | c  |
+-----+----+----+
| 111 | c2 | c2 |
+-----+----+----+
1 row in set (0.01 sec)
 
mysql>

但是如果在REPEATABLE-READ事务隔离级别下，总是读取事务开始时的数据，所以得到的结果截然不同，如下所示：

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
 
mysql> select * from t1 where a=1;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
 
mysql> select * from t1 where a=111;
Empty set (0.00 sec)
 
mysql>

对于READ-COMMITTED的事务隔离级别而言，从数据库理论的角度来看，其实违反了事务ACID的I的特性，既是隔离性，整理成时序表，如下图所示。

If you follow the I principle of isolation in the ACID principle, throughout the session## In #Session A, Select * from t1 where a=1;The queried data should remain the same, but at the moment of time 5 Before Session A ends, the query result has changed and is inconsistent with time 1 and time 3, and does not meet the isolation of ACID.

#5,SELECT ... FOR UPDATE && SELECT ... LOCK IN SHARE MODE

innodb storage engine's select operations use consistency Non-locking read, but in some cases, the read operation needs to be locked. InnodbThe storage engine supports 2 kinds of add lock operations## for the select statement. #;?

SELECT ... FOR UPDATE Add a to the read row record Xexclusive lock, other transactions will be blocked if they want to perform dml or select operations on these rows.

? SELECT ... LOCK IN SHARE MODE 对于读取的行记录添加一个S共享锁。其它事务可以向被锁定的行加S锁，但是不允许添加X锁，否则会被阻塞。

对于一致性 非锁定读，即使读取的行数已经被SELECT ... FOR UPDATE了，但是也是可以进行读取的。

PS：… FOR UPDATE以及LOCK IN SHARE MODE必须在一个事务中，如果事务commit了，锁也就释放了，所以在使用的时候务必加上begin;start transaction或者set autocommit=0;

例子如下：

会话A：开启事务，执行LOCK IN SHARE MODE;锁定

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c0 | c2 |
+---+----+----+
1 row in set (0.00 sec)
 
mysql>

同时在另外一个窗口开启会话B，执行dml操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update t1 set a=111 where a=1;

…这里会卡住，没有信息。

再开启一个会话C，查询INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表，就会看到锁的详细信息：

mysql> select * from INNODB_LOCKS;
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 3015708:797:3:2 | 3015708     | X         | RECORD    | `test`.`t1` | PRIMARY    |        797 |         3 |        2 | 1         |
| 3015706:797:3:2 | 3015706     | S         | RECORD    | `test`.`t1` | PRIMARY    |        797 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)
 
mysql>
mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 3015708           | 3015708:797:3:2   | 3015706         | 3015706:797:3:2  |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
 
mysql>
mysql> SELECT it2.`trx_id` AS waiting_trx_id, it2.`trx_state` AS waiting_trx_status,it2.`trx_mysql_thread_id` AS waiting_thread,it2.`trx_query` AS waiting_query,
    -> it1.`trx_id` AS blocking_trx_id, it1.`trx_mysql_thread_id` blocking_thread, it1.`trx_query` blocking_query
    -> FROM `information_schema`.`INNODB_LOCK_WAITS` ilw, `information_schema`.`INNODB_TRX` it1,`information_schema`.`INNODB_TRX` it2
    -> WHERE it1.`trx_id`=ilw.`blocking_trx_id` AND it2.`trx_id`=ilw.`requesting_trx_id`;
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| waiting_trx_id | waiting_trx_status | waiting_thread | waiting_query                 | blocking_trx_id | blocking_thread | blocking_query |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
| 3015708        | LOCK WAIT          |             18 | update t1 set a=111 where a=1 | 3015706         |              21 | NULL           |
+----------------+--------------------+----------------+-------------------------------+-----------------+-----------------+----------------+
1 row in set (0.00 sec)
 
mysql>

会话A开启的事务1（事务id：3015706）执行了SELECT * FROM t1 WHERE a=1 LOCK IN SHARE MODE;

statement has added a S lock on the line where a=1, so the session BOpened transaction2(Transactionid:23015708 ) The executed update t1 set a=111 where a=1;sql statement goes to the line where a=1 Adding the X lock will be blocked by the transaction 1 of session A, so the transaction The status value of 2 is LOCK WAIT, which has been waiting. Until the transaction waits for a timeout, the error is reported as follows:

mysql> update t1 set a=111 where a=1;

ERROR 1205 (HY000) : Lock wait timeout exceeded; try restarting transaction

##mysql>

At this time, sessionB## The transaction in #2 is terminatedupdate t1 set a=111 where a=1; dmlRequest operation.

6, self-growth and lock

自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。

SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;

插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。

从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.

自增长的分类：

在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：

mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
 
mysql>

而在myisam表中，则没有这样的限制，如下所示：

mysql>  CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)