我要评论背景介绍
在 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 语句。同时，通过实施最佳实践，可以显著减少锁表问题的发生，提高系统的并发性能和稳定性。
以上就是oracle锁表的解决方法及避免锁表问题的最佳实践的详细内容，更多关于oracle锁表的解决及避免的资料请关注代码网其它相关文章！
发表评论