Sybase中的锁及死锁问题

   一般来说，数据库都会有两种锁：内存锁和对象锁。Oracle中有latch和lock，sybase中有spinlock和lock。内存锁实际上就是数据库系统将自己管理的内存区按单元加锁，以防止一个任务在使用时被另一个任务修改。用完这个内存单元后，内存锁被立即释放。不过这篇文章只会论及sybase数据库的对象锁lock。

 

1锁类型：

在sybase中的lock总共有10个类型：

1-      排他表锁

2-      共享表锁

3-      排他意图锁

4-      共享意图锁

5-      排他页锁

6-      共享页锁

7-      更新页锁

8-      排它行锁

9-      共享行锁

10-   更新行锁

 

这十类锁，从作用上来说，其实就是排他、共享和更新。其他的限定词无非说明锁的作用范围和作用时机。后面会详细说明，这里先说一下排他、共享和更新之间的兼容性问题。如下表所示：

      

	共享锁	更新锁	排他锁
共享锁	兼容	兼容	不兼容
更新锁	兼容	不兼容	不兼容
排他锁	不兼容	不兼容	不兼容

 

简单说明一下这张表：

比如共享锁和更新锁兼容，就是说加了共享锁，还可以加更新锁；

排他锁和更新锁不兼容，就是说加了排他锁，就不可以再加更新锁。

这里可以看到，加了排他锁后，就不能再加任何锁了。

2锁方案：

锁方案有三类：Allpages、Datapages、Datarows。

       Allpages：全页锁，这里全页的意思是包括索引页和数据页。

       Datapages：数据页锁，设置这种锁方案后，只会对数据页加锁。

       Datarows：数据行锁，设置这种锁方案后，只会对数据行加锁。

       这里可以看到，Datapages和Datarows只会对数据加锁，不会对数据相对应的索引加锁。    

有了锁方案的概念，就好理解排他表锁、排他页锁、排他行锁的含义了。

比如：锁方案为Datarows，则会对表加XX行锁（XX可以是排他、共享或者更新）；

锁方案为Allpages或Datapages，则会对表加XX页锁（XX可以是排他、共享或者更新）。

 

3意图锁和更新锁：

这里将意图锁和更新锁单独列出来，原因是这两种锁只在某一个时机下才有，过了那个时机就没有了。

意图锁：它是一种表级锁，表示在一个数据页上获得一个共享锁或排他锁的意图。意图锁可以防止其他事务在该数据页的表上获得排它锁。它分为排他意图锁和共享意图锁。

更新锁：它是一种页级锁，它在一个更新操作开始时获得，当要修改这些页时，更新锁会升级为排它锁。

 

4各种操作的加锁过程：

其实上面说的那么多，主要就是为了说明最开始列举的10个锁类型。下面就谈谈这10个种类型的锁在实际操作中是如何加锁的。

4.1 假定锁方案为Allpages：

4.1.1 insert操作：

       当向表中执行Insert操作时，会先在表上加一个排他意图锁，然后加上排他页锁。可以用下面的示意图表示。

排他意图锁->排他页锁

4.1.2 select操作：

       Select操作时，会先在表上加一个共享意图锁，然后加上共享页锁。示意图如下：

共享意图锁->共享页锁

4.1.3 update操作：

       Update操作时，加锁过程稍微复杂一点，首先会在表上加一个共享意图锁，然后加上共享页锁，接着会加更新页锁，最后将更新页锁升级为排他页锁。

       之所以有这么锁，实际上和update的机制有关。因为update操作在数据库中分两步执行：先select，然后update。示意图如下：

共享意图锁->共享页锁->更新页锁->排他页锁

4.1.4 delete操作：

       Delete操作时，首先会在表上加一个共享意图锁，然后加上共享页锁，最后加上排他页锁。

       和update操作相比，少了一个更新页锁，这个锁只在update过程中存在。示意图如下：

共享意图锁->共享页锁->排他页锁

4.2 假定锁方案为Datapages：

加锁方式和allpages类似，只是allpages会对需要的索引页加锁，

而datapages则不会对索引页加锁，所以叫“数据页锁”。

4.3 假定锁方案为Datarows：

4.3.1 insert操作：

       当向表中执行Insert操作时，会先在表上加一个排他意图锁，然后加上排他行锁。可以用下面的示意图表示。

排他意图锁->排他行锁

4.3.2 select操作：

       Select操作时，会先在表上加一个共享意图锁，然后加上共享行锁。示意图如下：

共享意图锁->共享行锁

4.3.3 update操作：

       Update操作时，加锁过程稍微复杂一点，首先会在表上加一个共享意图锁，然后加上共享行锁，接着会加更新行锁，最后将更新行锁升级为排他行锁。示意图如下：

共享意图锁->共享行锁->更新行锁->排他行锁

4.3.4 delete操作：

       Delete操作时，首先会在表上加一个共享意图锁，然后加上共享行锁，最后加上排他行锁。示意图如下：

共享意图锁->共享行锁->排他行锁

5锁升级：

       在上面的加锁过程中，有两类锁没有用到，排他表锁和共享表锁。这两类锁什么时候会用到呢？在两种情况下用到。

5.1 影响所有记录数：

       在执行update或delete语句时，如果影响的是整张表的记录（即不带where条件的执行语句），则会产生排他表锁。比如：

       Deletefrom Tablename

       UpdateTablename set Columnname = value

 

5.2 锁升级：

       锁升级的概念是指当细粒度的锁达到一定数量时，就会升级为粗粒度的锁。

5.2.1 行锁向表锁升级：

       当一个表上的行锁达到一定数量时，就会将行锁升级为表锁，原来的行锁全部释放。

Sybase ASE有三个参数作为行锁升级到表锁的阈值，行锁超过这个数，就会升级为表锁。根据lockschema 如果设置的是Datarows则，设置如下三个参数：

row lock promotion HWM

row lock promotion LWM

row lock promotion PCT

       其中，HWM为最大值，LWM为最小值，PCT是一个可调的百分比。

       SybaseASE 根据PCT值按公式PCT*TAB_SZ/100得出计算阀限V ，如果V < LWM, 锁升级发生在LWM值；如果V > HWM，锁升级发生在HWM值。如果 LWM < V

< HWM ，锁升级发生在PCT*TAB_SZ/100值。

其中，TAB_SZ即是number ofrows in table , 表示表的大小，以行数表示。

 

5.2.2 页锁向表锁升级：

       当一个表上的页锁达到一定数量时，就会将页锁升级为表锁，原来的页锁全部释放。

       SybaseASE有三个参数作为页锁升级到表锁的阈值，页锁超过这个数，就会升级为表锁。根据lock schema 如果设置的是Datapages则，设置如下三个参数：

page lock promotion HWM

page lock promotion LWM

page lock promotion PCT

       其中，HWM为最大值，LWM为最小值，PCT是一个可调的百分比。

Sybase ASE 根据PCT值按公式PCT*TAB_SZ/100得出计算阀限V ，如果V < LWM,锁升级发生在LWM值；如果V > HWM，锁升级发生在HWM值。如果 LWM < V

< HWM ，锁升级发生在PCT*TAB_SZ/100值。

其中，TAB_SZ即是number of pagesin table , 表示表的大小，以页数表示。

 

6死锁：

       说了那么多，主要就是要对数据库中的锁机制有一定了解，因为数据库的性能问题在很大一部分是因为锁造成的。比如：死锁和锁阻塞。

锁阻塞会让等待该锁的所有事务处于等待状态，直到得到锁为止。它对性能的影响很大。

死锁发生时，系统会自动释放锁，并回滚持有锁的事务。看起来对系统的性能没有锁阻塞大，但是逻辑问题往往造成不断的死锁发生，这对性能的影响也是很大的。

前面可以看到，锁的粒度有表级、页级和行级，同样，死锁的发生也可能在表级、页级和行级；死锁可以发生在两个表之间，也可能发生在一个表内部。

6.1 两个表之间的死锁：

       首先，新建一个会话，声明一个事务开始，对表1进行update操作。如下图：

    

再建立一个会话，然后也声明一个事务开始，对表2进行update操作。如下图：

然后，在会话1中，对表2进行update操作。如下图：

在会话2中，再对表1执行update操作。如下图：

最后，回头看看会话1，报死锁错误了。

6.2 一个表内部的死锁：

首先，新建一个会话，声明一个事务开始，对表1的第一条记录进行update操作。如下图：

      

再建立一个会话，然后也声明一个事务开始，对表1的第二条记录进行update操作。如下图：

然后，在会话1中，对表1的第二条记录进行update操作。如下图：

在会话2中，再对表1的第一条记录执行update操作。如下图：

最后，回头看看会话1，报死锁错误了。

6.3 一个存储过程引起的死锁：

       存储过程的内容如下：

CREATE PROCEDURE S_update_exception

      @device_idchar(60),

@time datetime,

@type int,

@state int      

AS    

BEGIN    

              updatedevice_exception set exception_state=@state,clear_time=@time     

              wheredevice_id=@device_id and

exception_type=@type  and exception_state=1    

    

              insertinto device_exception_history    

                     selectdevice_id, occur_time, clear_time,exception_type,

exception_level, confirm_time,confirm_user,@state,0

                         fromdevice_exception where exception_state=1    

    

              deletefrom device_exception where exception_state=0

 

              updateencoder set encoder_state=@state where device_id=@device_id       

END

      

       再看看系统记录的当时的死锁告警日志。如下：

 

       00:00000:00031:2007/08/10 21:48:49.70server  Deadlock Id 1 detected

DeadlockId 1: detected. 1 deadlock chain(s) involved.

 

DeadlockId 1: Process (Familyid 0, Spid 23, Suid 1) was executing a DELETE command inthe procedure 'S_update_exception'.

SQL Text:S_update_exception '160201111000013','2007-8-10 21:47:44',100,0

DeadlockId 1: Process (Familyid 0, Spid 31, Suid 1) was executing a UPDATE command inthe procedure 'S_update_exception'.

SQL Text:S_update_exception '160201111000015','2007-8-10 21:47:44',100,0

DeadlockId 1: Process (Familyid 0, Spid 31) was waiting for a 'update page' lock onpage 1024021 of the 'device_exception' table in database 4 but process(Familyid 0, Spid 23) already held a 'update page' lock on it.

DeadlockId 1: Process (Familyid 0, Spid 23) was waiting for a 'exclusive page' lock onpage 1280264 of th e 'device_exception' table in database 4 but process(Familyid 0, Spid 31) already held a 'update page' lock on it.

 

DeadlockId 1: Process (Familyid 0, Spid 23) was chosen as the victim. End of deadlockinformation.

 

       通过这个日志，可以看出，进程23对表device_exception进行DELETE操作时，进程31正在对表device_exception进行UPDATE操作，产生死锁，死锁产生在1024021号页和1280264号页上。

       在分析之前，我们先看一下device_exception表的定义。如下：

CREATETABLE dbo.device_exception

(

    device_id       char(50)    NOT NULL,

    occur_time      datetime    NULL,

    clear_time      datetime    NULL,

    exception_type  int        NULL,

    exception_level int         NULL,

    confirm_time    datetime   NULL,

    confirm_user    varchar(50) NULL,

    exception_state int         NULL

)

 

通过上面的日志看到一个update操作，涉及到两个页，但是这个表一行记录的总数据量只有144个字节，不可能超过一页（一页的默认大小2k），为什么是两页呢？并且分别持有了1024021页和1280264页的更新锁。

会不会一个spid中同时执行了两个存储过程呢？答案是否定的。在联系后面出现的死锁，都集中在这两个页上，而表device_exception的数据量本身不大，一个数据页应该够了。所以可能的情况是其中有一页是索引页。因为结合前面的锁方案，Allpages情况下，是会对索引页和数据页同时加锁的。

下面便是这个死锁的示意图：

       通过这个死锁案例可以看出，在一个并发性很高的表上，锁方案不能使用Allpages。