Why update after select in serializable transaction cause dead lock?

Question

I was testing database transaction with different isolation levels using annotation

@Transactional(rollbackFor = Exception.class, isolation = Isolation.SERIALIZABLE)in Spring.

I have a table 'account':

create table account (id int primary key, balance int);

and one record inside:

insert into account values(1, 999);

I wrote some Java code, basically resembles following SQL in one transaction:

select * from account where id=1;
update account set balance=balance-1 where id=1;

I created 999 threads, each ran the transaction once.

I was expecting the record to have 0 balance, but most threads throws org.springframework.dao.DeadlockLoserDataAccessException: Deadlock found when trying to get lock; try restarting transaction when trying to update, and the record was left with about 700 balance.

I thought when select inside a serializable transaction, the transaction aquires read lock on the lines it reads, and will only release it after commit, so that other transactions can't select the lines until the former is committed.

Do I misunderstand something about serializable or transaction or lock? Or is it my implementation in Java causing the problem? I am using myBatis with mySQL. Here's my actual Java code for the transaction:

@Service
@RequiredArgsConstructor(onConstructor_ = {@Autowired})
@Transactional(rollbackFor = Exception.class, isolation = Isolation.SERIALIZABLE)
public class MyTestService {

    private final AccountMapper accountMapper;

    public void decreaseMoneyByOne(String id){
        Account account = accountMapper.selectByPrimaryKey(id);
        int balanceBefore = account.getBalance();
        account.setBalance(balanceBefore - 1);
        accountMapper.updateByPrimaryKey(account);
    }
}

Kayaman Kayaman · Accepted Answer · 2020-11-21T15:47:44

Here's how Postgres does serializable (Serializable Snapshot Isolation). If you're using a different database, it probably uses a different mechanism. Real databases wouldn'y do it the way you describe, as that's basically the naive implementation which would have horrible performance for locking all those rows. There are lighter (although more complex) ways, that involve killing deadlocked transactions and so on.

As the error message says "try restarting the transaction" if you want to get the end result with 0 balance.

Why update after select in serializable transaction cause dead lock?

1 Answers