内容简介：本文风哥教程参考MariaDB官方文档MariaDB Server、InnoDB等章节，详细讲解MariaDB锁机制的原理和实战应用，包括行锁、表锁、间隙锁的使用场景和冲突解决方法。
Part01-基础概念与理论知识
1.1 MariaDB锁类型与分类
MariaDB的锁机制主要分为以下几类：
- 按粒度分：行锁、页锁、表锁
- 按模式分：共享锁(S)、排他锁(X)
- 按算法分：记录锁、间隙锁、Next-Key Lock
1.2 行锁与表锁机制
行锁是InnoDB引擎的默认锁机制，粒度小，并发性能高。表锁是MyISAM引擎的默认锁机制，粒度大，并发性能低。
- 行锁：锁定单行数据，适用于高并发场景
- 表锁：锁定整个表，适用于批量操作场景
1.3 间隙锁与Next-Key Lock
间隙锁是InnoDB为了解决幻读问题而引入的锁机制，它锁定的是索引记录之间的间隙。Next-Key Lock是记录锁和间隙锁的组合。
- 间隙锁：锁定索引范围，防止插入新记录
- Next-Key Lock：锁定记录本身和相邻间隙
更多视频教程www.fgedu.net.cn
Part02-生产环境规划与建议
2.1 锁策略选择建议
风哥提示：选择合适的锁策略需要根据业务场景和并发需求来决定。
- 高并发OLTP系统：使用行锁，推荐InnoDB引擎
- 批量操作场景：可以使用表锁提高效率
- 读多写少场景：可以使用共享锁
- 写多读少场景：需要合理设计索引减少锁冲突
2.2 锁冲突预防措施
- 合理设计索引，减少锁范围
- 使用主键或唯一索引进行查询和更新
- 避免全表扫描和范围查询
- 控制事务大小，减少锁持有时间
- 统一更新顺序，避免死锁
2.3 生产环境最佳实践
- 使用InnoDB引擎，默认行锁机制
- 合理设置innodb_lock_wait_timeout参数
- 启用innodb_deadlock_detect检测死锁
- 定期监控锁等待和死锁情况
- 使用事务隔离级别READ COMMITTED减少间隙锁
学习交流加群风哥微信: itpux-com
Part03-生产环境项目实施方案
3.1 锁监控与分析
更多学习教程公众号风哥教程itpux_com
# 查看锁等待情况
MariaDB [(none)]> SHOW GLOBAL 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 |
+——————————-+——-+
# 查看InnoDB状态，包含锁信息
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
# 查看当前事务
MariaDB [(none)]> SHOW PROCESSLIST;
+—-+——+———–+——–+———+——+———-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——–+———+——+———-+——————+
| 1 | root | fgedu.localhost | fgedudb | Sleep | 10 | | NULL |
| 2 | root | fgedu.localhost | fgedudb | Query | 0 | executing | SHOW PROCESSLIST |
+—-+——+———–+——–+———+——+———-+——————+
MariaDB [(none)]> SHOW GLOBAL 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 |
+——————————-+——-+
# 查看InnoDB状态，包含锁信息
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
# 查看当前事务
MariaDB [(none)]> SHOW PROCESSLIST;
+—-+——+———–+——–+———+——+———-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+——–+———+——+———-+——————+
| 1 | root | fgedu.localhost | fgedudb | Sleep | 10 | | NULL |
| 2 | root | fgedu.localhost | fgedudb | Query | 0 | executing | SHOW PROCESSLIST |
+—-+——+———–+——–+———+——+———-+——————+
3.2 锁参数配置优化
# 查看当前锁相关参数
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_lock%’;
+————————–+———-+
| Variable_name | Value |
+————————–+———-+
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
+————————–+
# 查看死锁检测参数
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_deadlock%’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_deadlock_detect | ON |
+————————+
# 修改锁等待超时时间
MariaDB [(none)]> SET GLOBAL innodb_lock_wait_timeout = 30;
Query OK, 0 rows affected (0.00 sec)
# 启用事务超时回滚
MariaDB [(none)]> SET GLOBAL innodb_rollback_on_timeout = ON;
Query OK, 0 rows affected (0.00 sec)
# 在配置文件中设置
# /mariadb/app/my.cnf
[mysqld]
innodb_lock_wait_timeout = 30
innodb_rollback_on_timeout = 1
innodb_deadlock_detect = 1
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_lock%’;
+————————–+———-+
| Variable_name | Value |
+————————–+———-+
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
+————————–+
# 查看死锁检测参数
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_deadlock%’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_deadlock_detect | ON |
+————————+
# 修改锁等待超时时间
MariaDB [(none)]> SET GLOBAL innodb_lock_wait_timeout = 30;
Query OK, 0 rows affected (0.00 sec)
# 启用事务超时回滚
MariaDB [(none)]> SET GLOBAL innodb_rollback_on_timeout = ON;
Query OK, 0 rows affected (0.00 sec)
# 在配置文件中设置
# /mariadb/app/my.cnf
[mysqld]
innodb_lock_wait_timeout = 30
innodb_rollback_on_timeout = 1
innodb_deadlock_detect = 1
3.3 死锁检测与处理
# 查看死锁日志
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
# 模拟死锁场景
# 会话1
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session1’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
# 会话2
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session2’ WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
# 会话1尝试更新会话2锁定的行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session1’ WHERE id = 2;
# 等待中…
# 会话2尝试更新会话1锁定的行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session2’ WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
# 处理死锁：应用程序需要捕获死锁异常并重试
# 示例代码（伪代码）
try {
// 执行事务
} catch (DeadlockException e) {
// 重试逻辑
retryCount++;
if (retryCount < maxRetries) {
// 重新执行事务
} else {
// 抛出异常
}
}
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
# 模拟死锁场景
# 会话1
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session1’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
# 会话2
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session2’ WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
# 会话1尝试更新会话2锁定的行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session1’ WHERE id = 2;
# 等待中…
# 会话2尝试更新会话1锁定的行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘session2’ WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
# 处理死锁：应用程序需要捕获死锁异常并重试
# 示例代码（伪代码）
try {
// 执行事务
} catch (DeadlockException e) {
// 重试逻辑
retryCount++;
if (retryCount < maxRetries) {
// 重新执行事务
} else {
// 抛出异常
}
}
学习交流加群风哥QQ113257174
Part04-生产案例与实战讲解
4.1 行锁与表锁测试
# 测试行锁
# 会话1
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘locked’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
# 会话2 – 可以更新其他行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘unlocked’ WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
# 会话2 – 尝试更新被锁定的行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘try’ WHERE id = 1;
# 等待中…
# 会话1提交事务
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
# 会话2的操作执行完成
Query OK, 1 row affected (5.23 sec)
# 测试表锁
# 会话1 – 锁定表
MariaDB [fgedudb]> LOCK TABLES fgedu_users WRITE;
Query OK, 0 rows affected (0.00 sec)
# 会话2 – 尝试操作表
MariaDB [fgedudb]> SELECT * FROM fgedu_users;
# 等待中…
# 会话1释放锁
MariaDB [fgedudb]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
# 会话2的查询执行完成
+—-+——-+———————+
| id | name | created_at |
+—-+——-+———————+
| 1 | try | 2023-01-01 00:00:00 |
| 2 | test2 | 2023-01-01 00:00:00 |
+—-+——-+———————+
# 会话1
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘locked’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
# 会话2 – 可以更新其他行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘unlocked’ WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
# 会话2 – 尝试更新被锁定的行
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘try’ WHERE id = 1;
# 等待中…
# 会话1提交事务
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
# 会话2的操作执行完成
Query OK, 1 row affected (5.23 sec)
# 测试表锁
# 会话1 – 锁定表
MariaDB [fgedudb]> LOCK TABLES fgedu_users WRITE;
Query OK, 0 rows affected (0.00 sec)
# 会话2 – 尝试操作表
MariaDB [fgedudb]> SELECT * FROM fgedu_users;
# 等待中…
# 会话1释放锁
MariaDB [fgedudb]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
# 会话2的查询执行完成
+—-+——-+———————+
| id | name | created_at |
+—-+——-+———————+
| 1 | try | 2023-01-01 00:00:00 |
| 2 | test2 | 2023-01-01 00:00:00 |
+—-+——-+———————+
4.2 间隙锁与幻读测试
# 准备测试数据
MariaDB [fgedudb]> CREATE TABLE fgedu_gap (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> value INT
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_gap (value) VALUES (10), (20), (30), (40), (50);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
# 测试间隙锁（REPEATABLE READ隔离级别）
# 会话1
MariaDB [fgedudb]> SET SESSION tx_isolation = ‘REPEATABLE-READ’;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
+—-+——-+
# 会话2 – 尝试插入间隙中的数据
MariaDB [fgedudb]> INSERT INTO fgedu_gap (value) VALUES (16);
# 等待中…
# 会话1提交事务
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
# 会话2的插入操作执行完成
Query OK, 1 row affected (3.45 sec)
# 测试幻读
# 会话1
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
+—-+——-+
# 会话2 – 插入新数据
MariaDB [fgedudb]> INSERT INTO fgedu_gap (value) VALUES (18);
Query OK, 1 row affected (0.00 sec)
# 会话1 – 再次查询（无幻读）
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
+—-+——-+
# 会话1提交后查询
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
| 6 | 16 |
| 7 | 18 |
+—-+——-+
MariaDB [fgedudb]> CREATE TABLE fgedu_gap (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> value INT
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_gap (value) VALUES (10), (20), (30), (40), (50);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
# 测试间隙锁（REPEATABLE READ隔离级别）
# 会话1
MariaDB [fgedudb]> SET SESSION tx_isolation = ‘REPEATABLE-READ’;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
+—-+——-+
# 会话2 – 尝试插入间隙中的数据
MariaDB [fgedudb]> INSERT INTO fgedu_gap (value) VALUES (16);
# 等待中…
# 会话1提交事务
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
# 会话2的插入操作执行完成
Query OK, 1 row affected (3.45 sec)
# 测试幻读
# 会话1
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
+—-+——-+
# 会话2 – 插入新数据
MariaDB [fgedudb]> INSERT INTO fgedu_gap (value) VALUES (18);
Query OK, 1 row affected (0.00 sec)
# 会话1 – 再次查询（无幻读）
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
+—-+——-+
# 会话1提交后查询
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_gap WHERE value BETWEEN 15 AND 25;
+—-+——-+
| id | value |
+—-+——-+
| 2 | 20 |
| 6 | 16 |
| 7 | 18 |
+—-+——-+
4.3 锁冲突解决实战
# 场景：电商系统秒杀活动，多个用户同时抢购同一商品
# 问题：高并发下锁冲突严重，导致响应缓慢
# 解决方案1：使用悲观锁
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_products WHERE id = 1 FOR UPDATE;
+—-+——+——–+———-+
| id | name | price | stock |
+—-+——+——–+———-+
| 1 | iPhone | 6999 | 100 |
+—-+——+——–+———-+
MariaDB [fgedudb]> UPDATE fgedu_products SET stock = stock – 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_orders (user_id, product_id, quantity) VALUES (1, 1, 1);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
# 解决方案2：使用乐观锁
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT stock, version FROM fgedu_products WHERE id = 1;
+——-+———+
| stock | version |
+——-+———+
| 100 | 1 |
+——-+———+
MariaDB [fgedudb]> UPDATE fgedu_products SET stock = stock – 1, version = version + 1 WHERE id = 1 AND version = 1;
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> IF ROW_COUNT() > 0 THEN
-> INSERT INTO fgedu_orders (user_id, product_id, quantity) VALUES (1, 1, 1);
-> COMMIT;
-> ELSE
-> ROLLBACK;
-> — 重试逻辑
-> END IF;
# 解决方案3：使用队列异步处理
# 将秒杀请求放入消息队列，由后台服务按顺序处理
# 这样可以避免高并发下的锁冲突
# 问题：高并发下锁冲突严重，导致响应缓慢
# 解决方案1：使用悲观锁
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT * FROM fgedu_products WHERE id = 1 FOR UPDATE;
+—-+——+——–+———-+
| id | name | price | stock |
+—-+——+——–+———-+
| 1 | iPhone | 6999 | 100 |
+—-+——+——–+———-+
MariaDB [fgedudb]> UPDATE fgedu_products SET stock = stock – 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_orders (user_id, product_id, quantity) VALUES (1, 1, 1);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
# 解决方案2：使用乐观锁
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> SELECT stock, version FROM fgedu_products WHERE id = 1;
+——-+———+
| stock | version |
+——-+———+
| 100 | 1 |
+——-+———+
MariaDB [fgedudb]> UPDATE fgedu_products SET stock = stock – 1, version = version + 1 WHERE id = 1 AND version = 1;
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> IF ROW_COUNT() > 0 THEN
-> INSERT INTO fgedu_orders (user_id, product_id, quantity) VALUES (1, 1, 1);
-> COMMIT;
-> ELSE
-> ROLLBACK;
-> — 重试逻辑
-> END IF;
# 解决方案3：使用队列异步处理
# 将秒杀请求放入消息队列，由后台服务按顺序处理
# 这样可以避免高并发下的锁冲突
风哥提示：安全开发是防止SQL注入的第一道防线
Part05-风哥经验总结与分享
5.1 锁机制使用经验
在实际生产环境中，合理使用锁机制是保证数据一致性和系统性能的关键。以下是一些经验总结：
- 优先使用行锁，减少锁粒度
- 合理设计索引，避免全表扫描
- 使用主键或唯一索引进行更新操作
- 控制事务大小，减少锁持有时间
- 在高并发场景下考虑使用乐观锁
5.2 锁冲突优化建议
- 使用合适的事务隔离级别，READ COMMITTED可以减少间隙锁
- 合理设置锁等待超时时间，避免长时间阻塞
- 启用死锁检测，及时发现和处理死锁
- 使用批量操作减少事务数量
- 考虑使用队列机制处理高并发请求
5.3 生产故障案例分析
某金融系统在批量处理交易时出现锁冲突导致系统卡顿，经过分析发现：
- 问题原因：批量更新时使用了非索引字段作为条件，导致全表扫描和表锁
- 解决方案：
- 为查询条件添加索引
- 将大批次拆分为小批次处理
- 使用ROW_FORMAT=COMPRESSED减少锁开销
- 优化SQL语句，避免全表扫描
- 效果：系统响应时间从秒级降至毫秒级，并发处理能力提升10倍
from MariaDB视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息