时间：2021-07-01 10:21:17 帮助过：8人阅读
1 ---开启事务 2 begin tran 3 --错误扑捉机制，看好啦，这里也有的。并且可以嵌套。 4 begin try 5 --语句正确 6 insert into lives (Eat,Play,Numb) values (‘猪肉‘,‘足球‘,1) 7 --Numb为int类型，出错 8 insert into lives (Eat,Play,Numb) values (‘猪肉‘,‘足球‘,‘abc‘) 9 --语句正确 10 insert into lives (Eat,Play,Numb) values (‘狗肉‘,‘篮球‘,2) 11 end try 12 begin catch 13 select Error_number() as ErrorNumber, --错误代码 14 Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到 15 Error_state() as ErrorState , --错误状态码 16 Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。 17 Error_line() as ErrorLine, --发生错误的行号 18 Error_message() as ErrorMessage --错误的具体信息 19 if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务 20 rollback tran ---由于出错，这里回滚到开始，第一条语句也没有插入成功。 21 end catch 22 if(@@trancount>0) 23 commit tran --如果成功Lives表中，将会有3条数据。 24 25 --表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型 26 select * from lives
---开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try
--语句正确
insert into lives (Eat,Play,Numb) values (‘猪肉‘,‘足球‘,1)
--加入保存点
save tran pigOneIn
--Numb为int类型，出错
insert into lives (Eat,Play,Numb) values (‘猪肉‘,‘足球‘,2)
--语句正确
insert into lives (Eat,Play,Numb) values (‘狗肉‘,‘篮球‘,3)
end try
begin catch
select Error_number() as ErrorNumber, --错误代码
Error_severity() as ErrorSeverity, --错误严重级别，级别小于10 try catch 捕获不到
Error_state() as ErrorState , --错误状态码
Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
Error_line() as ErrorLine, --发生错误的行号
Error_message() as ErrorMessage --错误的具体信息
if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
rollback tran ---由于出错，这里回滚事务到原点，第一条语句也没有插入成功。
end catch
if(@@trancount>0)
rollback tran pigOneIn --如果成功Lives表中，将会有3条数据。
--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from lives
设置 xact_abort on/off , 指定是否回滚当前事务，为on时如果当前sql出错，回滚整个事务，为off时如果sql出错回滚当前sql语句，其它语句照常运行读写数据库。
需要注意的时：xact_abort只对运行时出现的错误有用，如果sql语句存在编译时错误，那么他就失灵啦。
delete lives --清空数据
set xact_abort off
begin tran
--语句正确
insert into lives (Eat,Play,Numb) values (‘猪肉‘,‘足球‘,1)
--Numb为int类型，出错,如果1234..那个大数据换成‘132dsaf‘ xact_abort将失效
insert into lives (Eat,Play,Numb) values (‘猪肉‘,‘足球‘,12345646879783213)
--语句正确
insert into lives (Eat,Play,Numb) values (‘狗肉‘,‘篮球‘,3)
commit tran
select * from lives
为on时，结果集为空，因为运行是数据过大溢出出错，回滚整个事务。
跟着做：打开两个查询窗口，把下面的语句，分别放入2个查询窗口，在5秒内运行2个事务模块。
begin tran update lives set play=‘羽毛球‘ waitfor delay ‘0:0:5‘ update dbo.Earth set Animal=‘老虎‘ commit tran
begin tran update Earth set Animal=‘老虎‘ waitfor delay ‘0:0:5‘ --等待5秒执行下面的语句 update lives set play=‘羽毛球‘ commit tran select * from lives select * from Earth
为什么呢，下面我们看看锁，什么是锁。
在多用户都用事务同时访问同一个数据资源的情况下，就会造成以下几种数据错误。
然而锁定，就是为解决这些问题所生的，他的存在使得一个事务对他自己的数据块进行操作的时候，而另外一个事务则不能插足这些数据块。这就是所谓的锁定。
锁定从数据库系统的角度大致可以分为6种：
这些锁之间的相互兼容性，也就是，是否可以同时存在。
|
|
现有的授权模式 |
|
|
|
|
|
|---|---|---|---|---|---|---|
|
请求的模式 |
IS |
S |
U |
IX |
SIX |
X |
|
意向共享 (IS) |
是 |
是 |
是 |
是 |
是 |
否 |
|
共享 (S) |
是 |
是 |
是 |
否 |
否 |
否 |
|
更新 (U) |
是 |
是 |
否 |
否 |
否 |
否 |
|
意向排他 (IX) |
是 |
否 |
否 |
是 |
否 |
否 |
|
意向排他共享 (SIX) |
是 |
否 |
否 |
否 |
否 |
否 |
|
排他 (X) |
否 |
否 |
否 |
否 |
否 |
否 |
锁兼容性具体参见：http://msdn.microsoft.com/zh-cn/library/ms186396.aspx
锁粒度和层次结构参见：http://msdn.microsoft.com/zh-cn/library/ms189849(v=sql.105).aspx
什么是死锁，为什么会产生死锁。我用 “事务把死锁给整出来啦” 标题下的两个事务产生的死锁来解释应该会更加生动形象点。
例子是这样的：
第一个事务（称为A）：先更新lives表 --->>停顿5秒---->>更新earth表
第二个事务（称为B）：先更新earth表--->>停顿5秒---->>更新lives表
先执行事务A----5秒之内---执行事务B，出现死锁现象。
过程是这样子的：
这样相互等待对方释放资源，造成资源读写拥挤堵塞的情况，就被称为死锁现象，也叫做阻塞。而为什么会产生，上例就列举出来啦。
然而数据库并没有出现无限等待的情况，是因为数据库搜索引擎会定期检测这种状况，一旦发现有情况，立马选择一个事务作为牺牲品。牺牲的事务，将会回滚数据。有点像两个人在过独木桥，两个无脑的人都走在啦独木桥中间，如果不落水，必定要有一个人给退回来。这种相互等待的过程，是一种耗时耗资源的现象，所以能避则避。
哪个人会被退回来，作为牺牲品，这个我们是可以控制的。控制语法：
set deadlock_priority <级别>
死锁处理的优先级别为 low<normal<high，不指定的情况下默认为normal，牺牲品为随机。如果指定，牺牲品为级别低的。
还可以使用数字来处理标识级别：-10到-5为low，-5为normal，-5到10为high。
死锁耗时耗资源，然而在大型数据库中，高并发带来的死锁是不可避免的，所以我们只能让其变的更少。
可参考：http://msdn.microsoft.com/zh-cn/library/ms191242(v=sql.105).aspx
查看锁活动情况：
--查看锁活动情况 select * from sys.dm_tran_locks --查看事务活动情况 dbcc opentran
可参考：http://msdn.microsoft.com/zh-cn/library/ms190345.aspx
所谓事物隔离级别，就是并发事务对同一资源的读取深度层次。分为5种。
--语法 set tran isolation level <级别>
read uncommitted隔离级别的例子：
begin tran set deadlock_priority low update Earth set Animal=‘老虎‘ waitfor delay ‘0:0:5‘ --等待5秒执行下面的语句 rollback tran
开另外一个查询窗口执行下面语句
set tran isolation level read uncommitted select * from Earth --读取的数据为正在修改的数据 ，脏读 waitfor delay ‘0:0:5‘ --5秒之后数据已经回滚 select * from Earth --回滚之后的数据
read committed隔离级别的例子：
begin tran update Earth set Animal=‘老虎‘ waitfor delay ‘0:0:10‘ --等待5秒执行下面的语句 rollback tran
set tran isolation level read committed select * from Earth ---获取不到老虎，不能脏读 update Earth set Animal=‘猴子1‘ --可以修改 waitfor delay ‘0:0:10‘ --10秒之后上一个事务已经回滚 select * from Earth --修改之后的数据，而不是猴子
剩下的几个级别，不一一列举啦，自己理解吧。
发生死锁的时候，数据库引擎会自动检测死锁，解决问题，然而这样子是很被动，只能在发生死锁后，等待处理。
然而我们也可以主动出击，设置锁超时时间，一旦资源被锁定阻塞，超过设置的锁定时间，阻塞语句自动取消，释放资源，报1222错误。
好东西一般都具有两面性，调优的同时，也有他的不足之处，那就是一旦超过时间，语句取消，释放资源，但是当前报错事务，不会回滚，会造成数据错误，你需要在程序中捕获1222错误，用程序处理当前事务的逻辑，使数据正确。
--查看超时时间,默认为-1 select @@lock_timeout --设置超时时间 set lock_timeout 0 --为0时，即为一旦发现资源锁定，立即报错，不在等待，当前事务不回滚，设置时间需谨慎处理后事啊，你hold不住的。
查看与杀死锁和进程
--检测死锁
--如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？
--这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。
use master
go
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select ‘现在没有阻塞和死锁信息‘ as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select ‘引起数据库死锁的是: ‘+ CAST(@bl AS VARCHAR(10)) + ‘进程号,其执行的SQL语法如下‘
else
select ‘进程号SPID：‘+ CAST(@spid AS VARCHAR(10))+ ‘被‘ + ‘进程号SPID：‘+ CAST(@bl AS VARCHAR(10)) +‘阻塞,其当前进程执行的SQL语法如下‘
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
--杀死锁和进程
--如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[p_killspid]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)
drop procedure [dbo].[p_killspid]
GO
create proc p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec(‘kill ‘+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
--用法
exec p_killspid ‘newdbpy‘
--查看锁信息
--如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。
--查看锁信息
create table #t(req_spid int,obj_name sysname)
declare @s nvarchar(4000)
,@rid int,@dbname sysname,@id int,@objname sysname
declare tb cursor for
select distinct req_spid,dbname=db_name(rsc_dbid),rsc_objid
from master..syslockinfo where rsc_type in(4,5)
open tb
fetch next from tb into @rid,@dbname,@id
while @@fetch_status=0
begin
set @s=‘select @objname=name from [‘+@dbname+‘]..sysobjects where id=@id‘
exec sp_executesql @s,N‘@objname sysname out,@id int‘,@objname out,@id
insert into #t values(@rid,@objname)
fetch next from tb into @rid,@dbname,@id
end
close tb
deallocate tb
select 进程id=a.req_spid
,数据库=db_name(rsc_dbid)
,类型=case rsc_type when 1 then ‘NULL 资源（未使用）‘
when 2 then ‘数据库‘
when 3 then ‘文件‘
when 4 then ‘索引‘
when 5 then ‘表‘
when 6 then ‘页‘
when 7 then ‘键‘
when 8 then ‘扩展盘区‘
when 9 then ‘RID（行 ID)‘
when 10 then ‘应用程序‘
end
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
from master..syslockinfo a left join #t b on a.req_spid=b.req_spid
go
drop table #t
SQL Server中的事务与锁
标签：lsp 搜索 也会 名称 varchar 冲突 现象 pre cut