ORACLE中有两种锁类型:排它锁(Exclusive Locks即X锁)和共享锁(Share Locks即S锁)
加了Exclusive Locks后,其他事务不能对此对象进行读取和修改;加了share locks后,其他事务能对此对象进行读取但不能进行修改。
按照数据保护的对象不同,oracle数据库锁可分为以下几类:
DDL锁(dictionary locks 字典锁) 保护数据库对象的结构,如表、索引等的结构定义
DML锁(data locks 数据锁) 保护数据完整性
内部锁和闩(internal locks and latches) 保护数据库的内部结构
我们使用数据库常碰到的是DML锁,它是为了保护并发情况下数据的完整性。DML锁又分为TM锁和TX锁,TM锁称为表级锁,TX锁称为事务锁和行级锁。
当Oracle执行DML语句时系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后系统再自动申请TX类型的锁并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志而只需检查TM锁模式的相容性即可大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式在数据库中用0-6来表示。不同的SQL操作产生不同类型的TM锁。
这是oracle的锁对应的数字:
0:none
1:null 空
2:Row-S 行共享(RS):共享表锁
3:Row-X 行专用(RX):用于行的修改
4:Share 共享锁(S):阻止其他DML操作
5:S/Row-X 共享行专用(SRX):阻止其他事务操作
6:exclusive 专用(X):独立访问使用
数字越大锁级别越高, 影响的操作越多。
select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。
在数据库行上只有TX锁,多个会话在同一记录上执行DML时,第一个会话在该记录上加tx锁,其他会话等待。当数据库发生tx锁等待时,不及时处理会引起oracle数据库挂起,导致死锁的发生。产生ORA-60的错误。
死锁的监控和处理:
死锁的监控和解决在数据库中当两个或多个会话请求同一个资源时会产生死锁的现象。死锁的常见类型是行级锁死锁和页级锁死锁Oracle数据库中一般使用行级锁。下面主要讨论行级锁的死锁现象。
这里来讨论下select,insert,update,delete的时候分别加的是什么锁:
要在scott库里看相关视图,首先要用sys登录数据库(要用sysdba),给scott用户赋予这些视图的查询权限:
grant select on sys.user_objects to scott;
grant select on sys.v$locked_object to scott;
grant select on sys.v_$session to scott;
grant select on sys.v_$process to scott;
grant select on sys.v_$lock to scott;
那么下面在scott用户下的test1表中插入两条同样的数据,第一条未commit,第二条就处于等待了,是在不同的PLSQL DEVELOPER的sql窗口下执行(意味着两个不同的session,查得了这两个窗口对应的的sid:43,56),查看下v$lock.select * from $lock where sid in('17','183');
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
2 070000005FCD8510 070000005FCD8568 183 AE 100 0 4 0 21 0
3 070000005FCD8DE8 070000005FCD8E40 183 TX 655385 33773 0 4 21 0
4 00000001109445F8 0000000110944658 17 TM 143924 0 3 0 23 0
5 00000001109445F8 0000000110944658 183 TM 143924 0 3 0 21 0
6 070000005BCC2D80 070000005BCC2DF8 17 TX 655385 33773 6 0 23 1
7 070000005BD023E0 070000005BD02458 183 TX 1245204 7620 6 0 21 0
sid是17的是我第一个执行的insert,第四行TM(行级锁)为3(行专用),第六行行级锁LMODE为6为X锁,对应的LMODE锁类型为6(X: exclusive) request为0 即没有被阻塞,BLOCK为1表示阻塞了别的SESSION。第五行为第二个insert,TM为3即行级锁,第三行为行级锁,request为4表示请求四次被阻塞
接着试update,对同一条记录进行update,第一个session的LMODE为6,第二个sessio的LMODE为0了,request是6(即请求多次失败),被阻塞了。
试了delete和update效果一样。都是对表加LMODE为3(RX)。
select ..for update对表加的LMODE为2.
常见的查sql死锁的语句:
SELECT s.username,
s.INST_ID,
l.OBJECT_ID,
l.SESSION_ID,
s.SERIAL#,
l.ORACLE_USERNAME,
l.OS_USER_NAME,
p.SPID
FROM gV$LOCKED_OBJECT l, gV$SESSION S, gv$process p
WHERE l.SESSION_ID = S.SID
and s.PADDR = p.ADDR
and l.INST_ID =s.INST_ID
and s.INST_ID = p.INST_ID
这里涉及到的oracle自带的几个视图:
V$LOCKED_OBJECT:只包含DML的锁信息,包括回滚段和会话信息。
V$SESSION:查询会话的信息和锁的信息。
V$PROCESS:查询进程信息。
而为什么上面的sql不用V$SESSION这些视图呢,是查得了所有实例的死锁信息。
我们常用的v$ 是v_$的同义词,v_$是基于真正的视图v$,而真正的v$视图是在gv$的基础上限制inst_id得到。
kill死锁进程的sql:
alter system kill session 'l.SESSION_ID,SERIAL#';