在MYSQL中，当复合键上发生并发事务时，如何维护自动递增

Question

我最近在应用程序中遇到一个并发事务错误。以前，复合键的自动递增是使用PHP使用应用程序本身实现的。然而，正如我所提到的，id被复制了，发生了各种各样的问题，我后来煞费苦心地手动修复了这些问题。

现在我已经阅读了相关问题，并找到了使用触发器的建议。

所以我正计划实现一个类似这样的触发器。

DELIMITER $$
CREATE TRIGGER auto_increment_my_table
BEFORE INSERT ON my_table FOR EACH ROW
BEGIN
SET NEW.id = SELECT MAX(id) + 1 FROM my_table WHERE type = NEW.type;
END $$
DELIMITER ;

但我对并发性的怀疑仍然存在。比如，如果这个触发器是并发执行的，并且在查询时两者都得到了相同的MAX(id(，该怎么办？

这是处理我的问题的正确方法吗？还是有更好的方法？

Answer 1

一个例子-如何解决复合索引中的自动递增问题。

CREATE TABLE test ( id INT,
type VARCHAR(192),
value INT,
PRIMARY KEY (id, type) );

-- create additional service table which will help
CREATE TABLE test_sevice ( type VARCHAR(192),
id INT AUTO_INCREMENT,
PRIMARY KEY (type, id) ) ENGINE = MyISAM;

-- create trigger which wil generate id value for new row
CREATE TRIGGER tr_bi_test_autoincrement
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
INSERT INTO test_sevice (type) VALUES (NEW.type);
SET NEW.id = LAST_INSERT_ID();
END

db<gt；小提琴这里

---

创建一个服务表来自动增加一个值对我来说似乎不太理想。–Mohamed Mufeed

此表非常小-您可以随时删除所有记录，但此组中自动递增值最大的组除外。–Akina

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=61f0dc36db25dd5f0cf4647d8970cdee

您可以安排在服务事件过程中删除多余的行(例如，每天(。

Answer 2

我已经设法解决了这个问题。

答案在某种程度上是朝着阿金娜的答案的方向。但不完全是这样。

我解决问题的方式确实涉及到一张额外的桌子，但不像他建议的那样。

我创建了一个额外的表来存储有关事务的元数据。

例如：我有这样的表密钥

CREATE TABLE `journals` (
`id` bigint NOT NULL AUTO_INCREMENT,
`type` smallint NOT NULL DEFAULT '0',
`trans_no` bigint NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `transaction` (`type`,`trans_no`)
)

所以我创建了一个类似的meta_journals表

CREATE TABLE `meta_journals` (
`type` smallint NOT NULL,
`next_trans_no` bigint NOT NULL,
PRIMARY KEY (`type`),
)

并将其与所有不同类型的期刊和下一个序列号一起播种。

每当我向journals插入新事务时，我都确保在meta_transactions表中增加相应type的next_trans_no。此增量操作在同一数据库TRANSACTION内发出，即在BEGINANDCOMMIT内发出

这允许我在meta_journals表的行上使用UPDATE语句获取的独占锁。因此，当同时为日志发出两个insert语句时，其中一个必须等到COMMITing释放另一个事务获取的锁