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

Question

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

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

我在想三种可能的方法：

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

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

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

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

Answer 1

好吧，取决于您有多勇敢，您还有其他选项，比如创建单独的序列表，用于维护WID值：

CREATE TABLE wid_seq (wid INT PRIMARY KEY AUTO_INCREMENT);

在你的交易中：

START TRANSACTION;
INSERT INTO wid_seq VALUES (null);
INSERT yourtable (WID) values (LAST_INSERT_ID());
COMMIT;