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>