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
    do different stuff including inserting (123, 'abc') into tbl_foo

<commit transaction>
  • Are there multiple 'replicas' ('slaves')? If so, your code seems to fail to deal with the "critical read" problem. – Rick James Oct 6 '19 at 2:04
  • No, all threads are connected to the same instance. – Inukshuk Oct 6 '19 at 15:59
SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;

You are missing FOR UPDATE on the second one?

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

| improve this answer | |
  • 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 '19 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 '19 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 '19 at 21:48
  • @Inukshuk - But you are about to insert into a gap: else ... inserting (123, 'abc') ... – Rick James Oct 6 '19 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 '19 at 18:51

Your Answer

