SqlServer数据库，并发运行，获取唯一流水号
当我们设计系统时，特别是涉及到主从表的时候，一般都会碰到从表的ID,要和主表的ID一致的情况。还有单据的流水号问题，要保证在并发请求时，流水号不能重复的现象。网上也有很多解决类似问题的代码，参照网上的方法，结合自己的理解，在SqlServer 2005下，设计相关的表，存储过程，实现这样的功能。我将一个流水号，分为四部分：头部、中间1、中间2，尾部。其中四部分类型，分成三种类型，固定值(fixed),日期类型（yyyyMMdd... ...）,流水类型(sync)。
固定值类型，值保持不变；日期类型，根据格式不同，值也就相应的不同；流水类型很容易理解，就是从1一直递增下去。
其中流水号的中间1，中间2，部分。是为了处理以下的这种情况设计的。比如说，当天的流水号从1，第二天又要从1进行。如果想要这样的流水号，则将中间1类型设置为日期类型，中间2类型设置为流水类型即可。
另外流水的四部分，中的每个部分，还有长度的定义，该长度用于当该部分为流水类型时，可以设置流水号的长度，不够长度则前面补充0；
表设计如下：
/****** 对象: Table . 脚本日期: 08/17/2014 23:47:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE .(
(8, 0) NOT NULL,
(200) COLLATE Chinese_PRC_CI_AS NOT NULL,
(200) COLLATE Chinese_PRC_CI_AS NULL,
(400) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(2, 0) NULL CONSTRAINT DEFAULT ((0)),
(50) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(2, 0) NULL CONSTRAINT DEFAULT ((0)),
(50) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(2, 0) NULL CONSTRAINT DEFAULT ((0)),
(50) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(2, 0) NULL CONSTRAINT DEFAULT ((0)),
(10) COLLATE Chinese_PRC_CI_AS NULL,
NULL,
(1) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
(500) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)WITH (IGNORE_DUP_KEY = OFF) ON
) ON
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当配置类为sync时，此字段记录流水的长度，如果长度不够，前面补充''0''' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_sequence_info', @level2type=N'COLUMN', @level2name=N'sync_head_len'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当配置类为sync时，此字段记录流水的长度，如果长度不够，前面补充''0''' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_sequence_info', @level2type=N'COLUMN', @level2name=N'sync_middle1_len'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当sync_middle_type1为日期类型，而sync_middle_type2为sync类型时，则sync_middle_value2值为当天的序列号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_sequence_info', @level2type=N'COLUMN', @level2name=N'sync_middle_type2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当配置类为sync时，此字段记录流水的长度，如果长度不够，前面补充''0''' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_sequence_info', @level2type=N'COLUMN', @level2name=N'sync_middle2_len'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当配置类为sync时，此字段记录流水的长度，如果长度不够，前面补充''0''' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_sequence_info', @level2type=N'COLUMN', @level2name=N'sync_end_len'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'流水信息表，方便生成流水编码。' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_sequence_info'
GO
USE
GO
ALTER TABLE . WITH CHECK ADD CONSTRAINT CHECK (( IS NULL OR (='sync' OR ='yyMMdd' OR ='yy-MM-dd' OR ='yyyyMMdd' OR ='yyyy-MM-dd' OR ='yyMM' OR ='yy-MM' OR ='yyyyMM' OR ='yyyy-MM' OR ='fixed')))
GO
ALTER TABLE . WITH CHECK ADD CONSTRAINT CHECK (( IS NULL OR (='sync' OR ='yyMMdd' OR ='yy-MM-dd' OR ='yyyyMMdd' OR ='yyyy-MM-dd' OR ='yyMM' OR ='yy-MM' OR ='yyyyMM' OR ='yyyy-MM' OR ='fixed')))
GO
ALTER TABLE . WITH CHECK ADD CONSTRAINT CHECK (( IS NULL OR (='sync' OR ='yyMMdd' OR ='yy-MM-dd' OR ='yyyyMMdd' OR ='yyyy-MM-dd' OR ='yyMM' OR ='yy-MM' OR ='yyyyMM' OR ='yyyy-MM' OR ='fixed')))
GO
ALTER TABLE . WITH CHECK ADD CONSTRAINT CHECK (( IS NULL OR (='sync' OR ='yyMMdd' OR ='yy-MM-dd' OR ='yyyyMMdd' OR ='yyyy-MM-dd' OR ='yyMM' OR ='yy-MM' OR ='yyyyMM' OR ='yyyy-MM' OR ='fixed')))
GO
而，如果想在并行环境下，获取唯一的流水号，经过思考，采用存储过程完成。
其原理是：当一个请求过来时，先使用一个guid,判断流水表的数据字段sync_ctrl是否被预占（0：未预占，1：已预占）；如果没有被预占，则当前请求，进行预占操作。然后计算流水号。计算完毕后，将预占位取消。将流水号返回。具体请参考存储过程：P_SeqStr
相关代码如下：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取当前日期的格式化字符串
--author:程晓鹏
--date:2014.8.3
CREATE function .(@dateType varchar(20))
returns varchar(50)
as
begin;
declare @result varchar(50); --定义返回值
declare @strYY varchar(4); --年份yyyy
declare @strYY2 varchar(2); --年份yy
declare @strMM varchar(2); --月份MM
declare @strDD varchar(2); --天数dd
declare @strDateType varchar(20);
set @strDateType = upper(@dateType); --全部变为大写，进行判断
set @strYY = dbo.TrimString(year(getdate())); --4位数字的年份字符串
set @strYY2 = substring(@strYY, 3, 2); --4为年份数字的最后两位
set @strMM = dbo.FormatString(dbo.TrimString(month(getdate())), 'H', '0', 2); --月份两位数据
set @strDD = dbo.FormatString(dbo.TrimString(day(getdate())), 'H', '0', 2); --日，两位数表示
set @result = '';
--根据入参，进行返回值的赋值
if (@strDateType = 'YYYYMMDD')
begin;
set @result = @strYY + @strMM + @strDD;
end;
else if (@strDateType = 'YYYY-MM-DD')
begin;
set @result = @strYY + '-' + @strMM + '-' + @strDD;
end;
else if (@strDateType = 'YYMMDD')
begin;
set @result = @strYY2 + @strMM + @strDD;
end;
else if (@strDateType = 'YY-MM-DD')
begin;
set @result = @strYY2 + '-' + @strMM + '-' + @strDD;
end;
else if (@strDateType = 'YYMM')
begin;
set @result = @strYY2 + @strMM;
end;
else if (@strDateType = 'YY-MM')
begin;
set @result = @strYY2 + '-' + @strMM;
end;
else if (@strDateType = 'YYYYMM')
begin;
set @result = @strYY + @strMM;
end;
else if (@strDateType = 'YYYY-MM')
begin;
set @result = @strYY + '-' + @strMM;
end;
else if (@strDateType = 'YYYY')
begin;
set @result = @strYY;
end;
else if (@strDateType = 'YY')
begin;
set @result = @strYY2;
end;
else
begin;
set @result = @strYY + '-' + @strMM + '-' + @strDD; --默认值
end;
return @result; --返回结果
end;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--格式化字符串，如果原字符串str长度，没有达到入参length，规定的长度，则使用fileChar，进行字符填充。
--其中填充位置fillPosition如果为'H',则在原字符串前；若为'E',则在原字符串后。
--author:程晓鹏
--date:2014.8.3
CREATE function .(
@str varchar(8000), --原始字符串
@fillPosition varchar(1), --填充位置;H:在原字符串前面；E:在原字符串的后面
@fillChar varchar(1), --填充的字符串
@length int --返回结果的字符串长度
)
returns varchar(8000)
as
begin;
declare @result varchar(8000); --定义返回值
declare @strFillTmp varchar(8000); --临时填充字符串
declare @strFileTmpLen int; --临时填充的长度
declare @strLen int; --输入字符串的长度
declare @strFillChar varchar(1); --填充字符
declare @i int; --循环变量
set @strFillChar = ''; --设置默认填充字符为空格
set @strFillTmp = ''; --临时填充字符串,默认为空
set @strLen = len(@str);
set @i = 0;
--判断入参,设置填充字符
if(len(@fillChar) > 0)
begin;
set @strFillChar = @fillChar;
end;
else
begin;
set @strFillChar = ' ';
end;
--判断是否需要进行字符串填充
if(@strLen < @length)
begin;
set @strFileTmpLen = @length - @strLen; --计算差异的长度
while(@i < @strFileTmpLen)
begin;
set @strFillTmp = @strFillTmp + @strFillChar; --进行差异字符串填充
set @i = @i + 1;
end;
end;
else
begin;
set @result = @str;
end;
--根据填充的位置,进行返回字符串的处理
if(@fillPosition = 'H')
begin;
set @result = @strFillTmp + @str;
end;
else if(@fillPosition = 'E')
begin;
set @result = @str + @strFillTmp
end;
--返回结果
return @result;
end;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--删除字符串左右两侧的空格
--author:程晓鹏
--date:2014.8.3
create function .(@str varchar(8000))
returns varchar(8000)
as
begin;
declare @result varchar(8000); --定义返回值
set @result = ltrim(rtrim(@str));
return @result;
end;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--判断类型是否是日期类型参数
--author:程晓鹏
--date:2014.8.11
create function .(@strType varchar(20))
returns varchar(1)
as
begin;
declare @result varchar(1); --定义返回值
declare @strDateType varchar(20);
set @strDateType = upper(@strType); --全部变为大写，进行判断
set @result = '0';
--根据入参，进行返回值的赋值
if (@strDateType = 'YYYYMMDD')
begin;
set @result = '1';
end;
else if (@strDateType = 'YYYY-MM-DD')
begin;
set @result = '1';
end;
else if (@strDateType = 'YYMMDD')
begin;
set @result = '1';
end;
else if (@strDateType = 'YY-MM-DD')
begin;
set @result = '1';
end;
else if (@strDateType = 'YYMM')
begin;
set @result = '1';
end;
else if (@strDateType = 'YY-MM')
begin;
set @result = '1';
end;
else if (@strDateType = 'YYYYMM')
begin;
set @result = '1';
end;
else if (@strDateType = 'YYYY-MM')
begin;
set @result = '1';
end;
else if (@strDateType = 'YYYY')
begin;
set @result = '1';
end;
else if (@strDateType = 'YY')
begin;
set @result = '1';
end;
else
begin;
set @result = '0'; --默认值
end;
return @result; --返回结果
end;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取流水号
--author:程晓鹏
--date:2014.8.14
CREATE proc .(@syncCode varchar(200), @result varchar(400) output)
as
begin;
--开始事务
set xact_abort on;
begin transaction;
--以下定义的是查询后，各个字段的值
declare @strHeadType varchar(50);
declare @strHeadValue varchar(50);
declare @intHeadLen numeric(2,0);
declare @strMiddleType1 varchar(50);
declare @strMiddleValue1 varchar(50);
declare @strMiddleValue1_new varchar(50);
declare @intMiddleLen1 numeric(2,0);
declare @strMiddleType2 varchar(50);
declare @strMiddleValue2 varchar(50);
declare @intMiddleLen2 numeric(2,0);
declare @strEndType varchar(50);
declare @strEndValue varchar(50);
declare @intEndLen numeric(2,0);
declare @strSplitValue varchar(10);
declare @updateTime datetime;
--最终结果各个节点的数据值
declare @str_HeadValue varchar(50);
declare @str_MiddleValue1 varchar(50);
declare @str_MiddleValue2 varchar(50);
declare @str_EndValue varchar(50);
--定义行id,避免存储过程，并发执行，出现返回的流水重复的现象
declare @strNewID varchar(50);
declare @intCtrl int;
--设置返回值为空字符串
set @result = '';
set @strNewID = newid();
--当sync_ctrl = '0',用当前的row_id，进行预占操作，这样可以防止并行运行造成流水重复现象
update dbo.sys_sequence_info set
row_id = @strNewID,
sync_ctrl = '1'
where sync_ctrl = '0' and sync_code = @syncCode;
commit;
--判断当前rowid，是否预占成功，如果没有预占成功，则返回1。否则继续执行，直到计算出流水ID
select @intCtrl = count(1)
from dbo.sys_sequence_info
where row_id = @strNewID and sync_code = @syncCode;
if (@intCtrl = 0) --当没有占用时，返回值1
begin;
return 1;
end;
--执行查询操作
select @strHeadType = upper(isnull(sync_head_type, 'fixed')),
@strHeadValue = isnull(sync_head_value, ''),
@intHeadLen = isnull(sync_head_len, 0),
@strMiddleType1 = upper(isnull(sync_middle_type1, 'fixed')),
@strMiddleValue1 = isnull(sync_middle_value1, ''),
@intMiddleLen1 = isnull(sync_middle1_len, 0),
@strMiddleType2 = upper(isnull(sync_middle_type2, 'fixed')),
@strMiddleValue2 = isnull(sync_middle_value2, ''),
@intMiddleLen2 = isnull(sync_middle2_len, 0),
@strEndType = upper(isnull(sync_end_type, 'fixed')),
@strEndValue = isnull(sync_end_value, ''),
@intEndLen = isnull(sync_end_len, 0),
@strSplitValue = isnull(sync_split_value, ''),
@updateTime = sync_update_time
from dbo.sys_sequence_info
where sync_ctrl = '1' and row_id =@strNewID and sync_code = @syncCode;
--对headType进行处理，当为日期类型时，将当前日期字符串进行赋值;
--若为sync类型时，则在原有值得基础上自增1
if (dbo.ValidDate(@strHeadType) = '1')
begin;
set @strHeadValue = dbo.FormatDate(@strHeadType);
end;
else if (@strHeadType = upper('sync'))
begin;
if (@strHeadValue = '')
begin;
set @strHeadValue = '1'; --当为空时，则值为'1'
end;
else
begin;
set @strHeadValue = convert(varchar, convert(int,@strHeadValue + 1));
end;
end;
--对MiddleType1进行处理，当为日期类型时，将当前日期字符串进行赋值;
--若为sync类型时，则在原有值得基础上自增1
if (dbo.ValidDate(@strMiddleType1) = '1')
begin;
set @strMiddleValue1_new = dbo.FormatDate(@strMiddleType1);
end;
else if (@strMiddleType1 = upper('sync'))
begin;
if (@strMiddleValue1 = '')
begin;
set @strMiddleValue1_new = '1'; --当为空时，则值为'1'
end;
else
begin;
set @strMiddleValue1_new = convert(varchar, convert(int,@strMiddleValue1 + 1));
end;
end;
else
begin;
set @strMiddleValue1_new = @strMiddleValue1;
end;
--对MiddleType2进行处理:
--当MiddleType1为日期类型，并且MiddleType2为sync类型时，则MiddleValue2的值为当前的流水号，流水号默认从1，进行累加计算
--若MiddleType2为日期类型时，将当前日期字符串进行赋值;
if (dbo.ValidDate(@strMiddleType1) = '1' and @strMiddleType2 = upper('sync'))
begin;
if (@strMiddleValue1 = dbo.FormatDate(@strMiddleType1))
begin;
set @strMiddleValue2 = convert(varchar, convert(int,@strMiddleValue2 + 1));
end;
else
begin;
set @strMiddleValue2 = '1'; --MiddleType1不是当天，则从'1',进行重新计数
end;
end;
else if (dbo.ValidDate(@strMiddleType2) = '1')
begin;
set @strMiddleValue2 = dbo.FormatDate(@strMiddleType2);
end;
--对EndType进行处理，当为日期类型时，将当前日期字符串进行赋值;
--若为sync类型时，则在原有值得基础上自增1
if (dbo.ValidDate(@strEndType) = '1')
begin;
set @strEndValue = dbo.FormatDate(@strEndType);
end;
else if (@strEndType = upper('sync'))
begin;
if (@strEndValue = '')
begin;
set @strEndValue = '1'; --当为空时，则值为'1'
end;
else
begin;
set @strEndValue = convert(varchar, convert(int,@strEndValue + 1));
end;
end;
--计算各个节点的返回值
--HeadType
if (@strHeadType = upper('sync'))
begin;
set @str_HeadValue = dbo.FormatString(@strHeadValue, 'H', '0', @intHeadLen);
end;
else if (len(@strHeadValue) > 0)
begin;
set @str_HeadValue = @strHeadValue;
end;
else
begin;
set @str_HeadValue = '';
end;
--MiddleType1
if (@strMiddleType1 = upper('sync'))
begin;
set @str_MiddleValue1 = dbo.FormatString(@strMiddleValue1_new, 'H', '0', @intMiddleLen1);
end;
else if (len(@strMiddleValue1) > 0)
begin;
set @str_MiddleValue1 = @strMiddleValue1_new;
end;
else
begin;
set @str_MiddleValue1 = '';
end;
--MiddleType2
if (@strMiddleType2 = upper('sync'))
begin;
set @str_MiddleValue2 = dbo.FormatString(@strMiddleValue2, 'H', '0', @intMiddleLen2);
end;
else if (len(@strMiddleValue2) > 0)
begin;
set @str_MiddleValue2 = @strMiddleValue2;
end;
else
begin;
set @str_MiddleValue2 = '';
end;
--EndType
if (@strEndType = upper('sync'))
begin;
set @str_EndValue = dbo.FormatString(@strEndValue, 'H', '0', @intEndLen);
end;
else if (len(@strEndValue) > 0)
begin;
set @str_EndValue = @strEndValue;
end;
else
begin;
set @str_EndValue = '';
end;
--计算，最后要返回的字符串
if (len(@str_HeadValue) > 0) --当HeadValue含有字符串时，添加进去
begin;
set @result = @result + @str_HeadValue;
end;
if (len(@str_MiddleValue1) > 0)
begin;
if (len(@str_HeadValue) > 0) --当HeadValue有数据时，添加分割字符串
begin;
set @result = @result + @strSplitValue;
end;
set @result = @result + @str_MiddleValue1;
end;
if (len(@str_MiddleValue2) > 0)
begin;
if (len(@str_MiddleValue1) > 0) --当MiddleValue1有数据时，添加分割符
begin;
set @result = @result + @strSplitValue;
end;
else if (len(@str_HeadValue) > 0) --当MiddleValue1为空，而HeadValue不为空，则添加分割字符串
begin;
set @result = @result + @strSplitValue;
end;
set @result = @result + @str_MiddleValue2;
end;
if(len(@str_EndValue) > 0)
begin;
if (len(@str_MiddleValue2) > 0) --当MiddleValue2有数据时，添加分割符
begin;
set @result = @result + @strSplitValue;
end;
else if (len(@str_HeadValue) > 0 or len(@str_MiddleValue1) > 0) --当MiddleValue2没有数据，而HeadValue或者MiddleValue有数据，则添加分割字符串
begin;
set @result = @result + @strSplitValue;
end;
set @result = @result + @str_EndValue;
end;
--更新数据库中的数据,为下次计算做准备
update dbo.sys_sequence_info set
sync_value = @result,
sync_head_value = @strHeadValue,
sync_middle_value1 = @strMiddleValue1_new,
sync_middle_value2 = @strMiddleValue2,
sync_end_value = @strEndValue,
sync_update_time = getdate(),
sync_ctrl = '0'
where sync_ctrl = '1' and row_id =@strNewID and sync_code = @syncCode;
return 0; --正常返回
commit transaction; --提交事务
end;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--获取流水号
--author:程晓鹏
--date:2014.8.15
CREATE proc .(@syncCode varchar(200), @syncStr varchar(400) output)
as
begin;
declare @i int;
declare @p_return_value int; --定义存储过程返回值
declare @maxcount int; --定义获取流水号时，如果失败，重试的次数
declare @strResult varchar(400); --返回的字符串
declare @intCount int; --记录syncCode是否存在
--状态值； 0：正常返回流水号；1:获取流水号超时；2:入参在配置流水表不存在
declare @state int;
set @state = 0;
set @i = 0;
set @maxcount = 500;
set @SyncStr = '';
set @strResult = ''; --要返回的字符串
--判断入参@syncCode，在数据库，是否配置的有
select @intCount = count(1)
from dbo.sys_sequence_info
where sync_code = @syncCode;
if (@intCount = 0) --当入参@syncCode，在数据库不存在时，返回值2
begin;
set @state = 2;
set @SyncStr = '错误:入参在配置表sys_sequence_info不存在。[@syncCode = ' + dbo.TrimString(@syncCode) + ']';
return @state;
end;
while (@i<@maxcount)
begin;
EXEC @p_return_value = .
@syncCode = @syncCode,
@result = @strResult OUTPUT;
set @i = @i + 1; --自加一
if (@p_return_value = 0)
begin;
set @state = 0;
set @syncStr = @strResult; --对返回的字符串进行赋值
break;
end;
else if (@i + 1 = @maxcount and @p_return_value = 1)
begin;
set @state = 1;
set @syncStr = '错误:获取流水号超时。[共尝试了' + dbo.TrimString(str(@maxcount)) + '次]';
end;
end;
return @state;
end;
=========================================================
为了方便，进行并行执行，获取流水号的测试，可以创建如下的测试表，进行测试。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE .(
(500) COLLATE Chinese_PRC_CI_AS NULL,
(50) COLLATE Chinese_PRC_CI_AS NULL,
NULL
) ON
GO
测试脚本如下：
declare @result varchar(400);
declare @i int;
declare @return_value int;
set @i=0;
while @i<10000
begin
EXEC @return_value = .
@syncCode = N'test_1',
@syncStr = @result OUTPUT;
if (@return_value = 0)
begin;
insert into sync_test(id,user_name, write_date) values(@result, 'test_2', getdate());
set @i=@i+1;
end;
end;
测试结果，截图如下：
完整的脚本文件如下：
页:
[1]