网站被别人域名绑定,昆明软件开发公司做门户网站的,网站建设需要注意什么,网络培训的心得体会背景介绍
在 Oracle 数据库中#xff0c;锁表或锁超时相信大家都不陌生#xff0c;是一个常见的问题#xff0c;尤其是在执行 DML#xff08;数据操作语言#xff09;语句时。当一个会话对表或行进行锁定但未提交事务时#xff0c;其他会话可能会因为等待锁资源而出现超…背景介绍
在 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 LINNER JOIN ALL_OBJECTS O ON O.OBJECT_ID L.OBJECT_IDINNER 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 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强制结束 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_MODEFROM V$LOCKED_OBJECT A, DBA_OBJECTS BWHERE B.OBJECT_ID A.OBJECT_ID;查询被锁对象的连接 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;关闭被锁对象连接 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_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 语句 查询引起锁表的 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#;查看所有被阻塞的会话 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);展示阻塞的树形结构 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;展示阻塞的树形结构并输出阻塞语句、被阻塞语句并给出杀会话语句 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;直接显示阻塞关系 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 语句。同时通过实施最佳实践可以显著减少锁表问题的发生提高系统的并发性能和稳定性。
