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.

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.

• mysql
• transactions
• sql-update
• innodb
• isolation-level

2 Answers 2

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?

There's a mistake in observation.

Step 1: Began transaction A [No locks taken rn]

Step 2: Ran an update statement via transaction B [Step 3 is part of same step]

Step 4: SELECT statement executed by transaction A SHOULD NOT RETURN THE RESULTS OF THE SNAPSHOT WHEN TRANSACTION A BEGAN.

Ideally, Step 4 should be:

Exec "SELECT * from user WHERE id = 8;" in tran A, and the result of fans is 1, it has nothing to do with repeatable read, since repeatable read isolation only begins after you have read the row once.

Why?

Coz repeatable read does not mean, one shall hold the snapshot of entire table the moment transaction began, but if you have already read some kinda data, the snapshot shall remain same through out the transaction.

In other words, post reading a particular row within a transaction, another transaction could not perform UPDATE or DELETE operation on the same row. However, the current transaction [i.e. transaction A] can change the row and those updates would be visible only to transaction in concern [i.e. transaction A] .

Hence,

Rest of the steps are consistent with expectations.

PS: I simulated this with mysql version 8 and my results are consistent with my statement.

Thanks.