#连接一： mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> update student set sname='小三' where stuid = 12; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> 连接二： mysql> update student set sname='小三' where stuid = 12; #进入阻塞状态 连接三查看 mysql> select * from Information_schema.innodb_trx; +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+----------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+----------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 24610 | LOCK WAIT | 2021-05-24 22:08:25 | 24610:54:4:13 | 2021-05-24 22:08:25 | 2 | 9 | update student set sname='小三' where stuid = 12 | starting index read | 1 | 1 | 2 | 1136 | 1 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | | 24608 | RUNNING | 2021-05-24 22:04:03 | NULL | NULL | 3 | 8 | NULL | NULL | 0 | 1 | 2 | 1136 | 1 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | +--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+----------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ 2 rows in set (0.00 sec) mysql>
当超过设置超时时间则：
mysql> update student set sname='小三' where stuid = 12; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql>
Information_schema.innodb_trx里是在事务的维度记录正在执行的事务信息的，虽然有包含事务的锁信息，但是不能判断锁是与哪个有冲突，需要继续通过data_locks记录查看
当一个事务获得锁和等待锁的时候，都会在这儿记录
mysql> 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 | 24610:1112 | 24610 | 47 | 38 | school | student | NULL | NULL | NULL | 140307387688984 | TABLE | IX | GRANTED | NULL | | INNODB | 24610:54:4:13 | 24610 | 47 | 38 | school | student | NULL | NULL | PRIMARY | 140307387685944 | RECORD | X,REC_NOT_GAP | WAITING | 12 | | INNODB | 24608:1112 | 24608 | 46 | 50 | school | student | NULL | NULL | NULL | 140307387683032 | TABLE | IX | GRANTED | NULL | | INNODB | 24608:54:4:13 | 24608 | 46 | 50 | school | student | NULL | NULL | PRIMARY | 140307387679992 | RECORD | X,REC_NOT_GAP | GRANTED | 12 | +--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ 4 rows in set (0.28 sec)
sys.innodb_lock_waits更直接的记录了InnoDB中事务之间相互等待锁的信息
mysql> select * from sys.innodb_lock_waits; +---------------------+----------+---------------+--------------------+---------------------+-------------------+------------------------+---------------------------+--------------+-------------+----------------+---------------------+-----------------+-------------------------+---------------------------+-------------+----------------------------------------------------+-----------------+-------------------+-----------------+--------------+----------------+------------------+--------------------+----------------------+------------------+--------------------------+----------------------------+-------------------------+------------------------------+ | wait_started | wait_age | wait_age_secs | locked_table | locked_table_schema | locked_table_name | locked_table_partition | locked_table_subpartition | locked_index | locked_type | waiting_trx_id | waiting_trx_started | waiting_trx_age | waiting_trx_rows_locked | waiting_trx_rows_modified | waiting_pid | waiting_query | waiting_lock_id | waiting_lock_mode | blocking_trx_id | blocking_pid | blocking_query | blocking_lock_id | blocking_lock_mode | blocking_trx_started | blocking_trx_age | blocking_trx_rows_locked | blocking_trx_rows_modified | sql_kill_blocking_query | sql_kill_blocking_connection | +---------------------+----------+---------------+--------------------+---------------------+-------------------+------------------------+---------------------------+--------------+-------------+----------------+---------------------+-----------------+-------------------------+---------------------------+-------------+----------------------------------------------------+-----------------+-------------------+-----------------+--------------+----------------+------------------+--------------------+----------------------+------------------+--------------------------+----------------------------+-------------------------+------------------------------+ | 2021-05-24 22:26:41 | 00:00:11 | 11 | `school`.`student` | school | student | NULL | NULL | PRIMARY | RECORD | 24610 | 2021-05-24 22:26:41 | 00:00:11 | 1 | 0 | 9 | update student set sname='小三' where stuid = 12 | 24610:54:4:13 | X,REC_NOT_GAP | 24608 | 8 | NULL | 24608:54:4:13 | X,REC_NOT_GAP | 2021-05-24 22:04:03 | 00:22:49 | 1 | 1 | KILL QUERY 8 | KILL 8 | +---------------------+----------+---------------+--------------------+---------------------+-------------------+------------------------+---------------------------+--------------+-------------+----------------+---------------------+-----------------+-------------------------+---------------------------+-------------+----------------------------------------------------+-----------------+-------------------+-----------------+--------------+----------------+------------------+--------------------+----------------------+------------------+--------------------------+----------------------------+-------------------------+------------------------------+ 1 row in set (0.12 sec) mysql>
如果要追根溯源，可通过performance_schema.data_locks里的thread_id 和 event_id在performance_schema.events_statements_current里定位
mysql> select * from performance_schema.events_statements_current where thread_id=46; +-----------+----------+--------------+----------------------+---------------------------------+------------------+------------------+------------+-----------+----------------------------------------------------+------------------------------------------------------------------+----------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+ | THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME | SOURCE | TIMER_START | TIMER_END | TIMER_WAIT | LOCK_TIME | SQL_TEXT | DIGEST | DIGEST_TEXT | CURRENT_SCHEMA | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | ERRORS | WARNINGS | ROWS_AFFECTED | ROWS_SENT | ROWS_EXAMINED | CREATED_TMP_DISK_TABLES | CREATED_TMP_TABLES | SELECT_FULL_JOIN | SELECT_FULL_RANGE_JOIN | SELECT_RANGE | SELECT_RANGE_CHECK | SELECT_SCAN | SORT_MERGE_PASSES | SORT_RANGE | SORT_ROWS | SORT_SCAN | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_LEVEL | +-----------+----------+--------------+----------------------+---------------------------------+------------------+------------------+------------+-----------+----------------------------------------------------+------------------------------------------------------------------+----------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+ | 46 | 48 | 49 | statement/sql/update | init_net_server_extension.cc:95 | 4362938279055000 | 4362938832688000 | 553633000 | 144000000 | update student set sname='小三' where stuid = 12 | 40ce967e64aeeb1f8e87d1bf618ba90d8f9a95fcb861618f52e1776b889f04fb | UPDATE `student` SET `sname` = ? WHERE `stuid` = ? | school | NULL | NULL | NULL | NULL | 0 | 00000 | Rows matched: 1 Changed: 1 Warnings: 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 | +-----------+----------+--------------+----------------------+---------------------------------+------------------+------------------+------------+-----------+----------------------------------------------------+------------------------------------------------------------------+----------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+ 1 row in set (0.00 sec) mysql>
mysql> select * from performance_schema.events_statements_history where thread_id=46 order by event_id; +-----------+----------+--------------+---------------------------------+---------------------------------+------------------+------------------+-------------+-----------+----------------------------------------------------+------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+ | THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME | SOURCE | TIMER_START | TIMER_END | TIMER_WAIT | LOCK_TIME | SQL_TEXT | DIGEST | DIGEST_TEXT | CURRENT_SCHEMA | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | MYSQL_ERRNO | RETURNED_SQLSTATE | MESSAGE_TEXT | ERRORS | WARNINGS | ROWS_AFFECTED | ROWS_SENT | ROWS_EXAMINED | CREATED_TMP_DISK_TABLES | CREATED_TMP_TABLES | SELECT_FULL_JOIN | SELECT_FULL_RANGE_JOIN | SELECT_RANGE | SELECT_RANGE_CHECK | SELECT_SCAN | SORT_MERGE_PASSES | SORT_RANGE | SORT_ROWS | SORT_SCAN | NO_INDEX_USED | NO_GOOD_INDEX_USED | NESTING_EVENT_ID | NESTING_EVENT_TYPE | NESTING_EVENT_LEVEL | +-----------+----------+--------------+---------------------------------+---------------------------------+------------------+------------------+-------------+-----------+----------------------------------------------------+------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+ | 46 | 33 | 33 | statement/com/Field List | init_net_server_extension.cc:95 | 4104147871107000 | 4104148468098000 | 596991000 | 450000000 | NULL | NULL | NULL | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 | | 46 | 34 | 35 | statement/sql/show_tables | init_net_server_extension.cc:95 | 4106939208994000 | 4106940533255000 | 1324261000 | 760000000 | show tables | e60aef21fcdd1f23c8bc9b9f5002614d0aeb5c0b7c5bc12618209f0726b16a46 | SELECT `cat` . `name` AS `TABLE_CATALOG` , `sch` . `name` AS `TABLE_SCHEMA` , `tbl` . `name` AS `TABLE_NAME` , `tbl` . `type` AS `TABLE_TYPE` , IF ( ( `tbl` . `type` = ? ) , `tbl` . `engine` , ? ) AS `ENGINE` , IF ( ( `tbl` . `type` = ? ) , ?, ... ) AS `VERSION` , `tbl` . `row_format` AS `ROW_FORMAT` , `internal_table_rows` ( `sch` . `name` , `tbl` . `name` , IF ( `isnull` ( `tbl` . `partition_type` ) , `tbl` . `engine` , ? ) , `tbl` . `se_private_id` , ( `tbl` . `hidden` != ? ) , `ts` . `se_private_data` , COALESCE ( `stat` . `table_rows` , ? ) , COALESCE ( CAST ( `stat` . `cached_time` AS UNSIGNED ) , ? ) ) AS `TABLE_ROWS` , `internal_avg_row_length` ( `sch` . `name` , `tbl` . `name` , IF ( `isnull` ( `tbl` . `partition_type` ) , `tbl` . `engine` , ? ) , `tbl` . `se_private_id` , ( `tbl` . `hidden` != ? ) , `ts` . `se_private_data` , COALESCE ( `stat` . `avg_row_length` , ? ) , COALESCE ( CAST ( `stat` . `cached_time` AS UNSIGNED ) , ? ) ) AS `AVG_ROW_LENGTH` , `internal_data_length` ( `sch` . `name` , `tbl` . | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 24 | 104 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 24 | 1 | 0 | 0 | NULL | NULL | 0 | | 46 | 36 | 37 | statement/sql/select | init_net_server_extension.cc:95 | 4115308650886000 | 4115326637721000 | 17986835000 | 114000000 | select * from t2 | c74f7ac2e7520fcbd10db5df148c439167b387c3a0f64d4980a9f549dbbf2119 | SELECT * FROM `t2` | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 | | 46 | 38 | 39 | statement/sql/select | init_net_server_extension.cc:95 | 4117384414808000 | 4117417965409000 | 33550601000 | 132000000 | select * from t1 | 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a | SELECT * FROM `t1` | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 | | 46 | 40 | 41 | statement/sql/select | init_net_server_extension.cc:95 | 4127935607763000 | 4127969000937000 | 33393174000 | 133000000 | select * from student | 054c03cefdffd38c41c9012f4df4fbaa40f747b8a6dbe924458daf8e92ccd3bc | SELECT * FROM `student` | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 13 | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 | | 46 | 42 | 42 | statement/sql/show_create_table | init_net_server_extension.cc:95 | 4136194629870000 | 4136194937040000 | 307170000 | 0 | show create table student | 23a0a09d9efd6567b078ffc509d9a4cc0455cd04872a5bda641323296536a671 | SHOW CREATE TABLE `student` | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 | | 46 | 43 | 44 | statement/sql/select | init_net_server_extension.cc:95 | 4153823209414000 | 4153823435210000 | 225796000 | 104000000 | select * from student | 054c03cefdffd38c41c9012f4df4fbaa40f747b8a6dbe924458daf8e92ccd3bc | SELECT * FROM `student` | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 13 | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | 0 | | 46 | 45 | 46 | statement/sql/select | init_net_server_extension.cc:95 | 4270354929331000 | 4270355208201000 | 278870000 | 110000000 | select * from student where stuid = 12 | 69387c1f86ce2121d9b343c1504316f27219170bbc09d63ea820da4420a1b2c4 | SELECT * FROM `student` WHERE `stuid` = ? | school | NULL | NULL | NULL | NULL | 0 | NULL | NULL | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 | | 46 | 47 | 47 | statement/sql/set_option | init_net_server_extension.cc:95 | 4330048794531000 | 4330048943630000 | 149099000 | 0 | set autocommit = 0 | 158bfe3d4b4b0a372a4acdb38affe2cb4f6b3cb21a9af0ea9fad9a9a083ac18a | SET `autocommit` = ? | school | NULL | NULL | NULL | NULL | 0 | 00000 | NULL | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 | | 46 | 48 | 49 | statement/sql/update | init_net_server_extension.cc:95 | 4362938279055000 | 4362938832688000 | 553633000 | 144000000 | update student set sname='小三' where stuid = 12 | 40ce967e64aeeb1f8e87d1bf618ba90d8f9a95fcb861618f52e1776b889f04fb | UPDATE `student` SET `sname` = ? WHERE `stuid` = ? | school | NULL | NULL | NULL | NULL | 0 | 00000 | Rows matched: 1 Changed: 1 Warnings: 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | 0 | +-----------+----------+--------------+---------------------------------+---------------------------------+------------------+------------------+-------------+-----------+----------------------------------------------------+------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+-------------+---------------+-------------+-----------------------+-------------+-------------------+------------------------------------------+--------+----------+---------------+-----------+---------------+-------------------------+--------------------+------------------+------------------------+--------------+--------------------+-------------+-------------------+------------+-----------+-----------+---------------+--------------------+------------------+--------------------+---------------------+ 10 rows in set (0.00 sec) mysql>
•performance_schema.data_locks记录了InnoDB中事务的每个锁信息，以及当前事务的锁正在阻⽌其他事务获得锁
mysql> select * from performance_schema.data_locks ; Empty set (0.28 sec) mysql> select * from Information_schema.innodb_trx ; Empty set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from teacher; +------+---------+ | tno | tname | +------+---------+ | t001 | 刘冬2 | | t002 | 刘冬 | | t003 | 刘冬 | | t004 | 刘冬 | | t005 | 刘冬 | +------+---------+ 5 rows in set (0.01 sec) mysql> select * from Information_schema.innodb_trx ; +-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking | +-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ | 421783691864824 | RUNNING | 2021-05-23 18:15:17 | NULL | NULL | 0 | 8 | select * from Information_schema.innodb_trx | NULL | 0 | 0 | 0 | 1136 | 0 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | +-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+ 1 row in set (0.00 sec) mysql> select * from performance_schema.data_locks ; Empty set (0.00 sec) mysql> mysql> mysql> #说明单纯的select查询不会加锁 mysql> select * from teacher lock in shade mode;#增加一个共享意图锁 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'shade mode' at line 1 mysql> select * from teacher lock in shade mode; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'shade mode' at line 1 mysql> select * from teacher lock in share mode;#增加一个共享意图锁 +------+---------+ | tno | tname | +------+---------+ | t001 | 刘冬2 | | t002 | 刘冬 | | t003 | 刘冬 | | t004 | 刘冬 | | t005 | 刘冬 | +------+---------+ 5 rows in set (0.00 sec) mysql> 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 | 421783691864824:1104 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | NULL | 140308595648536 | TABLE | IS | GRANTED | NULL | | INNODB | 421783691864824:46:4:1 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 421783691864824:46:4:3 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S | GRANTED | 't002' | | INNODB | 421783691864824:46:4:4 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S | GRANTED | 't003' | | INNODB | 421783691864824:46:4:5 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S | GRANTED | 't005' | | INNODB | 421783691864824:46:4:6 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S | GRANTED | 't004' | | INNODB | 421783691864824:46:4:7 | 421783691864824 | 46 | 43 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S | GRANTED | 't001' | +--------+------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ 7 rows in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from performance_schema.data_locks ; Empty set (0.00 sec) mysql> #刚刚由于没有用where筛选，所以对表里每一行都加了共享意图锁 mysql> select * from teacher where tno='t001' lock in share mode;#增加一个共享意图锁 +------+---------+ | tno | tname | +------+---------+ | t001 | 刘冬2 | +------+---------+ 1 row in set (0.00 sec) mysql> 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 | 421783691864824:1104 | 421783691864824 | 46 | 48 | school | teacher | NULL | NULL | NULL | 140308595648536 | TABLE | IS | GRANTED | NULL | | INNODB | 421783691864824:46:4:7 | 421783691864824 | 46 | 48 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S,REC_NOT_GAP | GRANTED | 't001' | +--------+------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ 2 rows in set (0.00 sec) mysql>
接着再另个session也执行下
mysql> use school Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from teacher where tno='t001' lock in share mode;#两个IS 之间是包容的 +------+---------+ | tno | tname | +------+---------+ | t001 | 刘冬2 | +------+---------+ 1 row in set (0.00 sec) mysql> 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 | 421783691864824:1104 | 421783691864824 | 46 | 48 | school | teacher | NULL | NULL | NULL | 140308595648536 | TABLE | IS | GRANTED | NULL | | INNODB | 421783691864824:46:4:7 | 421783691864824 | 46 | 48 | school | teacher | NULL | NULL | PRIMARY | 140308595645496 | RECORD | S,REC_NOT_GAP | GRANTED | 't001' | +--------+------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ 2 rows in set (0.00 sec) mysql> update teacher set tname='张三' where tno='t001';#IS与X是冲突的，所以进入阻塞状态，得等IS提交。 #
session 1 mysql> commit; Query OK, 0 rows affected (0.00 sec) session 2 mysql> update teacher set tname='张三' where tno='t001'; ERROR 1062 (23000): Duplicate entry 't001' for key 'PRIMARY' mysql>
•sys.innodb_lock_waits记录了InnoDB中事务之间相互等待锁的信息