Oracle 锁表的解决方法及避免锁表问题的最佳实践

背景介绍

在 Oracle 数据库中，锁表或锁超时相信大家都不陌生，是一个常见的问题，尤其是在执行 DML（数据操作语言）语句时。当一个会话对表或行进行锁定但未提交事务时，其他会话可能会因为等待锁资源而出现超时。这种情况不仅会影响数据库性能，还可能导致应用程序异常(java.sql.SQLException: Lock wait timeout exceeded)。

本文将详细介绍如何解决锁表问题以及如何查找引起锁表的 SQL 语句，并提供避免锁表问题的最佳实践。

锁表的原因

1. 独占式封锁机制：Oracle 使用独占式封锁机制来确保数据的一致性。当一个会话对数据进行修改时，会对其加锁，直到事务提交或回滚。
2. 长时间运行的 SQL 语句：某些 SQL 语句可能由于性能问题或其他原因而长时间运行，导致锁资源一直被占用。
3. 高并发场景：在高并发环境下，多个会话同时访问相同的数据，可能会导致锁竞争，从而引发死锁。

解决锁表的方法

临时解决方案

1. 找出锁资源竞争的会话

   SELECT L.SESSION_ID, S.SERIAL#, L.LOCKED_MODE AS "锁模式", L.ORACLE_USERNAME AS "所有者", L.OS_USER_NAME AS "登录系统用户名", S.MACHINE AS "系统名", S.TERMINAL AS "终端用户名", O.OBJECT_NAME AS "被锁表对象名", S.LOGON_TIME AS "登录数据库时间"FROM V$LOCKED_OBJECT LINNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_IDINNER JOIN V$SESSION S ON S.SID = L.SESSION_ID;
   

2. sql强制结束会话

   ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';
   

示例

假设 session1 修改了某条数据但未提交事务，session2 查询未提交事务的那条记录时会被阻塞。

1. 查询未提交事务的会话信息

   SELECT L.SESSION_ID, S.SERIAL#, L.LOCKED_MODE AS "锁模式", L.ORACLE_USERNAME AS "所有者", L.OS_USER_NAME AS "登录系统用户名", S.MACHINE AS "系统名", S.TERMINAL AS "终端用户名", O.OBJECT_NAME AS "被锁表对象名", S.LOGON_TIME AS "登录数据库时间"FROM V$LOCKED_OBJECT LINNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_IDINNER JOIN V$SESSION S ON S.SID = L.SESSION_ID;SESSION_ID	SERIAL#	锁模式	所有者	登录系统用户名	系统名	终端用户名	被锁表对象名	登录数据库时间
   ----------  ------- ----- ------ ------------- ----- --------- --------- ------------
   29	84	3 IN	test	WORKGROUP\LA...	LAPTOP-9FDC2903	LIN_USER	2023/2/26 11:08:08
   

2. 强制结束 session1

   ALTER SYSTEM KILL SESSION '29, 84';
   

3. 验证 session2 的执行情况

   • 强制结束 session1 后，session2 的等待会立即终止并执行。

查找被锁对象

1. 查询被锁对象数目

   SELECT COUNT(1) FROM V$LOCKED_OBJECT;
   

2. 查询被锁对象

   SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODEFROM V$LOCKED_OBJECT A, DBA_OBJECTS BWHERE B.OBJECT_ID = A.OBJECT_ID;
   

3. 查询被锁对象的连接

   SELECT T2.USERNAME, T2.SID, T2.SERIAL, T2.LOGON_TIMEFROM V$LOCKED_OBJECT T1, V$SESSION T2WHERE T1.SESSION_ID = T2.SIDORDER BY T2.LOGON_TIME;
   

4. 关闭被锁对象连接

   ALTER SYSTEM KILL SESSION '253, 9542';
   

查看当前系统中锁表情况

1. 查询所有被锁对象

   SELECT * FROM V$LOCKED_OBJECT;
   

2. 查询详细的锁表情况

   SELECT SESS.SID, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME, AO.OBJECT_NAME, LO.LOCKED_MODEFROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS, V$PROCESS PWHERE AO.OBJECT_ID = LO.OBJECT_IDAND LO.SESSION_ID = SESS.SID;
   

查找引起锁表的 SQL 语句

1. 查询引起锁表的 SQL 语句

   SELECT L.SESSION_ID SID, S.SERIAL#, L.LOCKED_MODE, L.ORACLE_USERNAME, S.USER#, L.OS_USER_NAME, S.MACHINE, S.TERMINAL, A.SQL_TEXT, A.ACTIONFROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT LWHERE L.SESSION_ID = S.SIDAND S.PREV_SQL_ADDR = A.ADDRESSORDER BY SID, S.SERIAL#;
   

2. 查看所有被阻塞的会话

   SET LINE 200;
   COL TERMINAL FORMAT A10;
   COL PROGRAM FORMAT A20;
   COL USERNAME FORMAT A10;
   COL MACHINE FORMAT A10;
   COL SQL_TEXT FORMAT A40;
   SELECT A.SID, A.SERIAL#, A.USERNAME, A.COMMAND, A.LOCKWAIT, A.STATUS, A.MACHINE, A.TERMINAL, A.PROGRAM, A.SECONDS_IN_WAIT, B.SQL_TEXTFROM V$SESSION A, V$SQL BWHERE B.SQL_ID = A.SQL_IDAND (A.BLOCKING_INSTANCE IS NOT NULL AND A.BLOCKING_SESSION IS NOT NULL);
   

3. 展示阻塞的树形结构

   WITH lk AS (SELECT BLOCKING_INSTANCE || '.' || BLOCKING_SESSION AS blocker, INST_ID || '.' || SID AS waiterFROM GV$SESSIONWHERE BLOCKING_INSTANCE IS NOT NULL AND BLOCKING_SESSION IS NOT NULL
   )
   SELECT LPAD('  ', 2 * (LEVEL - 1)) || WAITER LOCK_TREEFROM (SELECT * FROM lkUNION ALLSELECT DISTINCT 'root', BLOCKER FROM lkWHERE BLOCKER NOT IN (SELECT WAITER FROM lk))
   CONNECT BY PRIOR WAITER = BLOCKER
   START WITH BLOCKER = 'root';
   

4. 展示阻塞的树形结构，并输出阻塞语句、被阻塞语句，并给出杀会话语句

   WITH lk AS (SELECT A.BLOCKING_INSTANCE || '.' || A.BLOCKING_SESSION AS blocker,A.INST_ID || '.' || A.SID AS waiter,(SELECT B.SQL_TEXT || '  ALTER SYSTEM KILL SESSION ''' || C.SID || ', ' || C.SERIAL# || ''''FROM GV$SQLAREA B, GV$SESSION CWHERE A.BLOCKING_INSTANCE = C.INST_IDAND C.SID = A.BLOCKING_SESSIONAND (C.SQL_ID = B.SQL_ID OR C.PREV_SQL_ID = B.SQL_ID)) AS kill_block_sql,(SELECT B.SQL_TEXT || '  ALTER SYSTEM KILL SESSION ''' || A.SID || ', ' || A.SERIAL# || ''''FROM GV$SQLAREA BWHERE A.INST_ID = B.INST_IDAND A.SQL_ID = B.SQL_ID) AS kill_waiter_sqlFROM GV$SESSION AWHERE A.BLOCKING_INSTANCE IS NOT NULL AND A.BLOCKING_SESSION IS NOT NULL
   )
   SELECT LPAD('  ', 2 * (LEVEL - 1)) || WAITER || '  ' || KILL_WAITER_SQL LOCK_TREEFROM (SELECT BLOCKER, WAITER, KILL_WAITER_SQL FROM lkUNION ALLSELECT DISTINCT 'root', BLOCKER, KILL_BLOCK_SQL FROM lkWHERE BLOCKER NOT IN (SELECT WAITER FROM lk))
   CONNECT BY PRIOR WAITER = BLOCKER
   START WITH BLOCKER = 'root';
   

5. 直接显示阻塞关系

   COL BLOCK_MSG FOR A80
   SELECT C.TERMINAL || ' (''' || A.SID || ',' || C.SERIAL# || ''') is blocking ' || B.SID BLOCK_MSGFROM V$LOCK A, V$LOCK B, V$SESSION CWHERE A.ID1 = B.ID1AND A.ID2 = B.ID2AND A.BLOCK > 0AND A.SID <> B.SIDAND A.SID = C.SID;
   

避免锁表问题的最佳实践

1. 优化 SQL 语句

• 减少锁定范围：尽量使用行级锁而不是表级锁。例如，使用 SELECT ... FOR UPDATE 时，只锁定需要更新的行。
• 避免长时间运行的事务：确保事务尽可能短，尽快提交或回滚事务，减少锁的持有时间。
• 批量处理：对于大量数据的操作，考虑分批处理，以减少单个事务的持续时间和锁的持有时间。

2. 使用合适的隔离级别

• 调整隔离级别：根据应用需求选择合适的隔离级别。例如，使用 READ COMMITTED 而不是 SERIALIZABLE，以减少锁的竞争。
• 避免不必要的锁：在某些情况下，可以使用 NOLOCK 提示来避免读取操作时的锁，但这可能会导致脏读。

3. 优化索引

• 创建适当的索引：确保经常查询的列上有适当的索引，以减少全表扫描和锁的竞争。
• 维护索引：定期重建和重组索引，以保持其效率。

4. 使用分区表

• 分区表：对于大型表，可以使用分区技术来减少锁的竞争。分区表可以将数据分成多个部分，每个部分可以独立地进行操作，从而减少锁的影响。

5. 优化应用程序逻辑

• 减少并发冲突：设计应用程序逻辑时，尽量减少对同一数据的并发访问。例如，通过使用队列或其他机制来序列化对共享资源的访问。
• 使用乐观锁：对于一些非关键性操作，可以使用乐观锁（如版本号控制）来替代悲观锁，减少锁的竞争。

6. 监控和调优

• 监控锁情况：定期监控数据库中的锁情况，使用 V$LOCKED_OBJECT、V$SESSION 和 V$SQLAREA 等视图来识别潜在的锁问题。
• 设置超时：为会话设置合理的锁等待超时时间，防止某个会话长时间占用锁资源。可以通过 ALTER SYSTEM SET LOCK_TIMEOUT = <seconds> 来设置。

7. 使用数据库特性

• 闪回技术：利用 Oracle 的闪回技术（如 Flashback Query）来恢复数据，而不是依赖于复杂的事务回滚。
• 在线重定义：使用在线重定义（Online Redefinition）来修改表结构，而不影响现有事务。

8. 事务管理

• 最小化事务大小：尽量将大事务拆分为多个小事务，以减少锁的持有时间。
• 使用保存点：在长事务中使用保存点（SAVEPOINT），以便在发生错误时可以回滚到特定点，而不是整个事务。

9. 数据库配置

• 调整参数：根据实际情况调整数据库参数，如 UNDO_RETENTION、DB_FILE_MULTIBLOCK_READ_COUNT 等，以优化数据库性能。
• 使用并行处理：对于大规模数据操作，可以考虑使用并行处理来提高性能和减少锁的竞争。

10. 定期维护

• 定期分析和优化：定期分析数据库性能，找出瓶颈并进行优化。
• 清理无用数据：定期清理不再需要的数据，减少表的大小，从而减少锁的竞争。

总结

通过上述步骤，可以有效地解决 Oracle 数据库中的锁表问题，并找到引起锁表的 SQL 语句。同时，通过实施最佳实践，可以显著减少锁表问题的发生，提高系统的并发性能和稳定性。