I read mysql INNODB uses MVCC (optimistic) to solve READ_COMMITTED and REPEATABLE_READ isolation levels (and) 2PL (pessimistic) to solve SERIALIZABLE. But no where it's mentioned how it solves READ_UNCOMMITTED.
I opened two mysql sessions with READ_UNCOMMITTED isolation level. In both sessions, I tried to update the very same record. In first session, it got updated but in the second session, it was waiting for either first session to commit/rollback. Who locked this? since apparently there is no involvement of MVCC or 2PL here.
Write-Write conflict -> Also, I saw the same behaviour in all the isolation levels, atleast in READ_COMMITTED and REPEATABLE_READ, which are solved by MVCC, when first session updated a row, then second sessions waits. I know MVCC doesn't locks in case of,
i) First session reading (and) Second session reading
ii)First session writing (and) Second session reading
iii) First session reading (and) Second session writing
which is true to the statement "Readers don't block Writers and Writers don't block Readers". But, in the case of,
iv) First session writing (and) Second session writing - does INNODB lock the transaction and wait until the other commits/rollback's?
Mysql version: 5.7.32 Engine: INNODB
Locking happens regardless of isolation level. Read-uncommitted is not a free-for-all.
The first session locked rows that it examined as part of its update, in exactly the same way as it would in any other isolation level. The session holds those locks until it commits its transaction, or rolls back. This is also the same as in any other isolation level.
By the way, in 30+ years of using SQL databases, I've never encountered a legitimate use for read-uncommitted.