MySQL/MariaDB: can a SELECT query block an INSERT query?

Let's suppose that I have a query in the form:

INSERT INTO tableA (...) select ... from tableB;

Now, is it possible that this query, while executing, blocks plain INSERT statements to tableB? Additional information:

• All tables are InnoDB

• There are no explicit transactions (autocommit is on)

• There is NOT a FOR UPDATE clause in the SELECT statement (I'm not even sure if it would be possible to do it in an INSERT...SELECT construct)

• The actual SELECT query is obviously more complicated and involves grouping and unions, but those shouldn't change anything, right?

• Transaction isolation level for all queries is the default REPEATABLE READ

• The precise database version is MariaDB 10.0.12

Yes, SELECT is a locking select in this case.

If you use SELECT in any query that changes data, it is implicitly a locking query as if you had used SELECT ... LOCK IN SHARE MODE. This puts a shared lock on the rows it reads.

Any other statement that changes data acts the same way. This includes cases like:

• INSERT ... SELECT ...

• CREATE TABLE ... AS SELECT ...

• SELECT ... INTO variable or ... INTO OUTFILE

• SET @variable = (SELECT ...)

• A trigger on INSERT/UPDATE/DELETE that runs a SELECT in its body.

• Subqueries in UPDATE or DELETE statements

• other cases...

A shared lock may be held by multiple concurrent sessions. So you could do this INSERT ... SELECT query in multiple transactions. They won't conflict with each other.

But an INSERT to tableB must have an exclusive lock. An exclusive lock won't be granted if there are any shared locks present.

StackExchange Database Administrators Q#300543, answer score: 5