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 -

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

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

1. 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.
2. 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.
3. Same as #2, allowed but it does not see both newly inserted rows.
4. 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.

CodePudding user response：

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.

CodePudding user response：

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.