mysql8学习笔记32--锁机制和事务1

• InnoDB存储引擎支持行级锁,其大类可以细分为共享锁和排它锁两类 • 共享锁(S):允许拥有共享锁的事务读取该行数据。当一个事务拥有一行的共享锁时,另外的事务可以在同一行数据也获得共享锁,但另外的事务无法获得同一行数据上的排他锁 • 排它锁(X):允许拥有排它锁的事务修改或删除该行数据。当一个事务拥有一行的排他锁时,另外的事务在此行数据上无法获得共享锁和排它锁,只能等待第一个事务的锁释放 • 除了共享锁和排他锁之外,InnoDB也支持意图锁。该锁类型是属于表级锁,表明事务在后期会对该表的行施加共享锁或者排它锁。所以对意图锁也有两种类型:     • 共享意图锁(IS):事务将会对表的行施加共享锁     • 排他意图锁(IX):事务将会对表的行施加排它锁   • 举例来说select … for share mode语句就是施加了共享意图锁,⽽select … for update语句就是施加了排他意图锁 • 这四种锁之间的相互共存和排斥关系如下: mysql8学习笔记32--锁机制和事务1 • 所以决定⼀个事务请求为数据加锁时能否⽴即施加上锁,取决于该数据上已经存在的锁是否和请求的锁可以共存还是排斥关系,当相互之间是可以共存时则⽴即施加锁,当相互之间是排斥关系时则需要等待已经存在的锁被释放才能施加 •Information_schema.innodb_trx记录了InnoDB中每⼀个正在执⾏的事务,包括该事务获得的锁信息,事务开始时间,事务是否在等待锁等信息

mysql8学习笔记32--锁机制和事务1

 

#连接一:
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中事务的每个锁信息,以及当前事务的锁正在阻⽌其他事务获得锁

mysql8学习笔记32--锁机制和事务1

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中事务之间相互等待锁的信息

mysql8学习笔记32--锁机制和事务1

 

 

 

 

 

 

 
上一篇:MongoDB搭建及基础操作


下一篇:Java_School