select for update and insert on SERIALIZABLE isolation level

Question

isolation level: SERIALIZABLE

1. select other_col from my_table where pk_col = 1 for update
2. if there is no row returned by 1 goto 3, otherwise goto 5
3. insert ignore into my_table(pk_col, other_col) values(1, 10)
   
4. select other_col from my_table where pk_col = 1 for update
5. update my_table set other_col = other_col + 10 where pk_col = 1

Could there be a problem(deadlock, data inconsistency, ...) when multiple transactions run above logic concurrently? I think every statement acquire exclusive lock on pk_col = 1, so there is no deadlock and data inconsistency at least.

Answer 1

Deadlock is possible.
Situation: There is no row PK is 1.

TxA: 1 does not acquire lock because there is no row PK is 1.
TxB: 1 does not acquire lock because there is no row PK is 1.
TxC: 1 does not acquire lock because there is no row PK is 1.
TxA: 3 acquires exclusive lock.
TxA: 4,5 executes and release lock.
TxB: 3 acquires shared lock.
TxC: 3 acquires shared lock.
TxC: 4 tries to acquire exclusive lock but wait because TxB acquired shared lock.
TxB: 4 tries to acquire exclusive lock but wait because TxC acquired shared lock.

So TxB and TxC is deadlock.