Thesql/aurora not immediately seeing results of committed transactions

amazon-rdsamazon-rds-auroraaws-auroraconcurrencyMySQL

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>

Best Answer

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

Related Solutions

MySQL: Loop over cursor results ends ahead of schedule

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat; 

close my_cursor; 

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.

MySQL: Transaction and Deadlock

If you are totally sure about the separation status of your data, you can set the global isolation level to READ COMMITTED or even to READ UNCOMMITTED. The first blocks less from the index, which should be enough in your case. The second one arranges for all SELECT statements to be non blocking. This is as low as you can go.

Whatever you choose, you should set the global isolation level back to REPEATABLE READ as soon as you finished inserting your data.

global means that this applies to all connections, which are established. So this change also affects connections beside your jarfiles. If you have the possibility to change the jarfiles, you should set the connection based isolation level with the SESSION keyword instead in both jarfiles.

Best Answer

Related Solutions

MySQL: Loop over cursor results ends ahead of schedule

MySQL: Transaction and Deadlock

Related Question