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

Question

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?

Milone

Answer 1

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.

Signify

so is one essentially applying a 'write' lock to the selected rows when using FOR UPDATE? – Milone

Not a write lock, not yet. Just ensuring no-one else prevents you getting a write lock. This is sometimes called a read-intent-write lock (There are also intent-read locks). – Signify

if it prevents other threads from obtaining a read or write lock, then how is it different from a write lock? – Milone

There's more to this subject than can be answered in a comment. Please refer to Section 7.8.1 esp. Table 7.10 "Compatibility Matrix for Granular Locks" on page 408 of Gray and Reuter <amazon.com/…>. – Signify

I think this is incorrect, please read the other answer or the Consistent Nonlocking Reads section of the documentation for more info. The short version is that SELECTs use MVCC while on the strong isolation levels, NOT sharred locks on the rows. – Limbate

@Limbate MVCC is a way of implementing RR semantics, but neither is the answer to this question about why "FOR UPDATE". The other answer talks about write locks (which MySQL uses, but is also not an answer to this question), but the author doesn't seem to know about intent locks. I refer you to Gray&Reuter "Transaction Processing" Sec 7.8.1 "Intent Lock Modes". – Signify

@Signify i am able to update value. – Casta

@RajatAggarwal You are not guaranteed to be able to update, if another reader gets a read lock. SELECT....FOR UPDATE guarantees that a new reader will not get such a lock – Signify

@Signify if both transactions read a row and try to update same row, relying only on isolation level, one of the transaction would throw error but SELECT FOR UPDATE can be used to prevent that. – Casta

Answer 2

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.

Flabbergast