2. 首页 > MySQL教程 > 正文

MySQL教程FG269-MySQL锁机制详解

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：22

本文档风哥主要介绍MySQL锁机制的详细内容，包括锁类型、锁粒度、锁兼容性、死锁处理等，风哥教程参考MySQL官方文档，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 锁机制概述

MySQL锁机制是保证数据一致性和并发控制的重要手段：

# 锁机制概述

1. 锁的作用

MySQL锁的主要作用：
– 保证数据一致性
– 实现并发控制
– 防止数据冲突
– 实现事务隔离

锁的基本原理：
+——————-+
| 事务A请求锁 |
+——————-+
|
v
+——————-+
| 检查锁兼容性 |
+——————-+
|
+—–+—–+
| |
v v
+——-+ +——-+
| 兼容 | | 冲突 |
+——-+ +——-+
| |
v v
+——-+ +——-+
| 获取锁| | 等待 |
+——-+ +——-+

2. 锁的分类

按锁类型分类：
+——————-+——————————————+
| 锁类型 | 描述 |
+——————-+——————————————+
| 共享锁(S锁) | 允许读，阻止写 |
| 排他锁(X锁) | 阻止读写 |
| 意向共享锁(IS) | 表级锁，表示有意向获取行级S锁 |
| 意向排他锁(IX) | 表级锁，表示有意向获取行级X锁 |
+——————-+——————————————+

按锁粒度分类：
+——————-+——————————————+
| 锁粒度 | 描述 |
+——————-+——————————————+
| 全局锁 | 锁定整个数据库实例 |
| 表级锁 | 锁定整张表 |
| 页级锁 | 锁定数据页 |
| 行级锁 | 锁定单行数据 |
+——————-+——————————————+

3. 查看锁信息

查看当前锁：
mysql> SELECT * FROM performance_schema.data_locks\G

输出示例：
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 12345:1:2:1
ENGINE_TRANSACTION_ID: 12345
THREAD_ID: 25
EVENT_ID: 100
OBJECT_SCHEMA: production_db
OBJECT_NAME: orders
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140123456789012
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1

查看锁等待：
mysql> SELECT * FROM performance_schema.data_lock_waits\G

输出示例：
*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_LOCK_ID: 12346:1:2:1
REQUESTING_ENGINE_TRANSACTION_ID: 12346
REQUESTING_THREAD_ID: 26
REQUESTING_EVENT_ID: 101
REQUESTING_OBJECT_INSTANCE_BEGIN: 140123456789013
BLOCKING_ENGINE_LOCK_ID: 12345:1:2:1
BLOCKING_ENGINE_TRANSACTION_ID: 12345
BLOCKING_THREAD_ID: 25
BLOCKING_EVENT_ID: 100
BLOCKING_OBJECT_INSTANCE_BEGIN: 140123456789012

4. 锁相关参数

查看锁参数：
mysql> SHOW VARIABLES LIKE ‘%lock%’;

输出示例：
+—————————————+————-+
| Variable_name | Value |
+—————————————+————-+
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_autoinc_lock_mode | 2 |
| lock_wait_timeout | 31536000 |
+—————————————+————-+

查看锁状态：
mysql> SHOW STATUS LIKE ‘%lock%’;

输出示例：
+——————————-+——-+
| Variable_name | Value |
+——————————-+——-+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 12345 |
| Innodb_row_lock_time_avg | 1000 |
| Innodb_row_lock_time_max | 5000 |
| Innodb_row_lock_waits | 100 |
+——————————-+——-+

1.2 锁类型介绍

MySQL支持多种锁类型，不同类型适用于不同场景：

# 锁类型介绍

1. 全局锁

特点：
– 锁定整个数据库实例
– 只读状态
– 用于全库备份

获取全局锁：
mysql> FLUSH TABLES WITH READ LOCK;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看全局锁状态：
mysql> SHOW OPEN TABLES WHERE In_use > 0;

输出示例：
+—————+————-+——–+————-+
| Database | Table | In_use | Name_locked |
+—————+————-+——–+————-+
| production_db | orders | 1 | 0 |
+—————+————-+——–+————-+

释放全局锁：
mysql> UNLOCK TABLES;

输出示例：
Query OK, 0 rows affected (0.00 sec)

2. 表级锁

表共享读锁：
mysql> LOCK TABLE orders READ;

输出示例：
Query OK, 0 rows affected (0.00 sec)

表独占写锁：
mysql> LOCK TABLE orders WRITE;

输出示例：
Query OK, 0 rows affected (0.00 sec)

查看表锁：
mysql> SHOW OPEN TABLES WHERE In_use > 0;

输出示例：
+—————+————-+——–+————-+
| Database | Table | In_use | Name_locked |
+—————+————-+——–+————-+
| production_db | orders | 1 | 0 |
+—————+————-+——–+————-+

释放表锁：
mysql> UNLOCK TABLES;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 行级锁（InnoDB）

共享锁(S锁)：
mysql> SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
+—-+———+——–+———————+

排他锁(X锁)：
mysql> SELECT * FROM orders WHERE id = 1 FOR UPDATE;

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
+—-+———+——–+———————+

查看行锁：
mysql> SELECT * FROM performance_schema.data_locks
WHERE OBJECT_NAME = ‘orders’\G

输出示例：
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 12345:1:2:1
OBJECT_NAME: orders
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED

4. 意向锁

意向锁类型：
– 意向共享锁(IS)：事务想获取行级S锁
– 意向排他锁(IX)：事务想获取行级X锁

意向锁自动获取：
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1 FOR UPDATE;

输出示例：
— 自动获取表级IX锁和行级X锁

查看意向锁：
mysql> SELECT * FROM performance_schema.data_locks
WHERE LOCK_TYPE = ‘TABLE’\G

输出示例：
*************************** 1. row ***************************
ENGINE: INNODB
OBJECT_NAME: orders
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED

5. 记录锁(Record Lock)

记录锁特点：
– 锁定索引记录
– 防止其他事务修改

创建记录锁：
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;

输出示例：
Query OK, 1 row affected (0.00 sec)

查看记录锁：
mysql> SELECT * FROM performance_schema.data_locks
WHERE LOCK_TYPE = ‘RECORD’\G

输出示例：
*************************** 1. row ***************************
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_DATA: 1

6. 间隙锁(Gap Lock)

间隙锁特点：
– 锁定索引记录之间的间隙
– 防止幻读
– 不锁定记录本身

创建间隙锁：
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id > 10 AND id < 20 FOR UPDATE; 输出示例： Empty set (0.00 sec) 查看间隙锁： mysql> SELECT * FROM performance_schema.data_locks
WHERE LOCK_MODE LIKE ‘%GAP%’\G

输出示例：
*************************** 1. row ***************************
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_DATA: 20

7. 临键锁(Next-Key Lock)

临键锁特点：
– 记录锁 + 间隙锁
– 锁定记录和前面的间隙
– InnoDB默认使用

创建临键锁：
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id >= 10 FOR UPDATE;

查看临键锁：
mysql> SELECT * FROM performance_schema.data_locks
WHERE LOCK_MODE NOT LIKE ‘%GAP%’
AND LOCK_MODE NOT LIKE ‘%REC_NOT_GAP%’\G

输出示例：
*************************** 1. row ***************************
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_DATA: 10

1.3 锁粒度与兼容性

理解锁粒度和兼容性是优化锁使用的关键：

# 锁粒度与兼容性

1. 锁粒度对比

+——————-+————+————+————+
| 锁粒度 | 锁定范围 | 并发度 | 开销 |
+——————-+————+————+————+
| 全局锁 | 整个实例 | 最低 | 最低 |
| 表级锁 | 整张表 | 低 | 低 |
| 页级锁 | 数据页 | 中 | 中 |
| 行级锁 | 单行 | 最高 | 最高 |
+——————-+————+————+————+

2. 表级锁兼容性矩阵

+———–+——–+——–+——–+——–+
| | IS | IX | S | X |
+———–+——–+——–+——–+——–+
| IS | 兼容 | 兼容 | 兼容 | 冲突 |
| IX | 兼容 | 兼容 | 冲突 | 冲突 |
| S | 兼容 | 冲突 | 兼容 | 冲突 |
| X | 冲突 | 冲突 | 冲突 | 冲突 |
+———–+——–+——–+——–+——–+

示例：
— 会话1：获取表级S锁
mysql> LOCK TABLE orders READ;

— 会话2：尝试获取表级X锁
mysql> LOCK TABLE orders WRITE;
— 等待…

输出示例：
ERROR 1205 (HY000): Lock wait timeout exceeded

3. 行级锁兼容性矩阵

+———–+——–+——–+
| | S | X |
+———–+——–+——–+
| S | 兼容 | 冲突 |
| X | 冲突 | 冲突 |
+———–+——–+——–+

示例：
— 会话1：获取行级X锁
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1 FOR UPDATE;

— 会话2：尝试获取同一行的S锁
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;
— 等待…

输出示例：
ERROR 1205 (HY000): Lock wait timeout exceeded

4. 锁等待超时

查看锁等待超时设置：
mysql> SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;

输出示例：
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_lock_wait_timeout | 50 |
+————————–+——-+

设置锁等待超时：
mysql> SET innodb_lock_wait_timeout = 30;

输出示例：
Query OK, 0 rows affected (0.00 sec)

会话级别设置：
mysql> SET SESSION innodb_lock_wait_timeout = 10;

输出示例：
Query OK, 0 rows affected (0.00 sec)

5. 锁状态监控

查看行锁统计：
mysql> SHOW STATUS LIKE ‘Innodb_row_lock%’;

输出示例：
+——————————-+——–+
| Variable_name | Value |
+——————————-+——–+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 123456 |
| Innodb_row_lock_time_avg | 1234 |
| Innodb_row_lock_time_max | 10000 |
| Innodb_row_lock_waits | 100 |
+——————————-+——–+

计算锁等待比例：
mysql> SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

输出示例：
+—————-+—————-+—————–+——————+
| waiting_trx_id | waiting_thread | blocking_trx_id | blocking_thread |
+—————-+—————-+—————–+——————+
| 12346 | 26 | 12345 | 25 |
+—————-+—————-+—————–+——————+

Part02-生产环境规划与建议

2.1 InnoDB锁机制

InnoDB是MySQL最常用的存储引擎，其锁机制设计精巧：

# InnoDB锁机制

1. InnoDB锁类型

行级锁类型：
+——————-+——————————————+
| 锁类型 | 描述 |
+——————-+——————————————+
| Record Lock | 记录锁，锁定索引记录 |
| Gap Lock | 间隙锁，锁定记录之间的间隙 |
| Next-Key Lock | 临键锁，记录锁+间隙锁 |
| Insert Intention | 插入意向锁，插入操作使用 |
+——————-+——————————————+

查看InnoDB锁：
mysql> SELECT * FROM performance_schema.data_locks\G

输出示例：
*************************** 1. row ***************************
ENGINE: INNODB
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1

2. 不同SQL语句的锁

SELECT … FROM：不加锁（快照读）

SELECT … LOCK IN SHARE MODE：加S锁
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
+—-+———+——–+———————+

SELECT … FOR UPDATE：加X锁
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1 FOR UPDATE;

UPDATE … WHERE：加X锁
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;

输出示例：
Query OK, 1 row affected (0.00 sec)

DELETE FROM … WHERE：加X锁
mysql> BEGIN;
mysql> DELETE FROM orders WHERE id = 1;

INSERT INTO …：加插入意向锁
mysql> BEGIN;
mysql> INSERT INTO orders VALUES (1, ‘pending’, 100.00, NOW());

3. 隔离级别与锁

查看隔离级别：
mysql> SELECT @@transaction_isolation;

输出示例：
+————————-+
| @@transaction_isolation |
+————————-+
| REPEATABLE-READ |
+————————-+

不同隔离级别的锁：
+——————-+——————————————+
| 隔离级别 | 锁特点 |
+——————-+——————————————+
| READ UNCOMMITTED | 最少锁，可能脏读 |
| READ COMMITTED | 记录锁，无间隙锁 |
| REPEATABLE READ | 记录锁+间隙锁（默认） |
| SERIALIZABLE | 最严格锁，所有读加S锁 |
+——————-+——————————————+

设置隔离级别：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 锁优化配置

配置锁相关参数：
[mysqld]
# 锁等待超时时间
innodb_lock_wait_timeout = 50

# 死锁检测
innodb_deadlock_detect = ON

# 自动提交
autocommit = ON

查看配置：
mysql> SHOW VARIABLES LIKE ‘innodb_deadlock_detect’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_deadlock_detect | ON |
+————————+——-+

5. 事务与锁

事务开始：
mysql> BEGIN;
或
mysql> START TRANSACTION;

输出示例：
Query OK, 0 rows affected (0.00 sec)

事务提交：
mysql> COMMIT;

输出示例：
Query OK, 0 rows affected (0.01 sec)

事务回滚：
mysql> ROLLBACK;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看活跃事务：
mysql> SELECT * FROM information_schema.innodb_trx\G

输出示例：
*************************** 1. row ***************************
trx_id: 12345
trx_state: RUNNING
trx_started: 2026-04-01 12:00:00
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 25

2.2 锁策略设计

合理的锁策略设计可以提高并发性能：

# 锁策略设计

1. 减少锁持有时间

问题代码：
BEGIN;
— 长时间处理
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
— 业务逻辑处理（耗时10秒）
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;

优化代码：
— 先处理业务逻辑
— 最后获取锁并更新
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 减少锁范围

问题代码：
UPDATE orders SET status = ‘processed’ WHERE status = ‘pending’;

优化代码：
— 使用索引减少扫描范围
CREATE INDEX idx_status ON orders(status);
UPDATE orders SET status = ‘processed’ WHERE status = ‘pending’ LIMIT 1000;

输出示例：
Query OK, 1000 rows affected (0.05 sec)

3. 使用乐观锁

添加版本号：
mysql> ALTER TABLE orders ADD COLUMN version INT DEFAULT 0;

输出示例：
Query OK, 0 rows affected (0.05 sec)

乐观锁更新：
mysql> UPDATE orders
SET status = ‘processed’, version = version + 1
WHERE id = 1 AND version = 0;

输出示例：
Query OK, 1 row affected (0.00 sec)

检查更新结果：
mysql> SELECT ROW_COUNT();

输出示例：
+————-+
| ROW_COUNT() |
+————-+
| 1 |
+————-+

4. 使用SELECT FOR UPDATE NOWAIT

MySQL 8.0+支持NOWAIT：
mysql> SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 1 | pending | 100.00 | 2026-04-01 12:00:00 |
+—-+———+——–+———————+

如果锁冲突：
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately

MySQL 8.0+支持SKIP LOCKED：
mysql> SELECT * FROM orders WHERE status = ‘pending’ FOR UPDATE SKIP LOCKED;

输出示例：
+—-+———+——–+———————+
| id | status | amount | created_at |
+—-+———+——–+———————+
| 2 | pending | 200.00 | 2026-04-01 12:01:00 |
+—-+———+——–+———————+

5. 批量操作优化

问题代码：
for id in ids:
UPDATE orders SET status = ‘processed’ WHERE id = id;

优化代码：
UPDATE orders SET status = ‘processed’ WHERE id IN (1, 2, 3, …);

输出示例：
Query OK, 100 rows affected (0.05 sec)

2.3 死锁预防策略

死锁是并发系统中常见问题，需要预防和处理：

# 死锁预防策略

1. 死锁产生条件

死锁四个必要条件：
1. 互斥条件：资源不能共享
2. 请求与保持：持有资源同时请求新资源
3. 不剥夺：已分配资源不能强制剥夺
4. 循环等待：存在循环等待链

死锁示例：
会话1：
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;

会话2：
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 2;

会话1：
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 2;
— 等待…

会话2：
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;
— 死锁！

输出示例：
ERROR 1213 (40001): Deadlock found when trying to get lock

2. 死锁检测

查看死锁检测配置：
mysql> SHOW VARIABLES LIKE ‘innodb_deadlock_detect’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_deadlock_detect | ON |
+————————+——-+

查看死锁信息：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
————————
LATEST DETECTED DEADLOCK
————————
2026-04-01 12:00:00 0x7f8b1c0b4700
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 140123456789012, query id 100 localhost root updating
UPDATE orders SET status = ‘processing’ WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `production_db`.`orders`
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 26, OS thread handle 140123456789013, query id 101 localhost root updating
UPDATE orders SET status = ‘processing’ WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `production_db`.`orders`
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 4 n bits 72 index PRIMARY of table `production_db`.`orders`
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

3. 死锁预防措施

措施1：按固定顺序访问
— 始终按id升序访问
BEGIN;
SELECT * FROM orders WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE orders SET status = ‘processed’ WHERE id = 1;
UPDATE orders SET status = ‘processed’ WHERE id = 2;
COMMIT;

措施2：使用大事务替代小事务
— 合并多个小事务
BEGIN;
UPDATE orders SET status = ‘processed’ WHERE id IN (1, 2);
COMMIT;

措施3：使用索引减少锁范围
CREATE INDEX idx_status ON orders(status);
UPDATE orders SET status = ‘processed’ WHERE status = ‘pending’;

措施4：设置合理超时
SET innodb_lock_wait_timeout = 10;

4. 死锁处理

应用层重试：
import mysql.connector
import time

def execute_with_retry(conn, sql, max_retries=3):
for attempt in range(max_retries):
try:
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
return True
except mysql.connector.Error as err:
if err.errno == 1213: # Deadlock
conn.rollback()
if attempt < max_retries - 1: time.sleep(0.1) continue raise err return False 输出示例： Retry attempt 1/3 for deadlock Query executed successfully 5. 死锁监控 创建死锁监控表： mysql> CREATE TABLE deadlock_log (
id INT AUTO_INCREMENT PRIMARY KEY,
detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
deadlock_info TEXT
);

监控脚本：
#!/bin/bash
DEADLOCK=$(mysql -u root -p -N -e ”
SHOW ENGINE INNODB STATUS
” | grep -A 50 “LATEST DETECTED DEADLOCK”)

if [ -n “$DEADLOCK” ]; then
mysql -u root -p -e ”
INSERT INTO deadlock_log (deadlock_info)
VALUES (‘$DEADLOCK’)
”
fi

输出示例：
Deadlock logged successfully.

Part03-生产环境项目实施方案

3.1 锁监控实施

建立完善的锁监控体系：

# 锁监控实施

1. Performance Schema监控

启用锁监控：
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = ‘YES’, TIMED = ‘YES’
WHERE NAME LIKE ‘wait/lock%’;

输出示例：
Query OK, 5 rows affected (0.00 sec)

启用消费者：
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = ‘YES’
WHERE NAME LIKE ‘%events_waits%’;

输出示例：
Query OK, 3 rows affected (0.00 sec)

2. 锁等待监控

查看锁等待：
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT/1000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE ‘wait/lock%’
ORDER BY SUM_TIMER_WAIT DESC;

输出示例：
+—————————————+————+———-+
| EVENT_NAME | COUNT_STAR | total_ms |
+—————————————+————+———-+
| wait/lock/table/sql/handler | 1234 | 5000.0 |
| wait/lock/row/innodb | 5678 | 10000.0 |
+—————————————+————+———-+

3. Sys Schema监控

使用Sys Schema视图：
mysql> SELECT * FROM sys.innodb_lock_waits\G

输出示例：
*************************** 1. row ***************************
wait_started: 2026-04-01 12:00:00
wait_age: 00:00:05
locked_table: `production_db`.`orders`
locked_index: PRIMARY
waiting_pid: 26
waiting_query: UPDATE orders SET status = ‘processing’ WHERE id = 1
waiting_lock_mode: X
blocking_pid: 25
blocking_query: SELECT * FROM orders WHERE id = 1 FOR UPDATE

4. 自定义监控脚本

创建监控脚本：
vim /opt/mysql/scripts/lock_monitor.sh

脚本内容：
#!/bin/bash

mysql -u monitor -p -N -e ”
SELECT
CONCAT(‘Lock Wait: ‘, COUNT(*), ‘ transactions waiting’)
FROM sys.innodb_lock_waits;

SELECT
CONCAT(‘Row Lock Waits: ‘, VARIABLE_VALUE)
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Innodb_row_lock_waits’;
”

设置定时任务：
*/1 * * * * /opt/mysql/scripts/lock_monitor.sh >> /var/log/mysql/lock_monitor.log

输出示例：
Lock Wait: 2 transactions waiting
Row Lock Waits: 100

5. 告警配置

配置锁等待告警：
vim /opt/mysql/scripts/lock_alert.sh

脚本内容：
#!/bin/bash

LOCK_WAITS=$(mysql -u monitor -p -N -e ”
SELECT COUNT(*) FROM sys.innodb_lock_waits
“)

if [ $LOCK_WAITS -gt 5 ]; then
echo “CRITICAL: $LOCK_WAITS lock waits detected” | \
mail -s “MySQL Lock Alert” dba@company.com
fi

输出示例：
Alert check completed.

3.2 锁问题分析

系统化分析锁问题：

# 锁问题分析

1. 锁等待分析

查看当前锁等待：
mysql> SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

输出示例：
+————-+—————-+————————–+————–+—————–+—————-+
| waiting_trx | waiting_thread | waiting_query | blocking_trx | blocking_thread | blocking_query |
+————-+—————-+————————–+————–+—————–+—————-+
| 12346 | 26 | UPDATE orders SET … | 12345 | 25 | SELECT … FOR UPDATE |
+————-+—————-+————————–+————–+—————–+—————-+

2. 锁持有时间分析

查看事务持有锁时间：
mysql> SELECT
trx_id,
trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS hold_seconds,
trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started;

输出示例：
+——–+———–+————–+———————+
| trx_id | trx_state | hold_seconds | trx_mysql_thread_id |
+——–+———–+————–+———————+
| 12345 | RUNNING | 30 | 25 |
| 12346 | LOCK WAIT | 10 | 26 |
+——–+———–+————–+———————+

3. 锁冲突分析

查看锁冲突统计：
mysql> SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
COUNT(*) AS lock_count
FROM performance_schema.data_locks
GROUP BY OBJECT_SCHEMA, OBJECT_NAME
ORDER BY lock_count DESC;

输出示例：
+—————-+————-+————+
| OBJECT_SCHEMA | OBJECT_NAME | lock_count |
+—————-+————-+————+
| production_db | orders | 10 |
| production_db | users | 5 |
+—————-+————-+————+

4. 死锁分析

分析死锁日志：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
————————
LATEST DETECTED DEADLOCK
————————
分析要点：
1. 识别参与死锁的事务
2. 找出冲突的锁
3. 分析SQL语句
4. 确定解决方案

5. 锁等待超时分析

查看超时设置：
mysql> SELECT @@innodb_lock_wait_timeout;

输出示例：
+—————————-+
| @@innodb_lock_wait_timeout |
+—————————-+
| 50 |
+—————————-+

分析超时原因：
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT/1000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME = ‘wait/lock/row/innodb’;

输出示例：
+———————–+————+———-+
| EVENT_NAME | COUNT_STAR | total_ms |
+———————–+————+———-+
| wait/lock/row/innodb | 100 | 5000.0 |
+———————–+————+———-+

3.3 锁优化实施

系统化实施锁优化：

# 锁优化实施

1. 优化锁范围

问题：
mysql> EXPLAIN UPDATE orders SET status = ‘processed’ WHERE status = ‘pending’;

输出示例：
+—-+————-+——–+————+——+—————+——+
| id | select_type | table | partitions | type | possible_keys | key |
+—-+————-+——–+————+——+—————+——+
| 1 | UPDATE | orders | NULL | ALL | NULL | NULL |
+—-+————-+——–+————+——+—————+——+

优化：
mysql> CREATE INDEX idx_status ON orders(status);
mysql> UPDATE orders SET status = ‘processed’ WHERE status = ‘pending’;

输出示例：
Query OK, 10000 rows affected (5.23 sec)

2. 优化事务设计

问题事务：
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
— 长时间业务处理
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;

优化事务：
— 先处理业务逻辑
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 优化隔离级别

查看当前隔离级别：
mysql> SELECT @@transaction_isolation;

输出示例：
+————————-+
| @@transaction_isolation |
+————————-+
| REPEATABLE-READ |
+————————-+

调整隔离级别（如果允许）：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 优化锁参数

调整锁等待超时：
mysql> SET GLOBAL innodb_lock_wait_timeout = 30;

输出示例：
Query OK, 0 rows affected (0.00 sec)

调整死锁检测：
mysql> SET GLOBAL innodb_deadlock_detect = ON;

输出示例：
Query OK, 0 rows affected (0.00 sec)

5. 实施锁监控

创建监控视图：
mysql> CREATE VIEW v_lock_monitor AS
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

查询监控视图：
mysql> SELECT * FROM v_lock_monitor;

输出示例：
+————-+—————-+————–+—————–+————–+
| waiting_trx | waiting_thread | blocking_trx | blocking_thread | wait_seconds |
+————-+—————-+————–+—————–+————–+
| 12346 | 26 | 12345 | 25 | 10 |
+————-+—————-+————–+—————–+————–+

Part04-生产案例与实战讲解

4.1 锁等待案例

以下是锁等待问题的实战案例：

# 锁等待案例

# 案例：订单更新锁等待

# 问题描述：
# 订单更新操作长时间等待

# 步骤1：查看锁等待
mysql> SELECT * FROM sys.innodb_lock_waits\G

# 输出示例：
# *************************** 1. row ***************************
# wait_started: 2026-04-01 12:00:00
# wait_age: 00:00:30
# locked_table: `production_db`.`orders`
# locked_index: PRIMARY
# waiting_pid: 26
# waiting_query: UPDATE orders SET status = ‘processing’ WHERE id = 1
# waiting_lock_mode: X
# blocking_pid: 25
# blocking_query: SELECT * FROM orders WHERE id = 1 FOR UPDATE

# 步骤2：分析阻塞事务
mysql> SELECT * FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = 25\G

# 输出示例：
# *************************** 1. row ***************************
# trx_id: 12345
# trx_state: RUNNING
# trx_started: 2026-04-01 11:59:00
# trx_requested_lock_id: NULL
# trx_weight: 2
# trx_mysql_thread_id: 25
# trx_query: NULL
# trx_rows_modified: 1

# 步骤3：查找阻塞原因
mysql> SHOW PROCESSLIST;

# 输出示例：
# +—-+——+———–+——+———+——+———-+——————+
# | Id | User | Host | db | Command | Time | State | Info |
# +—-+——+———–+——+———+——+———-+——————+
# | 25 | root | localhost | prod | Sleep | 60 | | NULL |
# | 26 | root | localhost | prod | Query | 30 | updating | UPDATE orders… |
# +—-+——+———–+——+———+——+———-+——————+

# 问题分析：
# – 线程25持有锁但处于Sleep状态
# – 可能是事务未提交

# 步骤4：解决方案
# 方案1：终止阻塞事务
mysql> KILL 25;

# 输出示例：
# Query OK, 0 rows affected (0.00 sec)

# 方案2：通知应用提交事务

# 步骤5：预防措施
# – 设置合理的锁等待超时
# – 监控长时间运行的事务
# – 应用层确保事务及时提交

4.2 死锁案例

以下是死锁问题的实战案例：

# 死锁案例

# 案例：订单处理死锁

# 问题描述：
# 并发处理订单时出现死锁

# 死锁场景：
# 会话1：
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;

# 会话2：
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 2;

# 会话1：
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 2;
# 等待…

# 会话2：
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;
# ERROR 1213 (40001): Deadlock found when trying to get lock

# 步骤1：分析死锁日志
mysql> SHOW ENGINE INNODB STATUS\G

# 输出示例：
# ————————
# LATEST DETECTED DEADLOCK
# ————————
# *** (1) TRANSACTION:
# TRANSACTION 12345, ACTIVE 5 sec
# UPDATE orders SET status = ‘processing’ WHERE id = 2
# *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
# RECORD LOCKS … id = 2
#
# *** (2) TRANSACTION:
# TRANSACTION 12346, ACTIVE 3 sec
# UPDATE orders SET status = ‘processing’ WHERE id = 1
# *** (2) HOLDS THE LOCK(S):
# RECORD LOCKS … id = 2
# *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
# RECORD LOCKS … id = 1

# 步骤2：分析死锁原因
# – 两个事务按不同顺序获取锁
# – 形成循环等待

# 步骤3：解决方案
# 方案1：按固定顺序获取锁
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 2;
mysql> COMMIT;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 方案2：使用单个事务处理
mysql> BEGIN;
mysql> UPDATE orders SET status = ‘processing’ WHERE id IN (1, 2);
mysql> COMMIT;

# 输出示例：
# Query OK, 2 rows affected (0.01 sec)

# 步骤4：应用层重试
# 实现死锁重试逻辑

4.3 锁冲突案例

以下是锁冲突问题的实战案例：

# 锁冲突案例

# 案例：热点数据锁冲突

# 问题描述：
# 高并发更新热点数据导致锁冲突

# 场景：
# 大量并发更新同一行数据
mysql> UPDATE counter SET value = value + 1 WHERE id = 1;

# 步骤1：分析锁冲突
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT/1000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE ‘wait/lock%’;

# 输出示例：
# +—————————————+————+———-+
# | EVENT_NAME | COUNT_STAR | total_ms |
# +—————————————+————+———-+
# | wait/lock/row/innodb | 10000 | 50000.0 |
# +—————————————+————+———-+

# 步骤2：优化方案
# 方案1：使用乐观锁
mysql> ALTER TABLE counter ADD COLUMN version INT DEFAULT 0;

# 应用层实现：
# 1. 读取当前值和版本
# 2. 计算新值
# 3. 更新时检查版本
mysql> UPDATE counter
SET value = value + 1, version = version + 1
WHERE id = 1 AND version = 0;

# 输出示例：
# Query OK, 1 row affected (0.00 sec)

# 方案2：使用队列异步处理
# 1. 将更新请求放入队列
# 2. 后台进程批量处理

# 方案3：分片计数
mysql> CREATE TABLE counter_shard (
id INT AUTO_INCREMENT PRIMARY KEY,
shard_id INT,
value INT DEFAULT 0
);

# 更新时随机选择分片
mysql> UPDATE counter_shard
SET value = value + 1
WHERE shard_id = FLOOR(RAND() * 10);

# 输出示例：
# Query OK, 1 row affected (0.00 sec)

# 步骤3：验证优化效果
mysql> SELECT
EVENT_NAME,
COUNT_STAR,
SUM_TIMER_WAIT/1000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE ‘wait/lock%’;

# 输出示例：
# +—————————————+————+———-+
# | EVENT_NAME | COUNT_STAR | total_ms |
# +—————————————+————+———-+
# | wait/lock/row/innodb | 1000 | 5000.0 |
# +—————————————+————+———-+

# 性能提升：锁等待次数减少90%，等待时间减少90%

Part05-风哥经验总结与分享

5.1 锁优化最佳实践

以下是MySQL锁优化的最佳实践：

# 锁优化最佳实践

1. 事务设计原则

– 尽量使用小事务
– 减少锁持有时间
– 避免长事务
– 及时提交事务

2. 锁使用原则

– 按固定顺序获取锁
– 使用合适的隔离级别
– 避免锁升级
– 使用索引减少锁范围

3. 死锁预防原则

– 按固定顺序访问资源
– 使用大事务替代小事务
– 设置合理的超时
– 实现重试机制

4. 监控原则

– 监控锁等待
– 监控死锁
– 监控长事务
– 设置告警阈值

5. 应用层原则

– 实现死锁重试
– 使用乐观锁
– 合理设计业务流程
– 避免热点数据竞争

5.2 锁反模式避免

以下是MySQL锁使用中需要避免的反模式：

# 锁反模式避免

1. 避免长事务

反模式：
BEGIN;
— 大量操作
— 长时间处理
COMMIT;

正确做法：
拆分为多个小事务

2. 避免锁顺序不一致

反模式：
事务1：锁A -> 锁B
事务2：锁B -> 锁A

正确做法：
统一按固定顺序获取锁

3. 避免热点竞争

反模式：
大量并发更新同一行

正确做法：
使用乐观锁或分片

4. 避免不必要的锁

反模式：
SELECT * FROM orders FOR UPDATE;

正确做法：
只在需要时加锁

5. 避免锁等待超时

反模式：
不设置合理的超时

正确做法：
设置合适的innodb_lock_wait_timeout

5.3 锁优化检查清单

以下是MySQL锁优化的检查清单：

# 锁优化检查清单

1. 设计阶段检查

[ ] 是否设计了合理的事务边界
[ ] 是否考虑了锁顺序
[ ] 是否选择了合适的隔离级别
[ ] 是否避免了热点竞争

2. 开发阶段检查

[ ] 是否使用了合适的锁类型
[ ] 是否减少了锁持有时间
[ ] 是否实现了死锁重试
[ ] 是否使用了索引减少锁范围

3. 测试阶段检查

[ ] 是否测试了并发场景
[ ] 是否模拟了高并发
[ ] 是否验证了死锁处理
[ ] 是否测试了锁等待超时

4. 运维阶段检查

[ ] 是否配置了锁监控
[ ] 是否设置了告警
[ ] 是否定期检查锁状态
[ ] 是否有应急处理方案

5. 监控阶段检查

[ ] 是否监控了锁等待
[ ] 是否监控了死锁
[ ] 是否监控了长事务
[ ] 是否生成了分析报告

风哥提示：MySQL锁机制是保证数据一致性的重要手段，但不当的锁使用会导致性能问题。理解锁的类型、粒度和兼容性，合理设计事务和锁策略，可以有效提高并发性能。建议建立完善的锁监控体系，及时发现和处理锁问题。对于生产环境的锁优化，务必在测试环境验证后再执行。更多视频教程请访问www.fgedu.net.cn

注意：本文档内容基于MySQL 8.4官方文档编写，适合DBA人员在学习和测试中使用。在生产环境中应用时，请务必进行充分的测试和验证。锁优化可能影响数据一致性，需要在测试环境验证后再应用到生产环境。文档中的命令和配置可能因MySQL版本不同而有所差异，请根据实际情况进行调整。

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息