什么是正确的实现处理并发SQL插入，同时确保非主键列(即wid)唯一和自动增量？

Question

什么是正确的实现处理并发SQL插入，同时确保非主键列(即wid)唯一和自动增量？

注意:有一个设计限制导致这个字段(即wid)没有成为主键。一个单独的自动增量PK包含在名为id的列中。将wid更改为PK是不可行的，因为数据库设计现在严重耦合到多个软件项目。所以我得想个解决办法。

更多信息: wid列遵循分支id和序列号连接的预定义格式。

Sample rows:
ID - wid
1 - 11000001
2 - 11000002
3 - 10000001
4 - 11000003
5 - 10000002

我在想三种可能的方法：

1.)通过执行事务并继续处理本应包含在同一事务中的其他SQL语句来保留wid。保留在一个单独的事务中执行，以使锁定尽可能短。但是，如果后续事务失败(例如，由于数据库连接问题)，则包含保留的wid的无效行将保留在表中。(注:在wid中使用的最小和最大阈值是分支id和序列号的连接)

START TRANSACTION;
SET @wid_d = 0;
SELECT COALESCE(MAX(`wid`), 0) INTO @wid_d FROM table
WHERE `wid >= 10000000 AND `wid <= 10999999 FOR UPDATE;
INSERT INTO table (`wid`) VALUES (IF (@wid_d = 0, 10000001, @wid_d+1) )
COMMIT;

2.)包括在整个事务中保留wid，以消除插入无效行的问题，如果事务失败，但这种方法会延长表的锁定。

3.)依赖事务调用的失败来重新执行事务，直到事务成功执行；如果事务由于插入相同的MAX(wid) + 1的竞争条件而失败。这种方法将防止插入无效的行，但问题是事务应该重新运行的次数。也没有区别，知道问题是由冲突的wid或其他问题，因此，这是一个有问题的方法。

Answer 1

您正在查看的所有三个选项在并发环境中的性能都肯定会很差。在事务中包装SELECT (MAX(wid)) ...不会阻止竞争条件；为此您需要写锁整个表。

如果不需要序号从每个分支的1开始，只要这些值是递增的和唯一的，您可以考虑创建一个单独的表，其中包含一个自动合并列来生成wid值，如下所示：

create table gen (
  branch_id int not null,
  seq int not null auto_increment,
    index (seq),
    unique (branch_id, seq)
);

然后，您可以简单地将基本branch_id插入到该表中，并使用last_insert_id()检索生成的序列值。

Answer 2

我认为找到一个最大数字的过程是一个巨大的过程，当您的表有数百万条记录时，您不需要这样做--当您拥有表锁时，只需得到最后一条记录。虽然当有数百个并发访问时，这不是一个合适的方法，但是延迟变得非常糟糕。

对此，我认为创建新表是合理的，并使用自动增量主键作为顺序字段。但是，如果删除一条记录，则序列之间可能会有差距。