配景先容
在 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 的执行环境
- 强制竣事 session1 后，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 语句
- 减少锁定范围：只管利用行级锁而不是表级锁。例如，利用 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 语句。同时，通过实行最佳实践，可以显著减少锁表标题标发生，进步体系的并发性能和稳定性。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
