事务与锁

 小弟我想测试一下事务锁。思路是这样的：

1：写如下的存储过程（第一次写，见笑）
CREATE   PROCEDURE   insertTest  
@count   int,  
@type   nvarchar  
AS

declare   @k   int
set   @k=0

SET   TRANSACTION   ISOLATION   LEVEL   SERIALIZABLE

begin   TRANSACTION

          while   @k <@count
          begin
insert   into   tba   values(@type)  
set   @k=@k+1
          end

COMMIT   TRANSACTION

GO

数据表   tba
id   (自动增量)
type   (nvarchar)

2.写二个程序调用这个存储过程，第一个传递参数   type= 'a '   count=20000
第二个传递参数type= 'b "   count=40

3.运行。先运行程序一，在程序一运行的时候，再运行程序二
4.期望得到的结果：当程序一结束的以后，程序二才开始运行，如果程序一没有
运行完毕，程序二应该是在等待状态。得到的数据库结果应该是type=a的数据在
前，最后的40个应该是type=b的数据

5.实际结果：程序二没等程序一完事就自己运行结束了，数据库结果是40条
type=b的记录分别夹杂在type=a的数据中
即：
应该是                   结果是
a a
a a
a b
a b
. .
. .

b a
b a
b b

请问：应该如何修改存储过程，使得结果成为我想要的？

 

现在sqlserver版块的讨论风气不错，有一些比较深入的讨论，很让人高兴。
littleLiu(小刘)，你的探索精神很好，多用些例子实践，然后通过现象看本质，就能更好地搞清楚数据库的原理。
你所说的问题，我就我的理解给你一些提示：
一般来说对于数据库的插入操作，如果指定了事务隔离级别为SERIALIZABLE，sqlserver是使用了一种叫做键范围锁的事务锁来进行锁定的。当要插入记录时，sqlserver先使用RangeI_N模式键范围锁放置在对应于要插入记录的索引项上以测试范围。如果已授权锁定，则插入记录，并且把排它   (X)   锁放置在该记录上，一般为页锁。这时，其他事务要插入记录，使用RangeI_N模式键范围锁来测试时将得不到授权，只有到一个数据页已经添满记录以后，另一个事务才可能得到授权，开始插入记录，这时，前一个事务使用的RangeI_N锁又开始等待了。所以出现了你说的id号批量交替的现象，10条左右的记录应该包括在同一数据页中。
如果你没有设置SERIALIZABLE的事务隔离级别，则每个插入操作相当于一个隐形的事务，插入一条提交一次，所以id号交替也就很正常了。

不知道这么说，你是否明白，建议你不光测试插入操作，SERIALIZABLE的事务隔离级别最大的特点在查询控制，防止出现幻影行，你删，改，查也可以试试，从多方面来了解sqlserver对事务的管理。

 

littleLiu(小刘):可以看出你对事务级别已经有了一定的了解，但有些小地方理解可能还有偏差。
首先，我们要明白事务隔离级别主要解决的是什么，是并发操作问题。而并发操作中的关键，除了两个进程同时修改或删除某条记录以外，最重要的是一个进程查询另一个进程正在修改或删除的记录的问题。对于同时修改某条记录的问题，有排他锁来保证，问题不是很大，因此事务隔离级别要解决的是读取正在修改的记录，导致得到的数据和实际不一致的现象。
我们可以先分析一下都会有什么情况出现：
假设有两个事务A和B，A主要是读取记录，B则是正在修改A要读取的记录。
1。有一条记录B正在修改，还没有提交，这时A把这条没有提交的记录读取走了，而B因为其他语句执行失败，回滚了事务，那么A读取的这条记录实际在数据库中已经不存在，这就会导致数据不一致，这种读取数据的方式叫脏读。
        以上问题的解决办法就是事务A在读取数据的时候先设置一个共享锁，设置这个共享锁，有两个目的，一是如果读取的记录正在被事务B修改，还没有提交，则事务A是无法获取共享锁，只能等到B提交完后才能读取，从而避免脏读的现象出现；同时，当A已设置上共享锁，在读取期间，B是无法再进行修改。
        这种事务隔离级别就是READ   COMMITTED，是sqlserver默认的隔离级别。
2。以上方式可以保证没有脏读，但是也有其他问题，因为当事务A查询多个结果集时，每执行完一条SQL语句，所设置的共享锁就会释放，而事务B也就可以修改A已读取的记录。如果A读取了一条记录，然后释放共享锁，B马上修改了这条记录，A在随后又读取了一次这条记录，就会出现两次读取记录不一致的情况，这就是不可重复读取数据；另外还有一种情况，A读取了大于1小于10之间的一批数据，然后用共享锁锁住这批数据，但是只能保证B不修改和删除这些数据，但B可以插入数据，比如插入原先不存在的5这条数据，这时A再次读取大于1小于10之间的数据时就会多出一条记录，造成不一致，这种数据叫幻像数据。
        以上问题的解决方法就是将事务A中读取的所有数据都设置共享锁，并且在事务结束前一个都不释放，这样就可以保证已读取的数据不能被修改和删除，但对于增加数据这种情况，sqlserver采用的解决办法就是我跟你说的范围锁，把查询的这个范围（1－10）加上锁，插入的数据在范围锁锁定的区域就不执行，保证没有幻像数据产生。
        这种事务隔离级别就叫SERIALIZABLE，是最严格的隔离级别，相当于在事务内所有   SELECT   语句中的所有表上设置   HOLDLOCK。
        但这种方式只是放置共享锁，其他事务读取还是可以的，不是根本没法查询。
       
        至于设置数据页大小的问题，这是sqlserver物理构架的一部分，一页8K，是没法更改的。
        如果你一定想要先执行完一个事务，再执行另一个事务，可以在插入语句前先执行一条查询语句，把整个表锁住，然后再插入，不过这种方法我没有试过，你可以试试看。