- 锁结构
- 锁的监控
- 对一条数据加锁的本质就是创建一个锁结构与其关联
如果一个事务要获取10000条记录的锁，不会生成10000个锁结构，而是会将相同类型记录的锁放到一个锁结构中，只要符合以下条件：
- 加锁记录在同一个事务
- 加锁记录在同一个页面中
- 加锁类型和等待状态相同
具体结构
每一层是干啥的
- 锁所在事务信息：指针，指向事务更多信息
- 索引信息：指针，加锁记录属于哪个索引
- 表锁/行锁信息：
- 如果是表锁，记录对哪个表
- 如果是行锁，记录Spring ID表空间、PageNumber 所在页号、n_bits哪个比特位
- type_mode:32位的二进制数，划分成以下三个部分
- 锁的模式：表示锁是什么锁，S锁还是X锁，IS还是IX锁，通过十进制的0,1，2,3,4表示
- 锁的类型：表锁还是行锁
- 记录锁的类型：进一步划分，临键锁还是gap锁？记录锁？
- 这个32位数的第9位表示is_waiting，数值为1，表示为true。数值为0，表示为false；
- 其他信息：放了一些哈希表和链表
最常用的就是状态变量Innodb_row_lock，通过查看状态变量，我们可以分析行锁的竞争情况.
mysql> show status like 'innodb_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | +-------------------------------+-------+ 5 rows in set (0.04 sec)
MySQL中事务和锁的信息都记录在了information_schema库中,最常用的三张表如下
Innodb_TRX，lOCKS，Lock_WAITS
查看正在被阻塞的SQL语句
mysql> select *from information_schema.INNODB_TRXG
*************************** 1. row ***************************
trx_id: 93673
trx_state: RUNNING
trx_started: 2022-05-13 15:44:20
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 74
trx_query: select *from information_schema.INNODB_TRX
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1128
trx_rows_locked: 6
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
trx_schedule_weight: NULL
1 row in set (0.04 sec)
可以查看阻碍事务的锁和该事务持有的锁
mysql> select*from performance_schema.data_locksG
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:1158:2879228480536
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2879228480536
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:97:4:1:2879212424216
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:97:4:2:2879212424216
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:97:4:3:2879212424216
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:97:4:4:2879212424216
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 8
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:97:4:5:2879212424216
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 15
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2879223041216:97:4:6:2879212424216
ENGINE_TRANSACTION_ID: 93673
THREAD_ID: 157
EVENT_ID: 20
OBJECT_SCHEMA: db2
OBJECT_NAME: student
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2879212424216
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 20
7 rows in set (0.00 sec)
mysql> select*from performance_schema.data_lock_waitsG
Empty set (0.00 sec)
