多种数据库的并发控制比较

 

多种数据库的并发控制比较

内容：

比较的ORACLE INFORMIX DB2 SYBASE MSSQL等的并发控制机制。比较分析悲观与乐观并发控制机制的异同。

引言：

l       在关系数据库（DB2，Oracle，Sybase，Informix和SQL Server）最小的恢复和交易单位为一个事务（Transactions）,事务具有ACID(原子性，一致性，隔离性和永久性)特征。关系数据库为了确保并发用户在存取同一数据库对象时的正确性（即无丢失更新、可重复读、不读"脏"数据，无"幻像"读），数据库中引入了并发（锁）机制。基本的锁类型有两种：排它锁（Exclusive locks记为X锁）和共享锁（Share locks记为S锁）。

l       排它锁：若事务T对数据D加X锁，则其它任何事务都不能再对D加任何类型的锁，直至T释放D上的X锁；一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。

l       共享锁：若事务T对数据D加S锁，则其它事务只能对D加S锁，而不能加X锁，直至T释放D上的S锁；一般要求在读取数据前要向该数据加共享锁，所以共享锁又称为读锁。

 

 

 

相关解释

l 页：以磁盘页面（disk pages）为单位存储数据。一个磁盘页面包含一个或多个记录。

l 键字级：Informix(在一定情况下，数据库服务器需要锁一个不存在的记录。它的效果相当于在记录将要存在的地方放一个锁。当表使用记录锁时，对假想的记录使用键字锁。当表使用页级锁时，含有或可能含有键字的索引页将被设置键级锁)。MSSQL(索引中的行锁。用于保护可串行事务中的键范围)。

l 扩展盘区：相邻的八个数据页或索引页构成的一组。

l 表空间：DB2 的表空间按管理方式分为两种：系统管理空间和数据库管理空间。 按类型分为：规则表空间、长整数表空间、系统临时表空间、用户临时表空间 。其中长整数表空间只能是DMS的。规则表空间中包含用户数据的表。默认用户表空间名为USERSPACE1，索引也存在规则表空间中，另外系统目录表也放在规则表空间中。

 

 

DB2 数据库表锁的模式

 

几种表锁的模式进一步阐述：

lIS、IX、SIX方式用于表一级并需要行锁配合，他们可以阻止其他应用程序对该表加上排它锁。

l如果一个应用程序获得某表的IS锁，该应用程序可获得某一行上的S锁，用于只读操作，同时其他应用程序也可以读取该行，或是对表中的其他行进行更改。

l如果一个应用程序获得某表的IX锁，该应用程序可获得某一行上的X锁，用于更改操作，同时其他应用程序可以读取或更改表中的其他行。

l如果一个应用程序获得某表的SIX锁，该应用程序可以获得某一行上的X锁，用于更改操作，同时其他应用程序只能对表中其他行进行只读操作。

几种表锁的模式进一步阐述：

lS、U、X和Z方式用于表一级，但并不需要行锁配合，是比较严格的表加锁策略。

l如果一个应用程序得到某表的S锁。该应用程序可以读表中的任何数据。同时它允许其他应用程序获得该表上的只读请求锁。如果有应用程序需要更改读该表上的数据，必须等S锁被释放。

l如果一个应用程序得到某表的U锁，该应用程序可以读表中的任何数据，并最终可以通过获得表上的X锁来得到对表中任何数据的修改权。其他应用程序只能读取该表中的数据。U锁与S锁的区别主要在于更改的意图上。U锁的设计主要是为了避免两个应用程序在拥有S锁的情况下同时申请X锁而造成死锁的。

l如果一个应用程序得到某表上的X锁，该应用程序可以读或修改表中的任何数据。其他应用程序不能对该表进行读或者更改操作。

l如果一个应用程序得到某表上的Z锁，该应用程序可以读或修改表中的任何数据。其他应用程序，包括未提交读程序都不能对该表进行读或者更改操作。

lIN锁用于表上以允许未提交读这一概念。

DB2 数据库行锁的模式

 

DB2 数据库表锁的相容矩阵

 

DB2 数据库行锁的相容矩阵

 

DB2 中各SQL 语句产生表锁的情况（假设缺省的隔离级别为CS ）：

 

DB2 锁的升级 (1)

l每个锁在内存中都需要一定的内存空间，为了减少锁需要的内存开销，DB2提供了锁升级的功能。锁升级是通过对表加上非意图性的表锁，同时释放行锁来减少锁的数目，从而达到减少锁需要的内存开销的目的。锁升级是由数据库管理器自动完成的，有两个数据库的配置参数直接影响锁升级的处理：

llocklist--在一个数据库全局内存中用于锁存储的内存。单位为页（4K）。

lmaxlocks--一个应用程序允许得到的锁占用的内存所占locklist大小的百分比。

l锁升级会在这两种情况下被触发：

l某个应用程序请求的锁所占用的内存空间超出了maxlocks与locklist的乘积大小。这时，数据库管理器将试图通过为提出锁请求的应用程序申请表锁，并释放行锁来节省空间。

DB2 锁的升级 (2)

l在一个数据库中已被加上的全部锁所占的内存空间超出了locklist定义的大小。这时，数据库管理器也将试图通过为提出锁请求的应用程序申请表锁，并释放行锁来节省空间。

l锁升级虽然会降低OLTP应用程序的并发性能，但是锁升级后会释放锁占有内存并增大可用的锁的内存空间。

l锁升级是有可能会失败的，比如，现在一个应用程序已经在一个表上加有IX锁，表中的某些行上加有X锁，另一个应用程序又来请求表上的IS锁，以及很多行上的S锁，由于申请的锁数目过多引起锁的升级。数据库管理器试图为该应用程序申请表上的S锁来减少所需要的锁的数目，但S锁与表上原有的IX锁冲突，锁升级不能成功。

l如果锁升级失败，引起锁升级的应用程序将接到一个-912的SQLCODE。在锁升级失败后，DBA应该考虑增加locklist的大小或者增大maxlocks的百分比。同时对编程人员来说可以在程序里对发生锁升级后程序回滚后重新提交事务（例如：if sqlca.sqlcode=-912 then rollback and retry等）。

 

Oracle 多粒度锁机制介绍

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

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

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

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

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

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

l在Oracle中最主要的锁是DML（也可称为data locks，数据锁）锁。从封锁粒度（封锁对象的大小）的角度看，Oracle DML锁共有两个层次，即行级锁和表级锁。

 

Oracle 的TX 锁行级锁、事务锁

l许多对Oracle不太了解的技术人员可能会以为每一个TX锁代表一条被封锁的数据行，其实不然。 TX 的本义是Transaction （ 事务）当一个事务第一次执行数据更改（Insert、Update、Delete）或使用SELECT… FOR UPDATE语句进行查询时，它即获得一个TX（事务）锁，直至该事务结束（执行COMMIT或ROLLBACK操作）时，该锁才被释放。所以，一个TX锁，可以对应多个被该事务锁定的数据行（在我们用的时候多是启动一个事务，然后SELECT… FOR UPDATE NOWAIT）。

l 在Oracle 的每行数据上，都有一个标志位来表示该行数据是否被锁定。Oracle 不像DB2 那样，建立一个链表来维护每一行被加锁的数据，这样就大大减小了行级锁的维护开销，也在很大程度上避免了类似DB2 使用行级锁时经常发生的锁数量不够而进行锁升级的情况。数据行上的锁标志一旦被置位，就表明该行数据被加X 锁，Oracle 在数据行上没有S 锁。

Oracle 意向锁

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

l由两种基本的锁类型（S锁、X锁），可以自然地派生出两种意向锁：

l另外， 基本的锁类型（ S 、X ）与 意向锁类型（ IS 、IX ）之间还可以组合出新的锁类型，理论上可以组合出4种，即：S+IS，S+IX，X+IS，X+IX，但稍加分析不难看出，实际上只有S+IX有新的意义，其它三种组合都没有使锁的强度得到提高（即：S+IS=S，X+IS=X，X+IX=X，这里的"="指锁的强度相同）。所谓锁的强度是指对其它锁的排斥程度。

l这样我们又可以引入一种新的锁的类型： 共享意向排它锁（ Shared Intent Exclusive Lock, 简称SIX 锁）：如果对一个数据库对象加SIX锁，表示对它加S锁，再加IX锁，即SIX=S+IX。例如：事务对某个表加SIX锁，则表示该事务要读整个表（所以要对该表加S锁），同时会更新个别行（所以要对该表加IX锁）。

l具有意向锁的多粒度封锁方法中任意事务T要对一个数据库对象加锁，必须先对它的上层结点加意向锁。申请封锁时应按自上而下的次序进行；释放封锁时则应按自下而上的次序进行；具有意向锁的多粒度封锁方法提高了系统的并发度，减少了加锁和解锁的开销。

Oracle 的TM 锁（表级锁）

    

Oracle 数据库TM 锁小结

 

多粒度封锁机制的监控

l快照监控

l事件监控方式

l视图（Oracle）

v$lock视图列出当前系统持有的或正在申请的所有锁的情况

 

v$locked_object视图列出当前系统中哪些对象正被锁定

 

总结 (1)

l1．Oracle通过具有意向锁的多粒度封锁机制进行并发控制，保证数据的一致性。其DML锁（数据锁）分为两个层次（粒度）：即表级和行级。通常的DML操作在表级获得的只是意向锁（RS或RX），其真正的封锁粒度还是在行级；DB2也是通过具有意向锁的多粒度封锁机制进行并发控制，保证数据的一致性。其DML锁（数据锁）分为两个层次（粒度）：即表级和行级。通常的DML操作在表级获得的只是意向锁（IS，SIX或IX），其真正的封锁粒度也是在行级；另外，在Oracle数据库中，单纯地读数据（SELECT）并不加锁，这些都提高了系统的并发程度，Oracle强调的是能够"读"到数据，并且能够快速的进行数据读取。而DB2的锁强调的是"读一致性"，进行读数据(SELECT)时会根据不同的隔离级别（RR,RS,CS）而分别加S,IS,IS锁，只有在使用UR隔离级别时才不加锁。从而保证不同应用程序和用户读取的数据是一致的。

总结 (2)

l2. 在支持高并发度的同时，DB2和Oracle对锁的操纵机制有所不同：Oracle利用意向锁及数据行上加锁标志位等设计技巧，减小了Oracle维护行级锁的开销，使其在数据库并发控制方面有着一定的优势。而DB2中对每个锁会在锁的内存（locklist）中申请分配一定字节的内存空间，具体是X锁64字节内存，S锁32字节内存（注：DB2 V8之前是X锁72字节内存而S锁36字节内存）。

l3. Oracle数据库中不存在锁升级，而DB2数据库中当数据库表中行级锁的使用超过locklist*maxlocks会发生锁升级。

l4. 在Oracle中当一个session对表进行insert，update，delete时候，另外一个session仍然可以从Orace回滚段或者还原表空间中读取该表的前映象（before image）; 而在DB2中当一个session对表进行insert，update，delete时候，另外一个session仍然在读取该表数据时候会处于lock wait状态，除非使用UR隔离级别可以读取第一个session的未提交的值；所以Oracle同一时刻不同的session有读不一致的现象，而DB2在同一时刻所有的session都是"读一致"的。

 

SQLServer使用以下资源锁模式

 

l共享 (S):用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。

l更新 (U):    用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

l排它 (X):    用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更新。

l意向:用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

l架构:在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

l大容量更新 (BU):向表中大容量复制数据并指定了 TABLOCK 提示时使用。

架构锁
 

l执行表的数据定义语言 (DDL) 操作（例如添加列或除去表）时使用架构修改 (Sch-M) 锁。

l当编译查询时，使用架构稳定性 (Sch-S) 锁。架构稳定性 (Sch-S) 锁不阻塞任何事务锁，包括排它 (X) 锁。因此在编译查询时，其它事务（包括在表上有排它 (X) 锁的事务）都能继续运行。但不能在表上执行 DDL 操作。

大容量更新锁

l当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新 (BU) 锁。大容量更新 (BU) 锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。

 

Sybase 锁的状态

lSQL SERVER加锁有三种状态：

l1）意向锁（intend）—是一种表级锁，它表示在一个数据页上获得一个S或X锁的意向。意向锁可以防止其他事务在该数据页的表上获得排它锁。

l2）阻塞（blocking,简记blk）—它表明目前加锁进程的状态，带有blk后缀的锁说明该进程目前正阻塞另一个需要获得锁的进程，只有这一进程完成，其他进程才可以进行。

l3）需求锁（demand）—表示此时该进程企图得到一个排它锁。它可以防止在这一表或页上加过多的S锁，她表示某一事务是下一个去锁定该表和该页的事务。

l需求锁是一个内部过程，因此用sp_lock是无法看见的。

 

Informix

l锁的类型 INFORMIX有三种不同类型的锁。它们在不同的情况下使用。
1. SHARED锁
SHARED锁只保留对象的可读性。当锁存在时，对象不能改变。多个程序可对同个对象加SHARED锁。
2. EXCLUSIVE锁
只能使单个程序使用。在程序要改变对象时使用。当其他锁存在时，EXCLUSIVE锁不能使用。当使用了EXCLUSIVE 锁后，其他锁不能用于同一对象。
3. PROMOTABLE锁
实现更新的目的。PROMOTABLE锁可以放在已经有SHARED锁的记录，但不能放在已经有PROMOTABLE锁和EXCLUSIVE 锁的地方。当记录上无其他锁（含SHARED 锁）情况下，这时在程序准备改变锁的记录时，PROMOTABLE锁可以提升为EXCLUSIVE锁。如果在已有SHARED锁的记录上设置了PROMOTABLE锁，在PROMOTABLE锁可以提升到EXCLUSIVE锁
之前需要删除SHARED 锁。PROMOTABLE锁只能在INFORMIX Universal Server中支持。

 

END

Thank You