解决Sybase数据库死锁

Sybase数据库死锁
  
  　　死锁的发生对系统的性能和吞吐量都有重要影响，经检测发现，管理信息系统的死锁主要是因为两个或多个线程（登录）抢占同一表数据资源。引起长时间抢占同一资源不是因为我们需要处理的事务太复杂，时间太长，而往往是因为我们在前端应用程序对数据库作操作时忘了提交.本文介绍一种处理解决这种死锁的方法。 

Sybase封锁原理

　　数据共享与数据一致性是一对不可调和的矛盾，为了达到数据共享与数据一致，必须进行并发控制。并发控制的任务就是为了避免共享冲突而引起的数据不一致。Sybase SQL Server并发控制的方法是加锁机制（LOCKING）.

锁的类型 　　可申请的锁
已有的锁 S U X 
S ∨ ∨ × 
U ∨ × × 
X × × × 
　　Sybase SQL Server有三种封锁类型：排它锁（exclusive lock,简称X锁）；共享锁（share lock,简称S锁）；更新锁（update lock,简称U锁）。这三种锁的相容矩阵表如下：
　　×:表示不兼容。∨：表示兼容。　　Sybase SQL Server是自动决定加锁类型的。一般来说，读（SELECT）操作使用S锁，写（UPDATE,INSERT和delete）操作使用X锁。U锁是建立在页级上的，它在一个更新操作开始时获得，当要修改这些页时，U锁会升级为X锁。

锁的力度
　　SQL Server有两级锁：页锁和表锁。通常页锁比表锁的限制更少（或更小）。页锁对本页的所有行进行锁定，而表锁则锁定整个表。为了减小用户间的数据争用和改进并发性，SQL Server试图尽可能地使用页锁。
　　当SQL Server决定一个语句将访问整个表或表的大多数页时，它用表锁来提供更有效的锁定。锁定策略直接受查询方案约束，如果update或delete语句没有可用的索引，它就执行表扫描或请求一个表锁定。如果update或delete语句使用了索引，它就通过请求页锁来开始，如果影响到大多数行，它就要请求表锁。一旦一个语句积累的页锁超过锁提升阈值，SQL Server就设法给该对象分配一个表锁。如果成功了，页锁就不再必要了，因此被释放。表锁也在页层提供避免锁冲突的方法。对于有些命令SQL Server自动使用表锁。

锁的状态
　　SQL SERVER加锁有三种状态：
　　1）意向锁（intend）—是一种表级锁，它表示在一个数据页上获得一个S或X锁的意向。意向锁可以防止其他事务在该数据页的表上获得排它锁。
　　2）阻塞（blocking,简记blk）—它表明目前加锁进程的状态，带有blk后缀的锁说明该进程目前正阻塞另一个需要获得锁的进程，只有这一进程完成，其他进程才可以进行。
　　3）需求锁（demand）—表示此时该进程企图得到一个排它锁。它可以防止在这一表或页上加过多的S锁，她表示某一事务是下一个去锁定该表和该页的事务。
　　需求锁是一个内部过程，因此用sp_lock是无法看见的。

死锁DEADLOCK
　　简单地说，有两个用户进程，每个进程都在一个单独的页或表上有一个锁，而且每个进程都想在对方进程的页或表上请求不相容锁时就会发生“死锁”。在这种情况下，第一个进程在等待另一进程释放锁，但另一进程要等到第一个进程的对象释放时才会释放自己的锁。
　　SQL Server检查是否死锁，并终止事务中CPU时间积累最小的用户（即最后进入的用户）。SQL Server回滚该用户的事务，并用消息号1205通知有此死锁行为的应用程序，然后允许其他用户进程继续进行。
　　在多用户情形下，每个用户的应用程序都应检查每个修改数据的事务是否有1205号消息，以此确定是否有可能死锁。消息号1025表示该用户的事务因死锁而终止并被回滚。应用程序必须重新开始这个事务处理。

查找死锁原因

　　既然管理信息系统长时间死锁的原因是由于我们提交或者是提交不当，那么我们就可以通过修改程序防止出现死锁。定位死锁出错处主要经过以下三步：
　　1）在死锁出现时，用SP_WHO,SP_LOCK获得进程与锁的活动情况。
　　2）结合库表sysobjects和相应的操作员信息表查出被锁的库表与锁住别人的操作员。
　　3）根据锁定的库表与操作员的岗位，可以估计出程序大约出错处。询问操作员在死锁时执行的具体操作即可完全定位出错处。最后查找程序并修改之。

用sp_who获取关于被阻碍进程的信息
　　系统过程sp_who给出系统进程的报告。如果用户的命令正被另一进程保持的锁阻碍，则：
　　◆status列显示“lock sleep”。
　　◆blk列显示保持该锁或这些锁的进程标识，即被谁锁定了。
　　◆loginame列显示登录操作员。结合相应的操作员信息表，便可知道操作员是谁。
　　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 
　　……

用sp_lock浏览锁
　　要得到关于当前SQL Server上保持的锁的报告，可用系统过程sp_lock [spid1[,spid2]]，spid1,spid2是表master.dbo.sysprocesses中的sql server进程id号，用sp_who可以得到锁定与被锁定的spid号：
　　◆locktype列显示加锁的类型和封锁的粒度，有些锁的后缀还带有blk表明锁的状态。前缀表明锁的类型：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 
　　……

定位出错处
　　根据sp_who与sp_lock命令的结果，结合sysobjects和相应的操作员信息表。得到操作员及其在死锁时所操作的库表，便大约可以知道应用程序的出错处，再询问操作员在死锁时执行什么操作以进一步认证。最后查找程序并修正之。

 

--------------------------------------------------------------------------------
 zhaopingzi 回复于：2005-10-14 11:32:10

ding

--------------------------------------------------------------------------------
 ak472000 回复于：2005-10-14 12:20:55

又长了知识了，呵呵！

--------------------------------------------------------------------------------
 caoshangfei 回复于：2005-10-17 09:39:07

不錯！

--------------------------------------------------------------------------------
 dyhlx 回复于：2005-10-17 13:19:54

非常不错

--------------------------------------------------------------------------------
 zhhui2000 回复于：2005-10-17 15:23:19

hao yin.

--------------------------------------------------------------------------------
 sunfenglili 回复于：2005-10-17 15:53:38

马上就要从Oracle转成Sybase做项目了
第一次发帖 赫赫 ！！

--------------------------------------------------------------------------------
 flykite 回复于：2005-10-27 16:11:47

good~

--------------------------------------------------------------------------------
 jtyhyl 回复于：2005-10-31 13:20:39

很好的资料

--------------------------------------------------------------------------------
 1017of 回复于：2005-11-01 09:27:04

不好意思，由于系统会自动牺牲死锁的一方，偶觉得死锁一般用sp_who和sp_lock很难看到。可使用打开"print deadlock information"选项，死锁信息会打印在errorlog中，但同时可能会带了部分性能的降低。

--------------------------------------------------------------------------------
 qyfeng2002 回复于：2005-11-22 10:19:37

不错，不过已我的经验都是用DBArtisan或PADB等工具软件来检测出现问题的SYBASE，一发现有blk的，将其sql可以抓下来，而且很容易的就能将其处理。sp_who、sp_lock等输出的信息太多而且也不太好看懂。建议大家最好弄个DBArtisan来用用，很不错的！

--------------------------------------------------------------------------------
 goodcjh2005 回复于：2005-12-01 13:00:30

自己顶一下

--------------------------------------------------------------------------------
 大大狗 回复于：2005-12-07 15:27:30

学习了，谢谢楼主

--------------------------------------------------------------------------------
 ncowboy 回复于：2005-12-07 15:29:48

引用：原帖由 qyfeng2002 于 2005-11-22 10:19 发表
不错，不过已我的经验都是用DBArtisan或PADB等工具软件来检测出现问题的SYBASE，一发现有blk的，将其sql可以抓下来，而且很容易的就能将其处理。sp_who、sp_lock等输出的信息太多而且也不太好看懂。建议大家最好弄 ...

:em02::em02:
强烈支持，我的dbartisan7.2.1和sybase ase 12.5配合的很好。

--------------------------------------------------------------------------------
 black_chr 回复于：2005-12-08 09:05:25

请问哪里可以弄到dbartisan7.2.1？

--------------------------------------------------------------------------------
 richxyl 回复于：2005-12-08 09:25:06

学习了，谢谢

--------------------------------------------------------------------------------
 flybean 回复于：2005-12-08 13:14:32

引用：原帖由 1017of 于 2005-11-1 09:27 发表
不好意思，由于系统会自动牺牲死锁的一方，偶觉得死锁一般用sp_who和sp_lock很难看到。可使用打开"print deadlock information"选项，死锁信息会打印在errorlog中，但同时可能会带了部分性能的降低。

楼上说的是完全正确的。楼猪此文应是“解决Sybase数据库锁阻塞”。也希望LZ在发贴时，尽可能准确些，以免误导。

--------------------------------------------------------------------------------
 jtyhyl 回复于：2005-12-08 15:00:17

再次支持

--------------------------------------------------------------------------------
 混在南京 回复于：2006-06-22 15:08:55

我对SYBASE数据库新建了一个用户，如何对该用户授所有表的查询权限呢？
语句该如何写？谢谢高手赐教

--------------------------------------------------------------------------------
 tomer 回复于：2006-06-23 10:07:10

引用：原帖由 混在南京 于 2006-6-22 15:08 发表
我对SYBASE数据库新建了一个用户，如何对该用户授所有表的查询权限呢？
语句该如何写？谢谢高手赐教

grant select to username试试

--------------------------------------------------------------------------------
 小无赖 回复于：2006-06-23 11:27:40

引用：原帖由 flybean 于 2005-12-8 13:14 发表

楼上说的是完全正确的。楼猪此文应是“解决Sybase数据库锁阻塞”。也希望LZ在发贴时，尽可能准确些，以免误导。

 

对，我一开始就被误导了，现在才知道我原来见过的都不是死锁，阻塞而已。

 

 
 

原文链接：http://bbs.chinaunix.net/viewthread.php?tid=627319
转载请注明作者名及原文出处