浅谈 SQL 中的锁(七)如何生成自定义的自增 ID

在 SQL 表设计中,自增 ID 的使用很广泛。因为有些数据的属性并不具有唯一性,所以要给它加上一个生成的主键。生成主键最方便的方式,就是采用 SQL 产品提供的自增 ID 功能。可能自增 ID 的使用太过方便了,现在大有泛滥的趋势,甚至有资深的工程师说:所有的表都应该有一个自增的主键。

不过 SQL 产品的自增 ID 功能,一般都只使用简单的自增整型,就是第一行记录的 ID 是 1,第二行记录的 ID 是 2,如此类推。有时候我们会希望 ID 带有除序号之外额外的信息,比如希望用户的 ID 带有其部门简写:人事部的员工以 HR 做前缀;信息部的员工以 IT 做前缀:

create table app_user
(
	id char(5) primary key,
	dept char(2),
	number int
)

insert app_user values('HR001', 'HR', 1)
insert app_user values('IT001', 'IT', 1)

面对这样的需求,一般的处理逻辑是:先找出对应部门的最大 ID,把这个 ID 的序号部分加 1,作为新用户的 ID 添加到用户表中:

declare @id char(5)
declare @dept char(2)
declare @number int

set @dept = 'HR'

select @number = max(number) + 1 
from app_user where dept = @dept

set @id = @dept + right('00' + convert(varchar(3), @number), 3)

insert app_user values(@id, @dept, @number)

很明显,上面的代码在并发的时候会出现问题,因为计算新的 ID 和插入记录是两个独立的事务,在这两个事务之间,新的 ID 可能会被占用了。加入延时的代码,在两个连接中执行同样的语句:

declare @id char(5)
declare @dept char(2)
declare @number int

set @dept = 'HR'

select @number = max(number) + 1 
from app_user where dept = @dept

set @id = @dept + right('00' + convert(varchar(3), @number), 3)

--延长处理时间
waitfor delay '0:00:10'  

insert app_user values(@id, @dept, @number)

可以看到后执行的连接插入记录失败:

原因就是 ID 已经被先执行的连接占用了。

实际上,解决这个问题方法,和之前写的重复用户问题的解决方法是一样的:

浅谈 SQL 中的锁(三)重复用户问题
浅谈 SQL 中的锁(四)如何检查重复用户

关键就是在生成 ID 的时候加上范围锁:

declare @id char(5)
declare @dept char(2)
declare @number int

set @dept = 'HR'

--开始事务
begin transaction
  
--设置串行式事务
set transaction isolation level serializable

select @number = max(number) + 1 
from app_user(updlock) where dept = @dept

set @id = @dept + right('00' + convert(varchar(3), @number), 3)

--延长处理时间
waitfor delay '0:00:10'

insert app_user values(@id, @dept, @number)

--提交事务
commit transaction

上面的代码做了两处改动,第一是把计算新的 ID 和插入记录合并为一个事务,并使用 serializable 隔离级别,对事务中操作的数据使用范围锁;第二是在查询存在的最大 ID 时使用 updlock 更新锁,这样两个并发的操作才能互斥。

这样,就完成自定义自增 ID 的编写了。实际上,用 SQL 生成 ID并不是最适合的方法,因为 SQL 并不是一门标准的编程语言,而是一门查询数据的领域语言,使用 .NET 的 ODBC 或 Java 的 JDBC 编写生成自定义自增 ID 的逻辑会更简单,更好读。

知道了生成自定义自增 ID 的方法,可以再思考数据库自带的自增 ID 的功能。从逻辑上来说,生成自增 ID 就必须要使用范围锁,这会增加性能消耗。另外,自增 ID 一般会作为主键,其实很多数据本身的属性就有唯一性的,比如电话号码,电子邮箱等等,这样的数据还添加自增 ID 作为主键是多余的,增加了性能和存储空间的消耗,程序也会更啰嗦!所以,我是反对“所有的表都应该有一个自增的主键”这种迂腐的说法的。

广州必简信息科技有限公司 版权所有 粤ICP备15047625号-2