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

发布者: 雪落无声 | 发布时间: 2025-6-18 09:28| 查看数: 41| 评论数: 0|帖子模式

背景介绍

在 Oracle 数据库中，锁表或锁超时相信大家都不陌生，是一个常见的问题，尤其是在执行 DML（数据操作语言）语句时。当一个会话对表或行进行锁定但未提交事务时，其他会话可能会因为等待锁资源而出现超时。这种情况不仅会影响数据库性能，还可能导致应用程序异常(java.sql.SQLException: Lock wait timeout exceeded)。
本文将详细介绍如何解决锁表问题以及如何查找引起锁表的 SQL 语句，并提供避免锁表问题的最佳实践。

锁表的原因

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

解决锁表的方法

临时解决方案

找出锁资源竞争的会话

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 L
INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
INNER JOIN V$SESSION S ON S.SID = L.SESSION_ID;

复制代码

sql强制结束会话

ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';

复制代码

示例

假设

session1

复制代码

修改了某条数据但未提交事务，

session2

复制代码

查询未提交事务的那条记录时会被阻塞。

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

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 L
INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
INNER 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

复制代码

强制结束
session1
复制代码

ALTER SYSTEM KILL SESSION '29, 84';

复制代码

验证
session2
复制代码
的执行情况

- 强制结束
  1. session1
  复制代码
  后，
  1. session2
  复制代码
  的等待会立即终止并执行。

查找被锁对象

查询被锁对象数目

SELECT COUNT(1) FROM V$LOCKED_OBJECT;

复制代码

查询被锁对象

SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE B.OBJECT_ID = A.OBJECT_ID;

复制代码

查询被锁对象的连接

SELECT T2.USERNAME, T2.SID, T2.SERIAL, T2.LOGON_TIME
FROM V$LOCKED_OBJECT T1, V$SESSION T2
WHERE T1.SESSION_ID = T2.SID
ORDER BY T2.LOGON_TIME;

复制代码

关闭被锁对象连接

ALTER SYSTEM KILL SESSION '253, 9542';

复制代码

查看当前系统中锁表情况

查询所有被锁对象

SELECT * FROM V$LOCKED_OBJECT;

复制代码

查询详细的锁表情况

SELECT SESS.SID, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME, AO.OBJECT_NAME, LO.LOCKED_MODE
FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS, V$PROCESS P
WHERE AO.OBJECT_ID = LO.OBJECT_ID
AND LO.SESSION_ID = SESS.SID;

复制代码

查找引起锁表的 SQL 语句

查询引起锁表的 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.ACTION
FROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L
WHERE L.SESSION_ID = S.SID
AND S.PREV_SQL_ADDR = A.ADDRESS
ORDER BY SID, S.SERIAL#;

复制代码

查看所有被阻塞的会话

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_TEXT
FROM V$SESSION A, V$SQL B
WHERE B.SQL_ID = A.SQL_ID
AND (A.BLOCKING_INSTANCE IS NOT NULL AND A.BLOCKING_SESSION IS NOT NULL);

复制代码

展示阻塞的树形结构

WITH lk AS (
SELECT BLOCKING_INSTANCE || '.' || BLOCKING_SESSION AS blocker, INST_ID || '.' || SID AS waiter
FROM GV$SESSION
WHERE BLOCKING_INSTANCE IS NOT NULL AND BLOCKING_SESSION IS NOT NULL
)
SELECT LPAD(' ', 2 * (LEVEL - 1)) || WAITER LOCK_TREE
FROM (
SELECT * FROM lk
UNION ALL
SELECT DISTINCT 'root', BLOCKER FROM lk
WHERE BLOCKER NOT IN (SELECT WAITER FROM lk)
)
CONNECT BY PRIOR WAITER = BLOCKER
START WITH BLOCKER = 'root';

复制代码

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

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 C
WHERE A.BLOCKING_INSTANCE = C.INST_ID
AND C.SID = A.BLOCKING_SESSION
AND (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 B
WHERE A.INST_ID = B.INST_ID
AND A.SQL_ID = B.SQL_ID) AS kill_waiter_sql
FROM GV$SESSION A
WHERE A.BLOCKING_INSTANCE IS NOT NULL AND A.BLOCKING_SESSION IS NOT NULL
)
SELECT LPAD(' ', 2 * (LEVEL - 1)) || WAITER || ' ' || KILL_WAITER_SQL LOCK_TREE
FROM (
SELECT BLOCKER, WAITER, KILL_WAITER_SQL FROM lk
UNION ALL
SELECT DISTINCT 'root', BLOCKER, KILL_BLOCK_SQL FROM lk
WHERE BLOCKER NOT IN (SELECT WAITER FROM lk)
)
CONNECT BY PRIOR WAITER = BLOCKER
START WITH BLOCKER = 'root';

复制代码

直接显示阻塞关系

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

复制代码

避免锁表问题的最佳实践

1. 优化 SQL 语句

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

2. 使用合适的隔离级别

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

3. 优化索引

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

4. 使用分区表

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

5. 优化应用程序逻辑

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

6. 监控和调优

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

7. 使用数据库特性

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

8. 事务管理

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

9. 数据库配置

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

10. 定期维护

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

总结

通过上述步骤，可以有效地解决 Oracle 数据库中的锁表问题，并找到引起锁表的 SQL 语句。同时，通过实施最佳实践，可以显著减少锁表问题的发生，提高系统的并发性能和稳定性。
以上就是Oracle锁表的解决方法及避免锁表问题的最佳实践的详细内容，更多关于Oracle锁表的解决及避免的资料请关注脚本之家其它相关文章！

来源：https://www.jb51.net/database/331544999.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

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

最新评论

新人须知

常见问题

维权申诉

关注微博

扫描微信

关于合作

通讯联系