并发问题,锁,怎么处理死锁,脏数据处理
?1.?死锁原理 ????根据操作系统中的定义:死锁是指在一组进程中的各个进程均占有不会释放的资源,但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。 ????死锁的四个必要条件:
对应到SQL Server中,当在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务永久阻塞,从而出现死锁;这些资源可能是:单行(RID,堆中的单行)、索引中的键(KEY,行锁)、页(PAG,8KB)、区结构(EXT,连续的8页)、堆或B树(HOBT)?、表(TAB,包括数据和索引)、文件(File,数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。一个死锁示例如下图所示: ? 2.?死锁排查 (1).?使用SQL Server的系统存储过程sp_who和sp_lock,可以查看当前数据库中的锁情况;进而根据objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪个资源被锁,用dbcc ld(@blk),可以查看最后一条发生给SQL Server的Sql语句; ????ecid?? ????status?? nvarchar? (? 50? ),249)" data-src="https://www.52php.cn/res/2020/10-09/12/a6339ee3e57d1d52bc7d02b338e15a60.gif">????loginname?? ????hostname?? ????blk?? ????dbname?? ????cmd?? ????request_ID?? ); ? ?#Lock(spid?? ????dpid?? ????objid?? ????indld?? ????? [? Type? ]? 20? ????Resource?? ????Mode?? 10? ????Status?? ) ); INSERT? INTO? ?#Who ????? EXEC? ?sp_who?active??? --? 看哪个引起的阻塞,blk?? ?#Lock ?sp_lock??? 看锁住了那个资源id,objid?? DECLARE? @DBName? SET? @DBName? =? '? NameOfDataBase? '? SELECT? ?#Who.? *? FROM? ?#Who?? WHERE? ?dbname? =? ?#Lock.? ????? JOIN? ????????? ON? ?#Who.spid? #Lock.spid ????????????? AND? ; 最后发送到SQL?Server的语句? ?crsr?? Cursor? FOR? ?blk?? ?? ?blk? <>? 0? @blk? open? ?crsr; FETCH? NEXT? WHILE? ?(? @@FETCH_STATUS? BEGIN? dbcc? ?inputbuffer(? END? close? DEALLOCATE? 锁定的资源? ?#Who.spid,hostname,objid,? type? object_name? (objid)?? as? ?objName?? FROM ?objid? DROP? ?#Who; ?#Lock; 3.?避免死锁 ????上面1中列出了死锁的四个必要条件,我们只要想办法破其中的任意一个或多个条件,就可以避免死锁发生,一般有以下几种方法(FROM Sql Server 2005联机丛书): 4.?死锁处理方法: (1).?根据2中提供的sql,查看那个spid处于wait状态,然后用kill spid来干掉(即破坏死锁的第四个必要条件:循环等待);当然这只是一种临时解决方案,我们总不能在遇到死锁就在用户的生产环境上排查死锁、Kill sp,我们应该考虑如何去避免死锁。 (2).?使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定锁请求超时。默认情况下,数据库没有超时期限(timeout_period值为-1,可以用SELECT @@LOCK_TIMEOUT来查看该值,即无限期等待)。当请求锁超过timeout_period时,将返回错误。timeout_period值为0时表示根本不等待,一遇到锁就返回消息。设置锁请求超时,破环了死锁的第二个必要条件(请求与保持条件)。
服务器:?消息??
1222?
,级别??
16?
,状态??
50?
,行??
1?
已超过了锁请求超时时段。 (3). SQL Server ? 内部有一个锁监视器线程执行死锁检查,锁监视器对特定线程启动死锁搜索时,会标识线程正在等待的资源;然后查找特定资源的所有者,并递归地继续执行对那些线程的死锁搜索,直到找到一个构成死锁条件的循环。检测到死锁后,数据库引擎 ? 选择运行回滚开销最小的事务的会话作为死锁牺牲品,返回 1205? 错误,回滚死锁牺牲品的事务并释放该事务持有的所有锁,使其他线程的事务可以请求资源并继续运行。 5.?两个死锁示例及解决方法 5.1 SQL死锁 (1).?测试用的基础数据:
TABLE?
?Lock1(C1??
default?
));
?Lock2(C1?? ?Lock1?? VALUES? 1? ?Lock2?? ); (2).?开两个查询窗口,分别执行下面两段 sql
Query?1?
Begin?
Tran?
???
Update?
Set?
?C1?
C1?
+?
WaitFor?
?Delay??
00:01:00?
?Lock2
Rollback? ; ?
Query?2?
?Lock1
; ? 上面的SQL中有一句WaitFor Delay '00:01:00',用于等待1分钟,以方便查看锁的情况。 (3).?查看锁情况 在执行上面的WaitFor语句期间,执行第二节中提供的语句来查看锁信息:
Query1中,持有Lock1中第一行(表中只有一行数据)的行排他锁(RID:X),并持有该行所在页的意向更新锁(PAG:IX)、该表的意向更新锁(TAB:IX);Query2中,持有Lock2中第一行(表中只有一行数据)的行排他锁(RID:X),并持有该行所在页的意向更新锁(PAG:IX)、该表的意向更新锁(TAB:IX); 执行完Waitfor,Query1查询Lock2,请求在资源上加S锁,但该行已经被Query2加上了X锁;Query2查询Lock1,请求在资源上加S锁,但该行已经被Query1加上了X锁;于是两个查询持有资源并互不相让,构成死锁。 (4).?解决办法 a).?SQL Server自动选择一条SQL作死锁牺牲品:运行完上面的两个查询后,我们会发现有一条SQL能正常执行完毕,而另一个SQL则报如下错误:
1205?
13?
事务(进程?ID??xx)与另一个进程已被死锁在??lock?资源上,且该事务已被选作死锁牺牲品。请重新运行该事务。 这就是上面第四节中介绍的锁监视器干活了。 b).?按同一顺序访问对象:颠倒任意一条SQL中的Update与SELECT语句的顺序。例如修改第二条SQL成如下:
Query2?
?Lock1?
在Lock1上申请S锁?
???
;?
Lock2:RID:X?
;
当然这样修改也是有代价的,这会导致第一条
SQL
执行完毕之前,第二条
SQL
一直处于阻塞状态。单独执行
Query1
或
Query2
需要约
1
分钟,但如果开始执行
Query1
时,马上同时执行
Query2
,则
Query2
需要
2
分钟才能执行完;这种按顺序请求资源从一定程度上降低了并发性。
c).?SELECT语句加With(NoLock)提示:默认情况下SELECT语句会对查询到的资源加S锁(共享锁),S锁与X锁(排他锁)不兼容;但加上With(NoLock)后,SELECT不对查询到的资源加锁(或者加Sch-S锁,Sch-S锁可以与任何锁兼容);从而可以是这两条SQL可以并发地访问同一资源。当然,此方法适合解决读与写并发死锁的情况,但加With(NoLock)可能会导致脏读。
WITH?
(NOLock)
(NOLock) d).? 使用较低的隔离级别。 SQL Server 2000 支持四种事务处理隔离级别 (TIL) ,分别为: READ UNCOMMITTED 、 READ COMMITTED 、 REPEATABLE READ 、 SERIALIZABLE ; SQL Server 2005 中增加了 SNAPSHOT TIL 。默认情况下, SQL Server使用READ COMMITTED TIL ,我们可以在上面的两条 SQL 前都加上一句 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ,来降低 TIL以避免死锁 ;事实上,运行在 READ UNCOMMITTED TIL 的事务,其中的 SELECT 语句不对结果资源加锁或加 Sch-S 锁,而不会加 S 锁;但还有一点需要注意的是: READ UNCOMMITTED TIL允许脏读 ,虽然加上了降低 TIL 的语句后,上面两条 SQL 在执行过程中不会报错,但执行结果是一个返回 1 ,一个返回 2 ,即读到了脏数据,也许这并不是我们所期望的。 e).?在SQL前加SET LOCK_TIMEOUT timeout_period,当请求锁超过设定的timeout_period时间后,就会终止当前SQL的执行,牺牲自己,成全别人。 f).?使用基于行版本控制的隔离级别(SQL Server 2005支持):开启下面的选项后,SELECT不会对请求的资源加S锁,不加锁或者加Sch-S锁,从而将读与写操作之间发生的死锁几率降至最低;而且不会发生脏读。啊
?ALLOW_SNAPSHOT_ISOLATION??
?READ_COMMITTED_SNAPSHOT?? ON ???????g).? 使用绑定连接 ( 使用方法见下一个示例。 ) ? 5.2?程序死锁(SQL阻塞) 看一个例子:一个典型的数据库操作事务死锁分析,按照我自己的理解,我觉得这应该算是C#程序中出现死锁,而不是数据库中的死锁;下面的代码模拟了该文中对数据库的操作过程:
//?
略去的无关的code?
SqlConnection?conn??
=?
new?
?SqlConnection(connectionString);
conn.Open(); SqlTransaction?tran?? ?conn.BeginTransaction(); string? ?sql1?? "? Update?Lock1?SET?C1=C1+1? "? ?sql2?? SELECT?*?FROM?Lock1? ExecuteNonQuery(tran,?sql1);?? 使用事务:事务中Lock了Table? ExecuteNonQuery(? null? 新开一个connection来读取Table? public? static? void? ?ExecuteNonQuery(SqlTransaction?tran,?? ?sql) ? { ????SqlCommand?cmd?=?new?SqlCommand(sql); ????if?(tran?!=null) ????????????cmd.Connection??tran.Connection; ????????cmd.Transaction??tran; ????????cmd.ExecuteNonQuery(); ????} else ????????using?(SqlConnection?conn??SqlConnection(connectionString)) ????????????????????conn.Open(); ????????????cmd.Connection??conn; ????????????cmd.ExecuteNonQuery(); ????????} } 执行到ExecuteNonQuery(null,sql2)时抛出SQL执行超时的异常,下图从数据库的角度来看该问题: ??????????? ?????代码从上往下执行,会话1持有了表Lock1的X锁,且事务没有结束,回话1就一直持有X锁不释放;而会话2执行select操作,请求在表Lock1上加S锁,但S锁与X锁是不兼容的,所以回话2的被阻塞等待,不在等待中,就在等待中获得资源,就在等待中超时。。。从中我们可以看到,里面并没有出现死锁,而只是SELECT操作被阻塞了。也正因为不是数据库死锁,所以SQL Server的锁监视器无法检测到死锁。 ???????我们再从C#程序的角度来看该问题: ??????????? ???????C#程序持有了表Lock1上的X锁,同时开了另一个SqlConnection还想在该表上请求一把S锁,图中已经构成了环路;太贪心了,结果自己把自己给锁死了。。。 ???????虽然这不是一个数据库死锁,但却是因为数据库资源而导致的死锁,上例中提到的解决死锁的方法在这里也基本适用,主要是避免读操作被阻塞,解决方法如下: ???????a).?把SELECT放在Update语句前:SELECT不在事务中,且执行完毕会释放S锁;
tran??
?connection.BeginTransaction();
使用事务:事务中Lock了测试表Lock1? @"? DECLARE?@Token?varchar(255); exec?sp_getbindtoken?@Token?out; SELECT?@Token;? ?token?? ?ExecuteScalar(tran,?sql2).ToString(); ?sql3?? " EXEC?sp_bindsession?@Token;Update?Lock1?SET?C1=C1+1;exec?sp_bindsession?null;? SqlParameter?parameter?? ?SqlParameter(? @Token? parameter.Value?? ?token; ExecuteNonQuery(? 新开一个connection来操作测试表Lock1? tran.Commit(); ? ? 附:锁兼容性(FROM SQL Server 2005?联机丛书) 锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定,则仅当请求锁的模式与现有锁的模式相兼容时,才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容,则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |