Mysql中行级锁的示例分析

发布时间：2021-07-27 10:41:00 作者：小新
来源：亿速云阅读：183

# MySQL中行级锁的示例分析

## 一、行级锁概述

### 1.1 什么是行级锁
行级锁是MySQL中最细粒度的锁机制，它允许事务锁定表中的单行记录，而不是整个表或页。这种锁定方式可以最大程度地支持并发处理，因为不同事务可以同时访问表的不同行。

### 1.2 行级锁的特点
- **高并发性**：多个事务可以同时访问表的不同行
- **低冲突率**：相比表锁，行锁大大减少了锁冲突
- **开销较大**：需要维护更多的锁信息，内存消耗更高
- **死锁风险**：复杂的加锁顺序可能导致死锁

### 1.3 行级锁的实现基础
MySQL的行级锁是通过存储引擎实现的，InnoDB是MySQL中最常用的支持行级锁的存储引擎。InnoDB的行锁是通过对索引记录加锁实现的。

## 二、行级锁的类型

### 2.1 记录锁(Record Lock)
记录锁是锁定索引中的单条记录。

```sql
-- 示例：对id=1的记录加记录锁
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 其他事务不能修改或删除id=1的记录
COMMIT;

2.2 间隙锁(Gap Lock)

间隙锁锁定索引记录之间的间隙，防止其他事务在这个间隙中插入数据。

-- 示例：对id在5和10之间的间隙加锁
BEGIN;
SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- 其他事务不能插入id在5和10之间的新记录
COMMIT;

2.3 临键锁(Next-Key Lock)

临键锁是记录锁和间隙锁的组合，既锁定记录本身，也锁定记录前面的间隙。

-- 示例：对id=15的记录及其前面的间隙加锁
BEGIN;
SELECT * FROM users WHERE id = 15 FOR UPDATE;
-- 其他事务不能插入id=14到15之间的新记录，也不能修改id=15的记录
COMMIT;

2.4 插入意向锁(Insert Intention Lock)

插入意向锁是一种特殊的间隙锁，表示事务打算在某个间隙插入记录。

-- 示例：事务尝试在id=20和id=25之间插入记录
BEGIN;
INSERT INTO users (id, name) VALUES (22, 'John');
-- 这个操作会获取插入意向锁
COMMIT;

三、行级锁的实战分析

3.1 基本加锁场景分析

场景1：主键查询

-- 事务1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 对id=1的记录加记录锁

-- 事务2
BEGIN;
UPDATE users SET name = 'Alice' WHERE id = 1; -- 被阻塞

场景2：非唯一索引查询

-- 假设name字段有普通索引
-- 事务1
BEGIN;
SELECT * FROM users WHERE name = 'Bob' FOR UPDATE;
-- 对name='Bob'的索引记录加临键锁

-- 事务2
BEGIN;
INSERT INTO users (name) VALUES ('Bobby'); -- 可能被阻塞，取决于具体值

3.2 复杂加锁场景

场景3：范围查询

-- 事务1
BEGIN;
SELECT * FROM users WHERE id > 10 AND id < 20 FOR UPDATE;
-- 对id在10-20之间的记录和间隙加锁

-- 事务2
BEGIN;
INSERT INTO users (id) VALUES (15); -- 被阻塞

场景4：无索引查询

-- 事务1
BEGIN;
SELECT * FROM users WHERE age = 30 FOR UPDATE; -- age无索引
-- 实际上会升级为表锁

-- 事务2
BEGIN;
UPDATE users SET name = 'Charlie' WHERE id = 5; -- 被阻塞

3.3 死锁场景分析

死锁示例1：交叉更新

-- 事务1
BEGIN;
UPDATE users SET score = score + 10 WHERE id = 1;
-- 持有id=1的锁

-- 事务2
BEGIN;
UPDATE users SET score = score + 20 WHERE id = 2;
-- 持有id=2的锁

-- 事务1
UPDATE users SET score = score + 5 WHERE id = 2; -- 等待事务2释放锁

-- 事务2
UPDATE users SET score = score + 5 WHERE id = 1; -- 等待事务1释放锁
-- 此时发生死锁

死锁示例2：间隙锁冲突

-- 事务1
BEGIN;
SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- 持有5-10的间隙锁

-- 事务2
BEGIN;
SELECT * FROM users WHERE id BETWEEN 8 AND 15 FOR UPDATE;
-- 持有8-15的间隙锁

-- 事务1
INSERT INTO users (id) VALUES (12); -- 等待事务2

-- 事务2
INSERT INTO users (id) VALUES (7); -- 等待事务1
-- 死锁发生

四、行级锁的监控与诊断

4.1 查看锁状态

-- 查看当前InnoDB锁状态
SHOW ENGINE INNODB STATUS;

-- 查看锁等待情况
SELECT * FROM performance_schema.events_waits_current 
WHERE event_name LIKE '%lock%';

-- 查看被阻塞的事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'LOCK WT';

4.2 分析锁冲突

-- 查看锁等待关系
SELECT 
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

4.3 死锁日志分析

当发生死锁时，MySQL会记录死锁信息到错误日志中。通过分析这些日志可以了解死锁原因：

LATEST DETECTED DEADLOCK
------------------------
2023-05-01 10:20:30 0x7f8e4418b700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 140123, query id 100 localhost root update
UPDATE users SET name='Alice' WHERE id=1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table `test`.`users` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000012345; asc     E;;
 2: len 7; hex 82000000a20110; asc        ;;
 3: len 5; hex 416c696365; asc Alice;;

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 140124, query id 101 localhost root update
UPDATE users SET name='Bob' WHERE id=2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`users` trx id 123457 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000012346; asc     F;;
 2: len 7; hex 82000000a30110; asc        ;;
 3: len 3; hex 426f62; asc Bob;;

*** (2) WTING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table `test`.`users` trx id 123457 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000012345; asc     E;;
 2: len 7; hex 82000000a20110; asc        ;;
 3: len 5; hex 416c696365; asc Alice;;

*** WE ROLL BACK TRANSACTION (2)

五、行级锁优化策略

5.1 索引优化

确保查询使用合适的索引
避免全表扫描导致锁升级
考虑使用覆盖索引减少锁冲突

5.2 事务优化

缩短事务执行时间
减小事务粒度
避免在事务中执行耗时操作

5.3 锁优化

合理选择锁类型(FOR UPDATE vs LOCK IN SHARE MODE)
考虑使用乐观锁替代悲观锁
批量操作时注意锁升级风险

5.4 死锁预防

按照固定顺序访问表和行
减少长事务
设置合理的锁等待超时时间(innodb_lock_wait_timeout)

六、总结

MySQL的行级锁机制为高并发应用提供了强大的支持，但同时也带来了复杂性和潜在的性能问题。通过深入理解不同类型的行级锁及其应用场景，开发人员可以更好地设计数据库应用，避免常见的锁冲突和死锁问题。

实际应用中，应当结合业务场景选择合适的锁策略，并通过监控工具及时发现和解决锁相关问题。记住，没有放之四海而皆准的锁优化方案，每个应用都需要根据自身特点进行调优。

七、参考资料

MySQL官方文档 - InnoDB锁机制
《高性能MySQL》第三版
《MySQL技术内幕：InnoDB存储引擎》
各种MySQL锁相关的博客和技术文章

”`