*******************************************************
几张比较有用的字典:v$lock 、v$locked_object 、dba_blockers 、dba_waiters 、v$resource_limit
查看被锁的表
select p.spid,a.sid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_name
from v$process p,v$session a,v$locked_object b,all_objects c
where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id;
------------------------------查看哪个会话持有的锁锁住了别的会话----------------------------------
select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,
decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
c.ctime time_waited
from v$lock b, v$enqueue_lock c, v$session a
where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
order by time_held, time_waited;
BLOCKER_SID SERIAL# BLOCKER_USERNAME TYPE LOCK_MODE TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
----------- ---------- ------------------------------ ---- ------------------- ---------- ---------- ------------------- -----------
181 20 SYS TX Exclusive 120 98 Exclusive 50
181 20 SYS TX Exclusive 120 88 Exclusive 112
-------------------------------------------------------------------------------------------------------------------
杀掉锁表的session
alter system kill session '138,7482'; ------用这个去杀锁,尽量别杀os上的进程
*dba_blockers 和dba_waiters这两张字典可以直接找出阻塞方和被阻塞方的SID
***********************************************************
知道了SID就能查看锁的详细信息了:
select* from v$lock where sid in (133,132);
其中:LMODE>0表示当前会话以某种形式占有该锁
LMODE=0 表示当前会话正在等待该锁,被阻塞 ------被阻塞方
REQUEST >0 被阻塞
BLOCK=1 表示当前会话的锁阻塞了别的会话 ------阻塞方
*************************************************************
查看锁的级别:
select b.owner,b. object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id;
-----------------------------------------------------------------------------------------------------------------------------------------------------
锁的模式
0- none
1- null (NULL)
2 - row-S (SS) ---也叫RS锁
3 - row-X (SX) ---也叫RX
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
锁的模式(LMODE)
lock table emp in ROW SHARE mode; --2号锁 select empno from emp for update
Update scott.emp setsal=sal+1;--3号锁
lock table emp in share mode;--4号锁
lock table emp in SHARE ROW EXCLUSIVE mode; --5号锁
lock table emp in EXCLUSIVE mode; --6号锁
TX锁是行级锁(row-level locks)----其实是事务锁(你可以做一个试验:开一个事物,修改3个表,会产生3个TM锁和一个TX锁):
行级锁只有独占模式一种,也就是上面的6号锁
TM锁是表级锁(table-level locks):
而表级锁的模式就多了,我可以得到2、3、4、5、6等各种模式的TM锁(执行上面彩色的语句)
TM锁(表级锁)可对应的LMODE:2、3、4、5、6
TX锁可对应的LMODE:6
当我们做DML的时候,会形成3号(共享)TM锁,和TX锁
可以获得的TX锁定(也就是事物的最大数量)的总个数由初始化参数transactions决定,而可以获得的TM锁定(也就是能所多少个对象)的个数则由初始化参数dml_locks决定
select name,value from v$parameter where name in('transactions','dml_locks');
select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"
from v$resource_limit
where resource_name in('transactions','dml_locks');