事务与锁应用实战之手工生成流水号

事务与锁应用实战之手工生成流水号

作者:no_mIss

对于编号，通常我们会使用自动编号，但有时也会生成诸如BH0001之类的编号?
方法一般是查询表中最大的值，然后将这个值加1即得到新的编号。

这里我们不讨完全在一个表的例子，我们为了更好的扩展性，
单独建一个表，来放maxid，其原理是相同的。


首先建个表，为了生成不同项目的最大ID号
CREATE TABLE T_table(id int ,projectid char(1) PRIMARY KEY)
INSERT INTO T_table SELECT 1,'A'
INSERT INTO T_table SELECT 1,'B'
INSERT INTO T_table SELECT 1,'C'

注：A、B、C代表不同的项目，在这里我们只用A
本篇假定是为了生成订单号OrderId。


接下来创建一个存储过程，来返回订单号OrderId

CREATE PROC P_CreateOrderId       
    @ProjectId CHAR(1),--项目名称
    @reOrderId VARCHAR(50) OUTPUT--返回的订单号
AS
BEGIN TRAN
    DECLARE @maxid int
    --取projectid为A的最大id
    SELECT @maxid = (id+1) FROM T_table WHERE projectid = @ProjectId
    --这里我们显示的设置下延时10s钟
    WAITFOR DELAY '00:00:10'
    UPDATE T_table SET id = @maxid FROM T_table WHERE projectid = @ProjectId
    SELECT @reOrderId = @maxid    
COMMIT TRAN

调用方法：
declare @reOrderId int
exec P_CreateOrderId 'A',@reOrderId out
select @reOrderId

得到这个Orderid后，你可以再对其格式化了，比如BH00001之类。

初看这个你可能觉得没有任何问题，但如果你做过多用户并发存取数据的项目，你会发现一个问题：
如果两个用户同时执行
SELECT @maxid = (id+1) FROM T_table WHERE projectid = @ProjectId
的话，那么就会取到相同的值@maxid.这显然是不可以接受的。

为了更明显的说明问题，我加了这一句：
WAITFOR DELAY '00:00:10'


当我们执行
declare @reOrderId int
exec P_CreateOrderId 'A',@reOrderId out
select @reOrderId
时，我们同时再另一个进程里执行
select * from T_table where projectid='A'
发现，可以取到当前表里projectid为A的数据，显然说明当多个用户同时执行这个PROC时，
都取到了同一个(maxid+1）做为返回的订单ID。


呵呵。。。。。说点别的。。。。

我们知道MSSQL所有对数据的操作都是先放置锁，再操作的。
当SELECT时，放置共享锁，而共享锁执行完就释放，所以当SELECT完后,
其它的用户仍然可以再放置共享锁，所以我们只要让SELECT完后不释放，直到
其所在的事务完全释放后再释放共享锁就可以了。

好了，到这里目标很明确了。?

让我们来提高事务的隔离等级:
将这句SELECT @maxid = (id+1) FROM T_table WHERE projectid = @ProjectId
改成：SELECT @maxid = (id+1) FROM T_table(XLOCK,PAGLOCK) WHERE projectid = @ProjectId

说明：
XLOCK 使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。使用PAGLOCK或TABLOCK指定该锁。


如此设置后，当一个用户执行该存储过程时，其它的用户需要排下队等下了。
顺便说一声：像这种事务越短越好。