Is MariaDB Acquiring Exclusive Lock during Delete?

Question

There is a Spring batch job with one delete and many inserts within a transaction with isolation level READ_COMMITTED:

delete from TABLE_A;
insert into TABLE_A values (...);
insert into TABLE_A values (...);
...

However, when I tried to select from the DB TABLE_A while the batch job was still running (not yet committed), the select was not blocked by exclusive lock which I expected because there was a full table delete in the transaction. May I know if MariaDB is having some special locking mechanism?

Answer 1

It provided the data as if you looked at the table before the DELETE started. That's all your SELECT can see with READ_COMMITTED until the moment that the DELETE COMMITs. (No, you won't get a mixture of old data and new data.)

(Quoting Bill Karwin...)

Locking behavior is always up to the implementation. That is, both MySQL and Microsoft SQL Server are "special" in that their respective implementation is different. The developers have made choices about how to support concurrent access to data. MySQL's InnoDB storage engine was designed to behave similar to Oracle database, which uses Multiversion Concurrency Control (MVCC). In that design, writers do not block readers by default. The reader can read a copy of the data, even while the writer has updated or deleted it.