在SQL Server中使用种子表生成流水号注意顺序

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

前几天一个人问到了关于流水号重复的问题,我想了下,虽然说这个问题比较简单,但是具有广泛性,所以写了这篇博客来介绍下,希望对大家有所帮助。

在进行数据库应用开发时经常会遇到生成流水号的情况,比如说做了一个订单模块,要求订单号是唯一的,规则是:下订单时的年月日+6位的流水号这样的规则。

对于这种要生成流水号的系统,我们一般是在数据库中新建了一个种子表,每次生成新的订单时:

1.读取当天种子最大值。

2.根据种子最大值和当时的年月日生成唯一的订单号。

3.更新种子最大值,使最大值+1。

4.根据生成的订单号将订单数据插入到订单表中。

以上几步操作是在一个事务中完成,保证了流水号的连续。这个思路是正确的,使用起来好像也没有什么问题,但是在业务量比较大的情况下却经常报错:“订单号违反主键约束,不能将重复的订单号插入到订单表中。”这是怎么回事?让我们做一个简单的Demo来重现一下:

1.创建种子表和订单表,这里只是一个简单的Demo,所以就省去了很多字段,而且订单号假设就是一个流水号,不用再使用年月日+6位流水号了。

复制代码
CREATE   TABLE  Seek  -- 种子表
(
    SeekValue 
INT
)
GO
INSERT   INTO  Seek  VALUES ( 0 ) -- 种子初始值为0
GO
CREATE   TABLE  Orders
(
    OrderID 
INT   PRIMARY   KEY -- 订单号,主键
    Remark  VARCHAR ( 5 NOT   NULL

复制代码

 2.创建一个存储过程,该存储过程传入Remark参数,根据生成的流水号插入到订单表中:

复制代码
CREATE   PROC  AddOrder  -- Author:深蓝
@remark   VARCHAR ( 5 -- 传入的参数
AS
DECLARE   @seek   int  
BEGIN   TRAN    -- 开启一个事务
SELECT   @seek = SeekValue  -- 读取种子表中的最大值作为流水号
FROM  Seek

-- 生成订单号这一步省略,因为这里假定的订单的编号就是流水号

UPDATE  Seek  SET  SeekValue = @seek + 1   -- 更新种子表,使最大值+1

INSERT   INTO  t1  VALUES ( @seek , @remark -- 插入一条订单数据

COMMIT   -- 提交事务
复制代码

3.新建一个查询窗口,使用以下语句调用创建的存储过程,不断的插入新订单:

WHILE   1 = 1
EXEC  AddOrder  ' test1 '   -- 不断的插入订单

 

4.再新建一个查询窗口,使用通过的方式,不断的插入新订单,这样用于模拟高并发时候的情况:

WHILE   1 = 1
EXEC  AddOrder  ' test2 '

 

5.运行了一段时间后,我们停止这两个死循环,我们可以看到消息窗口中存在大量的异常:

消息 2627,级别 14,状态 1,过程 AddOrder,第 11 行
违反了 PRIMARY KEY 约束 'PK__Orders__C3905BAF08EA5793'。不能在对象 'dbo.Orders' 中插入重复键。

语句已终止。

为什么会这样呢?这得从事务隔离级别和锁来解释:

一般我们写程序时都是使用的是默认的事务隔离级别——已提交读,在第一步查询Seek表时,系统会为该表放置共享锁,而锁的兼容性中共享锁和共享锁是可以兼容的,所以一个事务在读取Seek表最大值时,其他事务也可以读取出相同的最大值,两个事务中读取到了相同的最大值,所以产生了相同的流水号,所以产生了相同的订单号,所以才会出现违反主键约束的错误。

既然知道了这其中的原理了,那么解决办法也就有了,只需要先对种子表中的数+1,然后再进行读取即可,修改存储过程如下:

复制代码
ALTER   PROC  AddOrder -- Author:深蓝
@remark   VARCHAR ( 5 )
AS
DECLARE   @seek   int
BEGIN   TRAN

UPDATE  Seek  SET  SeekValue = SeekValue + 1    -- 先修改数据

SELECT   @seek = SeekValue - 1   -- 已经加了1,所以这里-1下来
FROM  Seek

INSERT   INTO  Orders  VALUES ( @seek , @remark )

COMMIT
复制代码

 

为什么这样写就可以呢?第一步执行更新操作,系统会请求更新锁然后再升级为排他锁,因为更新锁和更新锁以及排他锁都是不兼容的,所以一个事务对Seek表进行了更新后,其他的事务就不能对表进行更新操作,只有等到事务提交以后才能继续。

这里附上锁兼容性表:


现有授予模式



请求模式 IS S U IX SIX X
意向共享 (IS)
共享 (S)
更新 (U)
意向排他 (IX)
意向排他共享 (SIX)
排他 (X)
目录
相关文章
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
428 13
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
273 9
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
180 6
|
SQL 监控 关系型数据库
PolarDB产品使用问题之SQL防火墙怎么拦截没有指定WHERE条件的特定表的SQL语
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
1025 1
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
1046 0
|
SQL 存储 数据库
MySQL设计规约问题之如何处理日志类型的表
MySQL设计规约问题之如何处理日志类型的表
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
456 0
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
319 0