MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
这是一个创建于 1992 天前的主题，其中的信息可能已经有所发展或是发生改变。
执行以下代码后，
create table t(id int primary key); insert into t values (5),(10); -- session 1 start transaction; select * from t where id > 8 for share; -- session 2 start transaction; insert into t values(6); -- is blocked select * from performance_schema.data_locks的输出为：
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+ | INNODB | 140043377180872:1063:140043381460688 | 2132 | 49 | 56 | test | t | NULL | NULL | NULL | 140043381460688 | TABLE | IX | GRANTED | NULL | | INNODB | 140043377180872:2:4:3:140043381457776 | 2132 | 49 | 56 | test | t | NULL | NULL | PRIMARY | 140043381457776 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 10 | | INNODB | 140043377180024:1063:140043381454544 | 421518353890680 | 48 | 105 | test | t | NULL | NULL | NULL | 140043381454544 | TABLE | IS | GRANTED | NULL | | INNODB | 140043377180024:2:4:1:140043381451552 | 421518353890680 | 48 | 105 | test | t | NULL | NULL | PRIMARY | 140043381451552 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 140043377180024:2:4:3:140043381451552 | 421518353890680 | 48 | 105 | test | t | NULL | NULL | PRIMARY | 140043381451552 | RECORD | S | GRANTED | 10 | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+------------------------+ 我们可以看到 session 2 正在等待插入意向锁，因为这个插入意向锁跟 session 1 正在拥有的“下一键锁(5, 10]”冲突。这跟我预想的一样。但是如果我将 schedule 修改为
create table t(id int primary key); insert into t values (5),(10); -- session 1 start transaction; insert into t values(6); -- session 2 start transaction; select * from t where id > 8 for share; -- is not blocked select * from performance_schema.data_locks的输出为：
+--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | INNODB | 140043377180024:1063:140043381454544 | 2147 | 48 | 125 | test | t | NULL | NULL | NULL | 140043381454544 | TABLE | IX | GRANTED | NULL | | INNODB | 140043377180872:1063:140043381460688 | 421518353891528 | 49 | 86 | test | t | NULL | NULL | NULL | 140043381460688 | TABLE | IS | GRANTED | NULL | | INNODB | 140043377180872:2:4:1:140043381457776 | 421518353891528 | 49 | 86 | test | t | NULL | NULL | PRIMARY | 140043381457776 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 140043377180872:2:4:3:140043381457776 | 421518353891528 | 49 | 86 | test | t | NULL | NULL | PRIMARY | 140043381457776 | RECORD | S | GRANTED | 10 | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------------------+ session 1 并没有拥有插入意向锁。那么，事务是在成功插入数据之后释放了插入意向锁吗？
 | 1 izgnod 2020-07-11 09:04:20 +08:00 via iPhone 有主键索引和辅助索引的插入，插入前获取辅助索引的插入意向锁，插入后获取主键的记录锁。你这个事只有主键所以只需要记录锁。 |
 | 2 JasonLaw OP @izgnod #1 我做了以下实验，事实并不是你所说的那样，并不是“插入前获取辅助索引的插入意向锁，插入后获取主键的记录锁”。而且问题中的两个 schedules 也能证明你所说的是不正确的。 create table t2(id int primary key, value int, index ix_t2_value(value)); insert into t2 values (5,10),(10,5); -- session 1 start transaction; select * from t2 where id = 3 for share; -- session 2 start transaction; insert into t2 values (2, 8); -- 等待获取 PRIMARY 索引上的插入意向锁(-∞ , 5) select * from performance_schema.data_locks 的输出为： +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+ | INNODB | 140311361761480:1063:140311280045776 | 2072 | 50 | 14 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL | | INNODB | 140311361761480:2:4:2:140311280042864 | 2072 | 50 | 14 | test | t2 | NULL | NULL | PRIMARY | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5 | | INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL | | INNODB | 140311361760632:2:4:2:140311280036640 | 421786338471288 | 48 | 22 | test | t2 | NULL | NULL | PRIMARY | 140311280036640 | RECORD | S,GAP | GRANTED | 5 | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+------------------------+-------------+-----------+ --- create table t2(id int primary key, value int, index ix_t2_value(value)); insert into t2 values (5,10),(10,5); -- session 1 start transaction; select * from t2 where value = 3 for share; -- session 2 start transaction; insert into t2 values (8, 2); -- 等待获取 ix_t2_value 索引上的插入意向锁(-∞, (5, 10)) select * from performance_schema.data_locks 的输出为： +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+ | INNODB | 140311361761480:1063:140311280045776 | 2073 | 50 | 18 | test | t2 | NULL | NULL | NULL | 140311280045776 | TABLE | IX | GRANTED | NULL | | INNODB | 140311361761480:2:5:3:140311280042864 | 2073 | 50 | 18 | test | t2 | NULL | NULL | ix_t2_value | 140311280042864 | RECORD | X,GAP,INSERT_INTENTION | WAITING | 5, 10 | | INNODB | 140311361760632:1063:140311280039632 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | NULL | 140311280039632 | TABLE | IS | GRANTED | NULL | | INNODB | 140311361760632:2:5:3:140311280036640 | 421786338471288 | 48 | 26 | test | t2 | NULL | NULL | ix_t2_value | 140311280036640 | RECORD | S,GAP | GRANTED | 5, 10 | +--------+---------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+------------------------+-------------+-----------+ |
Select Language