Oracle 锁
Oracle的锁是针对多个会话进行作用的,没有并发就没有锁。
锁的实质:是维护一个事务完整性的,锁的信息是数据块的一个属性,是物理的,并不是逻辑上属于某个表或者某几行的。
Oracle锁的分类
Enqueues --队列类型的锁,通常和业务相关
Latches --系统资源方面的锁,比如内存结构,sql解析。
分别模拟insert,update和delete造成阻塞的示例
--INSERT 阻塞
CREATE TABLE T (ID INT PRIMARY KEY,NAME VARCHAR2(10));
ADMIN@ORCL> INSERT INTO T VALUES(1,'ANKER');
1 ROW CREATED.
--另外一个会话被阻塞
ADMIN@ORCL> INSERT INTO T VALUES(1,'YANGEOO');
--从结果可以看出,会话197在请求持有模式为4的TX锁,而被会话9所阻塞
字段解释:
SID:会话id号
TYPE:锁的类型 TM表锁,发生在DML操作时,目的是保证操作能够正常进行,并且阻止其他人对表DDL操作。TX锁 事务锁(行锁)对于被修改的数据,阻止其他会话进行修改。
ID1:会话操作对象的id号
ID2:ID1+ID2 定位回滚段上的一个地址(即修改之前数据镜像地址),若TX锁的ID1+ID2相同,意味着操作的是同一行数据
LMODE:锁模式,不同的数字代表不同的锁模式 例如 0 现在没有申请到锁 3 共享锁模式(段级共享锁) 6 排他锁模式 锁的级别越高限制越多
REQUEST:目前会话没有锁,正在申请的锁模式
BLOCK:当前是否在阻塞会话 例如 1 当前正在阻塞一个会话
admin@ORCL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
2 FROM V$LOCK
3 WHERE TYPE IN ('TM', 'TX')
4 ORDER BY SID, TYPE;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
9 TM 15130 0 3 0 0
9 TX 327692 295 6 0 1
197 TM 15130 0 3 0 0
197 TX 327692 295 0 4 0
197 TX 196629 297 6 0 0
--UPDATE 阻塞
admin@ORCL> update t set NAME = 'ANKER1' where id = 1;
1 row updated.
admin@ORCL> update t set name='ANKER2' where id =1;
--结果可以看出会话197在请求模式为6的TX锁,而此锁已被会话9占用,引起阻塞
admin@ORCL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
2 FROM V$LOCK
3 WHERE TYPE IN ('TM', 'TX')
4 ORDER BY SID, TYPE;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
9 TM 15130 0 3 0 0
9 TX 196610 298 6 0 1
197 TM 15130 0 3 0 0
197 TX 196610 298 0 6 0
--DELETE 阻塞
admin@ORCL> delete t where ID = 1;
1 row deleted.
admin@ORCL> delete t where ID = 1;
--查看结果,会话197在请求模式6的TX锁,而被会话9阻塞
admin@ORCL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
2 FROM V$LOCK
3 WHERE TYPE IN ('TM', 'TX')
4 ORDER BY SID, TYPE;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
9 TM 15130 0 3 0 0
9 TX 393237 296 6 0 1
197 TM 15130 0 3 0 0
197 TX 393237 296 0 6 0
--当出现锁等待时,锁等待对应的事件,
enq = enqueque
从以下event中也可以看出会话197产生了行锁竞争的事件,也意味着197会话在等待其他会话释放tx锁。
admin@ORCL> SELECT SID,EVENT FROM V$SESSION_WAIT WHERE WAIT_CLASS <> 'Idle'
2 ;
SID EVENT
---------- ----------------------------------------------------------------
197 enq: TX - row lock contention
--2.模拟RI锁定导致阻塞的场景
--经过测试,在主表上insert时,会在从表上加一个3模式的TM锁,而update与delete均不在从表上加锁。
以下测试是对从表操作,导致主表阻塞。
当修改从表时,针对主表update 主键或者delete操作时,均会阻塞住。这是因为在主表修改或删除记录时,它需要查看从表中有没有
对应的值。
创建索引后,就没有这个问题。
admin@ORCL> CREATE TABLE P(ID INT PRIMARY KEY,NAME VARCHAR2(10));
admin@ORCL> CREATE TABLE C (ID REFERENCES P (ID),NAME VARCHAR2(10));
admin@ORCL> insert into p values(1,'A');
admin@ORCL> insert into p values(2,'B');
admin@ORCL> insert into p values(3,'C');
--130会话
admin@ORCL> update c set name = 'E' where id = 1;
1 row updated.
admin@ORCL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
2 FROM V$LOCK
3 WHERE TYPE IN ('TM', 'TX')
4 ORDER BY SID, TYPE;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
130 TM 15515 0 3 0 0
130 TX 393238 421 6 0 0
--10会话被阻塞
admin@ORCL> delete p where id = 4;
--查看 v$lock,p在进行删除操作时,会在15515表上添加一个4级别的TM锁,导致阻塞。
admin@ORCL> SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, BLOCK
2 FROM V$LOCK
3 WHERE TYPE IN ('TM', 'TX')
4 ORDER BY SID, TYPE;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
10 TM 15513 0 3 0 0
10 TM 15515 0 0 4 0
130 TM 15515 0 3 0 1
130 TX 393238 421 6 0 0
--锁模式与相互的排斥
模式 锁定的SQL 排斥的模式 允许的DML
2 lock table in row share mode 6 select,insert,update,delete,for update
3 lock table in row exclusive mode 4,5,6 select,insert,update,delete,for update
4 lock table in share mode 3,5,6 select
5 lock table in share row exclusive mode 3,4,5,6 select
6 lock table t in exclusive mode 2,3,4,5,6 select
Oracle TM锁的类型
模式2
Row Share(RS)又叫(SS)
行级共享锁,是限制最少的TM锁,可以提供最高程度的并发性。其他会话可以对锁定的表进行任何类型的DML操作,还可以与其他会话锁并存
Lock table t in row share mode;
模式3
Row Exclusive Table Lock(RX)又叫(SX)
行级排他锁,通常已经有事务在修改行或者select…for update 修改结果集。允许其他事务对锁定的表进行select insert update delete 或 lock table
同时锁定一张表
模式4
Share Table Lock(S)
共享锁,其他事务可以查询锁定的表但不能修改,只允许当前事务修改,但可以多个事务持有它
模式5
Share Row Exclusive Table Lock(SRX)又叫SSX
共享行级排他锁,同一时间只允许一个事务持有和修改锁定的表,其他事务可以查询但不能修改
模式6
Exclusive Table Lock (X)
排他锁,是限制最高的TM锁,禁止其他事务执行任何类型的DML语句或者锁表
一个表一般只能有一个6号锁
--查看当前是否有堵塞
SELECT WAIT_S.SID AS WAIT_SESSION,
WAIT_SQL.SQL_TEXT AS WAIT_SQL,
WAIT_S.BLOCKING_SESSION AS LOCK_SESSION,
OBJS.OBJECT_NAME AS LOCK_OBJECTS,
OBJS.OBJECT_TYPE AS LOCK_OBJ_TYPE
FROM V$SESSION WAIT_S
INNER JOIN V$SQL WAIT_SQL
ON WAIT_S.SQL_ID = WAIT_SQL.SQL_ID
INNER JOIN V$LOCKED_OBJECT LOCK_OBJ
ON LOCK_OBJ.SESSION_ID = WAIT_S.BLOCKING_SESSION
INNER JOIN DBA_OBJECTS OBJS
ON OBJS.OBJECT_ID = LOCK_OBJ.OBJECT_ID
WHERE WAIT_S.LOCKWAIT IS NOT NULL;
--查询哪些session 锁定了哪些object
SELECT S.SID,B.OWNER, B.OBJECT_NAME, S.USERNAME, S.MACHINE, S.TERMINAL, S.PROGRAM
FROM V$LOCKED_OBJECT L, DBA_OBJECTS B, V$SESSION S
WHERE L.OBJECT_ID = B.OBJECT_ID
AND L.SESSION_ID = S.SID