Sybase中的锁及死锁问题

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 = value5.2 锁升级： 锁升级的概念是指当细粒度的锁达到一定数量时，就会升级为粗粒度的锁。5.2.1 行锁向表锁升级： 当一个表上的行锁达到一定数量时，就会将行锁升级为表锁，原来的行锁全部释放。Sybase ASE有三个参数作为行锁升级到表锁的阈值，行锁超过这个数，就会升级为表锁。根据lockschema 如果设置的是Datarows则，设置如下三个参数：row lock promotion HWMrow lock promotion LWMrow lock promotion PCT 其中，HWM为最大值，LWM为最小值，PCT是一个可调的百分比。 SybaseASE 根据PCT值按公式PCT*TAB_SZ/100得出计算阀限V ，如果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 HWMpage lock promotion LWMpage lock promotion PCT 其中，HWM为最大值，LWM为最小值，PCT是一个可调的百分比。Sybase ASE 根据PCT值按公式PCT*TAB_SZ/100得出计算阀限V ，如果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 intASBEGIN updatedevice_exception set exception_state=state,clear_time=time wheredevice_id=device_id andexception_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_idEND 再看看系统记录的当时的死锁告警日志。如下： 00:00000:00031:2007/08/10 21:48:49.70server Deadlock Id 1 detectedDeadlockId 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,0DeadlockId 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,0DeadlockId 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 intNULL, exception_level int NULL, confirm_time datetimeNULL, confirm_user varchar(50) NULL, exception_state int NULL)通过上面的日志看到一个upda