CODEWITHSUNDEEP
mysqlselecttransactionssql-updateread-committed
Croco
Croco

Reputation: 356

InnoDB x-locks in READ COMMITTED isolation level

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?

Upvotes: 2

Views: 473

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

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

Upvotes: 1

Related Questions