获取ID方法探讨

http://blog.csdn.net/cassaba/archive/2006/11/29/1419798.aspx

获取ID方法探讨

 

在数据库开发过程中,我们经常需要在表的某一列上产生唯一的ID,以作为表的标志列.那么如何产生这些ID号并且保证其唯一性呢? 根据以往开发经验,总结方法不外乎以下几种:

<!--[if !supportLists]-->1.     <!--[endif]-->采用某列最大值加1的方式, 作为下一个序列号,如:

SELECT @MAXID = MAX(COL) + 1 FROM CASSABA

INSERT INTO CASSABA VALUES (@MAXID,…)

<!--[if !supportLists]-->2.     <!--[endif]-->时间序列, 或者时间序列 + 随机数等变种,如

20040820153402033 + RAND () * 100

<!--[if !supportLists]-->3.     <!--[endif]-->使用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')

<!--[if !supportLists]-->4.     <!--[endif]-->以Name-Value Pair 的方式, 将各种ID的当前值保存在一张表中,需要时,则统一从该表取用, 每次使用后递增,如:

SELECT @MAXID = CURR_ID FROM CASSABA

UPDATE CASSABA SET CURR_ID = @MAXID

<!--[if !supportLists]-->5.     <!--[endif]-->使用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的两行代码，调整了四次之多。

尝试的四种代码写法如下：

<!--[if !supportLists]-->A.     <!--[endif]-->不加事务,先取用MAX_ID, 再将MAX_ID 更新为 MAX_ID + 1;

<!--[if !supportLists]-->B.     <!--[endif]-->不加事务,先将MAX_ID更新成MAX_ID + 1,再取出,减1后使用;

<!--[if !supportLists]-->C.     <!--[endif]-->加事务,先取用MAX_ID, 再将MAX_ID 更新为 MAX_ID + 1;

<!--[if !supportLists]-->D.     <!--[endif]-->加事务, ,先将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的方法。

 

<!--[if !supportLists]-->M1)    <!--[endif]-->不加事务(不区分SELECT/UPDATE先后顺序);主要代码如下:

SELECT @MaxID = MAX_ID FROM CASSABA WHERE CATEGORY = @CATEGORY

UPDATE CASSABA SET MAX_ID = @MaxID + 1 WHERE CATEGORY = @CATEGORY

 

<!--[if !supportLists]-->M2)    <!--[endif]-->加事务, 先取用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

 

<!--[if !supportLists]-->M3)    <!--[endif]-->加事务, 先将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

 

<!--[if !supportLists]-->M4)    <!--[endif]-->通过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

 

 

结论如下：

<!--[if !supportLists]-->1．    <!--[endif]-->M1,M2均会产生重复ID,而且重复程度相当，M3, M4则不会。

<!--[if !supportLists]-->2．    <!--[endif]-->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 支持所有这些隔离级别：

<!--[if !supportLists]-->l         <!--[endif]-->未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。

<!--[if !supportLists]-->l         <!--[endif]-->提交读（SQL Server 默认级别）。

<!--[if !supportLists]-->l         <!--[endif]-->可重复读。

<!--[if !supportLists]-->l         <!--[endif]-->可串行读（事务隔离的最高级别，事务之间完全隔离）。

 

不同的隔离级别的区别是, 较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。

默认情况下，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。更多的时候, 可能还是需要打组合拳, 多管齐下,才能达到目的。

总之, “兵无常势，水无常形”, 我们碰到的问题也会多种多样,但是只要从实际需求出发,仔细分析,小心求证,就不难解决问题. 如果大家还有其他更好的方法,或者有什么疑问,都欢迎联系我共同探讨。