3

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
2
  • Perhaps we should work on optimizing the SELECT?
    – Rick James
    Oct 4 at 17:24
  • @RickJames - Yeah we did. In fact we realized that we don't need it at all and now the whole query is gone. :) I just wanted to know if it was likely that this was the culprit, because we weren't sure. It did run concurrently with other problems we had, but correlation isn't causation after all. However now it looks like it indeed was the problem and I learned something new today. :)
    – Vilx-
    Oct 4 at 18:20
5

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.

10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.