InnoDB locking for INSERT/UPDATE concurrent transa

Question

I'm looking to ensure isolation when multiple transactions may execute a database insert or update, where the old value is required for the process.

Here is a MVP in python-like pseudo code, the default isolation level is assumed:

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')

The issue with this is that FOR UPDATE leads to an IS lock, which does not prevent two transactions to proceed. This results in a deadlock when both transaction attempt to UPDATE or INSERT.

Another way to do is first try to insert, and update if there is a duplicated key:

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')

The issue in this solution is that a failed INSERT leads to an S lock, which does not prevent two transaction to proceed as well, as described here: https://stackoverflow.com/a/31184293/710358.

Of course any solution requiring hardcoded wait or locking the entire table is not satisfying for production environments.

Answer 1

A hack to solve this issue is to use INSERT ... ON DUPLICATE KEY UPDATE ... which always issues an X lock. Since you need the old value, you can perform a blank update and proceed as in your second solution:

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')