浅谈 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 作为主键是多余的，增加了性能和存储空间的消耗，程序也会更啰嗦！所以，我是反对“所有的表都应该有一个自增的主键”这种迂腐的说法的。