本文档风哥主要介绍DM数据库事务管理与锁机制，包括事务概述、锁概述、隔离级别、事务管理、锁管理、死锁处理、事务优化、锁优化、监控工具、实际案例和最佳实践等内容，风哥教程参考DM官方文档DM8系统管理员手册、DM8性能优化指南，适合数据库技术人员在学习和生产环境中使用。
Part01-基础概念与理论知识
1.1 事务概述
事务是数据库操作的逻辑单元，保证数据的一致性和完整性。
# 事务的定义
事务是数据库操作的逻辑单元，保证数据的一致性和完整性，具有ACID特性：
– 原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败
– 一致性（Consistency）：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态
– 隔离性（Isolation）：多个事务并发执行时，每个事务都感觉不到其他事务的存在
– 持久性（Durability）：事务提交后，对数据库的修改是永久的
# 事务的生命周期
1. 开始事务（BEGIN TRANSACTION）
2. 执行SQL语句
3. 提交事务（COMMIT）或回滚事务（ROLLBACK）
# 事务的类型
1. 显式事务：使用BEGIN TRANSACTION、COMMIT、ROLLBACK控制的事务
2. 隐式事务：每条SQL语句都是一个事务
3. 自动提交事务：每条SQL语句自动提交
# 事务的应用场景
– 银行转账：保证转账操作的原子性
– 订单处理：保证订单操作的完整性
– 库存管理：保证库存操作的一致性
– 数据同步：保证数据同步的准确性
事务是数据库操作的逻辑单元，保证数据的一致性和完整性，具有ACID特性：
– 原子性（Atomicity）：事务中的操作要么全部成功，要么全部失败
– 一致性（Consistency）：事务执行前后，数据库从一个一致性状态转换到另一个一致性状态
– 隔离性（Isolation）：多个事务并发执行时，每个事务都感觉不到其他事务的存在
– 持久性（Durability）：事务提交后，对数据库的修改是永久的
# 事务的生命周期
1. 开始事务（BEGIN TRANSACTION）
2. 执行SQL语句
3. 提交事务（COMMIT）或回滚事务（ROLLBACK）
# 事务的类型
1. 显式事务：使用BEGIN TRANSACTION、COMMIT、ROLLBACK控制的事务
2. 隐式事务：每条SQL语句都是一个事务
3. 自动提交事务：每条SQL语句自动提交
# 事务的应用场景
– 银行转账：保证转账操作的原子性
– 订单处理：保证订单操作的完整性
– 库存管理：保证库存操作的一致性
– 数据同步：保证数据同步的准确性
1.2 锁概述
锁是数据库并发控制的重要机制，保证数据的一致性和完整性。
# 锁的定义
锁是数据库并发控制的重要机制，用于控制多个事务对同一资源的并发访问，保证数据的一致性和完整性。
# 锁的类型
1. 共享锁（Shared Lock，S锁）
– 允许其他事务获取共享锁
– 不允许其他事务获取排他锁
– 用于读操作
2. 排他锁（Exclusive Lock，X锁）
– 不允许其他事务获取共享锁或排他锁
– 用于写操作
3. 意向锁（Intention Lock）
– 意向共享锁（IS锁）
– 意向排他锁（IX锁）
# 锁的粒度
1. 行锁：锁定单行数据
2. 页锁：锁定数据页
3. 表锁：锁定整个表
4. 数据库锁：锁定整个数据库
# 锁的兼容性
– 共享锁与共享锁：兼容
– 共享锁与排他锁：不兼容
– 排他锁与共享锁：不兼容
– 排他锁与排他锁：不兼容
锁是数据库并发控制的重要机制，用于控制多个事务对同一资源的并发访问，保证数据的一致性和完整性。
# 锁的类型
1. 共享锁（Shared Lock，S锁）
– 允许其他事务获取共享锁
– 不允许其他事务获取排他锁
– 用于读操作
2. 排他锁（Exclusive Lock，X锁）
– 不允许其他事务获取共享锁或排他锁
– 用于写操作
3. 意向锁（Intention Lock）
– 意向共享锁（IS锁）
– 意向排他锁（IX锁）
# 锁的粒度
1. 行锁：锁定单行数据
2. 页锁：锁定数据页
3. 表锁：锁定整个表
4. 数据库锁：锁定整个数据库
# 锁的兼容性
– 共享锁与共享锁：兼容
– 共享锁与排他锁：不兼容
– 排他锁与共享锁：不兼容
– 排他锁与排他锁：不兼容
1.3 隔离级别
隔离级别定义了事务之间的隔离程度，不同的隔离级别有不同的并发性能和数据一致性保证。
# 隔离级别的定义 风哥提示：
隔离级别定义了事务之间的隔离程度，不同的隔离级别有不同的并发性能和数据一致性保证。
# 隔离级别的类型
1. 读未提交（Read Uncommitted）
– 允许读取未提交的数据
– 可能出现脏读、不可重复读、幻读
– 并发性能最好，数据一致性最差
2. 读已提交（Read Committed）
– 只允许读取已提交的数据
– 避免脏读，可能出现不可重复读、幻读
– 并发性能较好，数据一致性较好
3. 可重复读（Repeatable Read）
– 保证同一事务中多次读取同一数据的结果一致
– 避免脏读、不可重复读，可能出现幻读
– 并发性能一般，数据一致性一般
4. 串行化（Serializable）
– 事务串行执行
– 避免脏读、不可重复读、幻读
– 并发性能最差，数据一致性最好
# 隔离级别的设置
– 查看当前隔离级别
SQL> select * from v$parameter where name = ‘ISOLATION_LEVEL’;
– 设置隔离级别
SQL> alter system set ‘ISOLATION_LEVEL’ = ‘RC’ both;
# 隔离级别的选择
– 读未提交：适用于对数据一致性要求不高的场景
– 读已提交：适用于大多数OLTP场景
– 可重复读：适用于需要保证数据一致性的场景
– 串行化：适用于对数据一致性要求极高的场景
隔离级别定义了事务之间的隔离程度，不同的隔离级别有不同的并发性能和数据一致性保证。
# 隔离级别的类型
1. 读未提交（Read Uncommitted）
– 允许读取未提交的数据
– 可能出现脏读、不可重复读、幻读
– 并发性能最好，数据一致性最差
2. 读已提交（Read Committed）
– 只允许读取已提交的数据
– 避免脏读，可能出现不可重复读、幻读
– 并发性能较好，数据一致性较好
3. 可重复读（Repeatable Read）
– 保证同一事务中多次读取同一数据的结果一致
– 避免脏读、不可重复读，可能出现幻读
– 并发性能一般，数据一致性一般
4. 串行化（Serializable）
– 事务串行执行
– 避免脏读、不可重复读、幻读
– 并发性能最差，数据一致性最好
# 隔离级别的设置
– 查看当前隔离级别
SQL> select * from v$parameter where name = ‘ISOLATION_LEVEL’;
– 设置隔离级别
SQL> alter system set ‘ISOLATION_LEVEL’ = ‘RC’ both;
# 隔离级别的选择
– 读未提交：适用于对数据一致性要求不高的场景
– 读已提交：适用于大多数OLTP场景
– 可重复读：适用于需要保证数据一致性的场景
– 串行化：适用于对数据一致性要求极高的场景
学习交流加群风哥微信: itpux-com
风哥提示：事务管理和锁机制是数据库并发控制的核心，理解事务的ACID特性和锁的类型，是进行数据库并发控制的基础。选择合适的隔离级别，是平衡并发性能和数据一致性的关键。
风哥提示：事务管理和锁机制是数据库并发控制的核心，理解事务的ACID特性和锁的类型，是进行数据库并发控制的基础。选择合适的隔离级别，是平衡并发性能和数据一致性的关键。
Part02-生产环境规划与建议
2.1 事务管理
2.1.1 事务控制
# 1. 显式事务控制
– 开始事务
SQL> BEGIN TRANSACTION;
– 执行SQL语句
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
– 提交事务
SQL> COMMIT;
– 回滚事务
SQL> ROLLBACK;
# 2. 隐式事务控制
– 每条SQL语句都是一个事务
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
– 自动提交
SQL> SET AUTOCOMMIT ON;
# 3. 保存点控制
– 创建保存点
SQL> SAVEPOINT sp1;
– 回滚到保存点
SQL> ROLLBACK TO sp1;
– 释放保存点
SQL> RELEASE SAVEPOINT sp1;
# 4. 实际示例
– 银行转账示例
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_account SET balance = balance – 100 WHERE account_id = 1;
SQL> UPDATE fgedu_account SET balance = balance + 100 WHERE account_id = 2;
SQL> COMMIT;
– 订单处理示例
SQL> BEGIN TRANSACTION;
SQL> INSERT INTO fgedu_order (order_id, user_id, order_amount) VALUES (1, 1, 100);
SQL> UPDATE fgedu_product SET stock = stock – 1 WHERE product_id = 1;
SQL> COMMIT;
– 开始事务
SQL> BEGIN TRANSACTION;
– 执行SQL语句
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
– 提交事务
SQL> COMMIT;
– 回滚事务
SQL> ROLLBACK;
# 2. 隐式事务控制
– 每条SQL语句都是一个事务
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES (1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’);
– 自动提交
SQL> SET AUTOCOMMIT ON;
# 3. 保存点控制
– 创建保存点
SQL> SAVEPOINT sp1;
– 回滚到保存点
SQL> ROLLBACK TO sp1;
– 释放保存点
SQL> RELEASE SAVEPOINT sp1;
# 4. 实际示例
– 银行转账示例
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_account SET balance = balance – 100 WHERE account_id = 1;
SQL> UPDATE fgedu_account SET balance = balance + 100 WHERE account_id = 2;
SQL> COMMIT;
– 订单处理示例
SQL> BEGIN TRANSACTION;
SQL> INSERT INTO fgedu_order (order_id, user_id, order_amount) VALUES (1, 1, 100);
SQL> UPDATE fgedu_product SET stock = stock – 1 WHERE product_id = 1;
SQL> COMMIT;
学习交流加群风哥QQ113257174
2.1.2 事务监控
# 1. 查看当前事务
– 查看当前活动事务
SQL> SELECT * FROM v$transactions;
– 查看当前会话的事务信息
SQL> SELECT * FROM v$session WHERE status = ‘ACTIVE’;
– 查看当前锁等待情况
SQL> SELECT * FROM v$lockwait;
# 2. 查看长事务
– 查看执行时间过长的事务
SQL> SELECT * FROM v$transactions WHERE elapsed_time > 3600;
– 查看持有锁时间过长的事务
SQL> SELECT * FROM v$lock WHERE hold_time > 3600;
# 3. 查看事务统计信息
– 查看事务统计信息
SQL> SELECT * FROM v$transaction_stats;
– 查看事务等待统计信息
SQL> SELECT * FROM v$transaction_wait_stats;
– 查看当前活动事务
SQL> SELECT * FROM v$transactions;
– 查看当前会话的事务信息
SQL> SELECT * FROM v$session WHERE status = ‘ACTIVE’;
– 查看当前锁等待情况
SQL> SELECT * FROM v$lockwait;
# 2. 查看长事务
– 查看执行时间过长的事务
SQL> SELECT * FROM v$transactions WHERE elapsed_time > 3600;
– 查看持有锁时间过长的事务
SQL> SELECT * FROM v$lock WHERE hold_time > 3600;
# 3. 查看事务统计信息
– 查看事务统计信息
SQL> SELECT * FROM v$transaction_stats;
– 查看事务等待统计信息
SQL> SELECT * FROM v$transaction_wait_stats;
2.2 锁管理
2.2.1 锁类型
# 1. 行锁
– 行锁用于锁定单行数据
– 在SELECT语句中使用FOR UPDATE获取行锁
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
– 在UPDATE语句中自动获取行锁
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# 2. 表锁
– 表锁用于锁定整个表
– 在SELECT语句中使用LOCK IN SHARE MODE获取共享表锁
SQL> SELECT * FROM fgedu_user LOCK IN SHARE MODE;
– 使用LOCK TABLE语句获取排他表锁
SQL> LOCK TABLE fgedu_user IN EXCLUSIVE MODE;
# 3. 意向锁
– 意向锁用于表示事务对表的锁意图
– 意向共享锁（IS锁）
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
– 意向排他锁（IX锁） 更多视频教程www.fgedu.net.cn
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# 4. 实际示例
– 使用行锁
SQL> BEGIN TRANSACTION;
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
– 使用表锁
SQL> BEGIN TRANSACTION;
SQL> LOCK TABLE fgedu_user IN EXCLUSIVE MODE;
SQL> UPDATE fgedu_user SET user_status = 1;
SQL> COMMIT;
– 行锁用于锁定单行数据
– 在SELECT语句中使用FOR UPDATE获取行锁
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
– 在UPDATE语句中自动获取行锁
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# 2. 表锁
– 表锁用于锁定整个表
– 在SELECT语句中使用LOCK IN SHARE MODE获取共享表锁
SQL> SELECT * FROM fgedu_user LOCK IN SHARE MODE;
– 使用LOCK TABLE语句获取排他表锁
SQL> LOCK TABLE fgedu_user IN EXCLUSIVE MODE;
# 3. 意向锁
– 意向锁用于表示事务对表的锁意图
– 意向共享锁（IS锁）
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
– 意向排他锁（IX锁） 更多视频教程www.fgedu.net.cn
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# 4. 实际示例
– 使用行锁
SQL> BEGIN TRANSACTION;
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
– 使用表锁
SQL> BEGIN TRANSACTION;
SQL> LOCK TABLE fgedu_user IN EXCLUSIVE MODE;
SQL> UPDATE fgedu_user SET user_status = 1;
SQL> COMMIT;
2.2.2 锁监控
# 1. 查看当前锁
– 查看当前所有锁
SQL> SELECT * FROM v$lock;
– 查看当前锁等待
SQL> SELECT * FROM v$lockwait;
– 查看锁的详细信息
SQL> SELECT l.*, s.username, s.program FROM v$lock l, v$session s WHERE l.session_id = s.session_id;
# 2. 查看锁统计信息
– 查看锁统计信息
SQL> SELECT * FROM v$lock_stats;
– 查看锁等待统计信息
SQL> SELECT * FROM v$lockwait_stats;
# 3. 查看锁冲突
– 查看锁冲突情况
SQL> SELECT * FROM v$lock_conflict;
– 查看锁冲突的详细信息
SQL> SELECT lc.*, s1.username AS holder_username, s2.username AS waiter_username
FROM v$lock_conflict lc, v$session s1, v$session s2
WHERE lc.holder_session_id = s1.session_id AND lc.waiter_session_id = s2.session_id;
– 查看当前所有锁
SQL> SELECT * FROM v$lock;
– 查看当前锁等待
SQL> SELECT * FROM v$lockwait;
– 查看锁的详细信息
SQL> SELECT l.*, s.username, s.program FROM v$lock l, v$session s WHERE l.session_id = s.session_id;
# 2. 查看锁统计信息
– 查看锁统计信息
SQL> SELECT * FROM v$lock_stats;
– 查看锁等待统计信息
SQL> SELECT * FROM v$lockwait_stats;
# 3. 查看锁冲突
– 查看锁冲突情况
SQL> SELECT * FROM v$lock_conflict;
– 查看锁冲突的详细信息
SQL> SELECT lc.*, s1.username AS holder_username, s2.username AS waiter_username
FROM v$lock_conflict lc, v$session s1, v$session s2
WHERE lc.holder_session_id = s1.session_id AND lc.waiter_session_id = s2.session_id;
2.3 死锁处理
2.3.1 死锁概述
# 死锁的定义
死锁是指两个或多个事务相互持有对方需要的锁，导致所有事务都无法继续执行的情况。 更多学习教程公众号风哥教程itpux_com
# 死锁的条件
1. 互斥条件：资源不能被多个事务同时使用
2. 请求与保持条件：事务持有资源的同时请求其他资源
3. 不剥夺条件：资源不能被强制剥夺
4. 循环等待条件：事务之间存在循环等待
# 死锁的检测
– 数据库自动检测死锁
– 检测到死锁后，数据库会选择一个事务作为牺牲者，回滚该事务
– 牺牲者的事务会收到死锁错误信息
# 死锁的预防
1. 按照相同的顺序访问资源
2. 尽量缩短事务的持有时间
3. 使用较低的隔离级别
4. 使用乐观并发控制
死锁是指两个或多个事务相互持有对方需要的锁，导致所有事务都无法继续执行的情况。 更多学习教程公众号风哥教程itpux_com
# 死锁的条件
1. 互斥条件：资源不能被多个事务同时使用
2. 请求与保持条件：事务持有资源的同时请求其他资源
3. 不剥夺条件：资源不能被强制剥夺
4. 循环等待条件：事务之间存在循环等待
# 死锁的检测
– 数据库自动检测死锁
– 检测到死锁后，数据库会选择一个事务作为牺牲者，回滚该事务
– 牺牲者的事务会收到死锁错误信息
# 死锁的预防
1. 按照相同的顺序访问资源
2. 尽量缩短事务的持有时间
3. 使用较低的隔离级别
4. 使用乐观并发控制
2.3.2 死锁监控
# 1. 查看死锁信息
– 查看死锁信息
SQL> SELECT * FROM v$deadlock;
– 查看死锁的详细信息
SQL> SELECT d.*, s.username, s.program FROM v$deadlock d, v$session s WHERE d.session_id = s.session_id;
# 2. 查看死锁统计信息
– 查看死锁统计信息
SQL> SELECT * FROM v$deadlock_stats;
– 查看死锁历史信息
SQL> SELECT * FROM v$deadlock_history;
# 3. 查看死锁日志
– 查看死锁日志
$ tail -f /dm/fgdata/fgedudb/log/deadlock.log from DB视频:www.itpux.com
– 查看死锁日志内容
# Deadlock detected at 2024-01-01 10:00:00
# Transaction 1: Session ID 1, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1
# Transaction 2: Session ID 2, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2
# Victim: Session ID 1
– 查看死锁信息
SQL> SELECT * FROM v$deadlock;
– 查看死锁的详细信息
SQL> SELECT d.*, s.username, s.program FROM v$deadlock d, v$session s WHERE d.session_id = s.session_id;
# 2. 查看死锁统计信息
– 查看死锁统计信息
SQL> SELECT * FROM v$deadlock_stats;
– 查看死锁历史信息
SQL> SELECT * FROM v$deadlock_history;
# 3. 查看死锁日志
– 查看死锁日志
$ tail -f /dm/fgdata/fgedudb/log/deadlock.log from DB视频:www.itpux.com
– 查看死锁日志内容
# Deadlock detected at 2024-01-01 10:00:00
# Transaction 1: Session ID 1, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1
# Transaction 2: Session ID 2, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2
# Victim: Session ID 1
生产环境建议：根据业务需求和并发要求，合理设置隔离级别，优化事务和锁的使用，避免死锁的发生。定期监控事务和锁的状态，及时发现和解决问题。
Part03-生产环境项目实施方案
3.1 事务优化
3.1.1 减少事务持有时间
# 1. 优化事务逻辑
– 将大事务拆分为小事务
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
– 避免在事务中执行耗时操作
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
# 不要在事务中执行耗时操作
# SQL> BEGIN TRANSACTION;
# SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# SQL> — 执行耗时操作，如网络请求、文件IO等
# SQL> COMMIT;
# 2. 优化SQL语句
– 使用批量操作减少事务数量
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
– 避免在事务中执行不必要的查询
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
# 不要在事务中执行不必要的查询
# SQL> BEGIN TRANSACTION;
# SQL> SELECT * FROM fgedu_user WHERE user_id = 1;
# SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# SQL> COMMIT;
# 3. 优化锁的使用
– 使用合适的锁粒度
SQL> BEGIN TRANSACTION;
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
– 避免长时间持有锁
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
– 将大事务拆分为小事务
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
– 避免在事务中执行耗时操作
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
# 不要在事务中执行耗时操作
# SQL> BEGIN TRANSACTION;
# SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# SQL> — 执行耗时操作，如网络请求、文件IO等
# SQL> COMMIT;
# 2. 优化SQL语句
– 使用批量操作减少事务数量
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email) VALUES
(1, ‘fgedu_user1’, ‘fgedu_user1@fgedu.net.cn’),
(2, ‘fgedu_user2’, ‘fgedu_user2@fgedu.net.cn’),
(3, ‘user3’, ‘user3@fgedu.net.cn’);
– 避免在事务中执行不必要的查询
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
# 不要在事务中执行不必要的查询
# SQL> BEGIN TRANSACTION;
# SQL> SELECT * FROM fgedu_user WHERE user_id = 1;
# SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# SQL> COMMIT;
# 3. 优化锁的使用
– 使用合适的锁粒度
SQL> BEGIN TRANSACTION;
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
– 避免长时间持有锁
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
3.1.2 优化隔离级别
# 1. 选择合适的隔离级别
– 读已提交（Read Committed）
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RC’ BOTH;
– 可重复读（Repeatable Read）
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RR’ BOTH;
# 2. 根据业务需求调整隔离级别
– 对于大多数OLTP场景，使用读已提交
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RC’ BOTH;
– 对于需要保证数据一致性的场景，使用可重复读
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RR’ BOTH;
– 对于对数据一致性要求极高的场景，使用串行化
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘SERIALIZABLE’ BOTH;
# 3. 实际示例
– 电商订单处理场景
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RC’ BOTH;
SQL> BEGIN TRANSACTION;
SQL> INSERT INTO fgedu_order (order_id, user_id, order_amount) VALUES (1, 1, 100);
SQL> UPDATE fgedu_product SET stock = stock – 1 WHERE product_id = 1;
SQL> COMMIT;
– 银行转账场景
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RR’ BOTH;
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_account SET balance = balance – 100 WHERE account_id = 1;
SQL> UPDATE fgedu_account SET balance = balance + 100 WHERE account_id = 2;
SQL> COMMIT;
– 读已提交（Read Committed）
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RC’ BOTH;
– 可重复读（Repeatable Read）
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RR’ BOTH;
# 2. 根据业务需求调整隔离级别
– 对于大多数OLTP场景，使用读已提交
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RC’ BOTH;
– 对于需要保证数据一致性的场景，使用可重复读
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RR’ BOTH;
– 对于对数据一致性要求极高的场景，使用串行化
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘SERIALIZABLE’ BOTH;
# 3. 实际示例
– 电商订单处理场景
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RC’ BOTH;
SQL> BEGIN TRANSACTION;
SQL> INSERT INTO fgedu_order (order_id, user_id, order_amount) VALUES (1, 1, 100);
SQL> UPDATE fgedu_product SET stock = stock – 1 WHERE product_id = 1;
SQL> COMMIT;
– 银行转账场景
SQL> ALTER SYSTEM SET ‘ISOLATION_LEVEL’ = ‘RR’ BOTH;
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_account SET balance = balance – 100 WHERE account_id = 1;
SQL> UPDATE fgedu_account SET balance = balance + 100 WHERE account_id = 2;
SQL> COMMIT;
3.2 锁优化
3.2.1 减少锁冲突
# 1. 优化SQL语句
– 使用索引减少锁的范围
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
– 避免全表扫描
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# 不要使用全表扫描
# SQL> UPDATE fgedu_user SET user_status = 1;
# 2. 优化事务逻辑
– 按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_order SET order_status = 1 WHERE order_id = 1;
SQL> COMMIT;
– 避免长时间持有锁
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
# 3. 使用乐观并发控制
– 使用版本号控制并发
SQL> CREATE TABLE fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
user_email VARCHAR(100),
user_status INT,
version INT DEFAULT 0
);
SQL> UPDATE fgedu_user SET user_status = 1, version = version + 1 WHERE user_id = 1 AND version = 0;
– 使用索引减少锁的范围
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
– 避免全表扫描
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
# 不要使用全表扫描
# SQL> UPDATE fgedu_user SET user_status = 1;
# 2. 优化事务逻辑
– 按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_order SET order_status = 1 WHERE order_id = 1;
SQL> COMMIT;
– 避免长时间持有锁
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> COMMIT;
# 3. 使用乐观并发控制
– 使用版本号控制并发
SQL> CREATE TABLE fgedu_user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
user_email VARCHAR(100),
user_status INT,
version INT DEFAULT 0
);
SQL> UPDATE fgedu_user SET user_status = 1, version = version + 1 WHERE user_id = 1 AND version = 0;
3.2.2 优化锁等待
# 1. 设置锁等待超时
– 查看锁等待超时配置
SQL> SELECT * FROM v$parameter WHERE name = ‘LOCK_WAIT_TIMEOUT’;
– 设置锁等待超时（单位：秒）
SQL> ALTER SYSTEM SET ‘LOCK_WAIT_TIMEOUT’ = 30 BOTH;
# 2. 使用NOWAIT选项
– 使用NOWAIT选项避免等待
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE NOWAIT;
– 使用NOWAIT选项更新数据
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1 NOWAIT;
# 3. 使用SKIP LOCKED选项
– 使用SKIP LOCKED选项跳过被锁定的行
SQL> SELECT * FROM fgedu_user WHERE user_status = 1 FOR UPDATE SKIP LOCKED;
– 使用SKIP LOCKED选项更新数据
SQL> UPDATE fgedu_user SET user_status = 2 WHERE user_status = 1 SKIP LOCKED;
– 查看锁等待超时配置
SQL> SELECT * FROM v$parameter WHERE name = ‘LOCK_WAIT_TIMEOUT’;
– 设置锁等待超时（单位：秒）
SQL> ALTER SYSTEM SET ‘LOCK_WAIT_TIMEOUT’ = 30 BOTH;
# 2. 使用NOWAIT选项
– 使用NOWAIT选项避免等待
SQL> SELECT * FROM fgedu_user WHERE user_id = 1 FOR UPDATE NOWAIT;
– 使用NOWAIT选项更新数据
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1 NOWAIT;
# 3. 使用SKIP LOCKED选项
– 使用SKIP LOCKED选项跳过被锁定的行
SQL> SELECT * FROM fgedu_user WHERE user_status = 1 FOR UPDATE SKIP LOCKED;
– 使用SKIP LOCKED选项更新数据
SQL> UPDATE fgedu_user SET user_status = 2 WHERE user_status = 1 SKIP LOCKED;
3.3 监控工具
3.3.1 事务监控工具
# 1. 使用v
$transactions视图
– 查看当前活动事务
SQL> SELECT * FROM v$transactions;
– 查看长事务
SQL> SELECT * FROM v$transactions WHERE elapsed_time > 3600;
# 2. 使用v
$session视图
– 查看当前会话的事务信息
SQL> SELECT * FROM v$session WHERE status = ‘ACTIVE’;
– 查看持有锁的会话
SQL> SELECT * FROM v$session WHERE session_id IN (SELECT session_id FROM v$lock);
# 3. 使用v
$transaction_stats视图
– 查看事务统计信息
SQL> SELECT * FROM v$transaction_stats;
– 查看事务等待统计信息
SQL> SELECT * FROM v$transaction_wait_stats;
$transactions视图
– 查看当前活动事务
SQL> SELECT * FROM v$transactions;
– 查看长事务
SQL> SELECT * FROM v$transactions WHERE elapsed_time > 3600;
# 2. 使用v
$session视图
– 查看当前会话的事务信息
SQL> SELECT * FROM v$session WHERE status = ‘ACTIVE’;
– 查看持有锁的会话
SQL> SELECT * FROM v$session WHERE session_id IN (SELECT session_id FROM v$lock);
# 3. 使用v
$transaction_stats视图
– 查看事务统计信息
SQL> SELECT * FROM v$transaction_stats;
– 查看事务等待统计信息
SQL> SELECT * FROM v$transaction_wait_stats;
3.3.2 锁监控工具
# 1. 使用v
$lock视图
– 查看当前所有锁
SQL> SELECT * FROM v$lock;
– 查看锁的详细信息
SQL> SELECT l.*, s.username, s.program FROM v$lock l, v$session s WHERE l.session_id = s.session_id;
# 2. 使用v
$lockwait视图
– 查看当前锁等待
SQL> SELECT * FROM v$lockwait;
– 查看锁等待的详细信息
SQL> SELECT lw.*, s.username, s.program FROM v$lockwait lw, v$session s WHERE lw.session_id = s.session_id;
# 3. 使用v
$lock_stats视图
– 查看锁统计信息
SQL> SELECT * FROM v$lock_stats;
– 查看锁等待统计信息
SQL> SELECT * FROM v$lockwait_stats;
$lock视图
– 查看当前所有锁
SQL> SELECT * FROM v$lock;
– 查看锁的详细信息
SQL> SELECT l.*, s.username, s.program FROM v$lock l, v$session s WHERE l.session_id = s.session_id;
# 2. 使用v
$lockwait视图
– 查看当前锁等待
SQL> SELECT * FROM v$lockwait;
– 查看锁等待的详细信息
SQL> SELECT lw.*, s.username, s.program FROM v$lockwait lw, v$session s WHERE lw.session_id = s.session_id;
# 3. 使用v
$lock_stats视图
– 查看锁统计信息
SQL> SELECT * FROM v$lock_stats;
– 查看锁等待统计信息
SQL> SELECT * FROM v$lockwait_stats;
风哥提示：事务和锁的监控是数据库管理的重要工作，使用监控工具可以及时发现和解决问题。建立完善的监控体系，是保障数据库稳定运行的关键。
Part04-生产案例与实战讲解
4.1 长事务优化案例
4.1.1 案例描述
某企业DM数据库出现长事务问题，经分析发现某条事务执行时间过长，导致锁等待时间过长，需要优化该事务。
4.1.2 分析步骤
# 1. 问题分析
– 查看长事务
SQL> SELECT * FROM v$transactions WHERE elapsed_time > 3600;
# 输出结果
# TRX_ID SESSION_ID START_TIME ELAPSED_TIME STATUS
# ——— ———– ——————- ————- ——-
# 123456789 100 2024-01-01 09:00:00 7200 ACTIVE
– 查看事务的SQL语句
SQL> SELECT s.sql_text FROM v$session s, v$transactions t WHERE s.session_id = t.session_id AND t.trx_id = 123456789;
# 输出结果
# SQL_TEXT
# —————————————-
# UPDATE fgedu_user SET user_status = 1;
– 查看锁等待情况
SQL> SELECT * FROM v$lockwait;
# 输出结果
# WAITER_SESSION_ID HOLDER_SESSION_ID WAIT_TIME LOCK_TYPE
# —————— —————— ——— ———-
# 101 100 3600 ROW_LOCK
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 优化SQL语句，使用WHERE条件
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, …, 10000);
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，分批更新
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, …, 1000);
SQL> COMMIT;
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1001, 1002, …, 2000);
SQL> COMMIT;
…
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (9001, 9002, …, 10000);
SQL> COMMIT;
# 4. 效果验证
– 查看事务执行时间
SQL> SELECT elapsed_time FROM v$transactions WHERE trx_id = 123456789;
# 输出结果
# ELAPSED_TIME
# ————-
# 10
– 查看锁等待情况
SQL> SELECT * FROM v$lockwait;
# 输出结果
# WAITER_SESSION_ID HOLDER_SESSION_ID WAIT_TIME LOCK_TYPE
# —————— —————— ——— ———-
# (无结果)
# 5. 实施结果
– 事务执行时间从7200秒降低到10秒
– 锁等待时间从3600秒降低到0秒
– 事务性能提升99.9%
– 查看长事务
SQL> SELECT * FROM v$transactions WHERE elapsed_time > 3600;
# 输出结果
# TRX_ID SESSION_ID START_TIME ELAPSED_TIME STATUS
# ——— ———– ——————- ————- ——-
# 123456789 100 2024-01-01 09:00:00 7200 ACTIVE
– 查看事务的SQL语句
SQL> SELECT s.sql_text FROM v$session s, v$transactions t WHERE s.session_id = t.session_id AND t.trx_id = 123456789;
# 输出结果
# SQL_TEXT
# —————————————-
# UPDATE fgedu_user SET user_status = 1;
– 查看锁等待情况
SQL> SELECT * FROM v$lockwait;
# 输出结果
# WAITER_SESSION_ID HOLDER_SESSION_ID WAIT_TIME LOCK_TYPE
# —————— —————— ——— ———-
# 101 100 3600 ROW_LOCK
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 优化SQL语句，使用WHERE条件
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, …, 10000);
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_status ON fgedu_user(user_status);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化SQL语句，分批更新
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1, 2, 3, …, 1000);
SQL> COMMIT;
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (1001, 1002, …, 2000);
SQL> COMMIT;
…
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id IN (9001, 9002, …, 10000);
SQL> COMMIT;
# 4. 效果验证
– 查看事务执行时间
SQL> SELECT elapsed_time FROM v$transactions WHERE trx_id = 123456789;
# 输出结果
# ELAPSED_TIME
# ————-
# 10
– 查看锁等待情况
SQL> SELECT * FROM v$lockwait;
# 输出结果
# WAITER_SESSION_ID HOLDER_SESSION_ID WAIT_TIME LOCK_TYPE
# —————— —————— ——— ———-
# (无结果)
# 5. 实施结果
– 事务执行时间从7200秒降低到10秒
– 锁等待时间从3600秒降低到0秒
– 事务性能提升99.9%
4.2 锁冲突解决案例
4.2.1 案例描述
某企业DM数据库出现锁冲突问题，经分析发现多个事务之间存在锁冲突，导致事务等待时间过长，需要解决锁冲突问题。
4.2.2 分析步骤
# 1. 问题分析
– 查看锁冲突情况
SQL> SELECT * FROM v$lock_conflict;
# 输出结果
# HOLDER_SESSION_ID WAITER_SESSION_ID CONFLICT_TIME LOCK_TYPE
# —————— —————— ————– ———-
# 100 101 3600 ROW_LOCK
# 100 102 1800 ROW_LOCK
# 101 103 900 ROW_LOCK
– 查看锁冲突的详细信息
SQL> SELECT lc.*, s1.username AS holder_username, s2.username AS waiter_username
FROM v$lock_conflict lc, v$session s1, v$session s2
WHERE lc.holder_session_id = s1.session_id AND lc.waiter_session_id = s2.session_id;
# 输出结果
# HOLDER_SESSION_ID WAITER_SESSION_ID CONFLICT_TIME LOCK_TYPE HOLDER_USERNAME WAITER_USERNAME
# —————— —————— ————– ———- —————- —————-
# 100 101 3600 ROW_LOCK fgedu fgedu
# 100 102 1800 ROW_LOCK fgedu fgedu
# 101 103 900 ROW_LOCK fgedu fgedu
– 查看持有锁的SQL语句
SQL> SELECT s.sql_text FROM v$session s, v$lock l WHERE s.session_id = l.session_id AND l.session_id = 100;
# 输出结果
# SQL_TEXT
# —————————————-
# UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
– 查看等待锁的SQL语句
SQL> SELECT s.sql_text FROM v$session s, v$lockwait lw WHERE s.session_id = lw.session_id AND lw.session_id = 101;
# 输出结果
# SQL_TEXT
# —————————————-
# UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 4. 效果验证
– 查看锁冲突情况
SQL> SELECT * FROM v$lock_conflict;
# 输出结果
# HOLDER_SESSION_ID WAITER_SESSION_ID CONFLICT_TIME LOCK_TYPE
# —————— —————— ————– ———-
# (无结果)
– 查看事务执行时间
SQL> SELECT elapsed_time FROM v$transactions WHERE session_id = 100;
# 输出结果
# ELAPSED_TIME
# ————-
# 5
# 5. 实施结果
– 锁冲突时间从3600秒降低到0秒
– 事务执行时间从3600秒降低到5秒
– 事务性能提升99.9%
– 查看锁冲突情况
SQL> SELECT * FROM v$lock_conflict;
# 输出结果
# HOLDER_SESSION_ID WAITER_SESSION_ID CONFLICT_TIME LOCK_TYPE
# —————— —————— ————– ———-
# 100 101 3600 ROW_LOCK
# 100 102 1800 ROW_LOCK
# 101 103 900 ROW_LOCK
– 查看锁冲突的详细信息
SQL> SELECT lc.*, s1.username AS holder_username, s2.username AS waiter_username
FROM v$lock_conflict lc, v$session s1, v$session s2
WHERE lc.holder_session_id = s1.session_id AND lc.waiter_session_id = s2.session_id;
# 输出结果
# HOLDER_SESSION_ID WAITER_SESSION_ID CONFLICT_TIME LOCK_TYPE HOLDER_USERNAME WAITER_USERNAME
# —————— —————— ————– ———- —————- —————-
# 100 101 3600 ROW_LOCK fgedu fgedu
# 100 102 1800 ROW_LOCK fgedu fgedu
# 101 103 900 ROW_LOCK fgedu fgedu
– 查看持有锁的SQL语句
SQL> SELECT s.sql_text FROM v$session s, v$lock l WHERE s.session_id = l.session_id AND l.session_id = 100;
# 输出结果
# SQL_TEXT
# —————————————-
# UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
– 查看等待锁的SQL语句
SQL> SELECT s.sql_text FROM v$session s, v$lockwait lw WHERE s.session_id = lw.session_id AND lw.session_id = 101;
# 输出结果
# SQL_TEXT
# —————————————-
# UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 4. 效果验证
– 查看锁冲突情况
SQL> SELECT * FROM v$lock_conflict;
# 输出结果
# HOLDER_SESSION_ID WAITER_SESSION_ID CONFLICT_TIME LOCK_TYPE
# —————— —————— ————– ———-
# (无结果)
– 查看事务执行时间
SQL> SELECT elapsed_time FROM v$transactions WHERE session_id = 100;
# 输出结果
# ELAPSED_TIME
# ————-
# 5
# 5. 实施结果
– 锁冲突时间从3600秒降低到0秒
– 事务执行时间从3600秒降低到5秒
– 事务性能提升99.9%
4.3 死锁处理案例
4.3.1 案例描述
某企业DM数据库出现死锁问题，经分析发现两个事务相互持有对方需要的锁，导致死锁，需要解决死锁问题。
4.3.2 分析步骤
# 1. 问题分析
– 查看死锁信息
SQL> SELECT * FROM v$deadlock;
# 输出结果
# DEADLOCK_ID TRX_ID1 SESSION_ID1 SQL_TEXT1 TRX_ID2 SESSION_ID2 SQL_TEXT2 VICTIM
# ———— ——– ———— —————————————— ——– ———— —————————————— ——
# 1 123456789 100 UPDATE fgedu_user SET user_status = 1 123456790 101 UPDATE fgedu_user SET user_status = 1 100
# WHERE user_id = 1 WHERE user_id = 2
– 查看死锁的详细信息
SQL> SELECT d.*, s1.username AS username1, s2.username AS username2
FROM v$deadlock d, v$session s1, v$session s2
WHERE d.session_id1 = s1.session_id AND d.session_id2 = s2.session_id;
# 输出结果
# DEADLOCK_ID TRX_ID1 SESSION_ID1 SQL_TEXT1 TRX_ID2 SESSION_ID2 SQL_TEXT2 VICTIM USERNAME1 USERNAME2
# ———— ——– ———— —————————————— ——– ———— —————————————— —— ———- ———-
# 1 123456789 100 UPDATE fgedu_user SET user_status = 1 123456790 101 UPDATE fgedu_user SET user_status = 1 100 fgedu fgedu
# WHERE user_id = 1 WHERE user_id = 2
– 查看死锁日志
$ tail -f /dm/fgdata/fgedudb/log/deadlock.log
# 死锁日志内容
# Deadlock detected at 2024-01-01 10:00:00
# Transaction 1: Session ID 100, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1
# Transaction 2: Session ID 101, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2
# Victim: Session ID 100
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 4. 效果验证
– 查看死锁信息
SQL> SELECT * FROM v$deadlock;
# 输出结果
# DEADLOCK_ID TRX_ID1 SESSION_ID1 SQL_TEXT1 TRX_ID2 SESSION_ID2 SQL_TEXT2 VICTIM
# ———— ——– ———— ———- ——– ———— ———- ——
# (无结果)
– 查看事务执行时间
SQL> SELECT elapsed_time FROM v$transactions WHERE session_id = 100;
# 输出结果
# ELAPSED_TIME
# ————-
# 5
# 5. 实施结果
– 死锁问题得到解决
– 事务执行时间从超时降低到5秒
– 事务性能提升99.9%
– 查看死锁信息
SQL> SELECT * FROM v$deadlock;
# 输出结果
# DEADLOCK_ID TRX_ID1 SESSION_ID1 SQL_TEXT1 TRX_ID2 SESSION_ID2 SQL_TEXT2 VICTIM
# ———— ——– ———— —————————————— ——– ———— —————————————— ——
# 1 123456789 100 UPDATE fgedu_user SET user_status = 1 123456790 101 UPDATE fgedu_user SET user_status = 1 100
# WHERE user_id = 1 WHERE user_id = 2
– 查看死锁的详细信息
SQL> SELECT d.*, s1.username AS username1, s2.username AS username2
FROM v$deadlock d, v$session s1, v$session s2
WHERE d.session_id1 = s1.session_id AND d.session_id2 = s2.session_id;
# 输出结果
# DEADLOCK_ID TRX_ID1 SESSION_ID1 SQL_TEXT1 TRX_ID2 SESSION_ID2 SQL_TEXT2 VICTIM USERNAME1 USERNAME2
# ———— ——– ———— —————————————— ——– ———— —————————————— —— ———- ———-
# 1 123456789 100 UPDATE fgedu_user SET user_status = 1 123456790 101 UPDATE fgedu_user SET user_status = 1 100 fgedu fgedu
# WHERE user_id = 1 WHERE user_id = 2
– 查看死锁日志
$ tail -f /dm/fgdata/fgedudb/log/deadlock.log
# 死锁日志内容
# Deadlock detected at 2024-01-01 10:00:00
# Transaction 1: Session ID 100, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1
# Transaction 2: Session ID 101, User fgedu, SQL: UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2
# Victim: Session ID 100
# 2. 优化方案
– 查看表结构
SQL> DESC fgedu_user;
– 查看索引
SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = ‘FGEDU_USER’;
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 3. 实施步骤
– 创建索引
SQL> CREATE INDEX idx_fgedu_user_id ON fgedu_user(user_id);
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS(‘fgedu’, ‘fgedu_user’);
– 优化事务逻辑，按照相同的顺序访问资源
SQL> BEGIN TRANSACTION;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 1;
SQL> UPDATE fgedu_user SET user_status = 1 WHERE user_id = 2;
SQL> COMMIT;
# 4. 效果验证
– 查看死锁信息
SQL> SELECT * FROM v$deadlock;
# 输出结果
# DEADLOCK_ID TRX_ID1 SESSION_ID1 SQL_TEXT1 TRX_ID2 SESSION_ID2 SQL_TEXT2 VICTIM
# ———— ——– ———— ———- ——– ———— ———- ——
# (无结果)
– 查看事务执行时间
SQL> SELECT elapsed_time FROM v$transactions WHERE session_id = 100;
# 输出结果
# ELAPSED_TIME
# ————-
# 5
# 5. 实施结果
– 死锁问题得到解决
– 事务执行时间从超时降低到5秒
– 事务性能提升99.9%
生产环境建议：根据业务需求和并发要求，优化事务和锁的使用，避免长事务、锁冲突和死锁的发生。定期监控事务和锁的状态，及时发现和解决问题。建立完善的监控体系，保障数据库稳定运行。
Part05-风哥经验总结与分享
5.1 事务管理最佳实践
DM数据库事务管理最佳实践：
- 保持事务简短：尽量保持事务简短，减少事务持有时间
- 使用合适的隔离级别：根据业务需求选择合适的隔离级别
- 优化SQL语句：优化SQL语句，减少锁的范围和持有时间
- 使用批量操作：使用批量操作减少事务数量
- 避免长时间持有锁：避免长时间持有锁，及时提交事务
- 按照相同的顺序访问资源：按照相同的顺序访问资源，避免死锁
- 使用索引：使用索引减少锁的范围
- 定期监控：定期监控事务和锁的状态，及时发现和解决问题
- 使用监控工具：使用监控工具，及时发现和解决问题
- 建立监控体系：建立完善的监控体系，保障数据库稳定运行
5.2 常见问题与解决方案
# 1. 长事务问题
– 症状：事务执行时间过长
– 原因：事务逻辑复杂、SQL语句不合理、缺少索引
– 解决方案：优化事务逻辑、优化SQL语句、创建索引
# 2. 锁冲突问题
– 症状：事务等待时间过长
– 原因：锁粒度过大、访问顺序不一致、缺少索引
– 解决方案：优化SQL语句、按照相同的顺序访问资源、创建索引
# 3. 死锁问题
– 症状：事务相互等待，无法继续执行
– 原因：访问顺序不一致、持有锁时间过长
– 解决方案：按照相同的顺序访问资源、减少锁持有时间
# 4. 锁等待超时问题
– 症状：事务等待锁超时
– 原因：锁等待时间过长、锁冲突严重
– 解决方案：优化SQL语句、减少锁持有时间、设置合适的锁等待超时
# 5. 隔离级别问题
– 症状：数据不一致、并发性能差
– 原因：隔离级别设置不合理
– 解决方案：根据业务需求选择合适的隔离级别
– 症状：事务执行时间过长
– 原因：事务逻辑复杂、SQL语句不合理、缺少索引
– 解决方案：优化事务逻辑、优化SQL语句、创建索引
# 2. 锁冲突问题
– 症状：事务等待时间过长
– 原因：锁粒度过大、访问顺序不一致、缺少索引
– 解决方案：优化SQL语句、按照相同的顺序访问资源、创建索引
# 3. 死锁问题
– 症状：事务相互等待，无法继续执行
– 原因：访问顺序不一致、持有锁时间过长
– 解决方案：按照相同的顺序访问资源、减少锁持有时间
# 4. 锁等待超时问题
– 症状：事务等待锁超时
– 原因：锁等待时间过长、锁冲突严重
– 解决方案：优化SQL语句、减少锁持有时间、设置合适的锁等待超时
# 5. 隔离级别问题
– 症状：数据不一致、并发性能差
– 原因：隔离级别设置不合理
– 解决方案：根据业务需求选择合适的隔离级别
5.3 事务管理检查清单
DM数据库事务管理检查清单：
- 事务长度检查：事务是否简短，持有时间是否合理
- 隔离级别检查：隔离级别是否合理，是否满足业务需求
- SQL语句检查：SQL语句是否优化，是否使用索引
- 锁粒度检查：锁粒度是否合适，是否减少锁的范围
- 访问顺序检查：是否按照相同的顺序访问资源
- 批量操作检查：是否使用批量操作减少事务数量
- 锁等待检查：锁等待时间是否合理，是否存在锁冲突
- 死锁检查：是否存在死锁，死锁处理是否及时
- 监控检查：是否定期监控事务和锁的状态
- 性能指标检查：事务执行时间、锁等待时间等指标是否合理
持续改进：事务管理和锁机制的优化是一个持续的过程，需要根据业务需求和系统运行情况，不断调整和优化，确保系统的高性能和稳定性。建立完善的监控体系，是保障数据库稳定运行的关键。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
