# 查看当前锁定的对象
SQL> SELECT * FROM v$lock;

# 查看等待锁的会话
SQL> SELECT * FROM v$session_wait WHERE event LIKE ‘%lock%’;

# 查看锁冲突
SQL> SELECT * FROM v$lock_conflicts;

# 查看事务状态
SQL> SELECT * FROM v$transaction;

# 查看会话信息
SQL> SELECT sid, serial#, username, program, status FROM v$session;

# 查看长时间运行的事务
SQL> SELECT * FROM v$longops;

# 锁监控脚本

# 输出文件
output_file=”/tmp/lock_monitor_$(date +%Y%m%d_%H%M%S).log”

echo “开始监控锁状态” > ${output_file}
echo “监控时间: $(date)” >> ${output_file}
echo “” >> ${output_file}

# 查看当前锁定的对象
echo “===== 当前锁定的对象 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM vlock;
EXIT;
EOF
echo “” >> ${output_file}

# 查看等待锁的会话
echo “===== 等待锁的会话 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM vsession_wait WHERE event LIKE ‘%lock%’;
EXIT;
EOF
echo “” >> ${output_file}

# 查看锁冲突
echo “===== 锁冲突 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM vlock_conflicts;
EXIT;
EOF
echo “” >> ${output_file}

# 查看长时间运行的事务
echo “===== 长时间运行的事务 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT * FROM vlongops;
EXIT;
EOF
echo “” >> ${output_file}

echo “监控完成，结果保存至：${output_file}” >> ${output_file}
echo “监控完成，结果保存至：${output_file}”

# 1. 检查锁状态
SQL> SELECT * FROM v$lock;

# 2. 检查等待锁的会话
SQL> SELECT * FROM v$session_wait WHERE event LIKE ‘%lock%’;

# 3. 检查锁冲突
SQL> SELECT * FROM v$lock_conflicts;

# 4. 查看事务状态
SQL> SELECT * FROM v$transaction;

# 5. 查看SQL语句
SQL> SELECT sql_id, sql_text FROM v$sql WHERE sql_id IN (SELECT sql_id FROM v$session WHERE sid IN (SELECT sid FROM v$session_wait WHERE event LIKE ‘%lock%’));

# 6. 查看会话信息
SQL> SELECT sid, serial#, username, program, status FROM v$session;

# 7. 分析锁等待链
SQL> SELECT * FROM v$lock_waits;

# 8. 解决锁问题
# 终止导致锁冲突的会话
SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

# 原因1：长事务导致锁持有时间过长
# 解决方法：缩短事务长度，拆分长事务

# 原因2：SQL语句未使用索引，导致全表扫描
# 解决方法：优化SQL语句，添加索引

# 原因3：事务隔离级别过高
# 解决方法：降低事务隔离级别，使用READ COMMITTED

# 原因4：死锁
# 解决方法：检测并终止死锁，优化事务顺序

# 原因5：锁升级
# 解决方法：优化SQL语句，减少锁定的行数

# 原因6：并发度过高
# 解决方法：控制并发度，使用连接池

# 修改事务隔离级别
SQL> ALTER SYSTEM SET transaction_isolation = 1 SCOPE=spfile;

# 修改锁超时时间
SQL> ALTER SYSTEM SET lock_timeout = 30000 SCOPE=spfile;

# 修改死锁检测间隔
SQL> ALTER SYSTEM SET deadlock_timeout = 1000 SCOPE=spfile;

# 修改最大锁数量
SQL> ALTER SYSTEM SET max_locks_per_transaction = 128 SCOPE=spfile;

# 修改并发事务数
SQL> ALTER SYSTEM SET max_connections = 200 SCOPE=spfile;

# 重启数据库使参数生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

# 1. 使用行级锁
# 避免使用表级锁，尽量使用行级锁

# 2. 优化SQL语句
# 使用索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id);

# 避免全表扫描
SQL> SELECT * FROM fgedu.orders WHERE customer_id = 12345; — 使用索引

# 3. 合理使用锁提示
# 使用NOWAIT避免锁等待
SQL> SELECT * FROM fgedu.orders WHERE customer_id = 12345 FOR UPDATE NOWAIT;

# 使用SKIP LOCKED跳过已锁定的行
SQL> SELECT * FROM fgedu.orders WHERE customer_id = 12345 FOR UPDATE SKIP LOCKED;

# 4. 分区表策略
# 创建分区表
SQL> CREATE TABLE fgedu.orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_date DATE
) PARTITION BY RANGE (order_date) (
PARTITION p202601 VALUES LESS THAN (TO_DATE(‘2026-02-01’, ‘YYYY-MM-DD’)),
PARTITION p202602 VALUES LESS THAN (TO_DATE(‘2026-03-01’, ‘YYYY-MM-DD’)),
PARTITION p202603 VALUES LESS THAN (TO_DATE(‘2026-04-01’, ‘YYYY-MM-DD’))
);