获取ID方法探讨
在数据库开发过程中,我们经常需要在表的某一列上产生唯一的ID,以作为表的标志列.那么如何产生这些ID号并且保证其唯一性呢? 根据以往开发经验,总结方法不外乎以下几种:
1. 采用某列最大值加1的方式, 作为下一个序列号,如:
SELECT @MAXID = MAX(COL) + 1 FROM CASSABA
INSERT INTO CASSABA VALUES (@MAXID,…)
2. 时间序列, 或者时间序列 + 随机数等变种,如
20040820153402033 + RAND () * 100
3. 使用uniqueidentifier 列及NEWID()函数，常见于数据库复制与发布等分布式应用,如:
-- Creating a table using NEWID for uniqueidentifier data type.
CREATE TABLE CASSABA
(
cust_id uniqueidentifier NOT NULL DEFAULT newid(),
company varchar(30) NOT NULL
)
GO
-- Inserting data into Test table.
INSERT CASSABA (cust_id, company) VALUES (newid(), 'Wartian Herkku')
4. 以Name-Value Pair 的方式, 将各种ID的当前值保存在一张表中,需要时,则统一从该表取用, 每次使用后递增,如:
SELECT @MAXID = CURR_ID FROM CASSABA
UPDATE CASSABA SET CURR_ID = @MAXID
5. 使用SQLServer提供的自增类型以及相关函数, 如:
CREATE TABLE CASSABA
(
CURR_ID IDENTITY (1, 1) NOT NULL ,
CURR_TIME DATETIME NOT NULL
)
GO
INSERT INTO CASSABA (CURR_TIME) VALUES (GETDATE())
Set @NewID = CONVERT(NVARCHAR, SCOPE_IDENTITY())
下面我着重对第四种方法进行分析讨论。
这种方法的特点是，系统使用一个表来保存各种ID的当前最大值(INT型)， 取用的时候， 调用统一的存储过程每次加1， 然后再处理成实际需要的ID格式。这样做的好处很明显，首先ID是可读的，二是通过查询该表，就可以知道各种ID使用的状况。
具体代码如下：
表定义：
CREATE TABLE CASSABA
(
CATEGORY NVARCHAR (30) NOT NULL,
MAX_ID INT NOT NULL
)
GO
存储过程定义：
CREATE PROCEDURE dbo.usp_GetMaxID
(
@Category NVARCHAR (30),
@MaxID INT OUTPUT
)
AS
SET NO COUNT ON
SELECT @MaxID = MAX_ID FROM CASSABA WHERE CATEGORY = @Category
UPDATE CASSABA SET MAX_ID = @MaxID + 1 WHERE CATEGORY = @Category
这样看来，获取ID的完整方案完成了，代码清楚，控制简单，一切似乎都很美好。可是平静的河面通常有不平坦的河床，这次也不例外。问题出在这个存储过程上,由于我开始没有考虑到并发的状况，导致产生了重复ID，为了解决这个问题，我前前后后对取用ID的两行代码，调整了四次之多。
尝试的四种代码写法如下：
A. 不加事务,先取用MAX_ID, 再将MAX_ID 更新为 MAX_ID + 1;
B. 不加事务,先将MAX_ID更新成MAX_ID + 1,再取出,减1后使用;
C. 加事务,先取用MAX_ID, 再将MAX_ID 更新为 MAX_ID + 1;
D. 加事务, ,先将MAX_ID更新成MAX_ID + 1,再取出,减1后使用;
表面上看, 这四种实现代码均是大同小异,不同点只是在于是否添加事务,以及select/update的前后顺序等。 事实上,在系统用户量不大,并发度低的时候,这四种实现都没有什么问题,但是一旦系统的用户量上来,并发度增高的时候, A,B都会产生一个非常严重的问题， 即生成重复的ID。这显然不符合我们对ID列的要求。问题出现在哪里呢?
赶紧学习一阵子onlinebook，总算从根本上了解了问题的原因。出于对读者的爱护，这里先提醒一下，如果你对数据库并发比较了解得话，这篇文章到此结束，你大可以去干些别的东西去了。当然，如果你是和我一样，对这方面比较模糊，则不妨耐心看下去。
场景：
P1,P2两个线程同时调用dbo.usp_GetMaxID获取某个类型的ID。
分析:
代码A，先取用再更新
基于SQLServer的锁机制，SELECT的时候,SQL Server一般加的是共享锁(S), P1线程取值的时候,P2线程同样也可以获得共享锁取值。这样就导致P1,P2就会读取到同一个值，从而产生重复ID。
随着并发的状况增多多,获取重复ID的机会也会相应增多, 因为该行已被其他线程添加共享锁, 已经取用ID而要对值进行加1更新的线程,可能就无法立即获得排他锁(X)从而使ID递增上去。
代码B，先更新再取用
同样基于SQLServer 的锁机制，UPDATE的时候,P1首先获得排他锁(X),此时P2处于阻塞当中。P1对MAX_ID更新后，并非如我们所相象的一样,在执行第二句 SQL(SELECT)的时候，会优先获得S锁。可能出现的情况是,半路里杀出一个程咬金,P2首先获取了X锁,它再次将值MAX_ID向上加1。X 锁释放后, P1,P2同时或者先后获得共享锁, 读到了一个+2的值, 因此它们也获得了相同的ID。
小结：
看来问题关键在于在读取,更新的过程中,没有采用合适的锁机制保证select/update动作的串行化。如果我们加上事务后,能不能保证这一点呢? 事实上C,D的尝试, 正是朝着这个思路发展。那么C,D是否能够切实的解决问题呢?
这里，建议读者暂时不看下文,思考一下,结果会是怎样的呢？
我开始以为,添加事务以后,觉得C，D两个实现应该都可以解决问题。实践出真知，为验证判断是否正确，我开发一个小的测试程序,分别对这些方法进行测试。为了对比效率，还特别增加了使用IDENTITY产生ID的方法。
M1) 不加事务(不区分SELECT/UPDATE先后顺序);主要代码如下:
SELECT @MaxID = MAX_ID FROM CASSABA WHERE CATEGORY = @CATEGORY
UPDATE CASSABA SET MAX_ID = @MaxID + 1 WHERE CATEGORY = @CATEGORY
M2) 加事务, 先取用MAX_ID, 再将MAX_ID 更新为 MAX_ID + 1;主要代码如下:
BEGIN TRANSACTION
SELECT @MaxID = MAX_ID FROM CASSABA WHERE CATEGORY = @CATEGORY
UPDATE CASSABA SET MAX_ID = @MaxID + 1 WHERE CATEGORY = @CATEGORY
COMMIT TRANSACTION
M3) 加事务, 先将MAX_ID更新成MAX_ID + 1,再取出,减1后使用; 主要代码如下:
BEGIN TRANSACTION
UPDATE CASSABA SET MAX_ID = MAX_ID + 1 WHERE CATEGORY = @CATEGORY
SELECT @MaxID = MAX_ID FROM CASSABA WHERE CATEGORY = @CATEGORY
COMMIT TRANSACTION
SELECT @MaxID = @MaxID - 1
M4) 通过Identity 方式生成ID;主要代码如下
CREATE TABLE CASSABA
(
CURR_ID IDENTITY (1, 1) NOT NULL ,
CURR_TIME DATETIME NOT NULL
)
GO
INSERT INTO CASSABA (CURR_TIME) VALUES (GETDATE())
SELECT @MaxID = CONVERT(NVARCHAR,SCOPE_IDENTITY())
同时从4个客户端C1,C2,C3,C4连续请求大批ID来模拟并发情况,测试结果如下:
|
method
|
client num
|
max duplicate times
|
unique/Total
|
time(ms)/total per client
|
average
(ms/per client)
|
|
M1
|
4
|
4
|
754/39947
|
C1: 565153/10,000
|
54.29
|
|
C2: 563086/9,947
|
|
C3: 564213/10,000
|
|
C4: 564430/10,000
|
|
M2
|
4
|
4
|
731/40,000
|
C1: 533840/10,000
|
53.48
|
|
C2: 532840/10,000
|
|
C3: 537793/10,000
|
|
C4: 532650/10,000
|
|
M3
|
4
|
--
|
40000/40000
|
C1: 573123/10,000
|
57.18
|
|
C2: 572826/10,000
|
|
C3: 570293/10,000
|
|
C4: 570873/10,000
|
|
M4
|
4
|
--
|
40000/40000
|
C1: 265100/10,000
|
26.77
|
|
C2: 261773/10,000
|
|
C3: 266176/10,000
|
|
C4: 277866/10,000
|
结论如下：
1． M1,M2均会产生重复ID,而且重复程度相当，M3, M4则不会。
2． M3平均每14ms产生一个ID,M4平均每7ms产生一个ID，M4的效率比M3几乎高出一倍。
下面我们着重分析(不)产生重复ID的原因：
M1很显然会产生重复ID, 前面已经分析过相关的原因。对于SQL Server 来说,它的默认模式是每个单独的 Transact-SQL 语句都在其完成后提交。所以简单调整SELECT/ UPDATE语句的前后,都无法保证相同的ID值不被其他的线程读取,产生重复ID几乎是必然的。
那么为什么M2添加了事务，还会产生重复的ID呢？弄清楚这个问题，我们首先需要了解一下有关隔离级别的知识。
SQL-92 定义了以下四种隔离级别，SQL Server 支持所有这些隔离级别：
l 未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。
l 提交读（SQL Server 默认级别）。
l 可重复读。
l 可串行读（事务隔离的最高级别，事务之间完全隔离）。
不同的隔离级别的区别是, 较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。
默认情况下，Microsoft® SQL Server™ 2000 在提交读(READ COMMITTED) 的一个隔离级别上操作. 执行提交读允许P2事务读取P1事务已读取（未修改）的数据，而不必等待P1事务完成,也就是说P1,P2两个事务可以同时检索相同的行。这就是所谓的脏读。这样P1,P2事务就读取到了同样一个ID值,并且先后完成基于该值的+1动作。这样M2产生重复ID的原因就清楚了。
那么如何使M2也能获得唯一ID呢？通过观察事务过程中锁的变化发现,Select行执行后，当前事务对选择的行便不再拥有任何锁。也就是说，事务首先获得了S锁，用来取出MAX_ID, 其后便被立即释放了,并没有被保持,或者为进行下一条Update语句而立即升级为X锁。如何避免这种情况呢? 通过修改select语句,添加锁定提示即可达到目的:
SELECT @MaxID = MAX_ID FROM CASSABA WITH(UPDLOCK) WHERE CATEGORY = @CATEGORY
添加WITH(UPDLOCK) 子句后,当前事务首先获得的是类型为RID的U锁,并且在TAB上放置IX锁,在页上放置IU锁,由于U锁与U锁不兼容,所以其他的获取ID事务被阻塞,直到当前事务完成update动作结束,其他的事务才能获得U锁。
这样M2也能获得唯一ID了。
既然添加锁定提示子句可以达到目标,那么通过提高事务的隔离级别（例如可串行读）的方法可不可以呢? 据SQL Server文档介绍, 可串行读能够避免脏读,保证任何并发重叠事务均是串行的. 如下例:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT @MaxID = MAX_ID FROM CASSABA WHERE CATEGORY = @CATEGORY
UPDATE CASSABA SET MAX_ID = @MaxID + 1 WHERE CATEGORY = @CATEGORY
COMMIT TRANSACTION
初步测试发现，结果比较出人意料。提高隔离级别也许并不能达到目标！因为我发现select的时候,只是获得了TAB类型的S锁和大量的RangeS_S组合锁,那么其他的线程应该可以读到相同的值。另外此方法非常容易产生死锁。
至于M3, 测试结果表明,是不会产生重复ID的.。具体的原因通过观察该事务获取与释放锁的过程可以知道,该事务过程中,持有的一直是X锁, update语句执行后,该锁并没有被立即释放,也没有降级为S锁,而是保持至事务的结束, 与此同时,其他获取ID的线程被阻塞, 在当前事务完成之前,无法获取X锁.可见,M3很好的保持了事务的串行性。
是否M3就完美无缺了呢? 同M4相比，显然存在效率上的劣势。通过实际运行反馈，当并发性比较高的时候,某些线程在请求锁的过程中,由于长时间被阻塞,而导致连接超时,从而无法获取ID值。这个问题,后来通过增大连接对象的超时属性,得到一定程度的解决。
那么M4(也就是本文开篇提到的第5种方法)如何呢？这种方法通过创建一个包含自增类型的表(我称之为种子表),每次需要ID的时候,就向里面插入一条数据,然后用SCOPE_IDENTITY() 函数取出当前生成的ID。这种方法不需要添加事务, 简单高效, 由SQL Server本身保证了唯一性, 而且可以扩展该表,添加一些监控栏位,如产生ID的时间,请求的客户端等。唯一美中不足的是, 每一种ID都需要与之对应的Seed表,维护起来稍显不便。
总的说来, 每一种生成ID的方法,都有自己的使用的范围,并无孰优孰劣之分。比如一些数据维护表,平时也许就是管理员访问一下,如果需要生成ID,通过开篇提到的方 法1就可以满足要求了; 对于高并发性,大批量的ID生成方式,可能需要采用方法4或者5。更多的时候, 可能还是需要打组合拳, 多管齐下,才能达到目的。
总之
, “兵无常势，水无常形”, 我们碰到的问题也会多种多样,但是只要从实际需求出发,仔细分析,小心求证,就不难解决问题. 如果大家还有其他更好的方法,或者有什么疑问,都欢迎联系我共同探讨。