tidb教程FG107-TiDB事务管理与优化生产实战

本文档详细介绍TiDB事务管理与优化方法，包括事务概念、隔离级别、事务模式、并发控制、死锁处理等内容。风哥教程参考TiDB官方文档事务管理指南、性能调优手册等内容，适合DBA和开发人员进行TiDB事务优化。

Part01-基础概念与理论知识

1.1 TiDB事务概述

TiDB支持ACID事务特性，提供乐观并发控制和悲观并发控制两种事务模式。TiDB的事务实现基于Percolator模型，通过两阶段提交保证分布式事务的一致性。

TiDB事务特性：

原子性（Atomicity）：事务要么全部成功，要么全部失败
一致性（Consistency）：事务执行前后数据状态一致
隔离性（Isolation）：不同事务之间相互隔离
持久性（Durability）：事务提交后数据永久保存

1.2 隔离级别详解

# TiDB支持的隔离级别

# 1. READ COMMITTED
# – 读已提交，只能看到已提交的数据
# – 最低隔离级别，并发性能最好
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

# 2. REPEATABLE READ
# – 可重复读，同一个事务内多次读取结果一致
# – TiDB默认隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 3. SERIALIZABLE
# – 序列化，最高隔离级别
# – 并发性能最差，不推荐使用
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

# 查看当前隔离级别
mysql> SELECT @@tx_isolation;
+—————–+
| @@tx_isolation |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)

1.3 事务类型与特点

TiDB支持两种事务模式：乐观事务和悲观事务。

风哥提示：乐观事务适合并发冲突少的场景，悲观事务适合并发冲突多的场景。选择合适的事务模式对性能影响很大。更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 事务模式选择

# 事务模式选择指南

# 1. 乐观事务模式
# 适用场景：
# – 并发冲突少
# – 读多写少
# – 短事务
SET GLOBAL tidb_txn_mode = ‘optimistic’;

# 2. 悲观事务模式
# 适用场景：
# – 并发冲突多
# – 写多读少
# – 金融交易系统
SET GLOBAL tidb_txn_mode = ‘pessimistic’;

# 3. 会话级别设置
SET SESSION tidb_txn_mode = ‘pessimistic’;

# 4. 语句级别设置
BEGIN OPTIMISTIC;
— 乐观事务
COMMIT;

BEGIN PESSIMISTIC;
— 悲观事务
COMMIT;

2.2 并发控制策略

# 并发控制策略

# 1. 乐观并发控制（OCC）
# – 特点：先执行，提交时检查冲突
# – 优点：高并发性能
# – 缺点：冲突时需要重试

# 2. 悲观并发控制（PCC）
# – 特点：先加锁，再执行
# – 优点：避免冲突，无需重试
# – 缺点：可能产生死锁

# 3. 死锁检测
SET GLOBAL tidb_deadlock_detection = 1;

# 4. 锁超时设置
SET GLOBAL innodb_lock_wait_timeout = 50;

# 5. 事务自动重试
SET GLOBAL tidb_disable_txn_auto_retry = 0;
SET GLOBAL tidb_retry_limit = 10;

2.3 事务大小控制

# 事务大小控制

# 1. 单事务限制
# TiDB对事务大小有以下限制：
# – 单事务语句数：建议不超过5000条
# – 单事务数据量：建议不超过500MB
# – 单事务执行时间：建议不超过10秒

# 2. 大事务拆分策略
# 按主键范围拆分
# 按时间范围拆分
# 按批次提交

# 3. 批量操作示例
# 错误：单事务插入100万条
INSERT INTO fgedu_logs SELECT * FROM fgedu_old_logs;

# 正确：分批插入
SET @batch_size = 1000;
SET @total = 1000000;
SET @offset = 0;

WHILE @offset < @total DO START TRANSACTION; INSERT INTO fgedu_logs (user_id, action, created_at) SELECT user_id, action, created_at FROM fgedu_old_logs LIMIT @offset, @batch_size; COMMIT; SET @offset = @offset + @batch_size; END WHILE;

生产环境建议：控制事务大小是保证TiDB稳定性的关键。大事务会导致内存压力、锁竞争和长时间阻塞。学习交流加群风哥微信: itpux-com

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

3.1 事务使用实战

3.1.1 基本事务操作

风哥提示：

# 事务基本操作

# 1. 显式事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu01’, ‘fgedu01@fgedu.net.cn’);
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE fgedu_orders SET status = ‘completed’ WHERE order_id = 1001;
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)

# 2. 自动提交
mysql> SET autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

# 3. 事务回滚
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM fgedu_users WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

# 4. 保存点
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO fgedu_logs (user_id, action) VALUES (1, ‘login’);
Query OK, 1 row affected (0.01 sec)

mysql> SAVEPOINT save1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO fgedu_logs (user_id, action) VALUES (1, ‘logout’);
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK TO SAVEPOINT save1;
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

3.1.2 事务隔离级别测试

# 隔离级别测试

# 会话1：设置隔离级别为READ COMMITTED
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

# 会话2：更新数据
mysql> UPDATE fgedu_users SET username = ‘fgedu01_updated’ WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

# 会话1：设置隔离级别为REPEATABLE READ
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

# 会话2：再次更新数据
mysql> UPDATE fgedu_users SET username = ‘fgedu01_updated2’ WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

3.2 死锁处理实战

# 死锁处理

# 1. 死锁场景模拟
# 会话1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE fgedu_users SET username = ‘user1’ WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

# 会话2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE fgedu_users SET username = ‘user2’ WHERE id = 2;
Query OK, 1 row affected (0.01 sec)

# 会话1
mysql> UPDATE fgedu_users SET username = ‘user1’ WHERE id = 2;
— 等待中…

# 会话2
mysql> UPDATE fgedu_users SET username = ‘user2’ WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

# 2. 死锁检测与处理

# 查看死锁历史
mysql> SELECT * FROM information_schema.deadlocks
*************************** 1. row ***************************
DEADLOCK_ID: 1
DETECT_TIME: 2024-04-09 10:00:00
RETRYABLE: 1
VICTIM_TRANSACTION: txn-12345
VICTIM_QUERY: UPDATE fgedu_users SET username = ‘user2’ WHERE id = 1
VICTIM_HOLDING_LOCKS: []
VICTIM_WAITING_FOR_LOCKS: [“lock_type: Write, table: fgedu_users, key: 1”]
KILLER_TRANSACTION: txn-67890
KILLER_QUERY: UPDATE fgedu_users SET username = ‘user1’ WHERE id = 2
KILLER_HOLDING_LOCKS: [“lock_type: Write, table: fgedu_users, key: 1”]
KILLER_WAITING_FOR_LOCKS: [“lock_type: Write, table: fgedu_users, key: 2”]
1 row in set (0.01 sec)

# 3. 死锁避免策略
# – 统一操作顺序
# – 减少事务持有锁的时间
# – 使用索引避免全表扫描
# – 合理设置锁超时

3.3 事务监控方法

# 事务监控

# 4. 使用TiDB Dashboard监控
# http://192.168.1.10:2379/dashboard
# – 事务页面：查看活跃事务、长事务
# – 锁页面：查看锁等待、死锁历史

风哥提示：定期监控事务状态，及时发现并处理长事务和死锁问题。建议设置长事务告警，当事务执行时间超过阈值时及时通知。学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 OLTP场景事务优化

# OLTP场景：电商交易系统

# 场景特点：
# – 高并发
# – 短事务
# – 读写频繁

# 优化策略：

# 1. 使用悲观事务模式
SET GLOBAL tidb_txn_mode = ‘pessimistic’;

# 2. 优化事务结构
# 优化前：多步骤事务
START TRANSACTION;
SELECT balance FROM fgedu_accounts WHERE user_id = 1 FOR UPDATE;
UPDATE fgedu_accounts SET balance = balance – 100 WHERE user_id = 1;
UPDATE fgedu_orders SET status = ‘paid’ WHERE order_id = 1001;
INSERT INTO fgedu_transactions (user_id, amount, type) VALUES (1, 100, ‘payment’);
COMMIT;

# 优化后：减少锁持有时间
START TRANSACTION;
# 1. 先获取锁
SELECT balance FROM fgedu_accounts WHERE user_id = 1 FOR UPDATE;
# 2. 快速执行核心操作
UPDATE fgedu_accounts SET balance = balance – 100 WHERE user_id = 1;
UPDATE fgedu_orders SET status = ‘paid’ WHERE order_id = 1001;
COMMIT;

# 3. 非核心操作异步处理
START TRANSACTION;
INSERT INTO fgedu_transactions (user_id, amount, type) VALUES (1, 100, ‘payment’);
COMMIT;

# 3. 使用索引避免全表扫描
CREATE INDEX idx_user_id ON fgedu_accounts(user_id);
CREATE INDEX idx_order_id ON fgedu_orders(order_id);

# 4. 监控事务性能
# 事务执行时间：< 100ms # 事务成功率：> 99.9%

# 验证结果
mysql> SELECT
-> AVG(timestampdiff(second, trx_started, NOW())) AS avg_duration,
-> COUNT(*) AS total_transactions
-> FROM information_schema.innodb_trx;
+————-+———————+
| avg_duration| total_transactions |
+————-+———————+
| 0.05 | 10000 |
+————-+———————+
1 row in set (0.01 sec)

4.2 批量事务处理

# 批量处理场景：数据迁移

# 场景特点：
# – 大量数据操作
# – 高可靠性要求
# – 避免大事务

# 优化策略：

# 1. 分批处理脚本
#!/bin/bash
# batch-process.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

batch_size=1000
start_id=1
end_id=1000000

while [ $start_id -le $end_id ]; do
end_batch=$((start_id + batch_size – 1))
if [ $end_batch -gt $end_id ]; then
end_batch=$end_id
fi

echo “Processing batch: $start_id to $end_batch”

mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
START TRANSACTION;
INSERT INTO fgedu_new_table (id, name, value)
SELECT id, name, value FROM fgedu_old_table
WHERE id BETWEEN $start_id AND $end_batch;
COMMIT;
”

start_id=$((end_batch + 1))
sleep 1 # 避免系统负载过高
done

echo “Batch processing completed”

# 2. 使用LOAD DATA加速
LOAD DATA LOCAL INFILE ‘/data/batch_data.csv’
INTO TABLE fgedu_batch_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(id, name, value);

# 3. 监控批量处理
# 每批次执行时间：< 5秒 # 错误处理：记录失败批次 # 执行结果示例 Processing batch: 1 to 1000 Processing batch: 1001 to 2000 ... Processing batch: 999001 to 1000000 Batch processing completed # 数据验证 mysql> SELECT COUNT(*) FROM fgedu_new_table;
+———-+
| count(*) |
+———-+
| 1000000 |
+———-+
1 row in set (0.01 sec)

4.3 长事务处理

# 长事务场景：数据报表生成

# 场景特点：
# – 复杂查询
# – 长时间执行
# – 锁定资源

# 优化策略：

# 2. 优化长事务
# – 使用TiFlash加速查询
# – 限制返回数据量
# – 避免在事务中进行复杂计算

# 优化前
START TRANSACTION;
SELECT * FROM fgedu_logs WHERE created_at > ‘2024-01-01’;
— 处理数据…
COMMIT;

# 优化后
— 1. 使用TiFlash
SELECT /*+ read_from_storage(tiflash[fgedu_logs]) */
DATE(created_at) AS date,
COUNT(*) AS cnt
FROM fgedu_logs
WHERE created_at > ‘2024-01-01’
GROUP BY DATE(created_at);

— 2. 分批处理
SET @batch_size = 10000;
SET @offset = 0;

WHILE 1 DO
SET @rows = 0;
START TRANSACTION;
SELECT COUNT(*) INTO @rows FROM fgedu_logs
WHERE created_at > ‘2024-01-01’
LIMIT @offset, @batch_size;

IF @rows = 0 THEN
COMMIT;
LEAVE;
END IF;

— 处理数据
COMMIT;
SET @offset = @offset + @batch_size;
END WHILE;

# 3. 长事务告警
# 设置监控：当事务执行时间超过30秒时告警
# 自动kill：当事务执行时间超过5分钟时自动kill

风哥提示：长事务是TiDB性能的大敌，会占用系统资源并可能导致锁竞争。建议将长事务拆分为多个短事务，或使用TiFlash进行分析查询。更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 事务管理最佳实践

选择合适的事务模式：根据并发冲突情况选择乐观或悲观模式
控制事务大小：单事务不超过5000条语句或500MB数据
减少锁持有时间：核心操作放在事务中，非核心操作异步处理
使用索引：避免全表扫描导致的锁范围扩大
统一操作顺序：避免死锁
监控事务状态：及时发现长事务和死锁

5.2 常见问题与解决方案

# 常见事务问题及解决方案

# 问题1：死锁
# 症状：ERROR 1213 (40001): Deadlock found when trying to get lock
# 解决方案：
# – 统一操作顺序
# – 减少事务持有锁的时间
# – 合理设置锁超时

# 问题2：事务冲突（乐观事务）
# 症状：ERROR 1062 (23000): Duplicate entry
# 解决方案：
# – 增加重试机制
# – 改用悲观事务模式
# – 合理设计主键

# 问题3：长事务
# 症状：事务执行时间过长，占用系统资源
# 解决方案：
# – 拆分长事务
# – 使用TiFlash进行分析查询
# – 设置长事务告警

# 问题4：锁超时
# 症状：ERROR 1205 (HY000): Lock wait timeout exceeded
# 解决方案：
# – 优化查询性能
# – 减少事务持有锁的时间
# – 合理设置innodb_lock_wait_timeout

# 问题5：内存不足
# 症状：OOM killed
# 解决方案：
# – 控制事务大小
# – 增加服务器内存
# – 优化SQL语句

5.3 事务优化检查清单

#!/bin/bash
# tidb-transaction-checklist.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

# TiDB事务优化检查清单

echo “=== TiDB事务优化检查清单 ===”

# 1. 事务模式检查
echo “[ ] 事务模式是否选择正确（乐观/悲观）？”
echo “[ ] 是否根据业务场景调整事务模式？”

# 2. 事务大小检查
echo “[ ] 单事务语句数是否不超过5000条？”
echo “[ ] 单事务数据量是否不超过500MB？”
echo “[ ] 单事务执行时间是否不超过10秒？”

# 3. 并发控制检查
echo “[ ] 死锁检测是否开启？”
echo “[ ] 事务自动重试是否配置？”
echo “[ ] 锁超时设置是否合理？”

# 4. 索引检查
echo “[ ] WHERE条件字段是否有索引支持？”
echo “[ ] 避免全表扫描导致的锁范围扩大？”

# 5. 监控检查
echo “[ ] 是否监控长事务？”
echo “[ ] 是否监控死锁情况？”
echo “[ ] 是否设置事务相关告警？”

# 6. 代码检查
echo “[ ] 是否使用批量操作处理大量数据？”
echo “[ ] 是否在事务中进行非必要操作？”
echo “[ ] 是否统一操作顺序避免死锁？”

echo “=== 检查完成 ===”

# 执行检查示例
mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
SELECT
COUNT(*) AS long_transactions
FROM information_schema.processlist
WHERE time > 60;
”

echo “长事务数量: $?”

mysql -h192.168.1.10 -P4000 -ufgedu -p’fgedu123′ -e ”
SELECT
COUNT(*) AS deadlock_count
FROM information_schema.deadlocks;
”

echo “死锁数量: $?”

风哥提示：事务管理是TiDB运维的重要组成部分。建议建立事务管理规范，定期进行事务性能分析，确保系统稳定运行。from tidb视频:www.itpux.com

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

tidb教程FG107-TiDB事务管理与优化生产实战

Part01-基础概念与理论知识

1.1 TiDB事务概述

1.2 隔离级别详解

1.3 事务类型与特点

Part02-生产环境规划与建议

2.1 事务模式选择

2.2 并发控制策略

2.3 事务大小控制

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

3.1 事务使用实战

3.1.1 基本事务操作

3.1.2 事务隔离级别测试

3.2 死锁处理实战

3.3 事务监控方法

Part04-生产案例与实战讲解

4.1 OLTP场景事务优化

4.2 批量事务处理

4.3 长事务处理

Part05-风哥经验总结与分享

5.1 事务管理最佳实践

5.2 常见问题与解决方案

5.3 事务优化检查清单

相关推荐

联系我们