This query expected to update 1 row at a time and after processing we move the same row to next queue and query picks and update next row in infinite loop.
Update queue_table set lock_flag = 150 Where row (entity, sub_entity, side) = (select entity, sub_entity, side from queue_table where lock_flag = 0 and entity = 'Abc' limit 1 for update skip locked ) and entity = 'Abc' and lock_flag = 0;
This happens perfectly for some time and issues happens after couple of hours, not sure whether due to row lock, caching, connection leakage etc.
Update queue_table set lock_flag = 150 Where row (entity, sub_entity, side) = (select entity, sub_entity, side from queue_table where lock_flag = 0 and entity = 'Abc' limit 1 for update skip locked ) and entity = 'Abc' and lock_flag = 0;
This happens perfectly for some time and issues happens after couple of hours, not sure whether due to row lock, caching, connection leakage etc.