本文档风哥主要介绍Oracle数据库锁管理与死锁处理相关知识，包括Oracle数据库锁的概念、Oracle数据库锁的类型、Oracle数据库锁的规划、Oracle数据库锁的监控、Oracle数据库死锁的检测与处理、Oracle数据库锁问题的解决方案等内容，由风哥教程参考Oracle官方文档Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 Oracle数据库锁的概念
Oracle数据库锁（Lock）是一种机制，用于控制多个并发事务对共享资源的访问，确保数据的一致性和完整性。锁可以防止多个事务同时修改同一数据，避免数据丢失或损坏。更多视频教程www.fgedu.net.cn
1.2 Oracle数据库锁的类型
Oracle数据库锁主要类型：
- DML锁（数据锁）：保护表数据，包括TM锁（表锁）和TX锁（事务锁）
- DDL锁（数据字典锁）：保护数据字典对象，防止DDL操作冲突
- 系统锁：保护内部数据库结构，如闩锁（Latch）和Mutex
1.3 Oracle数据库锁的模式
Oracle数据库锁的主要模式：
- NULL（空模式）：不阻塞任何操作
- ROW SHARE（RS，行共享）：允许其他用户查询和锁定行
- ROW EXCLUSIVE（RX，行排他）：允许其他用户查询，但阻止其他用户锁定行
- SHARE（S，共享）：允许其他用户查询，但阻止其他用户修改
- SHARE ROW EXCLUSIVE（SRX，共享行排他）：允许其他用户查询，但阻止其他用户锁定表
- EXCLUSIVE（X，排他）：阻止其他用户进行任何操作
Part02-生产环境规划与建议
2.1 Oracle数据库锁规划
Oracle数据库锁规划要点：
– DML_LOCKS：控制DML锁的数量，通常设置为SESSIONS的4-10倍
– ENQUEUE_RESOURCES：控制队列资源数量，通常设置为PROCESSES的2-3倍
– ENQUEUE_LOCKS：控制队列锁数量，通常设置为SESSIONS的2-3倍
# 事务设计原则
– 事务尽量短小精悍，避免长时间持有锁
– 按相同顺序访问对象，减少死锁风险
– 使用合适的隔离级别，平衡并发性和一致性
– 及时提交或回滚事务，释放锁资源
# 锁等待阈值规划
– 锁等待时间超过1分钟：警告
– 锁等待时间超过5分钟：严重
– 发现阻塞会话超过10个：警告
– 发现死锁：严重
2.2 Oracle数据库死锁预防
Oracle数据库死锁预防措施：
– 事务按相同顺序访问对象
– 事务尽量简短，避免长时间运行
– 使用较低的隔离级别
– 避免在事务中进行用户交互
– 及时提交或回滚事务
– 合理设置超时参数
– 使用乐观锁替代悲观锁
– 定期分析死锁日志，优化应用
# 死锁检测设置
– Oracle自动检测死锁，每1秒检测一次
– 死锁检测超时：通常设置为60秒
– 死锁发生时，自动回滚较小的事务
– 记录死锁信息到告警日志
2.3 Oracle数据库锁优化建议
Oracle数据库锁优化建议：
- 优化事务设计：事务尽量短小，避免长时间持有锁
- 使用合适的隔离级别：根据业务需求选择合适的隔离级别
- 避免全表扫描：使用索引减少锁定的行数
- 批量操作：合理使用批量操作，减少锁的获取次数
- 使用ROWID：使用ROWID直接定位行，减少锁等待
- 监控锁等待：及时发现和处理锁等待问题
Part03-生产环境项目实施方案
3.1 Oracle数据库锁查看方法
3.1.1 Oracle数据库查看当前锁
SQL> select
l.sid,
l.type,
l.lmode,
l.request,
l.ctime,
l.block,
s.username,
s.status,
s.program,
s.machine
from v$lock l, v$session s
where l.sid = s.sid
order by l.sid;SID TY LMODE REQUEST CTIME BLOCK USERNAME STATUS PROGRAM MACHINE
———- — ———- ———- ———- ———- ———- ——– —————– ————-
123 TM 3 0 300 0 FGAPP_USER ACTIVE app.exe server1
123 TX 6 0 300 1 FGAPP_USER ACTIVE app.exe server1
124 TM 3 0 200 0 FGAPP_USER ACTIVE app.exe server1
124 TX 0 6 200 0 FGAPP_USER ACTIVE app.exe server1
# 查看阻塞会话
SQL> select
blocking_session as blocker_sid,
sid as waiter_sid,
serial# as waiter_serial,
username as waiter_user,
wait_class,
seconds_in_wait,
event
from v$session
where blocking_session is not null;BLOCKER_SID WAITER_SID WAITER_SERIAL WAITER_USE WAIT_CLASS SECONDS_IN_WAIT EVENT
———– ———- ————- ———- ————— ————— ——————————
123 124 45679 FGAPP_USER Application 300 enq: TX – row lock contention
# 查看锁等待详情
SQL> select
s.sid,
s.serial#,
s.username,
s.sql_id,
s.event,
s.p1text,
s.p1,
s.p2text,
s.p2,
s.p3text,
s.p3
from v$session s
where s.wait_class = ‘Application’
order by s.seconds_in_wait desc;
3.1.2 Oracle数据库查看锁等待
SQL> select
s.sid,
s.serial#,
s.username,
s.status,
s.program,
s.machine,
s.sql_id,
s.event,
s.seconds_in_wait
from v$session s
where s.state = ‘WAITING’
and s.wait_class != ‘Idle’
order by s.seconds_in_wait desc;SID SERIAL# USERNAME STATUS PROGRAM MACHINE SQL_ID EVENT SECONDS_IN_WAIT
———- ———- ———- ——– —————– ————- ————- —————————— —————
124 45679 FGAPP_USER WAITING app.exe server1 abc123def456 enq: TX – row lock contention 300
# 查看锁的详细信息
SQL> select
l.sid,
l.type,
decode(l.lmode,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’,
‘Unknown’) lock_mode,
decode(l.request,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’,
‘Unknown’) request_mode,
l.ctime,
l.block
from v$lock l
where l.type in (‘TM’, ‘TX’)
order by l.sid;SID TY LOCK_MODE REQUEST_MODE CTIME BLOCK
———- — ———— ———— ———- ———-
123 TM Row-X (SX) None 300 0
123 TX Exclusive None 300 1
124 TM Row-X (SX) None 200 0
124 TX None Exclusive 200 0
# 查看被锁定的对象
SQL> select
o.object_name,
o.object_type,
l.sid,
l.type,
l.lmode,
s.username,
s.program
from v$locked_object lo,
dba_objects o,
v$lock l,
v$session s
where lo.object_id = o.object_id
and lo.session_id = l.sid
and l.sid = s.sid;OBJECT_NAME OBJECT_TYPE SID TY LMODE USERNAME PROGRAM
—————————— ———— ———- — ———- ———- —————–
FGEDU_EMPLOYEES TABLE 123 TM 3 FGAPP_USER app.exe
3.2 Oracle数据库死锁检测
3.2.1 Oracle数据库查看死锁
# 使用ADRCI查看
$ adrci
ADRCI> show alert
ADRCI> show alert -tail 100
# 或直接查看告警日志文件
$ tail -n 200 /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log
# 死锁信息示例
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00020005-00000123 123 123 X 124 124 X
TX-00020006-00000456 124 124 X 123 123 X
# 查询死锁历史
SQL> select
to_char(timestamp, ‘YYYY-MM-DD HH24:MI:SS’) deadlock_time,
username,
session_id,
serial#,
program,
machine
from dba_hist_snapshot s,
dba_hist_active_sess_history h
where s.snap_id = h.snap_id
and h.wait_class = ‘Application’
and h.event = ‘enq: TX – row lock contention’
order by timestamp desc;DEADLOCK_TIME USERNAME SESSION_ID SERIAL# PROGRAM MACHINE
——————— ———- ———- ———- —————– ————-
2026-03-31 10:00:00 FGAPP_USER 123 45678 app.exe server1
2026-03-31 10:00:00 FGAPP_USER 124 45679 app.exe server1
3.2.2 Oracle数据库死锁分析
SQL> oradebug setmypid
SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_12345.trc
# 查看死锁跟踪文件
$ cat /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_12345.trc
# 死锁跟踪文件示例
*** 2026-03-31 10:00:00.000
*** SESSION ID:(123.45678) 2026-03-31 10:00:00.000
Deadlock detected (ORA-00060)
Current SQL statement for this session:
UPDATE fgedu_employees SET salary = salary * 1.1 WHERE employee_id = 100
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL.
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00020005-00000123 123 123 X 124 124 X
TX-00020006-00000456 124 124 X 123 123 X
Information on waiting sessions:
Session 124:
obj #: 12345, row #: 0x00000001
lock row cache enqueue: session=123, mode=X, waiters=0
waiting for ‘enq: TX – row lock contention’ waitid=4294967295 p1=1415053316 p2=131077 p3=120
Information on blocking sessions:
Session 123:
obj #: 12346, row #: 0x00000001
lock row cache enqueue: session=124, mode=X, waiters=0
waiting for ‘enq: TX – row lock contention’ waitid=4294967295 p1=1415053316 p2=131078 p3=120
Session 123:
UPDATE fgedu_employees SET salary = salary * 1.1 WHERE employee_id = 100
Session 124:
UPDATE fgedu_employees SET salary = salary * 1.1 WHERE employee_id = 200
3.3 Oracle数据库锁处理方法
3.3.1 Oracle数据库终止阻塞会话
SQL> select
blocking_session as blocker_sid,
sid as waiter_sid,
serial# as waiter_serial,
username as waiter_user,
program as waiter_program,
machine as waiter_machine
from v$session
where blocking_session is not null;BLOCKER_SID WAITER_SID WAITER_SERIAL WAITER_USE WAITER_PROGRAM WAITER_MACHINE
———– ———- ————- ———- —————– —————–
123 124 45679 FGAPP_USER app.exe server1
# 查看阻塞会话详情
SQL> select
s.sid,
s.serial#,
s.username,
s.status,
s.program,
s.machine,
s.sql_id,
s.event,
s.seconds_in_wait,
p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = 123;SID SERIAL# USERNAME STATUS PROGRAM MACHINE SQL_ID EVENT SECONDS_IN_WAIT SPID
———- ———- ———- ——– —————– ————- ————- —————————— ————— —–
123 45678 FGAPP_USER ACTIVE app.exe server1 abc123def456 SQL*Net message from client 300 23456
# 终止阻塞会话
SQL> alter system kill session ‘123,45678’;System altered.
# 立即终止会话（不等待回滚）
SQL> alter system kill session ‘123,45678’ immediate;System altered.
# 查看会话状态
SQL> select sid, serial#, username, status from v$session where sid = 123;SID SERIAL# USERNAME STATUS
———- ———- ———- ——–
123 45678 KILLED
# 如果会话状态是KILLED但进程还在，可以使用OS命令终止
SQL> select s.sid, s.serial#, s.username, p.spid, p.program
from v$session s, v$process p
where s.paddr = p.addr
and s.status = ‘KILLED’;SID SERIAL# USERNAME SPID PROGRAM
———- ———- ———- —– ——————–
123 45678 23456 oracle@server1 (TNS V1-V3)
# 在操作系统层面终止进程
$ kill -9 23456
3.3.2 Oracle数据库预防锁等待
SQL> alter session set ddl_lock_timeout = 30;Session altered.
# 设置分布式事务超时
SQL> alter session set distributed_lock_timeout = 60;Session altered.
# 使用NOWAIT选项避免等待
SQL> select * from fgedu_employees for update nowait;# 使用WAIT选项设置等待时间
SQL> select * from fgedu_employees for update wait 10;# 使用SKIP LOCKED选项跳过被锁定的行
SQL> select * from fgedu_employees for update skip locked;# 设置资源管理器限制
SQL> begin
dbms_resource_manager.create_pending_area();dbms_resource_manager.create_plan(
plan => ‘LOCK_PLAN’,
comment => ‘Lock management plan’);dbms_resource_manager.create_plan_directive(
plan => ‘LOCK_PLAN’,
group_or_subplan => ‘APP_GROUP’,
comment => ‘Application group’,
max_idle_time => 300);dbms_resource_manager.submit_pending_area();end;/PL/SQL procedure successfully completed.
# 启用资源管理器
SQL> alter system set resource_manager_plan = ‘LOCK_PLAN’ scope=both;System altered.
Part04-生产案例与实战讲解
4.1 Oracle数据库锁异常处理
在Oracle数据库锁管理过程中，可能会遇到以下问题：
4.1.1 Oracle数据库长时间锁等待
# 分析步骤：
# 1. 查看当前锁等待
SQL> select
s.sid,
s.serial#,
s.username,
s.status,
s.program,
s.machine,
s.sql_id,
s.event,
s.seconds_in_wait,
s.blocking_session
from v$session s
where s.wait_class = ‘Application’
order by s.seconds_in_wait desc;SID SERIAL# USERNAME STATUS PROGRAM MACHINE SQL_ID EVENT SECONDS_IN_WAIT BLOCKING_SESSION
———- ———- ———- ——– —————– ————- ————- —————————— ————— —————-
124 45679 FGAPP_USER WAITING app.exe server1 abc123def456 enq: TX – row lock contention 600 123
125 45680 FGAPP_USER WAITING app.exe server1 def456abc123 enq: TX – row lock contention 500 123
126 45681 FGAPP_USER WAITING app.exe server1 ghi789jkl012 enq: TX – row lock contention 400 123
# 2. 查看阻塞会话
SQL> select
s.sid,
s.serial#,
s.username,
s.status,
s.program,
s.machine,
s.sql_id,
s.event,
s.seconds_in_wait,
p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = 123;SID SERIAL# USERNAME STATUS PROGRAM MACHINE SQL_ID EVENT SECONDS_IN_WAIT SPID
———- ———- ———- ——– —————– ————- ————- —————————— ————— —–
123 45678 FGAPP_USER INACTIVE app.exe server1 abc123def456 SQL*Net message from client 600 23456
# 3. 查看阻塞会话执行的SQL
SQL> select sql_text from v$sql where sql_id = ‘abc123def456’;SQL_TEXT
——————————————————————————–
BEGIN
UPDATE fgedu_employees SET salary = salary * 1.1 WHERE department_id = 10;– 缺少COMMIT
END;# 4. 处理方案
# 方案1：联系应用负责人，让其提交或回滚事务
# 方案2：如果无法联系，且业务受影响严重，终止阻塞会话
SQL> alter system kill session ‘123,45678’;System altered.
# 5. 预防措施
# – 检查应用程序，确保事务及时提交或回滚
# – 设置PROFILE的IDLE_TIME限制空闲会话
# – 监控长事务，及时告警
4.2 Oracle数据库死锁案例
# 分析步骤：
# 1. 查看告警日志
$ tail -n 200 /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/alert_fgedudb.log
# 死锁信息
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00020005-00000123 123 123 X 124 124 X
TX-00020006-00000456 124 124 X 123 123 X
# 2. 查看死锁跟踪文件
$ cat /oracle/app/oracle/diag/rdbms/fgedudb/fgedudb/trace/fgedudb_ora_12345.trc
# Session 123执行的SQL
UPDATE fgedu_employees SET salary = salary * 1.1 WHERE employee_id = 100;UPDATE fgedu_departments SET budget = budget * 1.1 WHERE department_id = 10;# Session 124执行的SQL
UPDATE fgedu_departments SET budget = budget * 1.1 WHERE department_id = 10;UPDATE fgedu_employees SET salary = salary * 1.1 WHERE employee_id = 100;# 3. 分析死锁原因
# – Session 123先锁定employee_id=100，再锁定department_id=10
# – Session 124先锁定department_id=10，再锁定employee_id=100
# – 两个会话互相等待对方释放锁，形成死锁
# 4. 处理方案
# 方案1：修改应用程序，让两个会话按相同顺序访问对象
# 方案2：将两个UPDATE合并为一个事务，减少锁的持有时间
# 方案3：使用乐观锁替代悲观锁
# 5. 预防措施
# – 确保所有事务按相同顺序访问对象
# – 事务尽量简短，避免长时间持有锁
# – 定期分析死锁日志，优化应用程序
4.3 Oracle数据库锁问题解决方案
Oracle数据库锁问题的常见解决方案：
- 优化事务设计：事务尽量短小，避免长时间持有锁
- 按相同顺序访问对象：减少死锁风险
- 使用索引：减少锁定的行数
- 批量操作：合理使用批量操作，减少锁的获取次数
- 设置超时：避免无限等待
- 监控锁等待：及时发现和处理锁等待问题
Part05-风哥经验总结与分享
5.1 Oracle数据库锁管理最佳实践
Oracle数据库锁管理最佳实践：
- 事务设计：事务尽量短小精悍，避免长时间持有锁
- 访问顺序：按相同顺序访问对象，减少死锁风险
- 索引优化：使用索引减少锁定的行数
- 批量操作：合理使用批量操作，减少锁的获取次数
- 监控告警：配置锁监控和告警，及时发现问题
- 定期分析：定期分析死锁日志，优化应用程序
5.2 Oracle数据库锁检查清单
– [ ] 锁相关参数配置合理
– [ ] 事务设计遵循最佳实践
– [ ] 按相同顺序访问对象
– [ ] 使用索引减少锁范围
– [ ] 设置合适的超时参数
– [ ] 配置锁监控和告警
– [ ] 定期分析死锁日志
– [ ] 及时处理锁等待问题
– [ ] 应用程序及时提交或回滚事务
– [ ] 定期 review 锁使用情况
# 锁问题处理流程
1. 发现锁等待或死锁
2. 收集锁相关信息
3. 分析锁等待或死锁原因
4. 制定处理方案
5. 执行处理方案
6. 验证问题解决
7. 总结经验，优化预防措施
5.3 Oracle数据库锁管理工具推荐
Oracle数据库锁管理常用工具：
- v$lock：查看当前锁信息
- v$session：查看会话信息，包括阻塞会话
- v$locked_object：查看被锁定的对象
- 告警日志：查看死锁信息
- 跟踪文件：查看死锁详细信息
- Oracle Enterprise Manager：图形化锁监控和管理
- AWR/ASH报告：分析历史锁等待情况
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
