在sqlserver2022中解决死锁问题

数据库操作的死锁是不可避免的，本文并不打算讨论死锁怎样产生，重点在于解决死锁，通过SQLServer2005,现在似乎有了一种新的解决办法。将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。useNorthwindbegintraninsertintoOrders(CustomerId)values(@#ALFKI@#)waitfordelay@#00:00:05@#select*fromOrderswhereCustomerId=@#ALFKI@#commitprint@#endtran@#SQLServer对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQLServer2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中，print@#endtran@#语句将不会被运行，所以我们很难在SQLServer2000的T-SQL中对死锁进行进一步的处理。现在不同了，SQLServer2005能够在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：下面利用的try...catch来解决死锁。SETXACT_ABORTONdeclare@rintset@r=1while@r<=3beginbegintranbegintryinsertintoOrders(CustomerId)values(@#ALFKI@#)waitfordelay@#00:00:05@#select*fromOrderswhereCustomerId=@#ALFKI@#commitbreakendtrybegincatchrollbackwaitfordelay@#00:00:03@#set@r=@r+1continueendcatchend解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目能够调整以应付不同的需要。但是现在又面临一个新的问题:错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQLServer2005有一个RaiseError语句，能够抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样:declare@rintset@r=1while@r<=3beginbegintranbegintryinsertintoOrders(CustomerId)values(@#ALFKI@#)waitfordelay@#00:00:05@#select*fromOrderswhereCustomerId=@#ALFKI@#commitbreakendtrybegincatchrollbackwaitfordelay@#00:00:03@#set@r=@r+1continueendcatchendifERROR_NUMBER()<>0begindeclare@ErrorMessagenvarchar(4000);declare@ErrorSeverityint;declare@ErrorStateint;select@ErrorMessage=ERROR_MESSAGE(),@ErrorSeverity=ERROR_SEVERITY(),@ErrorState=ERROR_STATE();raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);end我希望将来SQLServer2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清楚度。现在，对于DataAccess的代码，或许再也无需考虑死锁问题了**********************************************************************************************************************************************若表A被锁时(updatedata),使用select*from表A会不会出问题??若表A被锁时(updatedata),使用select*from表A会不会出问题??楼主feburary（feburary）2003-09-2511:54:07在MS-SQLServer/基础类提问若表A被锁时(updatedata),使用select*from表A会不会出问题??问题点数：20、回复次数：6Top1楼txlicenhe（马可）回复于2003-09-2511:57:36得分4若真锁住了，查不出。/**********加锁***************设table1(A,B,C)ABCa1b1c1a2b2c2a3b3c31）排它锁新建两个连接在第一个连接中执行以下语句begintranupdatetable1setA='aa'whereB='b2'waitfordelay'00:00:30'--等待30秒committran在第二个连接中执行以下语句begintranselect*fromtable1whereB='b2'committran若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒2）共享锁在第一个连接中执行以下语句begintranselect*fromtable1holdlock-holdlock人为加锁whereB='b2'waitfordelay'00:00:30'--等待30秒committran在第二个连接中执行以下语句begintranselectA,Cfromtable1whereB='b2'updatetable1setA='aa'whereB='b2'committran若同时执行上述两个语句，则第二个连接中的select查询可以执行而update必须等待第一个连接中的共享锁结束后才能执行即要等待30秒3）死锁增设table2(D,E)DEd1e1d2e2在第一个连接中执行以下语句begintranupdatetable1setA='aa'whereB='b2'waitfordelay'00:00:30'updatetable2setD='d5'whereE='e1'committran在第二个连接中执行以下语句begintranupdatetable2setD='d5'whereE='e1'waitfordelay'00:00:10'updatetable1setA='aa'whereB='b2'committran同时执行，系统会检测出死锁，并中止进程--------------------------------------------------------------SETIMPLICIT_TRANSACTIONSON--用户每次必须显式提交或回滚。否则当用户断开连接时,--事务及其所包含的所有数据更改将回滚SETIMPLICIT_TRANSACTIONSOFF--自动提交模式。在自动提交模式下，如果各个语句成功--完成则提交。Top2楼letsflytogether（伍子）回复于2003-09-2512:08:59得分4select是共享锁update,insert,delete是独占锁考虑独占锁的以下事实1。只有一个事务可以获得一个资源的独占锁2。事务不能获得有独占锁资源上的共享锁3。在资源上的所有共享锁释放后，才能获得它上面的独占锁Top3楼shizi_mhy（柿子）回复于2003-09-2512:49:56得分2markTop4楼benxie（结婚是幸福的！为了老婆努力赚钱！）回复于2003-09-2512:58:01得分2upTop5楼aierong（）回复于2003-09-2513:04:35得分4不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于SELECT语句。Top6楼lionstar（小狮子）回复于2003-09-2513:38:14得分4SETTRANSACTIONISOLATIONLEVEL{READCOMMITTED|READUNCOMMITTED|REPEATABLEREAD|SERIALIZABLE}参数READCOMMITTED指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是SQLServer的默认值。READUNCOMMITTED执行脏读或0级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置NOLOCK相同。这是四个隔离级别中限制最小的级别。*******************************************************************************************************************************************数据库加锁,一个困惑了几年的问题楼主zhangking（网眼-）2003-09-0311:27:37在MS-SQLServer/疑难问题提问在DOS时代的Netware上用Foxpro或VB进行编程时,更新记录或表时,为了避免"冲突",我们采用给记录或表"加锁"的办法,修改前lock它,完成后,在释放(unlock)它。但是，进入Windows下编程后，大量的数据处理需要在网络环境下完成了，但是大家反而好象把这个问题全都忽略了！我没有见过哪本书籍或文章谈到Windows下的程序（例如asp）的数据访问冲突。一定是我孤陋寡闻了，希望有好手可以帮我解决这个问题。谢谢！提示：ADO模型中的“光标”“锁定”等问题是不是和我的问题有关？问题点数：55、回复次数：13Top1楼txlicenhe（马可）回复于2003-09-0311:39:21得分15/**********加锁***************设table1(A,B,C)ABCa1b1c1a2b2c2a3b3c31）排它锁新建两个连接在第一个连接中执行以下语句begintranupdatetable1setA='aa'whereB='b2'waitfordelay'00:00:30'--等待30秒committran在第二个连接中执行以下语句begintranselect*fromtable1whereB='b2'committran若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒2）共享锁在第一个连接中执行以下语句begintranselect*fromtable1holdlock-holdlock人为加锁whereB='b2'waitfordelay'00:00:30'--等待30秒committran在第二个连接中执行以下语句begintranselectA,Cfromtable1whereB='b2'updatetable1setA='aa'whereB='b2'committran若同时执行上述两个语句，则第二个连接中的select查询可以执行而update必须等待第一个连接中的共享锁结束后才能执行即要等待30秒3）死锁增设table2(D,E)DEd1e1d2e2在第一个连接中执行以下语句begintranupdatetable1setA='aa'whereB='b2'waitfordelay'00:00:30'updatetable2setD='d5'whereE='e1'committran在第二个连接中执行以下语句begintranupdatetable2setD='d5'whereE='e1'waitfordelay'00:00:10'updatetable1setA='aa'whereB='b2'committran同时执行，系统会检测出死锁，并中止进程--------------------------------------------------------------SETIMPLICIT_TRANSACTIONSON--用户每次必须显式提交或回滚。否则当用户断开连接时,--事务及其所包含的所有数据更改将回滚SETIMPLICIT_TRANSACTIONSOFF--自动提交模式。在自动提交模式下，如果各个语句成功--完成则提交。Top2楼zhangking（网眼-）回复于2003-09-0311:47:40得分0十分感谢txlicenhe(马可@李)!!!最起码我知道了,加锁还是需要的!!有没有一些深入介绍和探讨数据库加锁的文章啊?Top3楼fly518（我要飞）回复于2003-09-0318:14:44得分2现在一般不需要我们来设置锁了，数据库系统会自动帮助我们使用锁的，除非你自己有一些特殊的要求的时候才需要使用锁Top4楼pengdali（）回复于2003-09-0318:25:41得分81如何锁一个表的某一行A连接中执行SETTRANSACTIONISOLATIONLEVELREPEATABLEREADbegintranselect*fromtablenamewith(rowlock)whereid=3waitfordelay'00:00:05'committranB连接中如果执行updatetablenamesetcolname='10'whereid=3--则要等待5秒updatetablenamesetcolname='10'whereid<>3--可立即执行2锁定数据库的一个表SELECT*FROMtableWITH(HOLDLOCK)注意:锁定数据库的一个表的区别SELECT*FROMtableWITH(HOLDLOCK)其他事务可以读取表，但不能更新删除SELECT*FROMtableWITH(TABLOCKX)其他事务不能读取表,更新和删除Top5楼pengdali（）回复于2003-09-0318:25:57得分7select*fromtablewith(..)SELECT语句中“加锁选项”的功能说明SQLServer提供了强大而完备的锁机制来帮助实现数据库系统的并发性和高性能。用户既能使用SQLServer的缺省设置也可以在select语句中使用“加锁选项”来实现预期的效果。本文介绍了SELECT语句中的各项“加锁选项”以及相应的功能说明。功能说明：NOLOCK（不加锁）此选项被选中时，SQLServer在读取或修改数据时不加任何锁。在这种情况下，用户有可能读取到未完成事务（UncommitedTransaction）或回滚(RollBack)中的数据,即所谓的“脏数据”。HOLDLOCK（保持锁）此选项被选中时，SQLServer会将此共享锁保持至整个事务结束，而不会在途中释放。UPDLOCK（修改锁）此选项被选中时，SQLServer在读取数据时使用修改锁来代替共享锁，并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。TABLOCK（表锁）此选项被选中时，SQLServer将在整个表上置共享锁直至该命令结束。这个选项保证其他进程只能读取而不能修改数据。PAGLOCK（页锁）此选项为默认选项，当被选中时，SQLServer使用共享页锁。TABLOCKX（排它表锁）此选项被选中时，SQLServer将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。使用这些选项将使系统忽略原先在SET语句设定的事务隔离级别（TransactionIsolationLevel)。请查阅SQLServer联机手册获取更多信息。Top6楼Wally_wu（广告招租位,欢迎各位前来刊登广告.）回复于2003-09-0318:42:40得分6什幺是事务事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列，这些操作要幺都执行，要幺都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要幺都执行，要幺都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。数据一致性问题多用户并发存取同一数据将会导致以下的数据不一致性问题。•丢失修改（LostUpdate）在下表中，T1、T2、T3和T4表示顺序的时间。用户T1T2T3T4Ax=40X=x-30BX=40X=x-20假设用户A和B都读取x(x=40),然后分别把x减少30和20。用户A在t3把改后的x(x=10)写入数据库。随后，用户B在t4把改后的x(x=20)写入数据库。于是，对用户A而言，他的修改在t4处丢失了。•脏读数据（DirtyRead）请看下表，用户T1T2T3T4Ax=40X=x+30X=x-30rollbackBX=70X=x-20用户A在t2把x增加30（尚没写入数据库），用户B在t3由数据缓存读出x=70。但用户A在t4时撤消（Undo）了对x的修改，数据库中仍维持x=40。但用户B已把改变的数据（x=70）取走。•不能重复读（Non-RepeatableRead）用户T1T2T3T4T5T6AX=40Y=30X+Y=70Z=30X+Y+Z=100Bx=40X=X+20CommitX=x-20用户A、用户B分别读取x=40后，在t3用户A取出y=30并计算x+y=70。在t4时用户B把x增加20，并于t5把x(x=60)写入数据库。在t6时，用户A取出z(z=30)并继续计算x+y+z=100。但如果用户A为进行核算而把x、y、x重读一次再进行计算，却出现x+y+z=120！（x已增加20）。如何标识一个事务在SQLServer中，通常事务是指以BEGINTRAN开始，到ROLLBACK或一个相匹配的COMMIT之间的所有语句序列。ROLLBACK表示要撤消（Undo）该事务已做的一切操作，回退到事务开始的状态。COMMIT表示提交事务中的一切操作，使得对数据库的改变生效。在SQLServer中，对事务的管理包含三个方面：•事务控制语句：它使程序员能指明把一系列操作（Transact-SQL命令）作为一个工作单位来处理。•锁机制（Locking）：封锁正被一个事务修改的数据，防止其它用户访问到“不一致”的数据。•事务日志(TransactionLog)：使事务具有可恢复性。SQLServer的锁机制所谓封锁,就是一个事务可向系统提出请求，对被操作的数据加锁(Lock)。其它事务必须等到此事务解锁（Unlock）之后才能访问该数据。从而，在多个用户并发访问数据库时,确保不互相干扰。可锁定的单位是：行、页、表、盘区和数据库。1.锁的类型SQLServer支持三种基本的封锁类型：共享（S）锁，排它（X）锁和更新（U）锁。封锁的基本粒度为行。1)共享（S）锁：用于读操作。•多个事务可封锁一个共享单位的数据。•任何事务都不能修改加Ｓ锁的数据。•通常是加Ｓ锁的数据被读取完毕，Ｓ锁立即被释放。2)独占（Ｘ）锁：用于写操作。•仅允许一个事务封锁此共享数据。•其它任何事务必须等到Ｘ锁被释放才能对该数据进行访问。•Ｘ锁一直到事务结束才能被释放。3)更新（Ｕ）锁。•用来预定要对此页施加Ｘ锁，它允许其它事务读，但不允许再施加ＵTop7楼Wally_wu（广告招租位,欢迎各位前来刊登广告.）回复于2003-09-0318:42:49得分6锁或Ｘ锁。•当被读取数据页将要被更新时，则升级为Ｘ锁。•Ｕ锁一直到事务结束时才能被释放。2.三种锁的兼容性如下表简单描述了三种锁的兼容性：通常，读操作（SELECT）获得共享锁，写操作（INSERT、DELETE）获得独占锁；而更新操作可分解为一个有更新意图的读和一个写操作，故先获得更新锁，然后再升级为独占锁。执行的命令获得锁其它进程可以查询？其它进程可以修改？Selecttitle_idfromtitlesSYesNodeletetitleswhereprice>25XNoNoinserttitlesvalues(...)XNoNoupdatetitlessettype=“general”UYesNowheretype=“business”然后ＸNONo使用索引降低锁并发性我们为什幺要讨论锁机制？如果用户操作数据时尽可能锁定最少的数据，这样处理过程,就不会等待被锁住的数据解锁，从而可以潜在地提高SQLServer的性能。如果有200个用户打算修改不同顾客的数据，仅对存储单个顾客信息的单一行进行加锁要比锁住整个表好得多。那幺，用户如何只锁定行而不是表呢？当然是使用索引了。正如前面所提到的，对存有要修改数据的字段使用索引可以提高性能，因为索引能直接找到数据所在的页面，而不是搜索所有的数据页面去找到所需的行。如果用户直接找到表中对应的行并进行更新操作，只需锁定该行即可，而不是锁定多个页面或者整个表。性能的提高不仅仅是因为在修改时读取的页面较少，而且锁定较少的页面潜在地避免了一个用户在修改数据完成之前其它用户一直等待解锁的情况。事务的隔离级别ANSI标准为SQL事务定义了4个隔离级别(isolationlevel)，隔离级别越高,出现数据不一致性的可能性就越小(并发度也就越低)。较高的级别中包含了较低级别中所规定了的限制。•隔离级别0：防止“丢失修改”，允许脏读。•隔离级别1：防止脏读。允许读已提交的数据。•隔离级别2：防止“不可重复读”。•隔离级别3：“可串行化”（serializable）。其含义为，某组并行事务的一种交叉调度产生的结果和这些事务的某一串行调度的结果相同（可避免破坏数据一致性）。SQLServer支持四种隔离级别，级别1为缺省隔离级别，表中没有隔离级别2，请参考表：SQLServer支持的隔离级别封锁方式数据一致性保证X锁施加于被修改的页S锁施加于被读取的页防止丢失修改防止读脏数据可以重复读取级别0封锁到事务结束是级别1（缺省）封锁到事务结束读后立即释放是是级别3封锁到事务结束封锁到事务结束是是是在SQLServer也指定级别2，但级别3已包含级别2。ANSI-92SQL中要求把级别3作为所有事务的缺省隔离级别。SQLServer用holdlock选项加强S锁的限制，实现隔离级别3。SQLServer的缺省隔离级别为级别1，共享读锁（S锁）是在该页被读完后立即释放。在select语句中加holdlock选项，则可使S锁一直保持到事务结束才释放。她符合了ANSI隔离级别3的标准─“可串行化”。下面这个例子中，在同一事务中对avg(advance)要读取两次，且要求他们取值不变─“可重复读”，为此要使用选项holdlock。BEGINtranDECLARE@avg-advmoneySELECT@avg-adv=avg(advance)FROMtitlesholdlockWHEREtype="business"if@avg-adv>5000SELECTtitlefromtitlesWHEREtype="business"andadvance>@avg_advCOMMITtran在SQLServer中设定事务隔离级别的方法有三种：Top8楼Wally_wu（广告招租位,欢迎各位前来刊登广告.）回复于2003-09-0318:43:41得分6•会话层设定语法如下：SETTRANSACTIONISOLATIONLEVEL{READCOMMITTED|READUNCOMMITTED|REPEATABLEREAD|SERIALIZABLE}系统提供的系统存储过程将在级别1下执行，它不受会话层设定的影响。•语法层设定在SELECT、DECLAREcursor及readtext语句中增加选项。比如：SELECT...atisolation{0|readuncommitted}注意：语法层的设定将替代会话层的设定。•利用关键词设定─在SELECT语句中，加选项holdlock则设定级别3─在SELECT语句中，加noholdlock则设定级别0如下程序清单中所列的脚本实例在authors表上持有一个共享锁，它将用户检查服务器当前活动的时间推迟两分钟。程序清单测试事务隔离等级SETTRANSACTIONISOLATIONLEVELR