Oracle基础 锁

一、锁

　　数据库是一个多用户使用的共享资源。当多个用户并发地存储数据时，数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

　　锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

 

二、阻塞　　

定义：当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。

4个常见的dml语句会产生阻塞 INSERT 、UPDATE 、DELETE 、SELECT…FOR UPDATE

INSERT：Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。当2个的会话同时试图向表中插入相同的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。一个会话提交时，另一个会话将收到主键重复的错误。回滚时，被阻塞的会话将继续执行。

UPDATE 和DELETE当执行Update和delete操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。

Select …for update

当一个用户发出select..for update的操作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。需要等另一个会话结束之后才可继续执行。可以通过发出 select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resource busy and acquire with nowait specified.

 

死锁-deadlock

当两个用户希望持有对象的资源时就会发生死锁，即两个用户相互等待对方释放资源时，Oracle认为为产生死锁，在这种情况下，将以牺牲一个用户为代价，另一个用户继续执行，牺牲的用户事务将回滚。

　　例如：

　　（1）用户1对A表进行update操作，没有提交。

　　（2）用户2对B表进行update操作，没有提交。

　　此时双方不存在资源共享的问题。

　　（3）如果用户2此时对A表进行update操作，则会发生阻塞，需要等待用户1的事务结束。

　　（4）如果用户1此时又对B表进行UPDATE操作，则会产生死锁。

起因: 
Oracle的死锁问题实际上很少见，如果发生，基本上都是不正确的程序设计造成的，经过调整后，基本上都会避免死锁的发生。

　　此时Oracle会选择其中一个用户进行回滚，使另一个用户继续进行操作。在Oracle系统中自动发现死锁，并选择代价最小的，即完成工作量最少的事务予以撤销，释放该事务所拥有的资源，让其他事务继续执行下去。

　　

　　通过以下注意事项可以避免死锁：

　　1、对于UPDATE和DELETE操作，应只锁要改动的行，在完成修改后立即提交。

　　2、当多个事务正利用共享更新的方式进行更新，则不要使用共享锁，而应采用共享更新锁。

　　3、尽可能对一个表的操作的并发事务施加共享更新锁，从而可提高并行性。

　　4、在应用负荷较高的期间，不宜对基础数据结构（表、索引、簇和视图）进行修改。

 

三、Oracle锁的分类

　　1、按用户与系统划分。

　　（1）自动锁：当进行一项数据库操作时，默认情况下，系统自动为此数据库操作获得所有有必要的锁。自动锁又分为DML锁、DDL锁、内部锁和闩。

　　（2）显示锁：在某些情况下，需要用户显示的锁定数据库操作要用到的数据，使数据库操作执行得更好。显示锁是用户为数据库对象设定的。

　　2、按锁级别划分。

　　（1）排他锁：当数据对象被加上排它锁时，其他的事务不能对它读取和修改；

　　（2）共享锁：加了共享锁的数据对象可以被其他事务读取，但不能修改。

　　3、按操作划分。　　

（1）DML lock（data locks，数据锁）：用于保护数据的完整性；
（2）DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）；
（3）Internal locks 和latches（内部锁与闩）：保护内部数据库结构；
（4）Distributed locks（分布式锁）：用于OPS（并行服务器）中；
（5）PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中。

4、悲观锁

锁在用户修改之前就发挥作用：
Select ..for update（nowait)
Select * from tab1 for update
用户发出这条命令之后，oracle将会对返回集中的数据建立行级封锁，以防止其他用户的修改。
如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。
1：对返回结果集进行update或delete操作会发生阻塞。
2：对该表进行ddl操作将会报：Ora-00054:resource busy and acquire with nowait specified.

 

原因分析： 
此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放，产生的外在现象就是其他的操作将发生阻塞，这个这个操作commit或rollback.
同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出ora-00054错误：:resource busy and acquire with nowait specified.

 

5、乐观锁

　　乐观的认为数据在select出来到update进取并提交的这段时间数据不会被更改。这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定，是存在一种可能被其他用户更改的可能。因此Oracle仍然建议是用悲观封锁，因为这样会更安全。

 

四、DML锁

　　在Oracle中最主要的锁是DML锁（也可称为data locks，数据锁）。DML锁的目的在于保证并发情况下的数据完整性。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

　　可以使用以下查询语句查询系统锁情况。　　

SELECT * FROM v$locked_object;  

SELECT * FROM v$lock;

 

　　1、TM锁（表级锁）

　　TM锁（表级锁）类型共有5种，分别称为共享锁（S锁）、排它锁（X锁）、行级共享锁（RS锁）、行级排它锁（RX锁）、共享行级排它锁（SRX锁）

　　

表1 Oracle的TM锁类型
锁模式	锁描述	解释	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行级共享锁，其他对象只能查询这些数据行	Select for update、Lock for update、Lock row share
3	SX(Row-X)	行级排它锁，在提交前不允许做DML操作	Insert、Update、Delete、Lock row share
4	S(Share)	共享锁	Create index、Lock share
5	SSX(S/Row-X)	共享行级排它锁	Lock share row exclusive
6	X(Exclusive)	排它锁	Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

　　当事务获得行锁后，此事务也将自动获得该行的表锁（共享锁），以防止其他事务通过DDL语句影响记录行的更新。事务也可以再进行过程中获得共享锁和排他锁，只有当事务显示使用LOCK TABLE 语句定义一个排他锁时，事务才会获得表上的排他锁，也可以使用LOCK TABLE显示定义一个标记的共享锁。

　　语法：

　　LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT];

　　lock_mode是锁定的模式：

　　表级锁的模式包括以下内容。　　

1）共享锁（Share Table Lock，S）：
加锁语法：Lock Table TableName In Share Mode;
允许的操作：一个共享锁由一个事务控制，仅允许其它事务查询被锁定的表。一个有效的共享锁明确地用Select … For update形式锁定行，或执行Lock Table TableName In Share Mode语法锁定整个表，不允许被其它事务更新。
禁止的操作：一个共享锁由一个事务来控制，防止其它事务更新该表或执行下面的语句：
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;

 

2）排它锁（Exclusive Table Lock，X）：
排它锁是在锁机制中限制最多的一种锁类型，允许加排它锁的事务独自控制对表的写权限。
加锁语法：Lock Table TableName In Exclusive Mode;
允许的操作：在一个表中只能有一个事务对该表实行排它锁，排它锁仅允许其它的事务查询该表。
禁止的操作：拥有排他锁的事务禁止其它事务执行其它任何DML类型的语句或在该表上加任何其它类型的锁。
定义排它锁的语法：
LOCK TABLE TableName IN EXCLUSIVE MODE;

 

3）行级锁（Row Share Table Lock，RS）：
一个行级锁（有时称为Subshare Table Lock，简称SS，子共享锁）需要该事务在被锁定行的表上用update的形式加锁。当有下面语句被执行的时候行级锁自动加在操作的表上。
SELECT . . . FROM TableName. . . FOR UPDATE OF . . . ;
LOCK TABLE TableName IN ROW SHARE MODE;
行级锁（Row Share Table Lock）在锁类型中是限制最少的，也是在表的并发程度中使用程度最高的。
允许的操作：行级共享锁由一个事务控制，允许其它事务查询、插入、更新、删除或同时在同一张表上锁定行。因此其它事务可以同时在同一张表上得到行级锁、共享行级排它锁、行级排它锁、排它锁。
禁止的操作：拥有行级锁的事务不允许其它事务执行排它锁，即：
Lock Table TableName In Exclusive Mode;

 

4.）行级排它锁（Row Exclusive Table Lock，RX）：
行级排它锁（亦称为Subexclusive Table Lock，简称SX，子排它锁）通常需要事务拥有的锁在表上被更新一行或多行。当有下面语句被执行的时候行级排它锁被加在操作的表上。
INSERT INTO TableName. . . ;
UPDATE TableName. . . ;
DELETE FROM TableName. . . ;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
行级排它锁比行级锁稍微多一些限制。
允许的操作：行级排它锁由一个事务拥有允许其它事务执行查询、修改、插入、删除或同时在同一张表上锁定行。执有行级排它锁的事务允许其它事务在同一张表上同时得到共享锁和行级排它锁。
禁止的操作：行级排它锁由一个事务拥有防止其它事务手动锁定表来排除其它事务的读写权。因此，其它事务不允许在同一张表上使用以下的语句来执行锁事务。
LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE;
LOCK TABLE table IN EXCLUSIVE MODE

 

5）共享行级排它锁（Share Row Exclusive Table Lock，SRX）：
共享行级排它锁有时也称共享子排它锁（Share Subexclusive Table Lock，SSX），它比共享锁有更多限制。定义共享行级排它锁的语法为：
Lock Table TableName In Share Row Exclusive Mode;
允许的操作：仅允许一个事务在某一时刻得到行级排它锁。拥有行级排它锁事务允许其它事务在被锁定的表上执行查询或使用Select … From TableName For update…来准确在锁定行而不能更新行。
禁止的操作：拥有行级排它锁的事务不允许其它事务有除共享锁外的其它形式的锁加在同一张表上或更新该表。即下面的语句是不被允许的：
LOCK TABLE TableName IN SHARE MODE;
LOCK TABLE TableName IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN ROW EXCLUSIVE MODE;
LOCK TABLE TableName IN EXCLUSIVE MODE;

　　当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。

 

　　2、TX锁（事务锁或行级锁）

Oracle数据库中一般使用行级锁。

 

当Oracle检测到死锁产生时，中断并回滚死锁相关语句的执行，报ORA-00060的错误并记录在数据库的日志文件alertSID.log中。同时在user_dump_dest下产生了一个跟踪文件，详细描述死锁的相关信息。

 

在日常工作中，如果发现在日志文件中记录了ora-00060的错误信息，则表明产生了死锁。这时需要找到对应的跟踪文件，根据跟踪文件的信息定位产生的原因。

 

表2 数据字典视图说明
视图名	描述	主要字段说明
v$session	查询会话的信息和锁的信息。	sid,serial#：表示会话信息。 program：表示会话的应用程序信息。 row_wait_obj#：表示等待的对象，和dba_objects中的object_id相对应。
v$session_wait	查询等待的会话信息。	sid：表示持有锁的会话信息。 Seconds_in_wait：表示等待持续的时间信息 Event：表示会话等待的事件。
v$lock	列出系统中的所有的锁。	Sid：表示持有锁的会话信息。 Type：表示锁的类型。值包括TM和TX等。 ID1：表示锁的对象标识。 lmode,request：表示会话等待的锁模式的信 息。用数字0－6表示，和表1相对应。
dba_locks	对v$lock的格式化视图。	Session_id：和v$lock中的Sid对应。 Lock_type：和v$lock中的type对应。 Lock_ID1： 和v$lock中的ID1对应。 Mode_held,mode_requested：和v$lock中 的lmode,request相对应。
v$locked_object	只包含DML的锁信息，包括回滚段和会话信息。	Xidusn,xidslot,xidsqn：表示回滚段信息。和 v$transaction相关联。 Object_id：表示被锁对象标识。 Session_id：表示持有锁的会话信息。 Locked_mode：表示会话等待的锁模式的信 息，和v$lock中的lmode一致。

 

解锁及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#';