17

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?

pdeva
  • 43,605
  • 46
  • 133
  • 171

2 Answers2

16

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.

cliffordheath
  • 2,536
  • 15
  • 16
  • so is one essentially applying a 'write' lock to the selected rows when using FOR UPDATE? – pdeva Nov 20 '15 at 00:45
  • 1
    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). – cliffordheath Nov 20 '15 at 10:55
  • if it prevents other threads from obtaining a read or write lock, then how is it different from a write lock? – pdeva Nov 22 '15 at 04:26
  • 1
    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 . – cliffordheath Nov 22 '15 at 23:32
  • 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. – Crazometer Dec 09 '20 at 07:44
  • 1
    @Crazometer 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". – cliffordheath Dec 10 '20 at 23:53
  • @cliffordheath i am able to update value. – Rajat Aggarwal Nov 28 '21 at 20:48
  • 1
    @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 – cliffordheath Nov 29 '21 at 00:04
  • @cliffordheath 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. – Rajat Aggarwal Dec 09 '21 at 18:25
3

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.

董诚怡
  • 41
  • 1
  • 3