深入解析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

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,