数据库锁细分及详解

引言：

简单的数据库锁的大致类型有三种： 
共享（S)锁：多个事务可封锁一个共享页；任何事务都不能修改该页； 通常是该页被读取完毕，S锁立即被释放。 
排它（X)锁：仅允许一个事务封锁此页；其他任何事务必须等到X锁被释放才能对该页进行访问；X锁一直到事务结束才能被释放。 
更新（U)锁：用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的页将要被更新时，则升级为X锁；U锁一直到事务结束时才能被释放。

 

数据库锁细分及详解

锁包括行级锁、表级锁、悲观锁、乐观锁

行级锁:一种它锁，防止另外事务修改此行;在使用以下语句时，Oracle会自动应用行级锁：INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新.使用commit或者rollback释放锁。MySql的innodb存储引擎默认是行级锁。特点:开锁大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。适合于有大量按索引更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。

 

    表级锁:5种

   行共享 (ROW SHARE) – 禁止排他锁定表，与行排他类似，区别是别的事务还可以在此表上加任何排他锁。（除排他（exclusive）外）
   行排他(ROW EXCLUSIVE) – 禁止使用排他锁和共享锁，其他事务依然可以并发地对相同数据表执行查询，插入，更新，删除操作，或对表内数据行加锁的操作，但不能有其他的排他锁（自身是可以的，没发现有什么用）
   共享锁(SHARE) - 锁定表，对记录只读不写，多个用户可以同时在同一个表上应用此锁，在表没有被任何DML操作时，多个事务都可加锁，但只有在仅一个事务加锁的情况下只有此事务才能对表更新；当表已经被更新或者指定要更新时（select for update），任何事务都不能加此锁了。
   共享行排他(SHARE ROW EXCLUSIVE) – 比共享锁更多的限制，禁止使用共享锁及更高的锁，在表没有被任何DML操作时，只有一个事务可以加锁，可以更新，书上说别的事务可以使用select for update锁定选中的数据行，可是实验后没被验证。
   排他(EXCLUSIVE) – 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表

 行级锁：

   行级锁和表级锁是根据锁的粒度来区分的，行记录，表都是资源，锁是作用在这些资源上的。如果粒度比较小(比如行级锁)，可以增加系统的并发量但需要较大的系统开销，会影响到性能，出现死锁，，因为粒度小则操作的锁的数量会增加;如果作用在表上，粒度大，开销小，维护的锁少，不会出现死锁，但是并发是相当昂贵的，因为锁定了整个表就限制了其它事务对这个表中其他记录的访问。

   悲观锁:

   Pessimistic Lock正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守悲观态度，事务每次去操作数据的时候都假设有其他事务会修改需要访问的数据，所以在访问之前都要求上锁，行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能 真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系 统不会修改数据）。 一个典型的倚赖数据库的悲观锁调用： select * from account where name=”Erica” for update 这条sql 语句锁定了account 表中所有符合检索条件（name=”Erica”）的记录。 本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。

    Hibernate悲欢锁实现:基于数据库锁机制

         Query q=Session.createQuery("select  * from t_profit where amount>10000");

          q.setLockMode("Profit",LockMode.UPGRADE);//Profit是Profit类的别名

         List<Profit> ps=q.list();

       执行的sql:select ....from t_profit where amount>10000 for update.hibernate的悲观锁通过数据库的for update实现。

     LockMode.NONE：无锁机制;

     LockMode.WRITE:insert,update记录时自动获取悲观锁;

     LockMode.READ在读取时自动获取悲观锁;

     LockMode.UPGRADE:利用数据库的for update子句加锁;

     LockMode.UPGRADE_NOWAIT:oracle特定实现，用oracle的for update nowait子句加锁

  乐观锁:

   Optimistic Lock,和悲欢锁相反，事务每次去操作数据之前，都假设其他事务不会修改这些需要访问的数据 ，所以 在访问之前不要求上锁，只是在进行更新修改操作的时候判断一下在访问的期间有没有其他人修改数据 了。它适用于多读的应用类型，冲突真的发生比较少的时候就比较好，这样省去了开销的开销，可以提高吞吐量;但如果是真的经常要发生冲突的，那每次还要去判断进行retry,反倒降低的性能，这个时候悲欢锁比较好。数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

          它的实现大多是基于数据版本versin记录机制。举个例子:

       1.利润表t_profit中有一个 version字段，当前值为1；而总资产余额字段（balance）为$10000

       2.操作员A读出version=1,从总资产减除2000，10000-2000=8000.

       3.A还没操作结束，此时操作员B也读出version=1,总资产减除5000，10000-5000=5000.

       4.A操作完成，把version加1，修改为2，把总资产减2000后提交更新数据库，更新成功

       5.B操作了，也加version加1，修改为2，把总资产减5000后提交更新数据库，此时发现version已经为2了，如B修改后加1的version一样，不满足乐观锁策略:"提交的版本必有大于记录当前的版本才能执行"。因此B的操作请求被驳回，这样就避免了B就version=1的旧数据修改的结果覆盖了A操作的结果的可能。如没有乐观锁，那A减去2000后剩余8000，但B操作的时候是用10000-5000剩余5000的，如果B的提交成功，总资产余额就是5000，但实际情况应该是8000-5000=3000的。出现总资产表记录和实际支出不一致。

    Hibernate对乐观锁的实现:

      <hibernate-mapping>

        <class name="com.f.TProfit" table="t_profit" optimistic-lock="version"></class>

      </hibernate-mapping>

 

 

根据保护对象的不同，Oracle数据库锁可以分为以下几大类：

(1) DML lock（data locks，数据锁）：用于保护数据的完整性；

(2) DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）；

(3) Internal locks 和latches（内部锁与闩）：保护内部数据库结构；

(4) Distributed locks（分布式锁）：用于OPS（并行服务器）中；

(5) PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中。

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

意向锁的含义是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁。如：对表中的任一行加锁时，必须先对它所在的表加意向锁，然后再对该行加锁。这样一来，事务对表加锁时，就不再需要检查表中每行记录的锁标志位了，系统效率得以大大提高。

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

当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。如表1所示。

表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

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的错误。

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#';