CODEWITHSUNDEEP
mysqlsqlconcurrencytransactionspercona
pdeva
pdeva

Reputation: 45481

Whats the use of SELECT .. FOR UPDATE when using Repeatable Read isolation?

When using Repeatable Read isolation, you are guaranteed that the rows you read using SELECT wont be modified until your transaction completes.

This seems to be similar to what SELECT .. FOR UPDATE offers.

So what is the point of using SELECT FOR UPDATE when using Repeatable Read isolation?

Upvotes: 20

Views: 4940

Answers (2)

董诚怡
董诚怡

Reputation: 41

Maybe something wrong.

When you read a record under Repeatable Read without using FOR UPDATE, Mysql using Consistent Nonlocking Reads for the read. It doesn't create any lock on it.

Using FOR UPDATE will create a write lock.

Repeatable Read without using FOR UPDATE: read data from the snapshot established by the first read in that transaction.

Using FOR UPDATE: read the fresh snapshot. It can read the up to date data that are committed. It behaves like "READ COMMITTED" even if you are using Repeatable Read isolation level.

Besides,if you create a transaction A and using FOR UPDATE on one row. Like this.

BEGIN;
select * from hero where id=3 for update ;

Then you create another transaction B and do a simple read.

BEGIN;
select * from hero where id=3 ;

transaction B uses Nonlocking-Read and will not check if write lock exist on the row. It wouldn't block.

Upvotes: 3

cliffordheath
cliffordheath

Reputation: 2606

When you read a record under Repeatable Read, you get a read-lock, but other transactions can also get a read lock, which might prevent you from making an update later. Using FOR UPDATE informs any other transactions which request a read lock that they should wait until you're finished updating the record.

Upvotes: 19

Related Questions