Oracle数据库的锁类型
根据保护的对象不同,Oracle数据库锁可以分为以下几大类:DML锁(data locks,数据锁),用于保护数据的完整性;DDL锁(dictionary locks,字典锁),用于保护数据库对象的结构,如表、索引等的结构定义;内部锁和闩(internal locks and latches),保护 数据库的内部结构。
DML锁的目的在于保证并发情况下的数据完整性,。在Oracle数据库中,DML锁主要包括TM锁和TX锁,其中TM锁称为表级锁,TX锁称为事务锁或行级锁。
当Oracle执行DML语句时,系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后,系统再自动申请TX类型的锁,并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志,而只需检查TM锁模式的相容性即可,大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式,在数据库中用0-6来表示。不同的SQL操作产生不同类型的TM锁。
DML 锁类型
SQL Statement | Mode of Table Lock | Lock Modes Permitted? | ||||
---|---|---|---|---|---|---|
RS | RX | S | SRX | X | ||
SELECT...FROM table... | none | Y | Y | Y | Y | Y |
INSERT INTO table ... | RX | Y | Y | N | N | N |
UPDATE table ... | RX | Y* | Y* | N | N | N |
DELETE FROM table ... | RX | Y* | Y* | N | N | N |
SELECT ... FROM table FOR UPDATE OF ... | RS | Y* | Y* | Y* | Y* | N |
LOCK TABLE table IN ROW SHARE MODE | RS | Y | Y | Y | Y | N |
LOCK TABLE table IN ROW EXCLUSIVE MODE | RX | Y | Y | N | N | N |
LOCK TABLE table IN SHARE MODE | S | Y | N | Y | N | N |
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE | SRX | Y | N | N | N | N |
LOCK TABLE table IN EXCLUSIVE MODE | X | N | N | N | N | N |
RS: row share
RX: row exclusive
S: share
SRX: share row exclusive
X: exclusive
*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.
上图中例如执行了insert table 语句就会加上rx锁 如果这条语句没有提交。可以执行rs锁的语句,rx锁的语句,但是s,srx,x锁的语句都执行不成功。
y*表示可以执行加此锁的语句如果没有冲突的行锁被另一个事务。否则,等待发生
下面拿一道题举个例子
User SCOTT executes the following command on the EMP table but has not issued COMMIT, ROLLBACK,
or any data definition language (DDL) command:
SQL> SELECT ename FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
SCOTT has opened another session to work with the database instance. Which three operations would
wait when issued in SCOTT's second session? (Choose three.)
A. LOCK TABLE emp IN SHARE MODE;
B. LOCK TABLE emp IN EXCLUSIVE MODE;
C. UPDATE emp SET sal=sal*1.2 WHERE job=ANAGER?UPDATE emp SET sal=sal*1.2 WHERE job=?
ANAGER?
D. INSERT INTO emp(empno,ename) VALUES (1289,'Harry');
E. SELECT ename FROM emp WHERE job='CLERK' FOR UPDATE OF empno;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27668565/viewspace-751299/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27668565/viewspace-751299/