--查询锁定详情
SELECT A.OWNER, --OBJECT所属用户
A.OBJECT_NAME, --OBJECT名称(表名)
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID, --锁表用户的session
B.ORACLE_USERNAME, --锁表用户的Oracle用户名
B.OS_USER_NAME, --锁表用户的操作系统登陆用户名
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE, --锁表用户的计算机名称(例如:WORKGROUP\UserName)
C.STATUS, --锁表状态
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM --锁表用户所用的数据库管理工具(例如:ob9.exe)
,c.SQL_HASH_VALUE
,c.SQL_ID
,c.LOGON_TIME,
c.BLOCKING_SESSION,
'alter system kill session '|| ''''|| s.SID|| ','||s.serial#||''';'
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
WHERE A.OBJECT_ID = B.OBJECT_ID
AND B.PROCESS = C.PROCESS
ORDER BY 1, 2;
---查询会话sid
select * from v$mystat t where rownum<=1;
---查询具体sql,其中查询条件是hashvalue
select *
from V$OPEN_CURSOR t
where --t.SQL_ID='g448tfpa2vnp6'
t.HASH_VALUE = '468763804';
select *
from v$sqltext t
where t.HASH_VALUE = '468763804'
ORDER BY piece ASC;
select * from v$sqlarea t where t.HASH_VALUE = '1166126392';
---杀会话
alter system kill session 'sid,serial#';
lock_mode状态
ORACLE里锁有以下几种模式:
0:none
1:null 空
2:Row-S 行共享(RS):共享表锁,sub share
3:Row-X 行独占(RX):用于行的修改,sub exclusive
4:Share 共享锁(S):阻止其他DML操作,share
5:S/Row-X 共享行独占(SRX):阻止其他事务操作,share/sub exclusive
6:exclusive 独占(X):独立访问使用,exclusive
数字越大锁级别越高, 影响的操作越多。
1级锁有:Select,有时会在v$locked_object出现。
2级锁有:Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级(Row-X)独占式锁定,
其他对象只能查询这些数据行,不能进行update、delete或select for update操作。
3级锁有:Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有:Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, "resource busy and acquire with NOWAIT specified"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有:Lock Share Row Exclusive
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
6级锁有:Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
附:
1,锁原因
1,主键唯一索引引起的死锁:两个回话互相插入冲突的主键或者唯一键
2,外键死锁:外键未加索引,在主键上互相删除在主表上的记录将产生
3,位图索引死锁:实质是发生在位图上,同一个键插入多条,不同回话互相在删除或者更新同一个键不同的记录使产生
4,自制事务引起死锁:治事务是一个会话可能引发死锁,PRAGMA AUTONOMOUS_TRANSACTION
5,事务引起的死锁
6,块级死锁:ITL(Interested Transaction List)主要跟创建表的时候INITRANS和PCTFREE有关系