新建 文本文档.txt

主要介绍了sql的基础教程

事务管理案例
一、课堂演示案例
use pubs
go
----------------------------------------------------------------------------------------------------------------------
例一：事务提交
begin  transaction test1

 update authors 
 set au_lname='a'
 where au_id='172-32-1176'

 update authors 
 set phone='cccccccccccc'
 where au_id='172-32-1176'

commit transaction test1
go
----------------------------------------------------------------------------------------------------------------------

例二：事务回滚
begin  transaction test2

 declare @i int
 set @i = 0

 update authors 
 set au_lname='b'
 where au_id='172-32-1176'

 if @@error > 0
   set @i = 1

 update authors 
 set phone='bccccccccccc'
 where au_id='172-32-1176'

 if @@error > 0
   set @i = 1

 if @i = 0
 begin
   commit transaction test2
 end
 else
 begin
  rollback transaction test2
 end  

go
----------------------------------------------------------------------------------------------------------------------

例三：事务
begin  transaction test1
 update authors 
 set au_lname='b'
 where au_id='172-32-1176'
 save transaction test2
 
 if 1=1 
 begin
   update authors 
   set phone='bbbbbbbbb'
   where au_id='172-32-1176'
   rollback transaction test2
   commit transaction test1
 end
 else
 begin
   commit transaction test1
 end
go
----------------------------------------------------------------------------------------------------------------------

例四：隐式事务
set implicit_transactions on  --启动隐性事务模式
set implicit_transactions off --关闭隐性事务模式
----------------------------------------------------------------------------------------------------------------------

例五：测试锁定(dirty read)
set transaction isolation level read uncommitted


begin tran
  insert into customers(customerid,companyname)
  values('aa','aa')

  waitfor delay '00:00:10'
rollback tran


select * from customers where customerid like 'a%'
----------------------------------------------------------------------------------------------------------------------

例六：测试锁定(nonrepeatable read)
set transaction isolation level read committed
begin tran
  select * from customers where customerid like 'a%'
  waitfor delay '00:00:10'
rollback tran
  select * from customers where customerid like 'a%'
commit tran

update customers set companyname = 'abcde' where customerid = 'ANATR'
----------------------------------------------------------------------------------------------------------------------

例七：测试锁定(phantom read)
set transaction isolation level read committed
begin tran
  select * from customers where customerid like 'a%'
  waitfor delay '00:00:10'
  select * from customers where customerid like 'a%'
commit tran

insert into customers(customerid,companyname)
values('abcde','abcde')
----------------------------------------------------------------------------------------------------------------------

例八：测试锁定
begin  transaction 

  select *
  from authors with(tabLOCKx) 

  waitfor delay '00:02:00'

commit transaction 

select * from authors