Timeline for MySQL consistent nonlocking reads vs. INSERT ... SELECT
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 15, 2020 at 9:05 | history | edited | CommunityBot | Commonmark migration | |
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot | replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/ | |
| Aug 11, 2014 at 6:00 | vote | accept | Luis Casillas | ||
| Aug 8, 2014 at 21:09 | comment | added | RolandoMySQLDBA | It does not have REPEATABLE READ semantics at all. Look back at the MySQL Document in my answer. It clearly says InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED. InnoDB performs internal lock escalation to make INSERT ... SELECT possible. | |
| Aug 8, 2014 at 21:01 | comment | added | Luis Casillas | Ok, let me see if I understand it now: when a transaction runs a INSERT ... SELECT or CREATE TABLE AS, later statements in the same transaction can see these writes. This means that even if the isolation level is REPEATABLE READ, the transaction's reads can no longer share the snapshot established by the first read; instead, "the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot." So then the engine has to fall back on row locks to preserve the REPEATABLE READ semantics, right? | |
| Aug 8, 2014 at 20:43 | comment | added | RolandoMySQLDBA | Look at my first paragraph. The SELECT needs to a frozen snapshot. That's the rule imposed by the InnoDB Storage Engine. | |
| Aug 8, 2014 at 20:41 | comment | added | Luis Casillas | But then why can't INSERT INTO target SELECT blah FROM source do the same, and use a transaction-level consistent MVCC snapshot just like the multiple simple SELECTS can? (Assuming no statements in the transaction modify the source table.) | |
| Aug 8, 2014 at 20:31 | comment | added | RolandoMySQLDBA | The MVCC within InnoDB would have a virtual snapshot of the data as it appears the moment you read it. Locks are not really needed once the snapshot exists. | |
| Aug 8, 2014 at 20:28 | comment | added | Luis Casillas | "The SELECT needs to be a frozen snapshot for loading the table. If it were a moving target, that would require a transaction within a transaction." But how is it then that row locks are not needed when you have multiple simple SELECTs of the same table within one REPEATABLE READ transaction? | |
| Aug 8, 2014 at 19:56 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 | added 1 character in body |
| Aug 8, 2014 at 19:50 | history | answered | RolandoMySQLDBA | CC BY-SA 3.0 |