oracle 锁机制

时间:2013年07月21日 | 分类:Oracle基础篇 | 评论:0 条 | 浏览:1,374 次

1.锁的类型有:[oracle锁机制是自动管理]

dml 锁

ddl 锁

shared 锁

互斥锁 : 主要针对内存的的锁[share pool]

2.锁的模式有:

0:none

1:null

2:row share(RS) (lock a row in shared mode)   ==> 行级锁
加锁语法:LOCK TABLE TableName IN ROW SHARE MODE;
仅能保护表不被删除

3:row exclusive(RX) (lock a row in exclusive mode)    ==> 行级排它锁
加锁语法:LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
不允许改变表的结构,可删除表内数据,但不可删除列

4:share(S) (lock the entire table in shared mode)    ==> 共享锁
加锁语法:LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
不允许两个人同时对一张表执行除select的dml语句或ddl语句

5:share row exclusive(SRX)(lock the table in shared mode but a row in exclusive) ==> 共享行级排它锁
加锁语法:Lock Table TableName In Share Row Exclusive Mode;
仅能查询(独占行,不得执行dml,除select外)

6:exclusive(X) (lock the entire table in exclusive mode) ==> 排他锁
加锁语法:Lock Table TableName In Exclusive Mode;
仅能查询

3.与锁相关的视图
DBMS_LOCK
V$DLM_ALL_LOCKS
V$DLM_LOCKS
V$ENQUEUE_LOCK
V$GLOBAL_BLOCKED_LOCKS
V$LOCK
V$LOCKED_OBJECT
V$LOCKS_WITH_COLLISIONS
V$LOCK_ACTIVITY
V$LOCK_ELEMENT
V$_LOCK

4.解锁及 Kill Session:
使用下面的语法查出锁并杀掉 Session。
SELECT A.SID,A.SERIAL#,A.USERNAME,B.TYPE FROM V$SESSION A,V$LOCK B WHERE A.SID=B.SID;
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

×