1

In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The problem we're running into is when two threads are both trying to insert 'abc' into the table at almost the same time.

As expected, if thread1 & thread2 start at the same time, thread2 gets stuck waiting on the "select...for update" statement at the start. Once thread1 commits its transaction, the lock is released and thread2 continues executing.

The problem is that thread2's next line selecting from tbl_foo returns no result causing it to execute the wrong block of code after, despite it just having been inserted. In fact, the later insert by thread2 will fail due to a unique constraint violation. Is this a known problem with mysql and/or aurora read caching? Is there some configuration setting that will change it?

<start transaction>

SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;

SELECT * FROM tbl_foo WHERE fk_user = 123 AND unique_column = 'abc';

if (found row in tbl_foo)
    do stuff
else
    do different stuff including inserting (123, 'abc') into tbl_foo

<commit transaction>
2
  • Are there multiple 'replicas' ('slaves')? If so, your code seems to fail to deal with the "critical read" problem.
    – Rick James
    Oct 6, 2019 at 2:04
  • No, all threads are connected to the same instance.
    – Inukshuk
    Oct 6, 2019 at 15:59

1 Answer 1

0
SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;
SELECT * FROM tbl_foo WHERE fk_user = 123 AND unique_column = 'abc';

You are missing FOR UPDATE on the second one?

Can you combine both SELECTs with a JOIN? (This would help avoid a deadlock.)

5
  • I don't want or need to lock a row in tbl_foo - and it wouldn't work anyway since it's not returning a row, there wouldn't be anything to lock.
    – Inukshuk
    Oct 6, 2019 at 16:04
  • @Inukshuk - There is a thing called "gap locking". (I am not sure whether it is relevant here.) Give the FOR UPDATE a try. If you do the JOIN, use LEFT JOIN since you are expecting the 'abc' row to be missing.
    – Rick James
    Oct 6, 2019 at 18:25
  • A gap lock wouldn't work - from mysql documentation "their only purpose is to prevent other transactions from inserting to the gap". I need thread1 to be allowed to insert the row. I just want thread2 to see it there when it checks after thread1 completes its transaction.
    – Inukshuk
    Oct 6, 2019 at 21:48
  • @Inukshuk - But you are about to insert into a gap: else ... inserting (123, 'abc') ...
    – Rick James
    Oct 6, 2019 at 23:17
  • Upon further reading, gap lock is not relevant here because there is a unique index on 'unique_column' and it's looking for a unique row. I don't know if a join would even work, but I can't easily do that because the tbl_user lock actually occurs at an abstract level that starts the transaction before getting into the specific details of this tbl_foo select statement. The only thing left then would be to try putting FOR UPDATE on the tbl_foo select as well to see if that forces the db engine to look more closely to see the row that was inserted a millisecond earlier.
    – Inukshuk
    Oct 7, 2019 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.