0

You have an InnoDB table A with the following two columns: id (PK), status

Two threads are accessing the table in the following order:

Thread 1: SELECT id from A where status = 0 LIMIT 1;
Thread 2: SELECT id from A where status = 0 LIMIT 1;

Both threads select id 1.

Thread 1: UPDATE A SET status = 1 WHERE id = 1 AND status = 0;
Thread 2: UPDATE A SET status = 2 WHERE id = 1 AND status = 0;

Is it ever possible that both threads update the same row?

Now add transactions:

Thread 1: SELECT id from A where status = 0 LIMIT 1;
Thread 2: SELECT id from A where status = 0 LIMIT 1;
Thread 1: START TRANSACTION;
Thread 2: START TRANSACTION;
Thread 1: UPDATE A SET status = 1 WHERE id = 1 AND status = 0;
Thread 2: UPDATE A SET status = 2 WHERE id = 1 AND status = 0;
Thread 1: COMMIT;
Thread 2: COMMIT;

Is it now possible that both threads will update the row?

In either case, if it is possible for both rows to be updated, what would be the correct way to address this?

phenompbg
  • 391
  • 2
  • 10
  • Possible duplicate of [How to deal with concurrent updates in databases?](http://stackoverflow.com/questions/1195858/how-to-deal-with-concurrent-updates-in-databases) – Stefano Zanini Mar 10 '17 at 09:12

1 Answers1

1

Locking mechanism by default on InnoDB is REPEATABLE_READ (see https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read). What this means is that if you do two updates on the same index will be done atomicly, however I do not believe you can guarantee which update will come through first, without adding some logic in your program. (the first update will be done by the Thread acquiring the lock first).

So to manage that updates are done in the correct order, you will want to implement the logic inside your program (if it were Java, I would look at Barrier, Latches, and Semaphores).

Adonis
  • 4,670
  • 3
  • 37
  • 57
  • Thanks! I am not too concerned by the order, I am concerned that only one of the updates would be possible (hence the status=0 in the where). In other words, would the change of the value of status be visible to the thread that acquired the lock after the first update? – phenompbg Mar 10 '17 at 09:36
  • Ok then two updates are definitely possible by default – Adonis Mar 10 '17 at 09:37
  • Will changing the transaction isolation to Serializable solve that? – phenompbg Mar 10 '17 at 09:41
  • Serializable won't work because the row will be updated to status 1 or 2 before the other does the select on status 0 (thus ending with an empty row). So if you really want this behaviour (I would recommend not to), the default level is ok, however, you need to ensure every select is done before any update occurs (see the part of my post using semaphores and so on) – Adonis Mar 10 '17 at 09:49
  • The behaviour I am after (should probably have made this clearer in the question) is that I want only one of the two threads to be able to update that row. Imagine the rows represent some resource that can only be consumed once. If both got that row, I want whoever gets there last to fail and try again finding a new row. If I could gurantee that from the start, that would be great too. – phenompbg Mar 13 '17 at 06:47
  • In that case *Serializable* would indeed work, though I would rather recommend to do those checks inside the program, (without testing) since using Serializable give me the feeling you could lose some performance on your database (more locking might result in more lock contention) – Adonis Mar 13 '17 at 08:04
  • I do not anticipate a lot of contention, or even two simultaneous threads almost ever. Just need to make sure. Thanks a lot for your help! – phenompbg Mar 13 '17 at 10:47