很多MIS系统，都需要用到流水号；一般的简单的流水号，由标识+日期+自增序号来组成；但如果考虑通用的话，就稍微复杂点儿的，需要考虑自定义日期格式、自增序号归1、自增序号溢出处理、前缀/中缀/后缀、并发访问、批量获取等，本文抽象出一个通用的生成流水号的方案。
1. 查询原始数据表 vs. 键值表
2. 键值表、取流水号的T-SQL实现
3. 并发处理需要考虑的三个因素
4. C#封装取流水号操作
5. 不给代码怎马叫给力~
1. 查询原始数据表 vs. 键值表
流水号的变动部分可分为日期和自增序号两部分，日期就是取当前的日期(yyyy、yyMM、或yyyyMMdd等)，自增序号部分可以有如下两种获取方式：
1.1. 每次查询原始数据表：
缺点就是要手工处理并发，如果并发量大的话，性能堪忧；好处就是每次可以取得准确的下一个自增序号，如果最后没有保存或者保存失败，取得的序号可以被重复读得，不会被浪费。
下面是一个并发示例：譬如当前表中最大的序号(这里暂时只考虑自增序号部分，忽略日期)为003，这是A/B两个用户同时打开页面并取得流水号，此时数据都没有保存，因此他们会取得相同的流水号004，保存时就会出现重复键值(违反唯一性约束)了。保险一点的做法，就是在保存的时候，去校验一下流水号是否已经被用过了；但是校验的时候，也必须十分小心，下面是一个保存时的未考虑并发的无效校验：
针对这个问题，一种解决办法就是，将操作串行化，保存前先获得锁：
可以在应用程序中加锁(但如果有集群的话，还要考虑多个服务器的问题)，或者锁数据库表。虽然加锁能解决并发问题，但是却带来更严重的性能问题。每次获取流水号时都要去查询原始数据表(或索引，如果有索引的话)，且插入前要进行加锁，操作只能被串行化，并发量一大，性能是个大问题。
1.2. 使用键值表
另一种思路就是使用键值表。可以为每个需要使用流水号的表，在键值表中保存一条记录，该记录保存其对应表中当前的最大流水号值。这样的操作的好处是：每次取流水号的时候，只需要操作该表中对应的一条记录即可，而不用去查询原始表/索引；还可以用于批量操作，一次获取一批流水号(批量录入或导入的时候，经常会用到)。
键值表还需要处理的一个问题，何时更新键值表中的记录(当前最大值)？有两种处理思路：
(1). 采用写时更新：能避免每次读取时查询原始表的问题，但还是会遇到上面1.1节中的并发问题。
(2). 每次读取最大值的时候更新：先锁记录再读，最后更新为新的最大值。下一个人来读的时候，再取到下一个流水号，这样可以获得最大的并发性，但带来的问题是，如果上一个人取到的业务流水号最后没有保存，则这个流水号就废了(跳过去了)，导致最后的实际的业务流水号不连续。如果业务上允许序号被浪费，建议采用这种方式。
本文的解决方案，也主要是针对后一种(读取时更新)获取流水号的方式。
2. 键值表、取流水号的T-SQL实现
还虑通用型，可以对业务流水号进行抽象：流水号 = 前缀+日期+中缀+流水号+后缀。
其中：
前缀/中缀/后缀：可以包含0个或多个字符；
日期：可以包含yyMM、yyyy、yyMMdd、yyyyMMdd等多种格式；
流水号：从1开始累加，按日期归1，长度可扩展(考虑到溢出)；
这些信息都可以放在键值表中统一维护。
继续考虑通用性，可以封装下取流水号的操作，提供一个批量获取方式，一次取一批序号(Max + N)，避免批量操作时循环去取(Max + 1)；批量录入或导入的时候，经常会用到批量获取的方式。
2.1 键值表的设计
1: /*happyhippy.cnblogs.com*/ 2: IF(OBJECT_ID('SequenceNumber') IS NOT NULL)
3: DROP TABLE SequenceNumber;
4: 5: Create Table SequenceNumber
6: (7: ID int identity(1,1),
8: Code nvarchar(10) primary key, /*Key*/
9: Prefix nvarchar(5), /*前缀*/
10: DateType nvarchar(8), /*日期类型，可以为yyyy，yymm, yyyymm，yymmdd，yyyymmdd等等等等。*/ 11: Infix nvarchar(5), /*中缀*/12: IndexLength int, /*自增流水号长度*/
13: Suffix nvarchar(5), /*后缀*/ 14: MaxDate nvarchar(8), /*当前最大日期值*/15: MaxIndex int default(0),/*当前最大流水号值*/
16: CurrentMaxValue AS (Prefix + MaxDate + Infix + Replace(STR(MaxIndex, IndexLength), ' ' , '0') + Suffix)
17: ) 注意：
(1). 表的主键设置在Code字段上；
(2). MaxData、MaxIndex等记录当前最大值，用于直接运算。
2.2 T-SQL获取流水号
1: /*happyhippy.cnblogs.com*/2: go
3: IF(OBJECT_ID('GetSequenceNumber') IS NOT NULL)
4: DROP PROCEDURE GetSequenceNumber;
5: 6: go
7: CREATE PROCEDURE GetSequenceNumber
8: ( 9: @Code nvarchar(10),10: @Count int = 1
11: )12: AS
13: BEGIN
14: DECLARE @NewValue nvarchar(20), @CurrentDate nvarchar(8);
15: DECLARE @Prefix nvarchar(5), @DateType nvarchar(8), @Infix nvarchar(5), @Suffix nvarchar(5);
16: DECLARE @MaxIndex int, @IndexLength tinyint, @MaxDate nvarchar(8);
17: 18: BEGIN TRAN
19: --读取配置信息20: SELECT @Prefix = Prefix, @Infix = Infix, @Suffix = Suffix,
21: @DateType = DateType, @MaxDate=MaxDate, 22: @MaxIndex = MaxIndex, @IndexLength = IndexLength23: FROM SequenceNumber with(xlock) WHERE Code=@Code;
24: 25: --取得日期部分，如果需要其他格式，需要自己再扩展，增加CASE分支。26: SET @CurrentDate= SUBSTRING(Convert(nvarchar(8), GetDate(), 112),
27: CASE SubString(@DateType, 1, 4)
28: WHEN 'yyyy' THEN 1
29: WHEN 'yyy' THEN 2
30: ELSE 3
31: END, LEN(@DateType));
32: 33: IF(@CurrentDate = @MaxDate)
34: SET @MaxIndex = @MaxIndex + @Count; --累加
35: ELSE
36: SET @MaxIndex = @Count; --归1
37: 38: 39: --超过自增长度限制，自动扩展自增部分的长度40: IF(@MaxIndex >= POWER(10, @IndexLength))
41: SET @IndexLength = @IndexLength + 1;
42: 43: --可以取消下面一行的注释，来测试并发44: --Waitfor delay '00:00:10';
45: 46: Update SequenceNumber SET MaxDate = @CurrentDate, MaxIndex=@MaxIndex, IndexLength=@IndexLength WHERE Code=@Code;
47: COMMIT TRAN
48: 49: --取得获取到的最大值，取得@IndexLength和Len(@Suffix)用于解析得到批量获取的序列号50: SELECT (@Prefix + @CurrentDate + @Infix + Replace(STR(@MaxIndex, @IndexLength), ' ' , '0') + @Suffix), @IndexLength, Len(@Suffix);
51: END 注意：
(1). 整个读取、更新过程，封装在一次事务操作中；
(2) 参数@Count，可以传一个正整数，批量获取多个流水号；
(3). 第19~22行，读取的时候获取排它锁(xlock)，用于处理并发情况；
(4). 第39~41行，如果溢出，则自动扩展自增序号的宽度；
3. 并发需要考虑的几个因素
并发要考虑两种情况：
(1) 并发访问同一种类的序列号(键值表中的一个Key)时，必须串行访问，以防止取得相同的流水号；
(2) 并发访问不同种类的序列号(键值表中的不同Key)时，必须允许并发访问，互不干扰才能获得最大的并发度；
3.1 在应用程序中处理锁，还是在数据库中处理锁？
.Net中提供了现成lock、Monitor等，我们可以用来处理锁；譬如可以维护一个字典Dictionary<string, Object>，Key中保存键值表中对应的键值，Value保存同步对象，伪代码如下：
1: private static object dictionarySyncObj = new object();
2: private static Dictionary<string, object> syncDictionary = new Dictionary<string, object>();
3: public static string GetMaxSequenceNumber(string key)
4: {5: lock (dictionarySyncObj)
6: {7: if (!syncDictionary.ContainsKey(key))
8: {9: syncDictionary.Add(key, new object());
10: } 11: }12: Object keySyncObj = syncDictionary[key];//针对不同的Key，使用不同的同步对象
13: lock (keySyncObj)
14: {15: //从数据库读取最大流水号....
16: return ....
17: } 18: }程序中所有需要取流水号的地方，都调用该函数来获取，以保证对同一种类序列号的访问被串行化。如果系统只是部署在单台服务器上，这种方法没有问题；但是如果使用了服务器集群，系统在多个系统上部署了多份，则还是无法串行化对同一个Key的所有访问。
比较理想的做法，是在一个统一的地方处理并发，譬如在数据库中。上面第2节中，给出的键值表实现和获取流水号的存储过程，其实已经实现了并发处理，下面展开进行讨论。讨论之前，先执行下列代码来构造几个测试用例：
1: /*构造测试用例*/
2: INSERT INTO SequenceNumber(Code, Prefix, DateType, Infix, IndexLength, Suffix)3: VALUES('Test1', 'P', 'yyyy', '', 8, ''),
4: ('Test2', '', 'yymmdd', 'M', 6, ''),
5: ('Test3', 'P', 'yymmdd', 'M', 6, 'S');
6: 7: UPDATE SequenceNumber SET MaxDate= SUBSTRING(Convert(nvarchar(8), GetDate(), 112), 8: CASE SubString(DateType, 1, 4)9: WHEN 'yyyy' THEN 1
10: WHEN 'yyy' THEN 2
11: ELSE 3 12: END, LEN(DateType));3.2 串行化访问同一种类的序列号
默认情况下(Read Committed事务隔离级别)，读取操作会对对应的数据Key(或行)加S锁(不考虑锁升级的情况)，对该行所属的页和表加IS锁；读取完毕后，就释放这些IS锁和S锁。可以加表提示(with (holdlock))，来让会话强制持有锁，直至事务结束(提交或回滚)后才释放锁。但是，如果多个会话并发访问的时候，由于IS锁与IS锁之间是兼容的，在值被更新(持有更新锁ulock)之前，可以并发读得相同的数据，因此这里读取时，必须要用排它锁(xlock)来独占资源，当一个线程读的时候，不允许其他线程并发读。有关并发和锁兼容性的更多介绍，可以参考我之前的文章《SQL Server死锁总结》。
可以取消2.2节中存储过程GetSequenceNumber中的第44行(Waitfor delay '00:00:10';)的注释，让T-SQL执行时等待10秒钟，以比较测试结果。开两个窗口分别同时执行下列一段测试代码：
1: exec dbo.GetSequenceNumber 'Test2', 1;
第一个窗口的执行结果：
第二个窗口的执行结果(操作过程中存在延时，所以显示的只有18秒)：
虽然两个会话“同时”执行(第二个会话，我在操作时存在延时，所以显示的只有18秒)，但两个会话没有读得相同的序列号。执行时，第二个会话等待被阻塞等待了；只有等到第一个会话执行完毕后，第二个会话才获得锁资源，并继续执行；因此用了2倍的时间(20秒)。这就达到了多线程访问同一个Key时必须被串行化的效果。
3.3 并发访问不同种类的序列号
多线程并发访问不同种类的序列号(键值表中的不同Key)时，必须允许并发访问，互不干扰才能获得最大的并发度。在2.1节键值表的设计中，我将Code设为主键，这样做的一个好处，就是在读取一条Code记录并获取锁的时候，锁的粒度只会限制在Key锁，而不会升级为页锁或表锁。
现在开两个查询窗口，分别同时执行下列两段代码（注意：这次，两个窗口访问的是不同的Key）：
1: exec dbo.GetSequenceNumber 'Test2', 1;
1: exec dbo.GetSequenceNumber 'Test3', 1;
第一个窗口的执行结果：
第二个窗口的执行结果：
虽然两个会话同时执行，但是第二个会话，并没有被第一个会话阻塞，所以第二个会话也只用10秒就执行完毕了。两个会话可以并发执行，这就达到了多线程可以并发访问不同Key的效果。
如果执行上面的查询时，我们sp_lock来查看锁的情况，也可以看到：
参考我之前的文章《SQL Server死锁总结》，并结合上图，可以看到，两个X锁是应用在不同的Resource上，他们之间不会冲突；IX锁虽然应用在同一个Table上/Page(1:828)上，但IX锁与IX锁之间是兼容的，他们之间也不存在冲突；因此多个线程之间不会相互影响。回过头来考虑3.2节中的测试，两个会话尝试对同一个Key加X锁，但X锁与X锁之间是不兼容的，因此读取操作被串行化了。这里利用SQL Server的锁机制来实现并行化/串行化的目的。
抛一个问题，如果键值表的主键，不在Code字段上，还能并发访问不同种类的序列号吗？有兴趣的可以试试。
4. C#封装取流水号操作
1: public static ReadOnlyCollection<string> GetSequenceNumbers(SequenceType type, int count = 1)
2: {3: string maxSequenceNumber = string.Empty;
4: byte indexLength = 0;
5: byte suffixLength = 0;
6: //以上三个值，调用存储过程读取，省略。。。。
7: 8: if (count == 1)
9: {10: return (new List<string>() { maxSequenceNumber }).AsReadOnly();
11: }12: else
13: {14: string prefix = maxSequenceNumber.Substring(0, maxSequenceNumber.Length - indexLength - suffixLength);
15: int index = Convert.ToInt32(maxSequenceNumber.Substring(prefix.Length, indexLength));
16: string suffix = maxSequenceNumber.Substring(maxSequenceNumber.Length - suffixLength);
17: 18: string format = "0000000000".Substring(0, indexLength);
19: return Enumerable.Range(index - count + 1, count)
20: .Select(i => prefix + i.ToString(format) + suffix) 21: .ToList() 22: .AsReadOnly(); 23: } 24: }使用方式：
1: foreach (string item in SequenceNumber.GetSequenceNumbers(SequenceType.Test3, 3))
2: {3: Response.Write(item + "<br/>");
4: }
5. 不给代码怎马叫给力~
happyhippy.cnblogs.com.SequenceNumber.rar
参考文献：
《企业应用架构模式》
