mysql8学习笔记32--锁机制和事务1
MySQL 5.5 版本以后，information_schema(ski:mə) 库中新增了三个关于锁的表，亦即 innodb_trx 、data_locks 和 innodb_lock_waits 。其中 innodb_trx 表记录当前运行的所有事务，data_locks 表记录当前出现的锁，innodb_lock_waits 表记录锁等待的对应关系
• InnoDB存储引擎支持行级锁，其大类可以细分为共享锁和排它锁两类
• 共享锁(S)：允许拥有共享锁的事务读取该行数据。当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外的事务无法获得同一行数据上的排他锁
• 排它锁(X)：允许拥有排它锁的事务修改或删除该行数据。当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放
• 除了共享锁和排他锁之外，InnoDB也支持意图锁。该锁类型是属于表级锁，表明事务在后期会对该表的行施加共享锁或者排它锁。所以对意图锁也有两种类型：
• 共享意图锁(IS)：事务将会对表的行施加共享锁
• 排他意图锁(IX)：事务将会对表的行施加排它锁
• 举例来说select … for share mode语句就是施加了共享意图锁，⽽select … for update语句就是施加了排他意图锁
• 这四种锁之间的相互共存和排斥关系如下：
• 所以决定⼀个事务请求为数据加锁时能否⽴即施加上锁，取决于该数据上已经存在的锁是否和请求的锁可以共存还是排斥关系，当相互之间是可以共存时则⽴即施加锁，当相互之间是排斥关系时则需要等待已经存在的锁被释放才能施加
•Information_schema.innodb_trx记录了InnoDB中每⼀个正在执⾏的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息
#连接一： 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中事务的每个锁信息，以及当前事务的锁正在阻⽌其他事务获得锁
lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
| 列名 | 含义 |
|---|---|
| ENGINE | 存储引擎 |
| ENGINE_LOCK_ID | 锁的ID |
| ENGINE_TRANSACTION_ID | 存储引擎内部ID |
| THREAD_ID | trx_id |
| EVENT_ID | 会话ID |
| OBJECT_SCHEMA | 数据库名称 |
| OBJECT_NAME | 表名称 |
| PARTITION_NAME | 分区名称 |
| SUBPARTITION_NAME | 子分区名称 |
| INDEX_NAME | 索引名称 |
| OBJECT_INSTANCE_BEGIN | 锁的内存中的地址 |
| LOCK_TYPE | 锁的类型 |
| LOCK_MODE | 如何请求锁定 |
| LOCK_STATUS | 请求状态 |
| LOCK_DATA | 锁定数据量 |
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中事务之间相互等待锁的信息
