关于SQL事务的测试

用了半辈子存贮过程，却没有严格意义地使用事务，今天看到自己以前写的一个SQL事务测试的存贮过程，心血来潮，开始整理一下：
从简单的说起，如果insert、update、delete放在begin tran与commit tran/rollback tran之间，则操作不会被立即执行，而是等到commit tran时才执行，如果遇到rollback tran则取消。这里使用insert来测试，数据库使用pubs的，示例代码：

CREATE PROCEDURE S_Test1 
AS

--删除杂项数据
delete from jobs where job_desc like 'yzx-test%'

--插入一条记录，并提交
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test1', 10, 20)
commit tran

--插入一条记录，但回滚
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test2', 33, 88)
rollback tran

--显示最后结果
select * from jobs where job_desc like 'yzx-test%'
GO

 

这个应该没有问题，现在的多一些问题，在没有提交或回滚前，SQL是否能检测到记录成功插入，示例代码：

CREATE PROCEDURE S_Test2 
AS

declare @i as int
--删除杂项数据
delete from jobs where job_desc like 'yzx-test%'

--插入一条记录，并提交
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test1', 10, 20)
  select @i=count(*) from jobs where job_desc='yzx-test1'
  print 'test1:  ' + str(@i)
commit tran

--插入一条记录，但回滚
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test2', 33, 88)
  select @i=count(*) from jobs where job_desc='yzx-test2'
  print 'test2:  ' + str(@i)
rollback tran

--显示最后结果
select * from jobs where job_desc like 'yzx-test%'
GO

结果表明，在回滚之前，SQL语句是检测得到记录插入的。

再来，如果在回滚之前，另一个进程的SQL语句能否检测得到记录的插入，代码如下：

CREATE PROCEDURE S_Test3
AS

--清空测试数据
delete from jobs where job_desc like 'yzx-test%'

--插入一条记录，延时，然后回滚
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test2', 33, 88)
  --等待10秒
  waitfor delay '00:00:10'
rollback tran

GO

查询分析器中开两个窗口，一个窗口执行S_Test3，另一个窗口执行
  select count(*) from jobs where job_desc='yzx-test2'
结果我“惊奇地”发现，第二个窗口要等到第一个窗口执行完毕后才能再执行。加大延迟时间，再去查看当前活动的“锁/进程ID”，
spid 53 (正在阻塞)，对应命令：S_Test3，
对象 锁类型 模式 状态 所有者 索引 资源
pubs DB S GRANT Sess
pubs.dbo.jobs KEY X GRANT Xact PK__jobs__117F9D94 (1500140b9389)
pubs.dbo.jobs PAG IX GRANT Xact PK__jobs__117F9D94 1:115
pubs.dbo.jobs TAB IX GRANT Xact

spid 54 (阻塞者53)，对应命令：select count(*) from jobs where job_desc='yzx-test2'
对象 锁类型 模式 状态 所有者 索引 资源
pubs DB S GRANT Sess
pubs.dbo.jobs KEY S WAIT Xact PK__jobs__117F9D94 (1500140b9389)
pubs.dbo.jobs PAG IS GRANT Xact PK__jobs__117F9D94 1:115
pubs.dbo.jobs TAB IS GRANT Xact
可以看出spid 53中在锁类型为KEY的位置有一个排它锁X，导致spid 54访问相同KEY位置时处于等待。（锁类型与模式的详细说明可查SQL帮助文档中的“访问和更改关系数据”->“锁定”->“显示锁定信息”与“SQL Server 中的锁定介绍”

这个结果意味着，对于事务，不要太随意使用了，否则造成其它进程的延时，与我的实际经验符合，即事务只用在要处理的那几句就好。

现在再试一种情况：事务中，如果直接退出，到底是提交还是回滚？

CREATE PROCEDURE S_Test4
AS

--清空测试数据
delete from jobs where job_desc like 'yzx-test%'

--插入一条记录，直接返回，看最后是回滚还是提交
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test2', 33, 88)
  return
rollback tran
GO

系统执行后出错，错误信息：
EXECUTE 后的事务计数指出缺少了 COMMIT 或 ROLLBACK TRANSACTION 语句。原计数 = 1，当前计数 = 2。
结果是数据写进去了。即默认是提交。

记得以前我与朋友曾经讨论过事务是否保证原子性，该问题的应用范围是生成最大单号，示例代码如下：

CREATE PROCEDURE S_Test5
AS

--清空测试数据
delete from jobs where job_desc like 'yzx-test%'

declare @i as tinyint

--取当前最大min_lvl，+1后插入到新记录中,
begin tran
  select @i=max(min_lvl) from jobs

  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test', @i+1, 250)

  waitfor delay '00:00:10'

commit tran
GO

在查询分析器的两个窗口中同时运行该存贮过程（一先一后，没差几秒），只有当前一个执行完毕后，后一个现在看来，因为有锁的存在，所以使用事务就可以保证单号的唯一性。

最后再试一个，如果事务中出错会如何？

CREATE PROCEDURE S_Test6
AS

declare @i as int
--清空测试数据
delete from jobs where job_desc like 'yzx-test%'

--插入一条记录，直接返回，看最后是回滚还是提交
begin tran
  insert into jobs(job_desc, min_lvl, max_lvl)
    values('yzx-test2', 33, 88)
  set @i=1/0
commit tran
--rollback tran
GO

测试结果，应该是系统继续向前走，所以后面是提交就提交，后面是回滚就回滚。

我能知道的事务就这些了，但关于锁、出错处理，还是要抽空去测试一下它的特性。