内容简介：本文风哥教程参考MariaDB官方文档Basic Concepts、MariaDB Server等章节，详细讲解MariaDB事务隔离级别与并发控制的原理和实战应用，包括隔离级别设置、并发参数调优、性能测试等内容。
Part01-基础概念与理论知识
1.1 MariaDB事务基本概念
事务是数据库操作的基本单位，具有ACID特性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。在MariaDB中，默认情况下，每个SQL语句都是一个独立的事务。
1.2 MariaDB隔离级别原理
MariaDB支持四种事务隔离级别：
- READ UNCOMMITTED：读未提交，可能出现脏读
- READ COMMITTED：读已提交，避免脏读，可能出现不可重复读
- REPEATABLE READ：可重复读，避免脏读和不可重复读，可能出现幻读
- SERIALIZABLE：串行化，避免所有并发问题，但性能最差
1.3 MariaDB并发控制机制
MariaDB使用锁机制和MVCC（多版本并发控制）来实现并发控制。InnoDB引擎默认使用REPEATABLE READ隔离级别，通过MVCC提供非阻塞读操作。
更多视频教程www.fgedu.net.cn
Part02-生产环境规划与建议
2.1 隔离级别选择建议
风哥提示：不同的业务场景需要选择不同的隔离级别，平衡一致性和性能需求。
- 金融交易系统：建议使用SERIALIZABLE或REPEATABLE READ
- 一般OLTP系统：建议使用READ COMMITTED
- 报表查询系统：可以使用READ UNCOMMITTED
2.2 并发控制参数调优
生产环境中需要调整以下参数来优化并发性能：
- innodb_thread_concurrency：控制InnoDB线程并发数
- innodb_lock_wait_timeout：锁等待超时时间
- innodb_rollback_on_timeout：超时是否回滚事务
- innodb_deadlock_detect：是否启用死锁检测
2.3 生产环境最佳实践
- 合理设置事务大小，避免长事务
- 使用索引减少锁范围
- 避免在事务中执行耗时操作
- 定期监控死锁和锁等待情况
学习交流加群风哥微信: itpux-com
Part03-生产环境项目实施方案
3.1 隔离级别配置步骤
更多学习教程公众号风哥教程itpux_com
# 查看当前隔离级别
MariaDB [(none)]> SHOW VARIABLES LIKE ‘tx_isolation’;
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| tx_isolation | REPEATABLE-READ |
+—————+—————–+
# 全局设置隔离级别
MariaDB [(none)]> SET GLOBAL tx_isolation = ‘READ-COMMITTED’;
Query OK, 0 rows affected (0.00 sec)
# 会话级别设置
MariaDB [(none)]> SET SESSION tx_isolation = ‘READ-COMMITTED’;
Query OK, 0 rows affected (0.00 sec)
# 在配置文件中设置
# /mariadb/app/my.cnf
[mysqld]
tx_isolation = READ-COMMITTED
MariaDB [(none)]> SHOW VARIABLES LIKE ‘tx_isolation’;
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| tx_isolation | REPEATABLE-READ |
+—————+—————–+
# 全局设置隔离级别
MariaDB [(none)]> SET GLOBAL tx_isolation = ‘READ-COMMITTED’;
Query OK, 0 rows affected (0.00 sec)
# 会话级别设置
MariaDB [(none)]> SET SESSION tx_isolation = ‘READ-COMMITTED’;
Query OK, 0 rows affected (0.00 sec)
# 在配置文件中设置
# /mariadb/app/my.cnf
[mysqld]
tx_isolation = READ-COMMITTED
3.2 并发控制参数配置
# 查看当前并发控制参数
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_thread_concurrency’;
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| innodb_thread_concurrency | 0 |
+————————-+——-+
# 设置并发线程数（根据CPU核心数调整）
MariaDB [(none)]> SET GLOBAL innodb_thread_concurrency = 16;
Query OK, 0 rows affected (0.00 sec)
# 设置锁等待超时
MariaDB [(none)]> SET GLOBAL innodb_lock_wait_timeout = 30;
Query OK, 0 rows affected (0.00 sec)
# 启用死锁检测
MariaDB [(none)]> SET GLOBAL innodb_deadlock_detect = ON;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE ‘innodb_thread_concurrency’;
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| innodb_thread_concurrency | 0 |
+————————-+——-+
# 设置并发线程数（根据CPU核心数调整）
MariaDB [(none)]> SET GLOBAL innodb_thread_concurrency = 16;
Query OK, 0 rows affected (0.00 sec)
# 设置锁等待超时
MariaDB [(none)]> SET GLOBAL innodb_lock_wait_timeout = 30;
Query OK, 0 rows affected (0.00 sec)
# 启用死锁检测
MariaDB [(none)]> SET GLOBAL innodb_deadlock_detect = ON;
Query OK, 0 rows affected (0.00 sec)
3.3 监控与告警设置
# 查看死锁信息
MariaDB [(none)]> SHOW ENGINE INNODB STATUS GLOBAL\G
# 监控锁等待
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 |
+——————————-+——-+
MariaDB [(none)]> SHOW ENGINE INNODB STATUS GLOBAL\G
# 监控锁等待
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 |
+——————————-+——-+
学习交流加群风哥QQ113257174
Part04-生产案例与实战讲解
4.1 不同隔离级别测试
# 测试READ UNCOMMITTED隔离级别
MariaDB [fgedudb]> SET SESSION tx_isolation = ‘READ-UNCOMMITTED’;
Query OK, 0 rows affected (0.00 sec)
# 会话1开始事务并修改数据
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘test’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 会话2读取数据（脏读）
MariaDB [fgedudb]> SELECT * FROM fgedu_users WHERE id = 1;
+—-+——+———————+
| id | name | created_at |
+—-+——+———————+
| 1 | test | 2023-01-01 00:00:00 |
+—-+——+———————+
# 会话1回滚事务
MariaDB [fgedudb]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
# 会话2再次读取数据
MariaDB [fgedudb]> SELECT * FROM fgedu_users WHERE id = 1;
+—-+——+———————+
| id | name | created_at |
+—-+——+———————+
| 1 | user | 2023-01-01 00:00:00 |
+—-+——+———————+
MariaDB [fgedudb]> SET SESSION tx_isolation = ‘READ-UNCOMMITTED’;
Query OK, 0 rows affected (0.00 sec)
# 会话1开始事务并修改数据
MariaDB [fgedudb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [fgedudb]> UPDATE fgedu_users SET name = ‘test’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 会话2读取数据（脏读）
MariaDB [fgedudb]> SELECT * FROM fgedu_users WHERE id = 1;
+—-+——+———————+
| id | name | created_at |
+—-+——+———————+
| 1 | test | 2023-01-01 00:00:00 |
+—-+——+———————+
# 会话1回滚事务
MariaDB [fgedudb]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
# 会话2再次读取数据
MariaDB [fgedudb]> SELECT * FROM fgedu_users WHERE id = 1;
+—-+——+———————+
| id | name | created_at |
+—-+——+———————+
| 1 | user | 2023-01-01 00:00:00 |
+—-+——+———————+
4.2 并发场景性能测试
# 准备测试数据
MariaDB [fgedudb]> CREATE TABLE fgedu_test (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50),
-> value INT
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入测试数据
MariaDB [fgedudb]> INSERT INTO fgedu_test (name, value) VALUES (‘test’, 1) REPEAT 10000;
Query OK, 10000 rows affected (0.10 sec)
Records: 10000 Duplicates: 0 Warnings: 0
# 使用sysbench测试并发性能
$ sysbench –test=oltp –oltp-table-size=10000 –mysql-db=fgedudb –mysql-user=fgedu –mysql-password=fgedu –max-time=60 –max-requests=0 –num-threads=16 run
Running the test with following options:
Number of threads: 16
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting…
OLTP test statistics:
queries performed:
read: 27648
write: 7900
other: 3950
total: 40498
transactions:
total: 1975 (32.92 per sec.)
deadlocks:
total: 0 (0.00 per sec.)
read/write requests:
total: 35548 (592.47 per sec.)
other operations:
total: 3950 (65.83 per sec.)
General statistics:
total time: 60.0052s
total number of events: 1975
Latency (ms):
min: 1.49
avg: 484.06
max: 2345.67
approx. 95 percentile: 1234.56
Threads fairness:
events (avg/stddev): 123.4375/12.34
execution time (avg/stddev): 59.9987/0.01
MariaDB [fgedudb]> CREATE TABLE fgedu_test (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50),
-> value INT
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
# 插入测试数据
MariaDB [fgedudb]> INSERT INTO fgedu_test (name, value) VALUES (‘test’, 1) REPEAT 10000;
Query OK, 10000 rows affected (0.10 sec)
Records: 10000 Duplicates: 0 Warnings: 0
# 使用sysbench测试并发性能
$ sysbench –test=oltp –oltp-table-size=10000 –mysql-db=fgedudb –mysql-user=fgedu –mysql-password=fgedu –max-time=60 –max-requests=0 –num-threads=16 run
Running the test with following options:
Number of threads: 16
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting…
OLTP test statistics:
queries performed:
read: 27648
write: 7900
other: 3950
total: 40498
transactions:
total: 1975 (32.92 per sec.)
deadlocks:
total: 0 (0.00 per sec.)
read/write requests:
total: 35548 (592.47 per sec.)
other operations:
total: 3950 (65.83 per sec.)
General statistics:
total time: 60.0052s
total number of events: 1975
Latency (ms):
min: 1.49
avg: 484.06
max: 2345.67
approx. 95 percentile: 1234.56
Threads fairness:
events (avg/stddev): 123.4375/12.34
execution time (avg/stddev): 59.9987/0.01
4.3 死锁检测与处理
# 模拟死锁场景
# 会话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
# 查看死锁信息
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2023-01-01 12:00:00 0x7f1234567890 INNODB MONITOR OUTPUT
=====================================
…
LATEST DETECTED DEADLOCK
————————
2023-01-01 12:00:00 0x7f1234567890
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 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 1, OS thread handle 139710456789016, query id 123 fgedu.localhost root updating
UPDATE fgedu_users SET name = ‘session1’ WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12345 lock_mode X locks rec but not gap waiting
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 000000003039;
asc 09;
2: len 7;
hex b60000012c0110;
asc ,;
3: len 6;
hex 73657373696f;
asc session;
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 139710456789017, query id 124 fgedu.localhost root updating
UPDATE fgedu_users SET name = ‘session2’ WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12346 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 000000003039;
asc 09;
2: len 7;
hex b60000012c0110;
asc ,;
3: len 6;
hex 73657373696f;
asc session;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12346 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 000000003038;
asc 08;
2: len 7;
hex b60000012c010f;
asc ,;
3: len 6;
hex 73657373696f;
asc session;
*** WE ROLL BACK TRANSACTION (2)
# 会话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
# 查看死锁信息
MariaDB [(none)]> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2023-01-01 12:00:00 0x7f1234567890 INNODB MONITOR OUTPUT
=====================================
…
LATEST DETECTED DEADLOCK
————————
2023-01-01 12:00:00 0x7f1234567890
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 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 1, OS thread handle 139710456789016, query id 123 fgedu.localhost root updating
UPDATE fgedu_users SET name = ‘session1’ WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12345 lock_mode X locks rec but not gap waiting
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 000000003039;
asc 09;
2: len 7;
hex b60000012c0110;
asc ,;
3: len 6;
hex 73657373696f;
asc session;
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2, OS thread handle 139710456789017, query id 124 fgedu.localhost root updating
UPDATE fgedu_users SET name = ‘session2’ WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12346 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 000000003039;
asc 09;
2: len 7;
hex b60000012c0110;
asc ,;
3: len 6;
hex 73657373696f;
asc session;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `fgedudb`.`fgedu_users` trx id 12346 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 000000003038;
asc 08;
2: len 7;
hex b60000012c010f;
asc ,;
3: len 6;
hex 73657373696f;
asc session;
*** WE ROLL BACK TRANSACTION (2)
风哥提示：安全开发是防止SQL注入的第一道防线
Part05-风哥经验总结与分享
5.1 隔离级别选择经验
在实际生产环境中，大多数业务场景使用READ COMMITTED隔离级别即可满足需求，它提供了较好的并发性能同时避免了脏读问题。对于金融等对一致性要求较高的场景，可以使用REPEATABLE READ或SERIALIZABLE。
5.2 并发控制优化建议
- 合理设计索引，减少锁范围
- 避免长事务，及时提交或回滚
- 使用批量操作减少事务数量
- 设置合理的锁等待超时时间
- 定期监控死锁和锁等待情况
5.3 生产故障案例分析
某电商系统在促销活动期间出现大量死锁，经过分析发现是由于多个事务同时更新同一商品的库存和订单信息导致的。解决方案是：
- 优化更新顺序，统一按主键顺序更新
- 减少事务范围，将库存更新和订单创建分开
- 增加重试机制，处理死锁异常
- 监控系统实时检测死锁情况
from MariaDB视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息