0

seems postgresql have some issue in transaction when update with subquery

there are one record in table with column lock_id is null;

TESTCASE1

1、excute update table set lock_id = 1 where id in (select id from table where lock_id is null order by creation_date limit 100) but no commit;

2、excute update table set lock_id = 2 where id in (select id from table where lock_id is null order by creation_date limit 100) but no commit;

3、commit step 1;

4、commit step 2;

5 query the column lock_id ,result is 2

TESTCASE2

1、excute update table set lock_id = 1 where lock_id is null but no commit;

2、excute update table set lock_id = 2 where lock_id is null but no commit;

3、commit step 1;

4、commit step 2;

5 query the column lock_id ,result is 1

seems when there are subquery in update's condition,the second update will cover up the first one

For me,I have some concurrent scenarios, two machine will try to excute update like testcase1 to lock 100 records and then query the records for subsequent processings by lock_id,but sometimes two machine both handle a same record;

How to avoid this behaviour?

alphas
  • 1
  • I suggest you review the answer provided in: https://stackoverflow.com/questions/6256610/updating-table-rows-in-postgres-using-subquery – Cninroh Aug 20 '21 at 07:39
  • the syntax `update ... from ( select ... from ...) where ...` still have the problem in TESTCASE 1,now I keep the sql singleton run to avoid this situation, but still looking for some other way to change the database transaction's behavior – alphas Sep 03 '21 at 07:59

0 Answers0