2

From MySQL glossary:

READ COMMITTED

When a transaction with this isolation level performs UPDATE ... WHERE or DELETE ... WHERE operations, other transactions might have to wait. The transaction can perform SELECT ... FOR UPDATE, and LOCK IN SHARE MODE operations without making other transactions wait.

Is that correct?

UPDATE sets x-locks to every row it scans and then releases those locks that do not match the WHERE-part. The remaining rows keep x-locks until the transaction ends. As far as I know, exactly same thing happens with SELECT-FOR UPDATE. So how it can be that UPDATE may block other transactions but SELECT-FOR UPDATE does not?

Community
  • 1
  • 1
Croco
  • 326
  • 1
  • 12

1 Answers1

1

The glossary is not accurate.

SELECT FOR UPDATE acquires an X-lock just like UPDATE does. In both cases, other transactions that need any type of lock have to wait.

SELECT FOR SHARE (or LOCK IN SHARE MODE) acquires an S-lock. Other transactions that need S-locks can get them, but other transactions that need X-locks need to wait.

The transaction isolation level has little to do with the locks required, except that some types of gap locks are not needed when your transaction uses READ-COMMITTED.

It seems the glossary is a bit neglected. Better to read https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html and https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828