InnoDB:使用插入选择的自定义自动增量.会出现重复键错误吗? [英] InnoDB: custom auto-increment using insert select. Can there be duplicate-key error?
问题描述
我有一个像这样的表: idx(PK)clmn_1
I have a table like: idx (PK) clmn_1
都是INT. idx 不是定义为自动增量，但我正在尝试模拟它.至插入到此表中，我正在使用:
Both are INTs. idx is not
defined as auto-increment, but I am trying to simulate it. To
insert into this table, I am using:
"INSERT INTO my_tbl (idx, clmn_1) \
SELECT IFNULL(MAX(idx), 0) + 1, %s \
FROM my_tbl", val_clmn_1
现在，这可行.我有关于原子性的查询.由于我读取然后插入到同一张表中，因此当同时发生多个插入时，可能会出现重复键错误?
Now, this works. The query that I have is about atomicity. Since I read and then insert to the same table, when multiple inserts happen simultaneous can there potentially be a duplicate-key error?
然后，我如何自己测试呢?
And, how can I test it myself?
我正在使用Percona XtraDB服务器5.5.
I am using Percona XtraDB server 5.5.
推荐答案
这不是一个好的解决方案，因为它在执行SELECT时会在my_tbl上创建一个共享锁.任意数量的线程可以同时拥有一个共享锁，但是它会阻塞并发写入锁.因此，这将导致插入序列化，等待SELECT完成.
This is not a good solution, because it creates a shared lock on my_tbl while it's doing the SELECT. Any number of threads can have a shared lock concurrently, but it blocks concurrent write locks. So this causes inserts to become serialized, waiting for the SELECT to finish.
您可以观察到此锁定.在一个会话中启动此查询:
You can observe this lock. Start this query in one session:
INSERT INTO my_tbl (idx, clmn_1)
SELECT IFNULL(MAX(idx), 0) + 1, 1234+SLEEP(60)
FROM my_tbl;
然后转到另一个会话并运行innotop并查看锁定屏幕(按"L"键).您将看到这样的输出:
Then go to another session and run innotop and view the locking screen (press key 'L'). You'll see output like this:
___________________________________ 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
这就是自动递增机制按其工作方式起作用的原因.无论事务是否隔离，插入线程都会简短地锁定表，只是增加自动递增编号.这是非常快的.然后释放该锁，允许其他线程立即进行.同时，第一个线程尝试完成其插入.
This is why the auto-increment mechanism works the way it does. Regardless of transaction isolation, the insert thread locks the table briefly only to increment the auto-inc number. This is extremely quick. Then the lock is released, allowing other threads to proceed immediately. Meanwhile, the first thread attempts to finish its insert.
请参见 http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html 了解有关自动增量锁定的更多详细信息.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html for more details about auto-increment locking.
我不确定为什么要模拟自动增量行为，而不是仅仅将列定义为自动增量列.您可以将现有表更改为自动递增.
I'm not sure why you want to simulate auto-increment behavior instead of just defining the column as an auto-increment column. You can change an existing table to be auto-incrementing.
发表您的评论
即使将PK声明为自动增量，您仍然可以指定一个值.仅当您不在INSERT中指定PK列，或者您将 NULL 或 DEFAULT 作为其值时，自动增量才会启动
Even if a PK is declared as auto-increment, you can still specify a value. The auto-incrementation only kicks in if you don't specify the PK column in the INSERT, or you specify NULL or DEFAULT as its value.
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
发表您的评论
Re your comment:
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锁.
This is actually worse than your first idea, because now the SELECT...FOR UPDATE creates an X lock instead of an S lock.
您真的不应该尝试重塑AUTO-INCREMENT的行为，因为任何SQL解决方案都受ACID属性的限制.自动递增功能必须在ACID之外起作用.
You should really not try to re-invent the behavior of AUTO-INCREMENT, because any SQL solution is limited by ACID properties. Auto-inc necessarily works outside of ACID.
如果您需要自动更正现有行，请使用替换或插入...在重复的密钥更新上.
If you need to correct existing rows atomically, use either REPLACE or INSERT...ON DUPLICATE KEY UPDATE.
这篇关于InnoDB:使用插入选择的自定义自动增量.会出现重复键错误吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
