事务与锁应用实战之手工生成流水号
事务与锁应用实战之手工生成流水号
作者: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指定该锁。
如此设置后，当一个用户执行该存储过程时，其它的用户需要排下队等下了。
顺便说一声：像这种事务越短越好。
说完这个希望对大家有所帮助，如果感兴趣，有时间再写一个降低事务隔离等级的例子。?
本人水平有限，如有不妥，请指正。
作者: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指定该锁。
如此设置后，当一个用户执行该存储过程时，其它的用户需要排下队等下了。
顺便说一声：像这种事务越短越好。
说完这个希望对大家有所帮助，如果感兴趣，有时间再写一个降低事务隔离等级的例子。?
本人水平有限，如有不妥，请指正。
