在SQL Server中使用种子表生成流水号注意顺序
2008-11-28 10:12:33 来源:WEB开发网前几天一个人问到了关于流水号重复的问题,我想了下,虽然说这个问题比较简单,但是具有广泛性,所以写了这篇博客来介绍下,希望对大家有所帮助。
在进行数据库应用开发时经常会遇到生成流水号的情况,比如说做了一个订单模块,要求订单号是唯一的,规则是:下订单时的年月日+6位的流水号这样的规则。
对于这种要生成流水号的系统,我们一般是在数据库中新建了一个种子表,每次生成新的订单时:
1.读取当天种子最大值。
2.根据种子最大值和当时的年月日生成唯一的订单号。
3.更新种子最大值,使最大值+1。
4.根据生成的订单号将订单数据插入到订单表中。
以上几步操作是在一个事务中完成,保证了流水号的连续。这个思路是正确的,使用起来好像也没有什么问题,但是在业务量比较大的情况下却经常报错:“订单号违反主键约束,不能将重复的订单号插入到订单表中。”这是怎么回事?让我们做一个简单的Demo来重现一下:
1.创建种子表和订单表,这里只是一个简单的Demo,所以就省去了很多字段,而且订单号假设就是一个流水号,不用再使用年月日+6位流水号了。
CREATETABLESeek--种子表
(
SeekValueINT
)
GO
INSERTINTOSeekVALUES(0)--种子初始值为0
GO
CREATETABLEOrders
(
OrderIDINTPRIMARYKEY,--订单号,主键
RemarkVARCHAR(5)NOTNULL
)
2.创建一个存储过程,该存储过程传入Remark参数,根据生成的流水号插入到订单表中:
CREATEPROCAddOrder--Author:深蓝
@remarkVARCHAR(5)--传入的参数
AS
DECLARE@seekint
BEGINTRAN --开启一个事务
SELECT@seek=SeekValue--读取种子表中的最大值作为流水号
FROMSeek
--生成订单号这一步省略,因为这里假定的订单的编号就是流水号
UPDATESeekSETSeekValue=@seek+1--更新种子表,使最大值+1
INSERTINTOt1VALUES(@seek,@remark)--插入一条订单数据
COMMIT--提交事务
3.新建一个查询窗口,使用以下语句调用创建的存储过程,不断的插入新订单:
WHILE1=1
EXECAddOrder'test1'--不断的插入订单
4.再新建一个查询窗口,使用通过的方式,不断的插入新订单,这样用于模拟高并发时候的情况:
WHILE1=1
EXECAddOrder'test2'
5.运行了一段时间后,我们停止这两个死循环,我们可以看到消息窗口中存在大量的异常:
消息 2627,级别 14,状态 1,过程 AddOrder,第 11 行
违反了 PRIMARY KEY 约束 'PK__Orders__C3905BAF08EA5793'。不能在对象 'dbo.Orders' 中插入重复键。
语句已终止。
为什么会这样呢?这得从事务隔离级别和锁来解释:
一般我们写程序时都是使用的是默认的事务隔离级别——已提交读,在第一步查询Seek表时,系统会为该表放置共享锁,而锁的兼容性中共享锁和共享锁是可以兼容的,所以一个事务在读取Seek表最大值时,其他事务也可以读取出相同的最大值,两个事务中读取到了相同的最大值,所以产生了相同的流水号,所以产生了相同的订单号,所以才会出现违反主键约束的错误。
既然知道了这其中的原理了,那么解决办法也就有了,只需要先对种子表中的数+1,然后再进行读取即可,修改存储过程如下:
ALTERPROCAddOrder--Author:深蓝
@remarkVARCHAR(5)
AS
DECLARE@seekint
BEGINTRAN
UPDATESeekSETSeekValue=SeekValue+1 --先修改数据
SELECT@seek=SeekValue-1--已经加了1,所以这里-1下来
FROMSeek
INSERTINTOOrdersVALUES(@seek,@remark)
COMMIT
为什么这样写就可以呢?第一步执行更新操作,系统会请求更新锁然后再升级为排他锁,因为更新锁和更新锁以及排他锁都是不兼容的,所以一个事务对Seek表进行了更新后,其他的事务就不能对表进行更新操作,只有等到事务提交以后才能继续。
这里附上锁兼容性表:
现有授予模式 | ||||||
请求模式 | IS | S | U | IX | SIX | X |
意向共享 (IS) | 是 | 是 | 是 | 是 | 是 | 否 |
共享 (S) | 是 | 是 | 是 | 否 | 否 | 否 |
更新 (U) | 是 | 是 | 否 | 否 | 否 | 否 |
意向排他 (IX) | 是 | 否 | 否 | 是 | 否 | 否 |
意向排他共享 (SIX) | 是 | 否 | 否 | 否 | 否 | 否 |
排他 (X) | 否 | 否 | 否 | 否 | 否 | 否 |
【出自博客园深蓝居,转载请注明作者出处】
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››使用word强大的搜索和替换功能
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››使用Win7自带屏幕录制功能的方法
- ››sql server自动生成批量执行SQL脚本的批处理
- ››使用linux中的quota教程
- ››sql server 2008亿万数据性能优化
更多精彩
赞助商链接