Oracle/MYSQL数据库中的锁机制研究

本文通过对Oracle数据库锁机制的研究，对死锁这一比较严重的现象，提出了相应的解决方法和具体的分析过程。

本文通过对Oracle数据库锁机制的研究，首先介绍了Oracle数据库锁的种类，并描述了实际应用中遇到的与锁相关的异常情况，特别对经常遇到的由于等待锁而使事务被挂起的问题进行了定位及解决，并对死锁这一比较严重的现象，提出了相应的解决方法和具体的分析过程。

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

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

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。

在实际应用中经常会遇到的与锁相关的异常情况，如由于等待锁事务被挂起、死锁等现象，如果不能及时地解决，将严重影响应用的正常执行，而目前对于该类问题的解决缺乏系统化研究和指导，本文在总结实际经验的基础上，提出了相应的解决方法和具体的分析过程。

Oracle数据库的锁类型

根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁（data locks，数据锁），用于保护数据的完整性；DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；内部锁和闩（internal locks and latches），保护数据库的内部结构。

DML锁的目的在于保证并发情况下的数据完整性，本文主要讨论DML锁。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

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

在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

TX锁等待的分析

在介绍了有关地Oracle数据库锁的种类后，下面讨论如何有效地监控和解决锁等待现象，及在产生死锁时如何定位死锁的原因。

监控锁的相关视图 数据字典是Oracle数据库的重要组成部分，用户可以通过查询数据字典视图来获得数据库的信息。和锁相关的数据字典视图如表2所示。

TX锁等待的监控和解决在日常工作中，如果发现在执行某条SQL时数据库长时间没有响应，很可能是产生了TX锁等待的现象。为解决这个问题，首先应该找出持锁的事务，然后再进行相关的处理，如提交事务或强行中断事务。

死锁的监控和解决在数据库中，当两个或多个会话请求同一个资源时会产生死锁的现象。死锁的常见类型是行级锁死锁和页级锁死锁，Oracle数据库中一般使用行级锁。下面主要讨论行级锁的死锁现象。

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

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

如果查询结果表明，死锁是由于bitmap索引引起的，将IND_T_PRODUCT_HIS_STATE索引改为normal索引后，即可解决死锁的问题。

表1 Oracle的TM锁类型锁模式锁描述解释SQL操作0none  1NULL空Select2SS(Row-S)行级共享锁，其他对象只能查询这些数据行

Select for update、Lock for update、Lock row share

3SX(Row-X)行级排它锁，在提交前不允许做DML操作

Insert、Update、Delete、Lock row share

4S(Share)共享锁Create index、Lock share5SSX(S/Row-X)共享行级排它锁Lock share row exclusive6X(Exclusive)排它锁

Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

 

表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一致。

在运用排他锁和共享锁 对数据对象加锁时，还需要约定一些规则，例如何时申请排他锁或共享锁、持锁时间、何时释放等。称这些规则为封锁协议（Locking Protocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。
1、一级封锁协议 (对应 read uncommited) 　　
     一级封锁协议是：事务 在对需要修改的数据上面（就是在发生修改的瞬间） 对其加共享锁（其他事务不能更改，但是可以读取-导致“脏读”），直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。
     一级封锁协议不能避免 丢失更新，脏读，不可重复读，幻读！


2、二级封锁协议 （对应read commited)
     二级封锁协议是：1）事务 在对需要更新的数据 上（就是发生更新的瞬间） 加 排他锁 （直到事务结束） ， 防止其他事务读取未提交的数据，这样，也就避免了 “脏读” 的情况。2）事务 对当前被读取的数据 上面加共享锁 （当读到时加上共享锁），一旦读完该行，立即 释放该 该行的共享锁 - 从数据库的底层实现更深入的来理解，既是，数据库会对游标当前的数据上加共享锁 ， 但是当游标离开当前行的时候，立即释放该行的共享锁。　
     二级封锁协议除防止了“脏读”数据，但是不能避免 丢失更新，不可重复读，幻读 。
     但在二级封锁协议中，由于读完数据后立即 释放共享锁，所以它不能避免可重复读 ，同时它也不能避免 丢失更新 ，如果事务A、B同时获取资源X，然后事务A先发起更新记录X，那么 事务B 将等待事务 A 执行完成，然后获得记录X 的排他锁，进行更改。这样事务 A 的更新将会被丢失。 具体情况如下：
 
事务A 事务B
读取X=100（同时上共享锁） 读取X=100（同时上共享锁）
读取成功（释放共享锁） 读取成功（释放共享锁）
UPDATE X=X+100 （上排他锁）
UPDATING A（等待事务A释放对X的排他锁）
事务成功（释放排他锁）X=200
UPDATE X=X+200（成功上排他锁）
事务成功（释放排他锁）X=300
 
由此可以看到，事务A的提交被事务B覆盖了，所以不能防止 丢失更新。
如果要避免 丢失更新，我们需要额外的操作， 对凡是读到的数据加 共享锁 和排他锁 ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是该事务读到的数据，额外的加上排他锁，防止其他数据同一时间获取相同数据，这样就防止了 丢失更新 ！ 


3、三级封锁协议 （对应reapetable read ）
      三级封锁协议是：二级封锁协议加上事务 在读取数据的瞬间 必须先对其加 共享锁 ，但是 直到事务结束才释放 ，这样保证了可重复读（既是其他的事务职能读取该数据，但是不能更新该数据）。　
      三级封锁协议除防止了“脏”数据 和不可重复读 。但是这种情况不能避免 幻读 和 丢失更新 的情况，在事务 A 没有完成之前，事务 B 可以新增数据，那么 当事务 A 再次读取的时候，事务B 新增的数据会被读取到，这样，在该封锁协议下，幻读 就产生了。 如果事务A 和 事务B 同时读取了资源X=100，同样，如果事务A先对X进行 更新X=X+100，等待事务A执行完成X=200，那么事务B 获得X的排他锁，进行更新 X=X+200，然后提交 X=300，同样A的更新被B所覆盖！( 如果要避免 丢失更新，我们需要额外的操作， 对凡是读到的数据加 共享锁 和排他锁 ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是读到的数据，我会加 排他锁，防止其他数据同一时间获取相同数据) ！ 


      进阶：repeatable read 导致死锁的情况（即便是 不同的资源在相同的顺序下获取）。 比如 事务1 读取 A，同时 事务2 也读取 A，那么事务1和事务2 同时对 A 上了共享锁，然后事务1 要UPDATE A，而此时 事务2 也要 UPDATE A，这个时候 事务1 等待 事务2 释放其在 A 上的共享锁，然后 事务2 要等待 事务1 释放其在 A 上的共享锁，这样，事务1 和 事务2 相互等待，产生死锁！（SQL Server/DB2 里面有 UPDATE LOCK 可以解决这种情况，具体的思路是，在 repeatable read 的情况下，将读取的数据 上的 UPDATE 锁，介于 共享锁 和 排他锁之间的一种锁，该锁的作用是 当出现上面这种情况后，事务1 和 事务2 对 A 上的是 UPDATE 锁，那么谁先 要修改 A，那么该事务就会将 UPDATE 锁可以顺利升级为 排他锁对该数据进行修改！）
 
4、最强封锁协议（对应Serialization)
      四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对 事务中 所 读取 或者 更改的数据所在的表加表锁，也就是说，其他事务不能 读写 该表中的任何数据。这样所有的 脏读，不可重复读，幻读 ，都得以避免！

追加：

DML锁与DDL锁
所有DML语句都至少需要两种锁定:受影响记录上的排他锁,以及包含受影响记录的表上的共享锁.排他锁能够防止其他会话干预指定的记录,而共享锁则能够阻止其他会话使用DDL语句修改表的定义.这两种锁定会被自动请求.如果某条DML语句在指定记录上无法获取所需的排他锁,那么这条语句会被挂起直至获得所需的排他锁.
执行DDL命令需要使用所涉及对象上的排他锁.只有在针对指定表的所有DML事务结束,并且记录上的排他锁以及表上的共享锁都被解除之后,我们才可以获得执行DDL命令所需的排他锁,任何DDL语句所需的排他锁都是被自动请求的.但是,如果无法获取所需的排他锁(通常是因为其他会话已经获得用于DML语句的共享锁),那么DDL语句就会由于错误立即终

例子:
1.       使用SQL*PLUS,作为用户SYSTEM连接数据库.
2.       创建一个表,并且在这个表中插入一条记录.
>create table t1(c1 number);
       >insert into t1 values(1);
       >commit;
3.再次使用SQL*PLUS并作为用户SYSTEM进行连接,从而打开另一个会话.
4.在第一个会话中执行一个DML命令,这个命令会在插入的记录上放置一个排他锁,同时还会在创建的表上放置一个共享锁.
   >update table t1 set c1=2 where c1=1;
5.如下所示,在第二个会话中执行第一条针对新建表的DDL语句.
   >alter table t1 add(c2 date);
   error at line 1:
ora-00054:resource busy and acquire with nowait specified
因为DDL语句需要表上的排他锁,而这与DML语句已在表上放置了共享锁相冲突,所以试图在表中插入一个列的这条DDL语句会失败.需要注意的是:在类似情况下,DML语句会等待并不断进行尝试,直至获得其所需的锁(换句话说就是挂起);而DDL语句则会由于错误立即终止.
       6.在第一个会话中,提交当前事务
          >commit;
       7.在第二个会话中,重新执行步骤5.此时,因为不纯在与DDL排他锁相冲突的DML共享锁,因此DDL语句将成功的执行.
       8.在第一个会话中 ,锁定整个表.
        >lock table t1 in exclusive mode;
       9.在第二个会话中,插入一条记录.此时,这个会话将被挂起.
        >insert into t1 values (1,sysdate);
       10.在第一个会话中,通过执行COMMIT命令解除整个表上的锁定.需要注意的是,ROLLBACK命令也可以实现相同的目的.
        >commit;
       11.第二个会话会释放并且现在会完成插入操作.随后,执行COMMIT命令,终止当前事务斌且解除该记录上的排他锁.

关于如何解决死锁的问题.
１.查哪个过程被锁
查V$DB_OBJECT_CACHE视图:
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER=''过程的所属用户'' AND LOCKS!=''0'';

2. 查是哪一个SID,通过SID可知道是哪个SESSION.
查V$ACCESS视图:
SELECT * FROM V$ACCESS WHERE OWNER=''过程的所属用户'' AND NAME=''刚才查到的过程名".

MYSQL锁机制分析

下面主要介绍了MySQL行级锁、表级锁、页级锁详细介绍,同时列举了一些实例说明。

页级:引擎 BDB。
表级:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行
行级:引擎 INNODB ， 单独的一行记录加锁

表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级,，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。
对WRITE，MySQL使用的表锁定方法原理如下：
如果在表上没有锁，在它上面放一个写锁。
否则，把锁定请求放在写锁定队列中。

对READ，MySQL使用的锁定方法原理如下：
如果在表上没有写锁定，把一个读锁定放在它上面   
否则，把锁请求放在读锁定队列中。

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。 

行级锁定的优点：
·         当在许多线程中访问不同的行时只存在少量锁定冲突。
·         回滚时只有少量的更改。
·         可以长时间锁定单一的行。

行级锁定的缺点：
·         比页级或表级锁定占用更多的内存。
·         当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
·         如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
·         用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

行级锁的优点有：
 在很多线程请求不同记录时减少冲突锁。
 事务回滚时减少改变数据。
 使长时间对单独的一行记录加锁成为可能。

行级锁的缺点有：
 比页级锁和表级锁消耗更多的内存。
 锁是计算机协调多个进程或线程并发访问某一资源的机制，不同的数据库的锁机制大同小异。由于数据库资源是一种供许多用户共享的资源，所以如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。了解锁机制不仅可以使我们更有效的开发利用数据库资源，也使我们能够更好地维护数据库，从而提高数据库的性能。

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

例如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level-locking）；BDB存储引擎采用的是页面锁（page-level-locking），同时也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下是采用行级锁。

上述三种锁的特性可大致归纳如下：
1） 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

     三种锁各有各的特点，若仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

     MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞 对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

     MyISAM表的读和写是串行的，即在进行读操作时不能进行写操作，反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行，其机制是通过控制一个系统变量（concurrent_insert）来进行的，当其值设置为0时，不允许并发插入；当其值设置为1 时，如果MyISAM表中没有空洞（即表中没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录；当其值设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

     MyISAM锁调度是如何实现的呢，这也是一个很关键的问题。例如，当一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，此时MySQL将会如优先处理进程呢？通过研究表明，写进程将先获得锁（即使读请求先到锁等待队列）。但这也造成一个很大的缺陷，即大量的写操作会造成查询操作很难获得读锁，从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates，使MyISAM默认引擎给予读请求以优先的权利，设置其值为1（set low_priority_updates=1),使优先级降低。

     InnoDB锁与MyISAM锁的最大不同在于：一是支持事务（TRANCSACTION），二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元，其有四个属性（简称ACID属性），分别为：

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行；
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态；
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行；
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

InnoDB有两种模式的行锁：

1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
    ( Select * from table_name where ......lock in share mode)

2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和  排他写锁。(select * from table_name where.....for update)
    为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。
    InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！

/* ==================== MySQL InnoDB 锁表与锁行 ======================== */

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单products ，里面有id跟name二个栏位，id是主键。

例1: (明确指定主键，并且有此笔资料，row lock)

复制代码代码如下:

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键，若查无此笔资料，无lock)

复制代码代码如下:

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键，table lock)

复制代码代码如下:

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确，table lock)

复制代码代码如下:

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确，table lock)

复制代码代码如下:

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。