减少SQL Server数据库死锁的方法
2009-03-31 10:25:41 来源:WEB开发网 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁炬儳缍婇弻鐔兼⒒鐎靛壊妲紒鐐劤缂嶅﹪寮婚悢鍏尖拻閻庨潧澹婂Σ顔剧磼閻愵剙鍔ょ紓宥咃躬瀵鎮㈤崗灏栨嫽闁诲酣娼ф竟濠偽i鍓х<闁绘劦鍓欓崝銈囩磽瀹ュ拑韬€殿喖顭烽幃銏ゅ礂鐏忔牗瀚介梺璇查叄濞佳勭珶婵犲伣锝夘敊閸撗咃紲闂佽鍨庨崘锝嗗瘱闂備胶顢婂▍鏇㈠箲閸ヮ剙鐏抽柡鍐ㄧ墕缁€鍐┿亜韫囧海顦﹀ù婊堢畺閺屻劌鈹戦崱娆忓毈缂備降鍔庣划顖炲Φ閸曨垰绠抽悗锝庝簽娴犻箖姊洪棃娑欐悙閻庢矮鍗抽悰顕€宕堕澶嬫櫖濠殿噯绲剧€笛囧箲閸ヮ剙钃熼柣鏂挎憸閻熷綊鏌涢…鎴濇灈妞ゎ剙鐗嗛—鍐Χ鎼粹€茬凹缂備緡鍠楅幐鎼佹偩閻戣棄纭€闁绘劕绉靛Λ鍐春閳ь剚銇勯幒鎴濐伀鐎规挷绀侀埞鎴︽偐閹绘帩浼€缂佹儳褰炵划娆撳蓟濞戞矮娌柟瑙勫姇椤ユ繈姊洪柅鐐茶嫰婢т即鏌熼搹顐e磳闁挎繄鍋涢埞鎴犫偓锝庘偓顓涙櫊閺屽秵娼幏灞藉帯闂佹眹鍊曢幊鎰閹惧瓨濯撮柛鎾村絻閸撳崬顪冮妶鍡楃仸闁荤啿鏅涢悾鐑藉Ψ瑜夐崑鎾绘晲鎼粹剝鐏嶉梺缁樻尰濞叉﹢濡甸崟顖氱疀闂傚牊绋愮花鑲╃磽娴h棄鐓愭慨妯稿妿濡叉劙骞樼拠鑼槰闂佸啿鎼崐濠毸囬弶搴撴斀妞ゆ梻銆嬪銉︺亜椤撶偛妲婚柣锝囧厴楠炴帡骞嬮弮鈧悗濠氭⒑鐟欏嫭鍎楅柛妯衡偓鐔插徍濠电姷鏁告慨鐑藉极閸涘﹥鍙忔い鎾卞灩绾惧鏌熼崜褏甯涢柍閿嬪灦閵囧嫰骞掗崱妞惧缂傚倷绀侀ˇ閬嶅极婵犳氨宓侀柛鈩冪⊕閸婄兘鏌涘┑鍡楊伀妞ゆ梹鍔曢埞鎴︽倻閸モ晝校闂佸憡鎸婚悷锔界┍婵犲洦鍤冮柍鍝勫暟閿涙粓姊鸿ぐ鎺戜喊闁告瑥楠搁埢鎾斥堪閸喓鍘搁柣蹇曞仧绾爼宕戦幘璇茬疀濞达絽鎲¢崐顖炴⒑绾懎浜归悶娑栧劦閸┾偓妞ゆ帒鍟惃娲煛娴e湱澧柍瑙勫灴閹瑩寮堕幋鐘辨闂備礁婀辨灙闁硅姤绮庨崚鎺楀籍閸喎浠虹紓浣割儓椤曟娊鏁冮崒娑氬幈闂佸搫娲㈤崝宀勬倶閻樼粯鐓曢柟鑸妼娴滄儳鈹戦敍鍕杭闁稿﹥鐗犲畷婵嬫晝閳ь剟鈥﹂崸妤€鐒垫い鎺嶈兌缁犲墽鈧厜鍋撳┑鐘辩窔閸嬫鈹戦纭烽練婵炲拑绲垮Σ鎰板箳閹冲磭鍠撻幏鐘绘嚑閼稿灚姣愰梻鍌氬€烽懗鑸电仚濠电偛顕崗妯侯嚕椤愩倖瀚氱€瑰壊鍠栧▓銊︾節閻㈤潧校缁炬澘绉瑰鏌ュ箵閹烘繄鍞甸柣鐘烘鐏忋劌顔忛妷褉鍋撶憴鍕碍婵☆偅绻傞~蹇涙惞閸︻厾锛滃┑鈽嗗灠閹碱偊锝炲鍥╃=濞达綁顥撻崝宥夋煙缁嬪灝鏆遍柣锝囧厴楠炲鏁冮埀顒傜不婵犳碍鍋i柛銉戝啰楠囬悗瑙勬尭缁夋挳鈥旈崘顔嘉ч柛鈩兠棄宥囩磽娴e壊鍎愰柛銊ュ缁顓兼径瀣偓閿嬨亜閹哄秶顦︾€殿喖鐏濋埞鎴﹀煡閸℃浠梺鍛婎焼閸曨収娲告俊銈忕到閸燁垶宕愰崹顐e弿婵☆垳鍘ф禍楣冩倵濮樼偓瀚�

如果两个用户进程分别锁定了不同的资源,接着又试图锁定对方所锁定的资源,就会产生死锁。此时,SQL Server将自动地选择并中止其中一个进程以解除死锁,使得另外一个进程能够继续处理。系统将回退被中止的事务,并向被回退事务的用户发送错误信息。
大多数设计良好的应用都会在接收到这个错误信息之后重新提交该事务,此时提交成功的可能性是很大的。但是,如果服务器上经常出现这种情况,就会显著地降低服务器性能。为避免死锁,设计应用应当遵循一定的原则,包括:
◆让应用每次都以相同的次序访问服务器资源。
◆在事务期间禁止任何用户输入。应当在事务开始之前收集用户输入。
◆尽量保持事务的短小和简单。
◆如合适的话,为运行事务的用户连接指定尽可能低的隔离级别。[适用于6.5,7.0,2000]
此外,对于SQL Server的死锁问题,下面是几则实践中很有用的小技巧。
◆使用SQL Server Profiler的Create Trace Wizard运行“Identify The Cause of a Deadlock”跟踪来辅助识别死锁问题,它将提供帮助查找数据库产生死锁原因的原始数据。[适用于7.0,2000]
◆如果无法消除应用中的所有死锁,请确保提供了这样一种程序逻辑:它能够在死锁出现并中止用户事务之后,以随机的时间间隔自动重新提交事务。这里等待时间的随机性非常重要,这是因为另一个竞争的事务也可能在等待,我们不应该让两个竞争的事务等待同样的时间,然后再在同一时间执行它们,这样的话将导致新的死锁。[适用于6.5,7.0,2000]
◆尽可能地简化所有T-SQL事务。此举将减少各种类型的锁的数量,有助于提高SQL Server应用的整体性能。如果可能的话,应将较复杂的事务分割成多个较简单的事务。[适用于6.5,7.0,2000]
◆所有条件逻辑、变量赋值以及其他相关的预备设置操作应当在事务之外完成,而不应该放到事务之内。永远不要为了接受用户输入而暂停某个事务,用户输入应当总是在事务之外完成。[适用于6.5,7.0,2000]
◆在存储过程内封装所有事务,包括BEGIN TRANSACTION和COMMIT TRANSACTION语句。此举从两个方面帮助减少阻塞的锁。首先,它限制了事务运行时客户程序和SQL Server之间的通信,从而使得两者之间的任何消息只能出现于非事务运行时间(减少了事务运行的时间)。其次,由于存储过程强制它所启动的事务或者完成、或者中止,从而防止了用户留下未完成的事务(留下未撤销的锁)。[适用于6.5,7.0,2000]
◆如果客户程序需要先用一定的时间检查数据,然后可能更新数据,也可能不更新数据,那么最好不要在整个记录检查期间都锁定记录。假设大部分时间都是检查数据而不是更新数据,那么处理这种特殊情况的一种方法就是:先选择出记录(不加UPDATE子句。UPDATE子句将在记录上加上共享锁),然后把它发送给客户。
如果用户只查看记录但从来不更新它,程序可以什么也不做;反过来,如果用户决定更新某个记录,那么他可以通过一个WHERE子句检查当前的数据是否和以前提取的数据相同,然后执行UPDATE。
类似地,我们还可以检查记录中的时间标识列(如果它存在的话)。如果数据相同,则执行UPDATE操作;如果记录已经改变,则应用应该提示用户以便用户决定如何处理。虽然这种方法需要编写更多的代码,但它能够减少加锁时间和次数,提高应用的整体性能。[适用于6.5,7.0,2000]
◆尽可能地为用户连接指定具有最少限制的事务隔离级别,而不是总是使用默认的READ COMMITTED。为了避免由此产生任何其他问题,应当参考不同隔离级别将产生的效果,仔细地分析事务的特性。[适用于6.5,7.0,2000]
◆使用游标会降低并发性。为避免这一点,如果可以使用只读的游标则应该使用READ_ONLY游标选项,否则如果需要进行更新,尝试使用OPTIMISTIC游标选项以减少加锁。设法避免使用SCROLL_LOCKS游标选项,该选项会增加由于记录锁定引起的问题。[适用于6.5,7.0,2000]
◆如果用户抱怨说他们不得不等待系统完成事务,则应当检查服务器上的资源锁定是否是导致该问题的原因。进行此类检查时可以使用SQL Server Locks Object: Average Wait Time (ms),用该计数器来度量各种锁的平均等待时间。
如果可以确定一种或几种类型的锁导致了事务延迟,就可以进一步探究是否可以确定具体是哪个事务产生了这种锁。Profiler是进行这类具体分析的最好工具。[适用于7.0,2000]
◆使用sp_who和sp_who2(SQL Server Books Online没有关于sp_who2的说明,但sp_who2提供了比sp_who更详细的信息)来确定可能是哪些用户阻塞了其他用户。[适用于6.5,7.0,2000]
◆试试下面的一个或多个有助于避免阻塞锁的建议:1)对于频繁使用的表使用集簇化的索引;2)设法避免一次性影响大量记录的T-SQL语句,特别是INSERT和UPDATE语句;3)设法让UPDATE和DELETE语句使用索引;4)使用嵌套事务时,避免提交和回退冲突。[适用于6.5,7.0,2000]
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››减少电脑关机的时间
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
更多精彩
赞助商链接