Sybase 数据库死锁问题

 死锁的发生对系统的性能和吞吐量都有重要影响，经检测发现，管理信息系统的死锁主要是因为两个或多个线程（登录）抢占同一表数据资源。引起长时间抢占同一资源不是因为我们需要处理的事务太复杂，时间太长，而往往是因为我们在前端应用程序对数据库作操作时忘了提交。本文介绍一种处理解决这种死锁的方法。　　Sybase封锁原理　　数据共享与数据一致性是一对不可调和的矛盾，为了达到数据共享与数据一致，必须进行并发控制。并发控制的任务就是为了避免共享冲突而引起的数据不一致。Sybase sql server(WINDOWS平台上强大的数据库平台)并发控制的方法是加锁机制（LOCKING）。　　锁的类型/可申请的锁　　已有的锁 S U X 　　S ∨ ∨ × 　　U ∨ × × 　　X × × × 　　Sybase sql server(WINDOWS平台上强大的数据库平台)有三种封锁类型：排它锁（exclusive lock,简称X锁）；共享锁（share lock,简称S锁）；更新锁（update lock,简称U锁）。这三种锁的相容矩阵表如下：　　×:表示不兼容。∨：表示兼容。Sybase sql server(WINDOWS平台上强大的数据库平台)是自动决定加锁类型的。一般来说，读（SELECT）操作使用S锁，写（UPDATE,INSERT和delete）操作使用X锁。U锁是建立在页级上的，它在一个更新操作开始时获得，当要修改这些页时，U锁会升级为X锁。　　锁的力度　　sql server(WINDOWS平台上强大的数据库平台)有两级锁：页锁和表锁。通常页锁比表锁的限制更少（或更小）。页锁对本页的所有行进行锁定，而表锁则锁定整个表。为了减小用户间的数据争用和改进并发性，sql server(WINDOWS平台上强大的数据库平台)试图尽可能地使用页锁。　　当sql server(WINDOWS平台上强大的数据库平台)决定一个语句将访问整个表或表的大多数页时，它用表锁来提供更有效的锁定。锁定策略直接受查询方案约束，如果update或delete语句没有可用的索引，它就执行表扫描或请求一个表锁定。如果update或delete语句使用了索引，它就通过请求页锁来开始，如果影响到大多数行，它就要请求表锁。一旦一个语句积累的页锁超过锁提升阈值，sql server(WINDOWS平台上强大的数据库平台)就设法给该对象分配一个表锁。如果成功了，页锁就不再必要了，因此被释放。表锁也在页层提供避免锁冲突的方法。对于有些命令sql server(WINDOWS平台上强大的数据库平台)自动使用表锁。　　锁的状态　　sql server(WINDOWS平台上强大的数据库平台)加锁有三种状态：　　1）意向锁（intend）—是一种表级锁，它表示在一个数据页上获得一个S或X锁的意向。意向锁可以防止其他事务在该数据页的表上获得排它锁。　　2）阻塞（blocking,简记blk）—它表明目前加锁进程的状态，带有blk后缀的锁说明该进程目前正阻塞另一个需要获得锁的进程，只有这一进程完成，其他进程才可以进行。 3）需求锁（demand）—表示此时该进程企图得到一个排它锁。它可以防止在这一表或页上加过多的S锁，她表示某一事务是下一个去锁定该表和该页的事务。

　　需求锁是一个内部过程，因此用sp_lock是无法看见的。 Sqlclub学习社区

　　死锁DEADLOCK

　　简单地说，有两个用户进程，每个进程都在一个单独的页或表上有一个锁，而且每个进程都想在对方进程的页或表上请求不相容锁时就会发生“死锁”。在这种情况下，第一个进程在等待另一进程释放锁，但另一进程要等到第一个进程的对象释放时才会释放自己的锁。

 

　　sql server(WINDOWS平台上强大的数据库平台)检查是否死锁，并终止事务中CPU时间积累最小的用户（即最后进入的用户）。sql server(WINDOWS平台上强大的数据库平台)回滚该用户的事务，并用消息号1205通知有此死锁行为的应用程序，然后允许其他用户进程继续进行。

 

　　在多用户情形下，每个用户的应用程序都应检查每个修改数据的事务是否有1205号消息，以此确定是否有可能死锁。消息号1025表示该用户的事务因死锁而终止并被回滚。应用程序必须重新开始这个事务处理。copyright Sqlclub

　　查找死锁原因 copyright Sqlclub

　　既然管理信息系统长时间死锁的原因是由于我们提交或者是提交不当，那么我们就可以通过修改程序防止出现死锁。定位死锁出错处主要经过以下三步：

　　1）在死锁出现时，用SP_WHO,SP_LOCK获得进程与锁的活动情况。

　　2）结合库表sysobjects和相应的操作员信息表查出被锁的库表与锁住别人的操作员。

 

　　3）根据锁定的库表与操作员的岗位，可以估计出程序大约出错处。询问操作员在死锁时执行的具体操作即可完全定位出错处。最后查找程序并修改之。

 

　　用sp_who获取关于被阻碍进程的信息

 

　　系统过程sp_who给出系统进程的报告。如果用户的命令正被另一进程保持的锁阻碍，则：

 

　　◆status列显示“lock sleep”。

 

　　◆blk列显示保持该锁或这些锁的进程标识，即被谁锁定了。

 

　　◆loginame列显示登录操作员。结合相应的操作员信息表，便可知道操作员是谁。 copyright Sqlclub

Fid spid status loginame origname blk dbname cmd
0 1 lock sleep lm lm 18 QJYD SELECT
0 2 sleeping NULL NULL 0 master NETWORK HANDLER
0 3 sleeping NULL NULL 0 master NETWORK HANDLER
……

  Sqlclub学习社区

　　用sp_lock浏览锁

 

　　要得到关于当前sql server(WINDOWS平台上强大的数据库平台)上保持的锁的报告，可用系统过程sp_lock [spid1[,spid2]]，spid1,spid2是表master.dbo.sysprocesses中的sql server(WINDOWS平台上强大的数据库平台)进程id号，用sp_who可以得到锁定与被锁定的spid号：

 

　　◆locktype列显示加锁的类型和封锁的粒度，有些锁的后缀还带有blk表明锁的状态。 Sqlclub学习社区

前缀表明锁的类型：Sh—共享锁，Ex—排它锁或更新锁，中间表明锁死在表上（”table”或’intent’）还是在页上（page）. 后缀“blk”表明该进程正在障碍另一个需要请求锁的进程。一旦正在障碍的进程一结束，其他进程就向前移动。“demand”后缀表明当前共享锁一释放， 该进程就申请互斥锁。

　　◆table_id列显示表的id号，结合sysobjects即可查出被封锁的表名。

 

　　执行该进程后屏幕显示

Fid Spid locktype table_id page row dbname Class context
0 1 Sh_intent 678293476 0 0 QJYD Non Cursor LockFam dur
0 1 Sh_page 678293476 31764 0 QJYD Non Cursor Lock
0 18 Ex_intent 9767092 0 0 QJYD Non Cursor LockFam dur
…… Sqlclub学习社区

　　定位出错处

　　根据sp_who与sp_lock命令的结果，结合sysobjects和相应的操作员信息表。得到操作员及其在死锁时所操作的库表，便大约可以知道应用程序。