1. 事务定义

事务是一组数据库操作的逻辑单元：
– 要么全部成功
– 要么全部失败
– 是不可分割的工作单位

事务生命周期：
+——————-+
| BEGIN | 开始事务
+——————-+
|
v
+——————-+
| SQL操作 | 执行SQL语句
+——————-+
|
v
+——————-+
| COMMIT/ROLLBACK | 提交或回滚
+——————-+

2. 事务基本操作

开始事务：
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> SAVEPOINT sp1;

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

回滚到保存点：
mysql> ROLLBACK TO SAVEPOINT sp1;

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

释放保存点：
mysql> RELEASE SAVEPOINT sp1;

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

3. 自动提交模式

查看自动提交状态：
mysql> SELECT @@autocommit;

输出示例：
+————–+
| @@autocommit |
+————–+
| 1 |
+————–+

关闭自动提交：
mysql> SET autocommit = 0;

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

开启自动提交：
mysql> SET autocommit = 1;

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

4. 事务状态

查看当前事务：
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
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1

5. 事务统计

查看事务统计：
mysql> SHOW STATUS LIKE ‘Innodb_trx%’;

输出示例：
+————————+——-+
| Variable_name | Value |
+————————+——-+
| Innodb_trx_rseg_history| 0 |
+————————+——-+

查看提交统计：
mysql> SHOW STATUS LIKE ‘Com_commit’;
mysql> SHOW STATUS LIKE ‘Com_rollback’;

输出示例：
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Com_commit | 12345 |
| Com_rollback | 100 |
+—————+——-+

1. 原子性（Atomicity）

定义：事务是不可分割的工作单位

实现机制：
– Undo Log（回滚日志）
– 事务失败时回滚所有操作

示例：
mysql> BEGIN;
mysql> INSERT INTO orders VALUES (1, ‘pending’, 100);
mysql> INSERT INTO order_items VALUES (1, 1, ‘product1’);
mysql> — 如果第二条插入失败，第一条也会回滚
mysql> COMMIT;

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

查看Undo Log：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
———————-
LOG
———————-
Log sequence number 12345678
Log flushed up to 12345678
Pages flushed up to 12345678
Last checkpoint at 12345678

2. 一致性（Consistency）

定义：事务执行后数据库状态保持一致

实现机制：
– 约束检查
– 触发器
– 外键约束

示例：
mysql> CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
CHECK (amount > 0)
);

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

mysql> BEGIN;
mysql> INSERT INTO orders VALUES (1, -100);
ERROR 3819 (HY000): Check constraint ‘orders_chk_1’ is violated.

3. 隔离性（Isolation）

定义：并发事务之间相互隔离

实现机制：
– 锁机制
– MVCC（多版本并发控制）

查看隔离级别：
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. 持久性（Durability）

定义：事务提交后永久保存

实现机制：
– Redo Log（重做日志）
– Doublewrite Buffer

查看Redo Log配置：
mysql> SHOW VARIABLES LIKE ‘innodb_log%’;

输出示例：
+—————————–+———–+
| Variable_name | Value |
+—————————–+———–+
| innodb_log_buffer_size | 16777216 |
| innodb_log_file_size | 1073741824|
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
+—————————–+———–+

查看刷盘策略：
mysql> SHOW VARIABLES LIKE ‘innodb_flush_log_at_trx_commit’;

输出示例：
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+

刷盘策略说明：
0 – 每秒刷盘一次
1 – 每次提交刷盘（最安全）
2 – 每次提交写入OS缓存，每秒刷盘

5. MVCC机制

MVCC（多版本并发控制）实现：
– 每行数据有隐藏列：DB_TRX_ID、DB_ROLL_PTR
– Read View用于判断可见性
– Undo Log用于版本回溯

查看MVCC相关状态：
mysql> SHOW ENGINE INNODB STATUS\G

输出示例：
———————-
TRANSACTIONS
———————-
Trx id counter 12345
Purge done for trx’s n:o < 12344 History list length 10

1. 隔离级别类型

+——————-+—————-+—————-+—————-+
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
+——————-+—————-+—————-+—————-+
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不可能 | 可能 | 可能 |
| REPEATABLE READ | 不可能 | 不可能 | 可能(InnoDB不会)|
| SERIALIZABLE | 不可能 | 不可能 | 不可能 |
+——————-+—————-+—————-+—————-+

2. READ UNCOMMITTED

特点：
– 最低隔离级别
– 可能读取未提交数据
– 性能最高，但数据不一致风险大

设置：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

示例（脏读）：
— 会话1
mysql> BEGIN;
mysql> UPDATE orders SET amount = 200 WHERE id = 1;
— 未提交

— 会话2（READ UNCOMMITTED）
mysql> SELECT amount FROM orders WHERE id = 1;

输出示例：
+——–+
| amount |
+——–+
| 200.00 | — 读到未提交数据
+——–+

3. READ COMMITTED

特点：
– 只能读取已提交数据
– 可能不可重复读
– Oracle默认级别

设置：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

示例（不可重复读）：
— 会话1
mysql> BEGIN;
mysql> SELECT amount FROM orders WHERE id = 1;

输出示例：
+——–+
| amount |
+——–+
| 100.00 |
+——–+

— 会话2
mysql> UPDATE orders SET amount = 200 WHERE id = 1;
mysql> COMMIT;

— 会话1再次查询
mysql> SELECT amount FROM orders WHERE id = 1;

输出示例：
+——–+
| amount |
+——–+
| 200.00 | — 数据变了
+——–+

4. REPEATABLE READ

特点：
– MySQL默认隔离级别
– 同一事务内读取一致
– InnoDB通过MVCC防止幻读

设置：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

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

示例：
— 会话1
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE status = ‘pending’;

输出示例：
+—-+———+——–+
| id | status | amount |
+—-+———+——–+
| 1 | pending | 100.00 |
+—-+———+——–+

— 会话2
mysql> INSERT INTO orders VALUES (2, ‘pending’, 200);
mysql> COMMIT;

— 会话1再次查询
mysql> SELECT * FROM orders WHERE status = ‘pending’;

输出示例：
+—-+———+——–+
| id | status | amount |
+—-+———+——–+
| 1 | pending | 100.00 | — 没有新数据
+—-+———+——–+

5. SERIALIZABLE

特点：
– 最高隔离级别
– 所有读操作加共享锁
– 性能最低，数据最安全

设置：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

示例：
— 会话1
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1;

— 会话2
mysql> UPDATE orders SET amount = 200 WHERE id = 1;
— 等待会话1提交或回滚

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

6. 隔离级别选择

选择建议：
+——————-+——————————————+
| 场景 | 推荐隔离级别 |
+——————-+——————————————+
| 高并发OLTP | READ COMMITTED |
| 报表查询 | REPEATABLE READ |
| 金融交易 | SERIALIZABLE |
| 日志记录 | READ UNCOMMITTED |
+——————-+——————————————+

全局设置：
vim /etc/my.cnf

[mysqld]
transaction_isolation = READ-COMMITTED

重启MySQL：
systemctl restart mysqld

输出示例：
MySQL service restarted.

1. 事务边界原则

原则：事务应该尽可能短小

问题设计：
BEGIN;
— 复杂业务逻辑处理（10秒）
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
— 更多处理（5秒）
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;
— 总耗时：15秒

优化设计：
— 先处理业务逻辑（不涉及数据库）
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
UPDATE orders SET status = ‘processed’ WHERE id = 1;
COMMIT;
— 总耗时：0.1秒

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

2. 事务粒度原则

原则：根据业务需求确定事务粒度

细粒度事务：
— 每个操作独立事务
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

粗粒度事务：
— 相关操作放在同一事务
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

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

3. 锁获取顺序原则

原则：按固定顺序获取锁，避免死锁

问题设计：
— 事务1
UPDATE orders SET … WHERE id = 1;
UPDATE orders SET … WHERE id = 2;

— 事务2
UPDATE orders SET … WHERE id = 2;
UPDATE orders SET … WHERE id = 1;
— 可能死锁

优化设计：
— 所有事务按id升序获取锁
— 事务1和事务2都按id=1, id=2的顺序

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

4. 只读事务原则

原则：只读操作使用只读事务

普通事务：
BEGIN;
SELECT * FROM orders WHERE id = 1;
COMMIT;

只读事务：
START TRANSACTION READ ONLY;
SELECT * FROM orders WHERE id = 1;
COMMIT;

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

好处：
– 减少锁开销
– 提高并发性能
– 优化器可以做更多优化

5. 错误处理原则

原则：正确处理事务错误

应用层错误处理：
try:
conn.begin()
cursor.execute(“UPDATE orders SET status = ‘processed’ WHERE id = 1”)
conn.commit()
except Exception as e:
conn.rollback()
raise e

存储过程错误处理：
DELIMITER //
CREATE PROCEDURE process_order(IN p_id INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT ‘Error occurred, transaction rolled back’ AS message;
END;

START TRANSACTION;
UPDATE orders SET status = ‘processing’ WHERE id = p_id;
— 其他操作
COMMIT;
END //
DELIMITER ;

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

1. 减少锁持有时间

问题代码：
mysql> BEGIN;
mysql> SELECT * FROM orders WHERE id = 1 FOR UPDATE;
— 长时间处理
mysql> UPDATE orders SET status = ‘processed’ WHERE id = 1;
mysql> COMMIT;

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

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

2. 批量操作优化

问题代码：
for i in range(1000):
cursor.execute(“INSERT INTO orders VALUES (%s, …)”, (i, …))
conn.commit()

优化代码：
cursor.execute(“INSERT INTO orders VALUES …”)
# 批量插入
cursor.executemany(“INSERT INTO orders VALUES (%s, %s, %s)”,
[(1, ‘pending’, 100), (2, ‘pending’, 200), …])
conn.commit()

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

3. 使用乐观锁

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

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

乐观锁更新：
mysql> BEGIN;
mysql> SELECT id, status, version FROM orders WHERE id = 1;

输出示例：
+—-+———+———+
| id | status | version |
+—-+———+———+
| 1 | pending | 0 |
+—-+———+———+

mysql> UPDATE orders
SET status = ‘processed’, version = version + 1
WHERE id = 1 AND version = 0;

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

mysql> COMMIT;

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

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

4. 避免热点数据

问题：
mysql> UPDATE counter SET value = value + 1 WHERE id = 1;

优化：
— 使用分片计数
mysql> UPDATE counter_shard
SET value = value + 1
WHERE shard_id = FLOOR(RAND() * 10);

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

— 汇总查询
mysql> SELECT SUM(value) FROM counter_shard;

输出示例：
+————+
| SUM(value) |
+————+
| 10000 |
+————+

5. 合理设置超时

锁等待超时：
mysql> SET innodb_lock_wait_timeout = 10;

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

事务超时（MySQL 8.0+）：
mysql> SET SESSION MAX_EXECUTION_TIME = 30000;

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

1. 监控活跃事务

查看活跃事务：
mysql> SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
trx_mysql_thread_id,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

输出示例：
+——–+———–+———————+————–+———————+——————+
| trx_id | trx_state | trx_started | duration_sec | trx_mysql_thread_id | trx_query |
+——–+———–+———————+————–+———————+——————+
| 12345 | RUNNING | 2026-04-01 12:00:00 | 30 | 25 | NULL |
| 12346 | LOCK WAIT | 2026-04-01 12:00:10 | 20 | 26 | UPDATE orders… |
+——–+———–+———————+————–+———————+——————+

2. 监控长事务

创建长事务监控视图：
mysql> CREATE VIEW v_long_transactions AS
SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
trx_mysql_thread_id,
trx_rows_modified
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

查询长事务：
mysql> SELECT * FROM v_long_transactions;

输出示例：
+——–+———–+———————+————–+———————+——————+
| trx_id | trx_state | trx_started | duration_sec | trx_mysql_thread_id | trx_rows_modified|
+——–+———–+———————+————–+———————+——————+
| 12345 | RUNNING | 2026-04-01 11:00:00 | 3600 | 25 | 100 |
+——–+———–+———————+————–+———————+——————+

3. 监控事务统计

查看事务统计：
mysql> SHOW STATUS LIKE ‘Com_%’;

输出示例：
+——————+——-+
| Variable_name | Value |
+——————+——-+
| Com_begin | 12345 |
| Com_commit | 12300 |
| Com_rollback | 100 |
+——————+——-+

计算回滚率：
mysql> SELECT
VARIABLE_VALUE AS rollbacks
FROM performance_schema.global_status
WHERE VARIABLE_NAME = ‘Com_rollback’;

输出示例：
+———–+
| rollbacks |
+———–+
| 100 |
+———–+

4. 监控锁等待

查看锁等待：
mysql> 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;

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

5. 自动化监控脚本

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

脚本内容：
#!/bin/bash

mysql -u monitor -p -N -e ”
SELECT COUNT(*) FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
” > /tmp/long_trx_count.txt

COUNT=$(cat /tmp/long_trx_count.txt)

if [ $COUNT -gt 0 ]; then
echo “Warning: $COUNT long transactions detected” | \
mail -s “MySQL Long Transaction Alert” dba@company.com
fi

设置定时任务：
*/5 * * * * /opt/mysql/scripts/transaction_monitor.sh

输出示例：
crontab: installing new crontab

1. 事务命名规范

事务注释规范：
mysql> BEGIN;
— 事务名称：订单处理事务
— 功能：处理订单状态变更
— 作者：DBA
— 日期：2026-04-01
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;
mysql> COMMIT;

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

2. 事务模板

标准事务模板：
— 事务开始
BEGIN;

— 1. 数据验证
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
— 验证业务条件

— 2. 业务操作
UPDATE orders SET status = ‘processing’ WHERE id = 1;
INSERT INTO order_log (order_id, action, created_at)
VALUES (1, ‘status_changed’, NOW());

— 3. 提交事务
COMMIT;

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

带错误处理的事务模板：
DELIMITER //
CREATE PROCEDURE safe_transaction(IN p_order_id INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT ‘Transaction failed and rolled back’ AS result;
END;

START TRANSACTION;

— 业务逻辑
UPDATE orders SET status = ‘processing’ WHERE id = p_order_id;

COMMIT;
SELECT ‘Transaction completed successfully’ AS result;
END //
DELIMITER ;

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

3. 事务日志记录

创建事务日志表：
mysql> CREATE TABLE transaction_log (
id INT AUTO_INCREMENT PRIMARY KEY,
trx_name VARCHAR(100),
start_time TIMESTAMP,
end_time TIMESTAMP NULL,
status VARCHAR(20),
rows_affected INT DEFAULT 0,
error_message TEXT
);

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

记录事务日志：
mysql> INSERT INTO transaction_log (trx_name, start_time, status)
VALUES (‘order_processing’, NOW(), ‘running’);

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

更新事务日志：
mysql> UPDATE transaction_log
SET end_time = NOW(), status = ‘completed’, rows_affected = 1
WHERE id = LAST_INSERT_ID();

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

4. 事务超时配置

配置事务超时：
[mysqld]
# 锁等待超时
innodb_lock_wait_timeout = 30

# 事务超时（MySQL 8.0.14+）
max_execution_time = 30000

应用层设置：
# Python
cursor.execute(“SET SESSION innodb_lock_wait_timeout = 10”)

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

5. 事务测试规范

单元测试示例：
import mysql.connector
import unittest

class TestTransaction(unittest.TestCase):
def setUp(self):
self.conn = mysql.connector.connect(…)
self.cursor = self.conn.cursor()

def test_commit(self):
self.cursor.execute(“BEGIN”)
self.cursor.execute(“INSERT INTO orders VALUES (1, ‘test’)”)
self.cursor.execute(“COMMIT”)

self.cursor.execute(“SELECT * FROM orders WHERE id = 1”)
result = self.cursor.fetchone()
self.assertIsNotNone(result)

def test_rollback(self):
self.cursor.execute(“BEGIN”)
self.cursor.execute(“INSERT INTO orders VALUES (2, ‘test’)”)
self.cursor.execute(“ROLLBACK”)

self.cursor.execute(“SELECT * FROM orders WHERE id = 2”)
result = self.cursor.fetchone()
self.assertIsNone(result)

def tearDown(self):
self.cursor.close()
self.conn.close()

输出示例：
Ran 2 tests in 0.050s
OK

1. 识别长事务

查找长事务：
mysql> SELECT
trx_id,
trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
trx_mysql_thread_id,
trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY trx_started;

输出示例：
+——–+———–+————–+———————+——————+
| trx_id | trx_state | duration_sec | trx_mysql_thread_id | trx_query |
+——–+———–+————–+———————+——————+
| 12345 | RUNNING | 3600 | 25 | NULL |
+——–+———–+————–+———————+——————+

2. 分析长事务原因

常见原因：
– 事务中包含耗时操作
– 忘记提交事务
– 锁等待导致阻塞
– 大批量数据处理

分析事务详情：
mysql> SELECT * FROM information_schema.innodb_trx
WHERE trx_id = 12345\G

输出示例：
*************************** 1. row ***************************
trx_id: 12345
trx_state: RUNNING
trx_started: 2026-04-01 11:00:00
trx_weight: 100
trx_mysql_thread_id: 25
trx_query: NULL
trx_rows_modified: 100
trx_rows_locked: 50

3. 处理长事务

方案1：终止长事务
mysql> KILL 25;

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

方案2：通知应用提交
— 联系开发人员处理

方案3：设置事务超时
mysql> SET GLOBAL max_execution_time = 300000;

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

4. 预防长事务

应用层预防：
– 设置事务超时
– 使用连接池检测
– 监控事务时长

数据库层预防：
[mysqld]
# 设置最大执行时间
max_execution_time = 300000

# 设置锁等待超时
innodb_lock_wait_timeout = 30

5. 长事务监控告警

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

脚本内容：
#!/bin/bash

THRESHOLD=60
ALERT_EMAIL=”dba@company.com”

COUNT=$(mysql -u monitor -p -N -e ”
SELECT COUNT(*) FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > $THRESHOLD
“)

if [ $COUNT -gt 0 ]; then
mysql -u monitor -p -e ”
SELECT trx_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > $THRESHOLD
” | mail -s “Long Transaction Alert: $COUNT transactions” $ALERT_EMAIL
fi

输出示例：
Alert check completed.

1. XA事务

开启XA事务：
mysql> XA START ‘xid1’;

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

执行操作：
mysql> UPDATE orders SET status = ‘processing’ WHERE id = 1;

结束XA事务：
mysql> XA END ‘xid1’;

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

准备XA事务：
mysql> XA PREPARE ‘xid1’;

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

提交XA事务：
mysql> XA COMMIT ‘xid1’;

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

回滚XA事务：
mysql> XA ROLLBACK ‘xid1’;

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

2. 查看XA事务

查看XA事务：
mysql> XA RECOVER;

输出示例：
+———-+————–+————–+——+
| formatID | gtrid_length | bqual_length | data |
+———-+————–+————–+——+
| 1 | 4 | 0 | xid1 |
+———-+————–+————–+——+

3. 两阶段提交

阶段1：准备阶段
— 参与者1
XA START ‘tx1’;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
XA END ‘tx1’;
XA PREPARE ‘tx1’;

— 参与者2
XA START ‘tx2’;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
XA END ‘tx2’;
XA PREPARE ‘tx2’;

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

阶段2：提交阶段
— 协调者决定提交
XA COMMIT ‘tx1’;
XA COMMIT ‘tx2’;

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

4. 分布式事务问题

常见问题：
– 性能开销大
– 协调者单点故障
– 网络分区问题
– 数据不一致风险

解决方案：
– 使用最终一致性
– 使用TCC模式
– 使用Saga模式
– 使用消息队列

5. 替代方案

本地消息表方案：
mysql> CREATE TABLE local_message (
id INT AUTO_INCREMENT PRIMARY KEY,
transaction_id VARCHAR(100),
message TEXT,
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

执行流程：
— 本地事务
BEGIN;
UPDATE accounts SET balance = balance – 100 WHERE id = 1;
INSERT INTO local_message (transaction_id, message, status)
VALUES (‘tx001’, ‘{“to”: 2, “amount”: 100}’, ‘pending’);
COMMIT;

— 消息处理
— 后台任务读取消息并发送

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

# 案例：订单处理死锁

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

# 死锁场景复现：
# 会话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：分析死锁原因
# – 两个事务按不同顺序获取锁
# – 形成循环等待

# 步骤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：使用单个UPDATE
mysql> UPDATE orders SET status = ‘processing’ WHERE id IN (1, 2);

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

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

# 步骤5：验证优化效果
# 死锁次数从每天100次降到0次

# 案例：订单处理超时

# 问题描述：
# 订单处理事务经常超时

# 步骤1：分析超时情况
mysql> SELECT
trx_id,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
trx_state,
trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;

# 输出示例：
# +——–+————–+———–+———————+
# | trx_id | duration_sec | trx_state | trx_mysql_thread_id |
# +——–+————–+———–+———————+
# | 12345 | 120 | RUNNING | 25 |
# +——–+————–+———–+———————+

# 步骤2：分析事务详情
mysql> SHOW PROCESSLIST;

# 输出示例：
# +—-+——+———–+——+———+——+———-+——————+
# | Id | User | Host | db | Command | Time | State | Info |
# +—-+——+———–+——+———+——+———-+——————+
# | 25 | app | localhost | prod | Sleep | 120 | | NULL |
# +—-+——+———–+——+———+——+———-+——————+

# 问题分析：
# – 事务持有锁但处于Sleep状态
# – 可能是应用层未正确提交

# 步骤3：解决方案
# 方案1：设置锁等待超时
mysql> SET GLOBAL innodb_lock_wait_timeout = 30;

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

# 方案2：终止超时事务
mysql> KILL 25;

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

# 方案3：应用层优化
# – 确保事务及时提交
# – 添加事务超时检测
# – 使用连接池检测

# 步骤4：预防措施
# – 监控长事务
# – 设置事务超时告警
# – 定期检查连接状态

# 案例：批量订单处理性能优化

# 问题描述：
# 批量处理订单性能差

# 原始代码：
for order in orders:
cursor.execute(“BEGIN”)
cursor.execute(“UPDATE orders SET status = ‘processed’ WHERE id = %s”, (order.id,))
cursor.execute(“COMMIT”)

# 性能：1000条订单处理时间60秒

# 步骤1：分析性能瓶颈
# – 大量小事务开销
# – 每次提交都刷盘

# 步骤2：优化方案
# 方案1：批量事务
cursor.execute(“BEGIN”)
for order in orders:
cursor.execute(“UPDATE orders SET status = ‘processed’ WHERE id = %s”, (order.id,))
cursor.execute(“COMMIT”)

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

# 方案2：批量UPDATE
cursor.execute(“””
UPDATE orders SET status = ‘processed’
WHERE id IN (%s, %s, %s, …)
“””, tuple(order.id for order in orders))

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

# 方案3：使用CASE WHEN
cursor.execute(“””
UPDATE orders SET status = CASE id
WHEN 1 THEN ‘processed’
WHEN 2 THEN ‘processed’
…
END
WHERE id IN (1, 2, …)
“””)

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

# 步骤3：性能对比
# +—————-+————+
# | 方案 | 执行时间 |
# +—————-+————+
# | 原始方案 | 60秒 |
# | 批量事务 | 5秒 |
# | 批量UPDATE | 0.5秒 |
# | CASE WHEN | 0.3秒 |
# +—————-+————+

# 性能提升：从60秒降到0.3秒，提升200倍

1. 事务设计原则

– 保持事务短小
– 减少锁持有时间
– 按固定顺序获取锁
– 及时提交事务

2. 隔离级别选择

– 根据业务需求选择
– 默认使用REPEATABLE READ
– 高并发场景考虑READ COMMITTED
– 金融场景使用SERIALIZABLE

3. 错误处理原则

– 正确处理事务错误
– 实现死锁重试机制
– 设置合理的超时
– 记录事务日志

4. 监控原则

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

5. 测试原则

– 进行并发测试
– 模拟高并发场景
– 测试事务回滚
– 验证数据一致性

1. 避免长事务

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

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

2. 避免热点竞争

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

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

3. 避免锁顺序不一致

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

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

4. 避免忘记提交

反模式：
BEGIN;
UPDATE …;
— 忘记COMMIT

正确做法：
使用try-finally确保提交

5. 避免不合理的隔离级别

反模式：
所有场景使用SERIALIZABLE

正确做法：
根据业务需求选择

1. 设计阶段检查

[ ] 是否确定了事务边界
[ ] 是否选择了合适的隔离级别
[ ] 是否考虑了并发场景
[ ] 是否设计了错误处理

2. 开发阶段检查

[ ] 是否保持了事务短小
[ ] 是否按固定顺序获取锁
[ ] 是否正确处理了错误
[ ] 是否实现了重试机制

3. 测试阶段检查

[ ] 是否测试了并发场景
[ ] 是否测试了事务回滚
[ ] 是否测试了超时处理
[ ] 是否验证了数据一致性

4. 运维阶段检查

[ ] 是否配置了事务监控
[ ] 是否设置了告警阈值
[ ] 是否有应急处理方案
[ ] 是否定期检查长事务

5. 监控阶段检查

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