InnoDB锁定，用于INSERT/UPDATE并发事务

Question

我希望在多个事务可能执行数据库插入或更新时确保隔离，其中该过程需要旧值。

下面是类似 python 伪代码中的 MVP，假设默认隔离级别：

sql('BEGIN')
rows = sql('SELECT `value` FROM table WHERE `id`=<id> FOR UPDATE')
if rows:
    old_value, = rows[0]
    process(old_value, new_value)
    sql('UPDATE table SET `value`=<new_value> WHERE `id`=<id>')
else:
    sql('INSERT INTO table (`id`, `value`) VALUES (<id>, <new_value>)')
sql('COMMIT')

这样做的问题是FOR UPDATE会导致IS锁，这不会阻止两个事务继续进行。当两个事务都尝试UPDATE或INSERT时，这会导致死锁。

另一种方法是首先尝试插入，如果有重复的键，请更新：

sql('BEGIN')
rows_changed = sql('INSERT IGNORE INTO table (`id`, `value`) VALUES (<id>, <new_value>)')
if rows_changed == 0:
    rows = sql('SELECT `value` FROM table WHERE `id`=<id> FOR UPDATE')
    old_value, = rows[0]
    process(old_value, new_value)
    sql('UPDATE table SET `value`=<new_value> WHERE `id`=<id>')
sql('COMMIT')

此解决方案中的问题是失败的INSERT会导致S锁，这不会阻止两个事务继续进行，如下所述：https://stackoverflow.com/a/31184293/710358。

当然，任何需要硬编码等待或锁定整个表的解决方案对于生产环境来说都不令人满意。

Answer 1

解决此问题

的技巧是使用始终发出X锁的INSERT ... ON DUPLICATE KEY UPDATE ...。由于您需要旧值，因此可以执行空白更新并继续执行第二个解决方案：

sql('BEGIN')
rows_changed = sql('INSERT INTO table (`id`, `value`) VALUES (<id>, <new_value>) ON DUPLICATE KEY UPDATE `value`=`value`')
if rows_changed == 0:
    rows = sql('SELECT `value` FROM table WHERE `id`=<id> FOR UPDATE')
    old_value, = rows[0]
    process(old_value, new_value)
    sql('UPDATE table SET `value`=<new_value> WHERE `id`=<id>')
sql('COMMIT')