InnoDB locking for INSERT/UPDATE concurrent transactions

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.

How long does `process(old_value, new_value)` take? If it is more than a few seconds, then you are asking for trouble by keeping the transaction open that long. — Rick James, Jan 29 '18 at 22:02
It's very quick (<1ms), but enough to observe deadlocks in prod — M1L0U, Jan 29 '18 at 22:12

score 0 · Answer 1 · answered Jan 26 '18 at 00:00

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

Use this trick to get the `id`: `... ON DUPLICATE KEY UPDATE id=last_insert_id(id)` and then `SELECT last_insert_id()`. — Rick James, Nov 11 '19 at 20:45

InnoDB locking for INSERT/UPDATE concurrent transactions

1 Answers1

Linked