当值是UNIQUE时,是否需要对INSERT INTO…SELECT值+ 1进行锁定?

说我有这张桌子:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果我像这样插入数据,是否需要某种锁:

INSERT INTO test(number)
SELECT COALESCE(MAX(number), 0) + 1 FROM test;

In other words, if I have this statement executed in parallel multiple times, should I be worried that the same number could be inserted twice? I obviously will create a UNIQUE key (which will in fact be a composite key, that's why the classic AUTO INCREMENT feature does not fit my needs), but in that case should I be worry that a UNIQUE CONSTRAINT error might be thrown?

评论
  • mut
    mut 回复

    默认情况下,InnoDB使用自动提交模式,因此每个查询都是单个事务。因此它将自动执行必要的锁定以防止重复。