InnoDb - SELECT while a Repeatable Read Transaction is Executing

Question

Consider a table with fields ID, X, Y, and Z.

CREATE TABLE TABLE_NAME (
    ID int NOT NULL,
    X varchar(255) NOT NULL,
    Y varchar(255) NOT NULL,
    Z varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);

create index idx on TABLE_NAME (X, Y);

Suppose I have the following transaction with repeatable read isolation level -

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

SELECT * FROM TABLE_NAME WHERE X="A" AND Y="B";

INSERT INTO TABLE (ID, X, Y, Z) VALUES (25, "A", "B", "C");
INSERT INTO TABLE (ID, X, Y, Z) VALUES (26, "A", "B", "D");

DELETE FROM TABLE_NAME WHERE X="A" AND Y="B" AND (Z != "C" OR Z != "D")

COMMIT TRANSACTION;

Now suppose I have a normal SELECT statement being executed outside the transaction. I want to know what happens to the SELECT statement if -

It executes between the SELECT and INSERT of the transaction.
It executes between the first INSERT statement and second INSERT statement of the transaction.
It executes between the INSERT statement 2 and DELETE statement of the transaction.
It executes between DELETE statement and committing the transaction.

I did go through the docs, and these are my assumptions for the scenarios -

SELECT only obtains a shared lock, so the SELECT statement outside the transaction is allowed to execute and sees all related rows in the table.
INSERT obtains an exclusive lock the the newly inserted row. The SELECT statement outside the transaction is allowed to execute but it does not see the newly inserted row.
Same as #2, allowed but it does not see both newly inserted rows.
DELETE obtains an exclusie lock, so the SELECT statement outside the transaction is blocked, until the executing transaction is committed.

Is my assumption correct? Specially confused about #2 and #3 here.

What indexes are there on the Table? (Please provide `SHOW CREATE TABLE`) — Rick James, Mar 23 '22 at 05:03

danblack · Answer 1 · 2022-03-23T04:17:44.330

SELECT operations outside the transaction see the TABLE as if the transaction hasn't occurred. This is so because the transaction hasn't occurred until the COMMIT happens.

This is the C out of ACID - consistency. You'll never see a partial transaction, only a completed one (ignore the READ UNCOMMITED (non-)isolation for the time being).

The MVCC means that the prior state is available. You start getting deadlocks or lock waits once you start doing modifications to the same rows of the table in a different concurrent transaction.

Example of SHOW ENGINE INNODB STATUS\G with x,y,z as a primary key before the commit:

------------
TRANSACTIONS
------------
Trx id counter 25
Purge done for trx's n:o < 22 undo n:o < 0 state: running but idle
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 20, ACTIVE 58 sec
2 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 4
MariaDB thread id 3, OS thread handle 140131094689344, query id 13 localhost dan 
Trx read view will not see trx with id >= 20, sees < 20

If at this point I can in a new session:

MariaDB [test]> select * from t;
Empty set (0.001 sec)

No blocking locks occur, the status prior to the start of the transaction is observed.

Correct, consistency holds in my assumptions above. What I also want to know is when and which rows get locked, and what type of lock. Does `INSERT` only locks those rows that are newly inserted? Or does it lock the entire table? Or something else? Same way with `DELETE` - I am guessing `DELETE` locks all rows that are affected, and the `SELECT` outside the transaction won't be allowed to execute until the transaction is committed or rolled back. — user2684198, Mar 23 '22 at 04:02
locks are on index(es) including the primary key, so it depends a little on the table indexing and can include gaps too. This prevents further modification of those rows if another transaction attempts to modify them also. However importantly another connection can run a non-blocking read of the table. Sometimes pulling up the `mysql` prompt is a good way to test assumptions. — danblack, Mar 23 '22 at 04:21

score 0 · Answer 2 · answered Mar 23 '22 at 16:31

Another way to visualize InnoDB actions:

When a transaction starts (or a statement starts with autocommit=ON), a snapshot of the entire database is taken. The transaction can see only that snapshot while it is running. When it finishes, all changes that were made are made permanent (COMMIT) or tossed (ROLLBACK).

Now for a little detail of how such a massive 'snapshot' could be taken efficiently. Each row has a number that indicates 'when' it was created. Meanwhile, when another transaction starts a similar number is grabbed, thereby defining its snapshot. Any rows older than that number are visible in READ COMMITTED. All rows, even newer ones, are visible to READ_UNCOMMITTED.

Ah, but this implies that each row is potentially a set of versions of that row. For example, an UPDATE that has not yet been committed has at least 2 copies of the row in what is called a "history list".

I guess that REPEATABLE_READ grabs the number at the time the SELECT starts, which may be later than when the transaction started?

At COMMIT or ROLLBACK time, the history list of modified rows gets cleaned up. This happens (I think) after the client has been told that the Commit/Rollback is finished.

InnoDb - SELECT while a Repeatable Read Transaction is Executing

2 Answers2