澳门新葡亰网址下载SQL Server中的事物

by admin on 2020年2月2日

        
在实际的多用户并发访问的生产环境里边,我们经常要尽可能的保持数据的一致性。而其中最典型的例子就是我们从表里边读取数据,检查验证后对数据进行修改,然后写回到数据
中。在读取和写入的过程中,如果在多用户并发的环境里边,其他用户已经把你要修改的数据
进行了修改是非常有可能发生的情况,这样就造成了数据的不一致性。解决这样的办法,SQL
SERVER
提出了乐观锁定和悲观锁定的概念,下边我以一个实例来说明如何使用乐观锁定和悲观锁定
解决这样的问题。

1.事务的四个属性

银行转账存储过程

/*
建立测试表:Card,代表一个真实的卡库,供用户注册.用户要从里边选出一个未使用的卡,也就是F_Flag=0的卡,给用户注册:更新F_Name,F_Time,F_澳门新葡亰网址下载,Flag字段.
如果出现两个用户同时更新一张卡的情况,是不能容忍的,也就是我们所说的数据不一致行。*/

原子性Atomicity,一致性Consistency,隔离性Isolation,持久性Durability ,即ACID特性。

USE [BankInfor]

create table Card(F_CardNO varchar(20),F_Name varchar(20),F_Flag
bit,F_Time datetime)
Go
insert Card(F_CardNo,F_Flag) select ‘1111-1111’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1112’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1113’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1114’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1115’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1116’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1117’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1118’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1119’,0
insert Card(F_CardNo,F_Flag) select ‘1111-1110’,0
Go

原子性:事务必须是一个完整工作的单元,要么全部执行,要么全部不执行。

GO

—  下边是我们经常使用的更新方案如下:

一致性:事务结束的时候,所有的内部数据都是正确的。

SET ANSI_NULLS ON

declare @CardNo varchar(20)
Begin Tran

隔离性:并发多个事务时,各个事务不干涉内部数据,处理的都是另外一个事务处理之前或之后的数据。

GO

       —  选择一张未使用的卡
        select top 1 @CardNo=F_CardNo
        from Card    where F_Flag=0
        
        —  延迟50秒,模拟并发访问.
        waitfor delay ‘000:00:50’

持久性:事务提交之后,数据是永久性的,不可再回滚。

SET QUOTED_IDENTIFIER ON

       —  把刚才选择出来的卡进行注册.

2.在SQL Server中事务被分为3类常见的事务

GO

        update Card
        set F_Name=user,
            F_Time=getdate(),
            F_Flag=1
        where F_CardNo=@CardNo

自动提交事务:是SQL
Server默认的一种事务模式,每条Sql语句都被看成一个事务进行处理。如果成功执行,则自动提交,如果错误,则自动回滚。

ALTER PROCEDURE [dbo].[Transfer](@inAccount int,@outAccount
int,@amount float)

commit

显式事务:T-sql标明,由Begin Transaction开启事务开始,由Commit
Transaction 提交事务、Rollback Transaction 回滚事务结束。

as declare

问题:如果我们在同一窗口执行同一段代码,但是去掉了waitfor
delay子句.两边执行完毕后
我们发现尽管执行了两次注册,但是只注册了一张卡,也就是两个人注册了同一张卡.

隐式事务:使用Set IMPLICIT_TRANSACTIONS ON
将将隐式事务模式打开,不用Begin
Transaction开启事务。当一个事务结束,这个模式会自动启用下一个事务,只用Commit
Transaction 提交事务、Rollback Transaction 回滚事务即可。

   @totalDeposit float;

悲观锁定解决方案

3.事物的语法

   begin

—  我们只要对上边的代码做微小的改变就可以实现悲观的锁定.

Begin Transaction:标记事务开始。

   select @totalDeposit=total from Account where AccountNum=@outAccount;

declare @CardNo varchar(20)
Begin Tran

Commit Transaction:事务已经成功执行,数据已经处理妥当。

   if @totalDeposit is null

       —  选择一张未使用的卡
        select top 1 @CardNo=F_CardNo
        from Card   with (UPDLOCK)  where F_Flag=0
        
        —  延迟50秒,模拟并发访问.
        waitfor delay ‘000:00:50’

Rollback
Transaction
:数据处理过程中出错,回滚到没有处理之前的数据状态,或回滚到事务内部的保存点。

   begin

 &

Save
Transaction
:事务内部设置的保存点,就是事务可以不全部回滚,只回滚到这里,保证事务内部不出错的前提下。

   rollback;

4.示例

   print’转出账户不存在或账户中没有存款’

—开启事务

   return;

begin tran

   end

begin try  –在这里我们可以添加错误的扑捉机制

   

   insert into A(id,name,typeid) values (1,’小王’,1)     –语句正确

   if @totalDeposit<@amount

  — save tran pigOneIn  –在这里我们可以添加保存点,保存之前正确的数据

   begin

   insert into A(id,name,typeid) values (2,’小李’,’学生’)   
–语句类型错误

   rollback;

   insert into A(id,name,typeid) values (1,’小张’,2)     –语句正确

   print’余额不足,不能操作’

end try

   return;

begin catch

   end

   SELECT Error_number() as ErrorNumber,  –错误代码

   

        Error_severity() as ErrorSeverity,  –错误严重级别,级别小于10
try catch 捕获不到

   update Account set total=total-@amount where AccountNum=@outAccount;

        Error_state() as ErrorState ,  –错误状态码

   update Account set total=total+@amount where AccountNum=@inAccount;

        Error_Procedure() as ErrorProcedure ,
–出现错误的存储过程或触发器的名称。

   print’转账成功!’

        Error_line() as ErrorLine,  –发生错误的行号

   commit;

        Error_message() as ErrorMessage  –错误的具体信息

   end;

   if(@@trancount>0)
–全局变量@@trancount,事务开启此值+1,他用来判断是有开启事务

流水号生成存储过程

      rollback tran  –出错回滚,A表中0条数据

 

     — rollback tran pigOneIn  –出错回滚,A表中1条数据

if exists(select 1 from sysobjects where id=OBJECT_ID(‘GetSerialNo’)
and xtype=’p’)

end catch

drop proc GetSerialNo

if(@@trancount>0)

go

commit tran

 

SELECT * FROM A   –如果成功A表中,将会有3条数据。

Create procedure [dbo].[GetSerialNo]   

5.使用set xact_abort

 指定是否回滚当前事务(xact_abort on/off) ,
为on时,如果当前sql出错,回滚整个事务,为off时,如果sql出错回滚当前sql语句,其它语句照常运行读写数据库。

注意:xact_abort只对运行时出现的错误有用,如果sql语句本身存在错误,那么xact_abort就没用了。

示例:

set xact_abort off

begin tran

   insert into A(id,name,typeid) values (1,’小王’,1)     –语句正确

   insert into A(id,name,typeid) values (2,’小李’,12313212313212313)   
–算术溢出错误,将插入其他两条。如果这里是’学生’,
xact_abort将失效,不插入任何数据

   insert into A(id,name,typeid) values (1,’小张’,2)     –语句正确

commit tran

select * from A

(   

6.事物并发

在多用户都用事务同时访问同一个数据资源的情况下,就会造成以下几种数据错误。

更新丢失:多个用户同时对一个数据资源进行更新,必定会产生被覆盖的数据,造成数据读写异常。

不可重复读:如果一个用户在一个事务中多次读取一条数据,而另外一个用户则同时更新啦这条数据,造成第一个用户多次读取数据不一致。

脏读:第一个事务读取第二个事务正在更新的数据表,如果第二个事务还没有更新完成,那么第一个事务读取的数据将是一半为更新过的,一半还没更新过的数据,这样的数据毫无意义。

幻读:第一个事务读取一个结果集后,第二个事务,对这个结果集经行增删操作,然而第一个事务中再次对这个结果集进行查询时,数据发现丢失或新增

我们用锁定正在操作的数据,来解决这些问题,当一个事务对一些数据块进行操作的时候,另外一个事务则不能插足这些数据块。

锁定从数据库角度看大致可以分为6种:

共享锁(S):用于读操作(SELECT),还可以叫它读锁。多个事务可以并发读取数据,但任何事务都不能修改数据,直到数据读取完成,共享锁释放。S锁通常数据被读取完毕,立即被释放。

排它锁(X):用于写操作(
INSERT、DELETE),还可以叫他独占锁、写锁。仅允许一个事务处理数据,也就是说如果你对数据资源进行增删改的操作时,其它任何事务不允许操作这块资源,直到排它锁被释放,防止同时对同一资源进行多重操作。X锁一直到事务结束才能被释放。

更新锁(U):用来预定要对此页施加X锁,它允许其它事务读,但不允许再施加U。U锁是为了防止出现死锁模式,当两个事务对一个数据资源进行先读取在修改的情况下,使用共享锁和排它锁有时会出现死锁现象,而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务,如果需要对资源进行修改,更新锁会变成排他锁,否则变为共享锁。U锁一直到事务结束时才能被释放。

意向锁:SQL
Server需要在层次结构中的底层资源上(如行,列)获取共享锁,排它锁,更新锁。例如表级放置了意
向共享锁,就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁,可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能,因为数据引
擎不需要检测资源的每一列每一行,就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型:意向共享锁(IS),意向排他锁(IX),意向排他共享锁
(SIX)。

架构锁:防止修改表结构时,并发访问的锁。

大容量更新锁:允许多个线程将大容量数据并发的插入到同一个表中,在加载的同时,不允许其它进程访问该表。

 

这些锁之间的相互兼容性,也就是,是否可以同时存在。

 

 现有的授权模式     

 请求的模式

 IS

 S

 U

 IX

 SIX

 X

 意向共享 (IS)

 是

 是

 是

 是

 是

 

 共享 (S)

 是

 是

 是

 

 

 

 更新 (U)

 是

 是

 

 

 

 

 意向排他 (IX)

 是

 

 

 是

 

 

 意向排他共享 (SIX)

 是

 

 

 

 

 

 排他 (X)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

    @sCode varchar(50)   

7.死锁

死锁是指在一组进程中的各个进程均占有不会释放的资源,但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

减少死锁的方法大致有一下几种:

按同一顺序访问对象:并发事务按同一顺序访问对象,则发生死锁的可能性会降低。

保持事务简短:尽量不要让一个事务处理过于复杂的读写操作,事务过于复杂,占用资源会增多,处理时间增长,并发执行事物通常会发生死锁。

避免事务中的用户交互:尽量不要在事务中要求用户响应,因为事务持有的任何锁只有在事务提交或回滚后才会释放,等待用户响应的时间,容易导致阻塞或死锁。

减少并发量及占用时间长的数据操作:尽量减少数据库的并发量,减少事务长时间等待。

使用较低的隔离级别:使用较低的隔离级别比使用较高的隔离级别持有共享锁的时间更短。这样就减少了锁争用。注意:先确定事务是否能在较低的隔离级别上运行。

使用基于行版本控制的隔离级别:如果将 READ_COMMITTED_SNAPSHOT
数据库选项设置为
ON,则在已提交读隔离级别下运行的事务在读操作期间将使用行版本控制而不是共享锁。

)   

8.为事务设置隔离级别

所谓事物隔离级别,就是并发事务对同一资源的读取深度层次。分为5种。

read
uncommitted
:这个隔离级别最低啦,可以读取到一个事务正在处理的数据,但事务还未提交,这种级别的读取叫做脏读。

read
committed:
这个级别是默认选项,不能脏读,不能读取事务正在处理没有提交的数据,但能修改。

repeatable
read:
不能读取事务正在处理的数据,也不能修改事务处理数据前的数据。

snapshot:指定事务在开始的时候,就获得了已经提交数据的快照,因此当前事务只能看到事务开始之前对数据所做的修改。

serializable:最高事务隔离级别,只能看到事务处理之前的数据。

语法

set tran isolation level <级别>

示例1:read uncommitted

begin tran
  set deadlock_priority low
  update A set name=’小王1′ where id=1 –原数据小王
  waitfor  delay ‘0:0:5’ –等待5秒执行下面的语句
rollback tran

set tran isolation level read uncommitted
select * from A  –读取的数据为正在修改的数据 ,脏读
waitfor  delay ‘0:0:5’  –5秒之后数据已经回滚
select * from A –回滚之后的数据

得到 name=’小王’。

示例2:read committed
begin tran
  update A set name=’小王’
  waitfor  delay ‘0:0:10’ –等待10秒执行下面的语句
rollback tran

set tran isolation level read committed
select * from A –获取不到A,不能脏读
update A set name=’小王2′  where id=1 –可以修改
waitfor  delay ‘0:0:10’  –10秒之后上一个事务已经回滚
select * from A–修改之后的数据,而不是A

得到 name=’小王2’。

 

as 

begin 

 Declare @sValue  varchar(16),@dToday   datetime,@sQZ  varchar(50)
 –这个代表前缀 

   Begin Tran     

   Begin Try   

     —
锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了 

    –在同一个事物中,执行了update语句之后就会启动锁 

    Update SerialNo set sValue=sValue where sCode=@sCode   

    Select @sValue = sValue From SerialNo where sCode=@sCode    

    Select @sQZ = sQZ From SerialNo where sCode=@sCode   

     — 因子表中没有记录,插入初始值   

     If @sValue is null   

     Begin 

       Select @sValue = convert(bigint, convert(varchar(6), getdate(),
12) + ‘000001’)   

       Update SerialNo set sValue=@sValue where sCode=@sCode   

     end else   

     Begin               –因子表中没有记录   

       Select @dToday = substring(@sValue,1,6)   

       –如果日期相等,则加1   

       If @dToday = convert(varchar(6), getdate(), 12)   

         Select @sValue = convert(varchar(16), (convert(bigint, @sValue)

  • 1))   

       else              –如果日期不相等,则先赋值日期,流水号从1开始
  

         Select @sValue = convert(bigint, convert(varchar(6), getdate(),
12) +’000001′)   

      Update SerialNo set sValue =@sValue where sCode=@sCode   

     End 

    Select result = @sQZ+@sValue     

     Commit Tran   

   End Try   

   Begin Catch   

     Rollback Tran   

     Select result = ‘Error’ 

   End Catch   

end 

 

select*from SerialNo

 

select  convert(varchar(6), getdate(), 12)+’000001′

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图