InnoDB:使用insert select自定义自增.是否存在重复密钥错误?

Question

我有一个表，如:idx (PK) clmn_1

都是int。idx不是定义为自动增量，但我试图模拟它。来在这个表中，我使用:

"INSERT INTO  my_tbl (idx, clmn_1)   
 SELECT IFNULL(MAX(idx), 0) + 1, %s  
 FROM my_tbl", val_clmn_1

现在，这是可行的。我的问题是关于原子性的。由于我读取并插入到同一个表，当多个插入同时发生时，可能会有一个潜在的重复键错误呢?

我自己怎么测试呢?

我使用的是Percona XtraDB server 5.5.

Answer 1

这不是一个好的解决方案，因为它在执行SELECT时在my_tbl上创建了一个共享锁。任何数量的线程都可以并发地拥有一个共享锁，但它会阻塞并发写锁。因此，这会导致insert被序列化，等待SELECT完成。

你可以观察到这个锁。在一个会话中启动这个查询:

INSERT INTO  my_tbl (idx, clmn_1) 
 SELECT IFNULL(MAX(idx), 0) + 1, 1234+SLEEP(60) 
 FROM my_tbl;

然后进入另一个会话并运行innotop并查看锁定屏幕(按'L'键)。您将看到如下输出:

___________________________________ InnoDB Locks ___________________________________
ID  Type    Waiting  Wait   Active  Mode  DB    Table   Index    Ins Intent  Special
61  TABLE         0  00:00   00:00  IS    test  my_tbl                    0         
61  RECORD        0  00:00   00:00  S     test  my_tbl  PRIMARY           0         

这就是为什么自动递增机制是这样工作的。无论是否存在事务隔离，插入线程都会短暂地锁定表，只是为了增加自动加入数。这是非常快的。然后锁被释放，允许其他线程立即继续。同时，第一个线程试图完成它的插入。

有关自动自增锁的详细信息，请参阅http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html。

我不知道为什么要模拟自动递增行为，而不是仅仅将列定义为自动递增列。您可以将现有表更改为自动递增。

---

你的评论:

即使将PK声明为自动递增，您仍然可以指定一个值。只有当没有指定INSERT中的PK列，或者指定NULL或DEFAULT作为其值时，自动递增才会生效。

CREATE TABLE foo (id INT AUTO_INCREMENT PRIMARY KEY, c CHAR(1));
INSERT INTO foo (id, c) VALUES (123, 'x'); -- inserts value 123
INSERT INTO foo (id, c) VALUES (DEFAULT, 'y'); -- inserts value 124
INSERT INTO foo (id, c) VALUES (42, 'n'); -- inserts specified value 42
INSERT INTO foo (c) VALUES ('Z'); -- inserts value 125
REPLACE INTO foo (id, c) VALUES (125, 'z'); -- changes existing row with id=125

---

你的评论:

START TRANSACTION; 
SELECT IFNULL(MAX(idx), 0)+1 FROM my_tbl FOR UPDATE; 
INSERT INTO my_tbl (idx, clmn_1) VALUES (new_idx_val, some_val); 
COMMIT; 

这实际上比你的第一个想法更糟糕，因为现在SELECT...FOR UPDATE创建了一个X锁而不是S锁。

您真的不应该尝试重新发明AUTO-INCREMENT的行为，因为任何SQL解决方案都受到ACID属性的限制。Auto-inc必须在ACID之外工作。

如果您需要自动更正现有行，请使用REPLACE或INSERT…ON DUPLICATE KEY UPDATE.