mysql 设置主键 sqlserver_sqlserver数据库主键的生成方式小结(sqlserver,mysql)(2)

--创建键值表

CREATE TABLE KeyTable(

ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

TCode VARCHAR(20) UNIQUE NOT NULL,

TName VARCHAR(50) NOT NULL,

TKey INT NOT NULL,

)

GO

--插入测试记录

INSERT INTO KeyTable(TCode,TName,TKey)

VALUES('T001','Test',0)

GO

--创建获取指定表ID的存储过程，也可以修改成函数

CREATE PROCEDURE UP_NewTableID

@TCode VARCHAR(20),@NextID INT OUTPUT

AS

DECLARE @CurTKey INT,@NextTKey INT

BEGIN TRAN TransID

SELECT @CurTKey=TKey

FROM KeyTable

WHERE TCode = @TCode

IF @@ROWCOUNT = 0

BEGIN

ROLLBACK TRAN TransID

RAISERROR('Warning: No such row is exists',16,1)

RETURN

END

SET @NextTKey = @CurTKey + 1

--WAITFOR DELAY '00:00:05'

UPDATE KeyTable

SET TKey = @NextTKey

WHERE TCode = @TCode

IF @@ROWCOUNT = 0

BEGIN

ROLLBACK TRAN TransID

RAISERROR('Warning: No such row is updated',16,1)

RETURN

END

COMMIT TRAN TransID

SET @NextID = @NextTKey

GO

执行存储过程UP_NewTableID:

复制代码 代码如下:

DECLARE @NextID INT

EXEC UP_NewTableID 'T001',@NextID OUTPUT

PRINT @NextID

运行的时会发现很正常，获取的结果也很正确。但是如果在高并发的情况，多个用户可能就会获取相同的ID，如果获取的ID后是用于保存对应表中的记录，那么最多只有一个用户能保存成功。

下面模拟一下并发情形，将上面的存储过程UP_NewTableID中语句WAITFOR DELAY '00:00:05'的注释去掉，打开3个查询分析器的窗体，依次执行上面语句。

预期是想分别获得1,2,3，但是也许会发现多个窗体的运行结果都是：1。这就是说在更新语句执行之前，大家都获取的ID是0，所以下一个数值都是为1。(实际的数值，根据DELAY的参数大小及运行时间按间隔有关)

从这方面来分析的话有的朋友可能就会想到，是否可以在更新语句执行时判断ID是不是原始ID了？修改过程：

复制代码 代码如下:

ALTER PROCEDURE UP_NewTableID

@TCode VARCHAR(20),@NextID INT OUTPUT

AS

DECLARE @CurTKey INT,@NextTKey INT

BEGIN TRAN TransID

SELECT @CurTKey=TKey

FROM KeyTable

WHERE TCode=@TCode

IF @@ROWCOUNT=0BEGIN

ROLLBACK TRAN TransID

RAISERROR('Warning: No such row is exists',16,1)

RETURN

END

SET @NextTKey=@CurTKey+1

WAITFOR DELAY '00:00:05'

UPDATE KeyTable

SET TKey=@NextTKey

WHERE TCode=@TCode AND TKey=@CurTKey--此处加上TKey的校验

IF @@ROWCOUNT=0BEGIN

ROLLBACK TRAN TransID

RAISERROR('Warning: No such row is updated',16,1)

RETURN

END

COMMIT TRAN TransID

SET @NextID=@NextTKey

GO

如果打开个3个执行过程来模拟并发，那么会有2个窗体出现：

消息 50000，级别 16，状态 1，过程 UP_NewTableID，第 28 行

Warning: No such row is updated

由此会看到还是会由于并发导致有用户操作失败，但是较上一个至少将错误出现的时间点提前了。

那么有没有更好的方法，从查询到更新结束整个事务过程中，不会有任何其他事务插入其中来搅局的办法呢，答案很明确，有，使用锁！需要选择适当的锁，否则效果将和上面的一样。

复制代码 代码如下:

ALTER PROCEDURE UP_NewTableID

@TCode VARCHAR(20),@NextID INT OUTPUT

AS

DECLARE @CurTKey INT,@NextTKey INT

BEGIN TRAN TransID

SELECT @CurTKey=TKey

FROM KeyTable WITH (UPDLOCK)--采用更新锁，并保持到事务完成

WHERE TCode=@TCode

IF @@ROWCOUNT=0BEGIN

ROLLBACK TRAN TransID

RAISERROR('Warning: No such row is exists',16,1)

RETURN

END

SET @NextTKey=@CurTKey+1

WAITFOR DELAY '00:00:05'

UPDATE KeyTable

SET TKey=@NextTKey

WHERE TCode=@TCode--此处无需验证TKey是否与SELECT的相同

COMMIT TRAN TransID

SET @NextID=@NextTKey

GO

可以打开N(N>=2)个窗体来进行测试，将会看到所有操作都被串行化，结果就是我们想要的那样。如此注释或者去掉模仿并发的语句WAITFOR DELAY '00:00:05'即可。

如前面所说，这同样适应于单据编号类似编码的产生形式，只要对前面的代码及键值表稍作修改即可，有兴趣的朋友可以一试。如果是从前端取得这个编号，并应用于各个记录，那么可能存在跳号的可能。如果为了保证不存在跳号，一种解决方案就是使用跳号表，将跳号记录定期扫描并应用于其他记录。另一种解决方案是将记录的保存操作放置到编号产生的过程中，形成一个串行化的事务。

俗话说萝卜白菜各有所爱，您用哪一种自有你的道理。