CODEWITHSUNDEEP
mysql
StackyStack
StackyStack

Reputation: 115

MySQL concurrent SELECT, INSERT, UPDATE

Let's say I have one very large table such that a SELECT * query takes more than 5 minutes (exaggerated for the example) and while that is running, an UPDATE happens on the first row of the table. If the global isolation level is READ_REPLICATED, does that make the UPDATE wait for the full SELECT query to read all the rows? Could the second transaction make an INSERT into the table while the SELECT is running?

Upvotes: 3

Views: 1212

Answers (1)

Croco
Croco

Reputation: 366

I can't write comments so I must write here. User Psi claims in the comments that:

regardless of the isolation level, when a read is running on a table, the table gets locked for write-operations

This is wrong, at least on InnoDB tables when the isolation level is REPEATABLE READ. From the manual ( https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html ):

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

So, to your questions:

Let's say I have one very large table such that a SELECT * query takes more than 5 minutes (exaggerated for the example) and while that is running, an UPDATE happens on the first row of the table. If the global isolation level is READ_REPLICATED, does that make the UPDATE wait for the full SELECT query to read all the rows?

If you meant REPEATABLE READ, then no. The SELECT gives you a snapshot of the data that was in the table at the time the SELECT started. If another session does an UPDATE during your query that modifies the rows your SELECT won't see the changes, it'll give you the "old" values.

Could the second transaction make an INSERT into the table while the SELECT is running?

Yes.

Upvotes: 2

Related Questions