一、表级锁（Table Lock）
1、表锁（Table Lock）
- 表共享读锁（Table Read Lock）：通过
LOCK TABLES table_name READ语句获取，允许其他会话读取表，但不允许写入。 - 表独占写锁（Table Write Lock）：通过
LOCK TABLES table_name WRITE语句获取，不允许其他会话读取或写入表。
2、元数据锁（Metadata Lock，MDL）
- 当执行DDL操作（如ALTER TABLE、DROP TABLE等）或者某些特定的操作（如LOCK TABLES、FLUSH TABLES WITH READ LOCK等）时，会自动获取MDL锁。
- MDL锁用于保护表的元数据，防止其他会话同时修改表结构。
3、出现表级锁的情况
- 显式使用
LOCK TABLES语句获取表锁。 - 执行 DDL 操作时自动获取 MDL 锁。
- 使用 MyISAM 存储引擎时，读写操作会自动获取表锁。
- FLUSH TABLES WITH READ LOCK 语句也会获取表级别的读锁，用于备份等场景。
4、定位和排查表级锁
- 使用
SHOW PROCESSLIST或INFORMATION_SCHEMA.INNODB_TRX表查看当前的锁信息和事务状态。 - 通过 MySQL 错误日志或慢查询日志分析锁等待和阻塞情况。
- 使用 MySQL 性能分析工具，如 MySQL Enterprise Monitor、Percona Toolkit 等，分析锁的使用情况。
5、表级锁的影响
- 并发性能下降：表级锁会阻塞其他会话对该表的访问，降低并发性能。
- 锁等待和死锁：如果多个会话同时请求表锁，可能导致锁等待或死锁，影响系统可用性。
- DDL 操作阻塞：当执行 DDL 操作时，会获取 MDL 锁，阻塞其他会话对该表的访问。
6、模拟表级锁：
1、使用 LOCK TABLES 语句显式获取表锁：
- 创建数据
(root@localhost) [test]> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
(root@localhost) [test]> insert into t1 values(1,'yc');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [test]> insert into t1 values(2,'wz');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> insert into t1 values(3,'zw');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [test]> insert into t1 values(3,'szs');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> commit;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test]> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | yc |
| 2 | wz |
| 3 | zw |
| 3 | szs |
+------+------+
4 rows in set (0.00 sec)模拟锁
--session 1
(root@localhost) [test]> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)
--session 2
(root@localhost) [test]> insert into t1 values(6,'sh');
--session 3
(root@localhost) [test]> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
| 7 | root | localhost | test | Sleep | 77 | | NULL |
| 8 | root | localhost | test | Query | 48 | Waiting for table metadata lock | insert into t1 values(6,'sh') |
| 9 | root | localhost | test | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
3 rows in set (0.00 sec)由Waiting for table metadata lock可以看出等待获取元数据锁。
--session 1
(root@localhost) [test]> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)
--session 2
(root@localhost) [test]> insert into t1 values(6,'sh');
--session 3
(root@localhost) [test]> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
| 7 | root | localhost | test | Sleep | 22 | | NULL |
| 8 | root | localhost | test | Query | 7 | Waiting for table metadata lock | insert into t1 values(6,'sh') |
| 9 | root | localhost | test | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+---------------------------------+-------------------------------+
3 rows in set (0.00 sec)为了避免表级锁的影响，可以采取以下措施：
- 尽量避免显式使用
LOCK TABLES语句。 - 使用 InnoDB 存储引擎，它支持行级锁，可以提高并发性能。
- 合理设计索引，加快查询速度，减少锁的持有时间。
- 优化SQL语句，避免长时间的事务和锁等待。
- 合理设置MySQL参数，如
innodb_lock_wait_timeout，防止长时间的锁等待。
二、行级锁（Row Lock）
1、行级锁的类型：
- 共享锁（S Lock）：允许事务读取一行数据。
- 排他锁（X Lock）：允许事务删除或更新一行数据。
2、出现行级锁的情况
- 当事务对数据行执行 SELECT … FOR UPDATE 或 SELECT … LOCK IN SHARE MODE 时，会获取该行的排他锁或共享锁。
- 当事务对数据行执行 UPDATE、DELETE 或 INSERT 操作时，会自动获取该行的排他锁。
- 当查询条件没有使用到索引，或者使用了索引但未完全匹配时，可能会进行全表扫描，导致大量的行被锁定。
3、定位和排查行级锁问题：
- 使用 SHOW ENGINE INNODB STATUS 命令查看 InnoDB 的锁情况，包括事务的锁等待和锁争用情况。
- 查询 information_schema.INNODB_TRX 表，了解当前运行的事务及其状态。
- 查询 information_schema.INNODB_LOCKS 和 information_schema.INNODB_LOCK_WAITS 表，了解锁的详细信息和等待情况。
- 使用 performance_schema 数据库中的 data_locks 和 data_lock_waits 表，获取更详细的锁信息。
- 通过慢查询日志和 EXPLAIN 分析 SQL 语句的执行计划，优化查询条件和索引使用。
4、行级锁的影响
- 行级锁可以提高数据库的并发性能，因为锁定粒度更小，不会阻塞其他行的读写操作。
- 但是，如果查询条件没有合适的索引，或者锁争用频繁，也可能导致性能下降和死锁问题。
- 频繁的行级锁操作会增加系统开销，因为需要维护锁信息和进行锁的获取和释放。
5、模拟行级锁
- 模拟行级锁
--session 1
(root@localhost) [test]> update t1 set name='bj' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--session 2
(root@localhost) [test]> update t1 set name='gz' where id=1;- 查看锁
(root@localhost) [test]> 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 | trx_schedule_weight |
+-----------------+-----------+---------------------+----------------------------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
| 17432 | LOCK WAIT | 2024-03-15 11:43:38 | 140187079622656:83:4:2:140186994401432 | 2024-03-15 11:45:50 | 2 | 8 | update t1 set name='gz' where id=1 | starting index read | 1 | 1 | 2 | 1128 | 2 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | 1 |
| 17431 | RUNNING | 2024-03-15 11:43:42 | NULL | NULL | 3 | 7 | NULL | NULL | 0 | 1 | 2 | 1128 | 5 | 1 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL |
| 421662056332504 | RUNNING | 2024-03-15 11:43:33 | NULL | NULL | 0 | 10 | SELECT * FROM information_schema.INNODB_TRX | NULL | 0 | 0 | 0 | 1128 | 0 | 0 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL |
+-----------------+-----------+---------------------+----------------------------------------+---------------------+------------+---------------------+---------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+
3 rows in set (0.00 sec)(root@localhost) [test]> SELECT * FROM performance_schema.data_lock_waits;
+--------+----------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
| ENGINE | REQUESTING_ENGINE_LOCK_ID | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN |
+--------+----------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
| INNODB | 140187079622656:83:4:2:140186994401432 | 17432 | 47 | 11 | 140186994401432 | 140187079623464:83:4:2:140186994407072 | 17431 | 46 | 18 | 140186994407072 |
+--------+----------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+----------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
1 row in set (0.00 sec)(root@localhost) [test]> 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 | 140187079622656:1336:140186994404000 | 17432 | 47 | 10 | test | t1 | NULL | NULL | NULL | 140186994404000 | TABLE | IX | GRANTED | NULL |
| INNODB | 140187079622656:83:4:2:140186994401432 | 17432 | 47 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994401432 | RECORD | X | WAITING | 0x000000007400 |
| INNODB | 140187079623464:1336:140186994409984 | 17431 | 46 | 18 | test | t1 | NULL | NULL | NULL | 140186994409984 | TABLE | IX | GRANTED | NULL |
| INNODB | 140187079623464:83:4:1:140186994407072 | 17431 | 46 | 18 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 140187079623464:83:4:2:140186994407072 | 17431 | 46 | 18 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007400 |
| INNODB | 140187079623464:83:4:3:140186994407072 | 17431 | 46 | 18 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007401 |
| INNODB | 140187079623464:83:4:4:140186994407072 | 17431 | 46 | 18 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007402 |
| INNODB | 140187079623464:83:4:5:140186994407072 | 17431 | 46 | 18 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007403 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.00 sec)(root@localhost) [test]> show processlist;
+----+------+-----------+------+---------+------+----------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------------------------+
| 7 | root | localhost | test | Sleep | 19 | | NULL |
| 8 | root | localhost | test | Query | 15 | updating | update t1 set name='gz' where id=1 |
| 10 | root | localhost | test | Query | 0 | init | show processlist |
+----+------+-----------+------+---------+------+----------+------------------------------------+以上可以看到，最方便的定位方法是
select ENGINE,REQUESTING_ENGINE_TRANSACTION_ID,BLOCKING_ENGINE_TRANSACTION_ID from performance_schema.data_lock_waits;
--阻塞者
select a.trx_id,a.trx_state,a.trx_started,a.trx_wait_started,a.trx_query from information_schema.INNODB_TRX a,performance_schema.data_lock_waits b where a.trx_id=b.BLOCKING_ENGINE_TRANSACTION_ID;
--被阻塞者
select a.trx_id,a.trx_state,a.trx_started,a.trx_wait_started,a.trx_query from information_schema.INNODB_TRX a,performance_schema.data_lock_waits b where a.trx_id=b.REQUESTING_ENGINE_TRANSACTION_ID ;(root@localhost) [test]> 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 | 140187079622656:1336:140186994404000 | 17434 | 50 | 11 | test | t1 | NULL | NULL | NULL | 140186994404000 | TABLE | IX | GRANTED | NULL |
| INNODB | 140187079623464:1336:140186994409984 | 421662056334120 | 52 | 11 | test | t1 | NULL | NULL | NULL | 140186994409984 | TABLE | IS | GRANTED | NULL |
| INNODB | 140187079623464:83:4:1:140186994407072 | 421662056334120 | 52 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | S | GRANTED | supremum pseudo-record |
| INNODB | 140187079623464:83:4:2:140186994407072 | 421662056334120 | 52 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | S | GRANTED | 0x000000007400 |
| INNODB | 140187079623464:83:4:3:140186994407072 | 421662056334120 | 52 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | S | GRANTED | 0x000000007401 |
| INNODB | 140187079623464:83:4:4:140186994407072 | 421662056334120 | 52 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | S | GRANTED | 0x000000007402 |
| INNODB | 140187079623464:83:4:5:140186994407072 | 421662056334120 | 52 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | S | GRANTED | 0x000000007403 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+可以看到IX和IS冲突
三、意向锁
意向锁是MySQL InnoDB存储引擎中的一种表级锁，用于在行级锁之前指示事务意图获取哪种类型的锁（共享锁或排他锁）。意向锁可以提高并发性能，减少锁冲突。
1、锁种类
- 意向共享锁（IS Lock）：事务打算在表中的某些行上设置共享锁（S Lock）。
- 意向排他锁（IX Lock）：事务打算在表中的某些行上设置排他锁（X Lock）。
2、出现意向锁的情况
- 当事务准备在表中的某些行上设置共享锁时，会在表级别上请求意向共享锁。
- 当事务准备在表中的某些行上设置排他锁时，会在表级别上请求意向排他锁。
3、定位和排查意向锁问题
- 使用 SHOW ENGINE INNODB STATUS 命令查看 InnoDB 的锁情况，包括意向锁的争用情况。
- 查询 information_schema.INNODB_TRX、information_schema.INNODB_LOCKS 和 information_schema.INNODB_LOCK_WAITS 表，了解事务、锁的详细信息和等待情况。
- 使用 performance_schema 数据库中的 data_locks 和 data_lock_waits 表，获取更详细的锁信息。
4、意向锁的影响
- 意向锁本身不会阻塞其他事务的读写操作，它们只是表明事务意图获取行级锁的一种机制。
- 意向锁可以提高并发性能，因为它们允许多个事务同时在表级别上表明自己的意图，而不需要实际获取行级锁。
- 意向锁与行级锁的兼容性规则可以减少锁冲突，提高并发性。例如，多个事务可以同时持有意向共享锁，但意向共享锁与意向排他锁是互斥的。
5、模拟意向锁
(root@localhost) [test]> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | gz |
| 2 | wz |
| 3 | zw |
| 3 | szs |
+------+------+
4 rows in set (0.00 sec)
(root@localhost) [test]> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_tables_locked,trx_lock_structs,trx_rows_locked from information_schema.INNODB_TRX;
+-----------------+-----------+---------------------+-----------------------+------------------+-------------------+------------------+-----------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-----------------+-----------+---------------------+-----------------------+------------------+-------------------+------------------+-----------------+
| 421662056334120 | RUNNING | 2024-03-15 15:15:23 | NULL | NULL | 0 | 0 | 0 |
| 421662056333312 | RUNNING | 2024-03-15 15:10:46 | NULL | NULL | 0 | 0 | 0 |
| 421662056332504 | RUNNING | 2024-03-15 11:43:33 | NULL | NULL | 0 | 0 | 0 |
+-----------------+-----------+---------------------+-----------------------+------------------+-------------------+------------------+-----------------+
3 rows in set (0.00 sec)(root@localhost) [test]> select * from t1 where id=1 lock in share mode;
+------+------+
| id | name |
+------+------+
| 1 | gz |
+------+------+
1 row in set (0.00 sec)
(root@localhost) [test]> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_tables_locked,trx_lock_structs,trx_rows_locked from information_schema.INNODB_TRX;
+-----------------+-----------+---------------------+-----------------------+------------------+-------------------+------------------+-----------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-----------------+-----------+---------------------+-----------------------+------------------+-------------------+------------------+-----------------+
| 421662056334120 | RUNNING | 2024-03-15 15:15:23 | NULL | NULL | 0 | 0 | 0 |
| 421662056333312 | RUNNING | 2024-03-15 15:10:46 | NULL | NULL | 0 | 0 | 0 |
| 421662056332504 | RUNNING | 2024-03-15 11:43:33 | NULL | NULL | 0 | 0 | 0 |
+-----------------+-----------+---------------------+-----------------------+------------------+-------------------+------------------+-----------------+
3 rows in set (0.00 sec)--session 1
(root@localhost) [test]> select * from t1 where id=1 lock in share mode;
+------+------+
| id | name |
+------+------+
| 1 | gz |
+------+------+
1 row in set (0.00 sec)
--session 2
(root@localhost) [test]> update t1 set name='bj' where id=1;
--session 3
(root@localhost) [test]> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_tables_locked,trx_lock_structs,trx_rows_locked from information_schema.INNODB_TRX;
+-----------------+-----------+---------------------+----------------------------------------+---------------------+-------------------+------------------+-----------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-----------------+-----------+---------------------+----------------------------------------+---------------------+-------------------+------------------+-----------------+
| 17434 | LOCK WAIT | 2024-03-15 15:10:46 | 140187079622656:83:4:2:140186994401088 | 2024-03-15 15:16:55 | 1 | 2 | 1 |
| 421662056334120 | RUNNING | 2024-03-15 15:15:23 | NULL | NULL | 1 | 2 | 5 |
| 421662056332504 | RUNNING | 2024-03-15 11:43:33 | NULL | NULL | 0 | 0 | 0 |
+-----------------+-----------+---------------------+----------------------------------------+---------------------+-------------------+------------------+-----------------+
3 rows in set (0.00 sec)利用前面sql看一下
(root@localhost) [test]> select a.trx_id,a.trx_state,a.trx_started,a.trx_wait_started,a.trx_query from information_schema.INNODB_TRX a,performance_schema.data_lock_waits b where a.trx_id=b.BLOCKING_ENGINE_TRANSACTION_ID;
+-----------------+-----------+---------------------+------------------+-----------+
| trx_id | trx_state | trx_started | trx_wait_started | trx_query |
+-----------------+-----------+---------------------+------------------+-----------+
| 421662056334120 | RUNNING | 2024-03-15 15:15:23 | NULL | NULL |
+-----------------+-----------+---------------------+------------------+-----------+
1 row in set (0.00 sec)
(root@localhost) [test]> select a.trx_id,a.trx_state,a.trx_started,a.trx_wait_started,a.trx_query from information_schema.INNODB_TRX a,performance_schema.data_lock_waits b where a.trx_id=b.REQUESTING_ENGINE_TRANSACTION_ID ;
+--------+-----------+---------------------+---------------------+------------------------------------+
| trx_id | trx_state | trx_started | trx_wait_started | trx_query |
+--------+-----------+---------------------+---------------------+------------------------------------+
| 17434 | LOCK WAIT | 2024-03-15 15:10:46 | 2024-03-15 15:18:47 | update t1 set name='bj' where id=1 |
+--------+-----------+---------------------+---------------------+------------------------------------+
1 row in set (0.00 sec)前面的意向锁因为与update不兼容阻塞了后面的update
四、间隙锁
MySQL间隙锁(Gap Lock)是InnoDB存储引擎为了防止幻读(Phantom Read)而设计的一种锁机制。它锁定的是索引记录之间的间隙,而不是记录本身。
1、间隙锁种类
- 记录锁(Record Lock):锁定单个索引记录。
- 间隙锁(Gap Lock):锁定索引记录之间的间隙。
- 次级间隙锁(Next-Key Lock):记录锁和间隙锁的结合,锁定记录本身和记录之前的间隙。
2、间隙锁场景
- 事务隔离级别为可重复读(REPEATABLE READ)时。
- 使用了范围条件检索记录,如between、>、<、>=、<=等操作符。
- 执行了加锁的操作,如SELECT … FOR UPDATE。
3、出现间隙锁的定位排查
- 通过SHOW ENGINE INNODB STATUS检查事务是否被阻塞。
- 使用INFORMATION_SCHEMA.INNODB_TRX表查看当前运行的事务。
- 使用INFORMATION_SCHEMA.INNODB_LOCKS表查看加锁的对象。
- 开启innodb_lock_monitor输出被阻塞的锁信息。
4、间隙锁的影响
- 并发性能降低,因为间隙被锁住了,其他事务需要等待。
- 可能导致死锁的发生。
- 如果间隙很大,会消耗大量内存。
5、模拟间隙锁
--session 1
(root@localhost) [test]> select * from t1 where id <5 for update;
+------+------+
| id | name |
+------+------+
| 1 | gz |
| 2 | wz |
| 3 | zw |
| 3 | szs |
+------+------+
4 rows in set (0.00 sec)
--session 2
(root@localhost) [test]> insert into t1 values(4,'sh');--session 3
(root@localhost) [test]> show processlist;
+----+------+-----------+------+---------+------+--------+-------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+--------+-------------------------------+
| 10 | root | localhost | test | Query | 0 | init | show processlist |
| 14 | root | localhost | test | Query | 2 | update | insert into t1 values(4,'sh') |
| 15 | root | localhost | test | Sleep | 152 | | NULL |
+----+------+-----------+------+---------+------+--------+-------------------------------+
3 rows in set (0.00 sec)
(root@localhost) [test]> 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 | 140187079622656:1336:140186994404000 | 17436 | 53 | 10 | test | t1 | NULL | NULL | NULL | 140186994404000 | TABLE | IX | GRANTED | NULL |
| INNODB | 140187079622656:83:4:1:140186994401432 | 17436 | 53 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994401432 | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record |
| INNODB | 140187079623464:1336:140186994409984 | 17435 | 54 | 11 | test | t1 | NULL | NULL | NULL | 140186994409984 | TABLE | IX | GRANTED | NULL |
| INNODB | 140187079623464:83:4:1:140186994407072 | 17435 | 54 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 140187079623464:83:4:2:140186994407072 | 17435 | 54 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007400 |
| INNODB | 140187079623464:83:4:3:140186994407072 | 17435 | 54 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007401 |
| INNODB | 140187079623464:83:4:4:140186994407072 | 17435 | 54 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007402 |
| INNODB | 140187079623464:83:4:5:140186994407072 | 17435 | 54 | 11 | test | t1 | NULL | NULL | GEN_CLUST_INDEX | 140186994407072 | RECORD | X | GRANTED | 0x000000007403 |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+--------------------+-------------+------------------------+可以看到X,INSERT_INTENTION
(root@localhost) [test]> select a.trx_id,a.trx_state,a.trx_started,a.trx_wait_started,a.trx_query from information_schema.INNODB_TRX a,performance_schema.data_lock_waits b where a.trx_id=b.BLOCKING_ENGINE_TRANSACTION_ID;
+--------+-----------+---------------------+------------------+-----------+
| trx_id | trx_state | trx_started | trx_wait_started | trx_query |
+--------+-----------+---------------------+------------------+-----------+
| 17435 | RUNNING | 2024-03-15 15:45:48 | NULL | NULL |
+--------+-----------+---------------------+------------------+-----------+
1 row in set (0.00 sec)
(root@localhost) [test]> select a.trx_id,a.trx_state,a.trx_started,a.trx_wait_started,a.trx_query from information_schema.INNODB_TRX a,performance_schema.data_lock_waits b where a.trx_id=b.REQUESTING_ENGINE_TRANSACTION_ID ;
+--------+-----------+---------------------+---------------------+-------------------------------+
| trx_id | trx_state | trx_started | trx_wait_started | trx_query |
+--------+-----------+---------------------+---------------------+-------------------------------+
| 17436 | LOCK WAIT | 2024-03-15 15:45:39 | 2024-03-15 15:48:05 | insert into t1 values(4,'sh') |
+--------+-----------+---------------------+---------------------+-------------------------------+
1 row in set (0.00 sec)五、自增锁
MySQL的自增锁是一种特殊的表级锁,用于协调多个事务对AUTO_INCREMENT列的插入操作,以保证自增值的唯一性和连续性。
1、MySQL的自增锁类型
- AUTO-INC锁:用于INSERT语句对AUTO_INCREMENT列的插入操作。
- 轻量级AUTO-INC锁:用于"简单插入"(不使用INSERT … SELECT等复杂语句)对AUTO_INCREMENT列的插入操作。
2、出现自增锁的情况
- 当多个事务同时对一个包含AUTO_INCREMENT列的表进行插入操作时,就可能出现自增锁的竞争。
- 当语句中包含多个AUTO_INCREMENT列时,也会出现自增锁。
- 当INSERT语句比较复杂(如INSERT … SELECT)时,会使用AUTO-INC锁而不是轻量级AUTO-INC锁。
3.定位和排查自增锁
- 通过查询 INFORMATION_SCHEMA.INNODB_LOCKS 和 INFORMATION_SCHEMA.INNODB_LOCK_WAITS 表,可以查看当前的锁情况和锁等待情况。
- 通过SHOW ENGINE INNODB STATUS命令,可以查看InnoDB的状态信息,其中包括锁的信息。
- 通过设置innodb_print_all_deadlocks参数为ON,可以在发生死锁时打印死锁信息到错误日志中。
4、自增锁的影响
- 自增锁的竞争可能导致插入操作的性能下降,因为多个事务需要等待锁的释放。
- 在某些情况下,自增锁可能导致死锁。例如,一个事务获取了AUTO-INC锁,然后尝试获取另一个事务持有的行锁,而那个事务又在等待AUTO-INC锁,就会导致死锁。
总的来说,自增锁是MySQL为了保证AUTO_INCREMENT列的一致性而设计的一种锁机制。在实际应用中,我们需要注意监控自增锁的竞争情况,优化插入操作以减少自增锁的影响,并且要注意避免可能导致死锁的情况。
