mysqltransactionssql-updateinnodbisolation-level

Strange behavior of Update Statement

The thing is about a very simple update statement:

UPDATE `user` SET fans = fans +1 where id = 8;

(because it will not cause any concurrency issue when just single[auto commit] use it, so I want to figure it out how it behaves in a manual transaction.) And it turns out that this statement seems to break the limitations of RR's isolation level in a transaction.

Let's assume that fans = 0.

That is:

  1. Begin transaction A, Exec "SELECT * from user WHERE id = 8;", and hold. (the query gets fans = 0);
  2. Begin transaction B, UPDATE user SET fans = fans +1 where id = 8, and Commit.
  3. And now, in the current table, fans for id = 8 sets to 1.
  4. Exec "SELECT * from user WHERE id = 8;" , again, in tran A, and the result of fans is 0, it meets expectation. (because we are in RR, should not see any updates after our transaction begin.)
  5. But, when exec "UPDATE user SET fans = fans +1 where id = 8;" In tran A, and use "SELECT * from user WHERE id = 8;" again, we can see that 'fans' field is updated to 2. It seems that is an RC behavior, not an RR hehavior. (but 'SELECT @@transaction_isolation;' says it is in 'REPEATABLE-READ').
  6. Commit tran A, 'fans' value goes to 2.

My mysql version is '8.0.41-0ubuntu0.22.04.1', isolation is 'REPEATABLE-READ', engine is Innodb.

So I assume, that is a special-designed feature for the 'self-increment statement', or it is something go wrong? I am not an expert so I am very confused, looking forward for any insight of this.

Solution

  • Your expectation regarding the behaviour of repeatable read after an update is not correct. In repeatable read isolation level insert / update / delete operations still work with committed versions of rows.

    Since the result of the update statement in transaction A is fans = 2, and repeatable read does reflect changes done by the current transaction to the resultset, the subsequent select in transaction A also correctly reflects fans = 2.

    To conclude that this is the correct behaviour, just imagine that

    1. This table represented current account balances at a bank

    2. Row with id = 8 was your current account balance

    3. These two transactions represented incoming payments crediting money to your account.

    What would you like your balance be after the two transactions?