我的MariaDB数据库中有一个InnoDB表，它可以进行大量写入和读取。这是一种"；仅追加"；这样的表格：
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| data_id | int(11) | NO | | NULL | |
| data | varchar(225) | YES | | NULL | |
| creation_date | datetime | NO | | NULL | |
| deactivation_date | datetime | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
当添加数据时，它当然会被插入。当数据"；"改变"；，我把旧唱片标记为"；去激活"；(通过设置字段值(并插入新数据。
我的代码看起来像这样：
SELECT ... WHERE id IN (...) FOR UPDATE
foreach row {
// Do we have a value for this data_id
if(saves.contains(row.data_id)) {
// Is it actually NEW?
if(row.data != saves[row.data_id]) {
// Disable the record (later)
disables.add(row.id)
} else {
// Preserve the data in the db
saves.remove(row.data_id);
}
}
foreach save {
INSERT...
}
foreach disables {
UPDATE ... SET deactivation_date=NOW() WHERE id=?
}
那个伪代码很糟糕，但我想你明白了。
在过去，我首先对所有东西进行UPDATE，然后对剩下的东西进行INSERT。这让我陷入了死锁，因为(我认为(UPDATE正在获取表索引上的锁，然后获取INSERT的表级锁。这允许两个独立的事务相互持有锁，其中一个事务将被回滚以让另一个事务继续。所以我添加了一个简单的"；尝试3次"；逻辑到我的代码。：(
我还重新编写了代码来决定要做什么，然后采取行动，所以我总是先执行INSERTs，然后执行UPDATEs。但有时我仍然会遇到死锁。两个同时发生的事务在它们正在修改的记录中重叠的情况非常罕见，因此SELECT ... FOR UPDATE通常应该锁定当前未锁定的行。
执行UPDATE+INSERT(或INSERT+UPDATE(的最佳方式是什么，以最大限度地减少必须在我自己的代码中解决(重试(的死锁？有没有更好的技术，或者我应该继续检测死锁并简单地重试？
我计划很快转移到Galera集群，那里的情况会变得更加复杂，我可能不得不检测提交失败的情况，因为集群中的另一个节点抱怨提交顺序等。无论如何，我可能必须实现事务重试。搬到加莱拉会改变以上问题的答案吗？
更新
我应该提到，当然，这一切都发生在具有适当回滚等的事务中。
此外，这个示例表并没有完全捕捉到我的用例的复杂性；data_id"；实际上。。。很多事情。不仅如此，data_id并不是可以完全匹配和替换的东西，所以INSERT ... ON DUPLICATE KEY UPDATE有两个原因：
- PKs从不重复
- data_ id对于"；活动的"；记录，因此可以有多个具有相同data_id的记录；只有当来自用户的数据与数据库中的数据不完全匹配时，才需要禁用该记录(即，我正在进行多对多差异以避免多余的写入(
除了Dave在评论中所说的：
- 将事务隔离级别设置为READ-COMMITTED。这将防止相邻的行被锁定
- 将INSERT合并为一个多行INSERT。请注意，整个语句必须适合您的
max_allowed_packet大小，因此您可能需要将其分解为几个INSERT语句，以获得较大的行数或较大的有效负载 - 如果您对auto_increment值中的间隙不感兴趣，请将innodb_autoinc_lock_mode设置为2