[MDEV-35335] implicit commit at START TRANSACTION doesn't reset characteristics - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6.17
Fix Version/s: 10.6.21, 10.11.11, 11.4.5, 11.7.2
Component/s: Server
Labels:
None
Environment:
Ubuntu 22.04

Description

When I tested serializable isolation levels on Mariadb, I found a write skew exception.
The test cases and results are as follows.
The execution demonstrates a write skew exception (A5B: r1[x]... .r2[y]... .w1[y]... .w2[x]... (c1 and c2 occur), ANSI).
Also, the result of this execution contains a circle of read-write dependencies, which is against the serializable isolation level.
In other words, the two concurrent transactions are not equivalent to the result of the serialized execution of the transaction. This is a contradiction to the definition of serializability.
Therefore, it seems to me that this is a logical bug for the isolation level.

--- session 0 ---

CREATE TABLE tYv10enE (ID INT AUTO_INCREMENT UNIQUE, VAL INT, c0 INT  NULL UNIQUE KEY);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 10989748);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -1404643822);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -335483076);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 942162931);

--- session 1 ---

BEGIN;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--- session 2 ---

BEGIN;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

--- session 1 ---

BEGIN;

--- session 2 ---

BEGIN;

--- session 1 ---

SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 );

+----+-------+-------------+

| ID | VAL   | c0          |

+----+-------+-------------+

|  2 | 58093 | -1404643822 |

|  3 | 67578 |  -335483076 |

+----+-------+-------------+

2 rows in set (0.00 sec)

--- session 2 ---

SELECT * FROM tYv10enE WHERE ((1) | (0)) IS UNKNOWN OR ( c0 >= 889101988 );

+----+-------+-----------+

| ID | VAL   | c0        |

+----+-------+-----------+

|  4 | 63609 | 942162931 |

+----+-------+-----------+

1 row in set (0.00 sec)

--- session 1 ---

DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 );

Query OK, 1 row affected (0.00 sec)

--- session 2 ---

DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 );

Query OK, 2 rows affected (0.00 sec)

--- session 1 ---

COMMIT;

--- session 2 ---

COMMIT;

Attachments

Issue Links

relates to

MDEV-6905 START TRANSACTION IF NOT EXISTS

Open

MDEV-32652 Commit/rollback must emit an error when not inside active transaction

Open

Activity

Ascending order - Click to sort in descending order

huicong xu created issue - 2024-11-04 09:15

Marko Mäkelä made changes - 2024-11-04 14:04

Field	Original Value	New Value
Link		This issue relates to MDEV-6905 [ MDEV-6905 ]

Marko Mäkelä added a comment - 2024-11-04 14:04

I converted the input to the .test file format of the mtr tool:

--source include/have_innodb.inc

CREATE TABLE tYv10enE (ID INT AUTO_INCREMENT UNIQUE, VAL INT, c0 INT  NULL UNIQUE KEY) ENGINE=InnoDB;

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 10989748);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -1404643822);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -335483076);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 942162931);

--connect con1,localhost,root

BEGIN;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 );

--connect con2,localhost,root

BEGIN;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 );

--connection con1

DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 );

--connection con2

DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 );

COMMIT;

--disconnect con2

--connection con1

COMMIT;

--disconnect con1

--connection default

DROP TABLE tYv10enE;

With this test I can reproduce the reported phenomenon. But:

BEGIN or START TRANSACTION does not actually do anything in InnoDB; the transaction would not be started before the first row operation. There is a START TRANSACTION WITH CONSISTENT SNAPSHOT variant that would immediately create a read view within InnoDB.
SET SESSION TRANSACTION ISOLATION LEVEL only takes effect before the transaction is started.
Unfortunately, no error is being flagged for a duplicated BEGIN or START TRANSACTION.

If I ensure that the transactions will be created at the expected isolation level, there will be a locking conflict, just as expected:

--source include/have_innodb.inc

CREATE TABLE tYv10enE (ID INT AUTO_INCREMENT UNIQUE, VAL INT, c0 INT  NULL UNIQUE KEY) ENGINE=InnoDB;

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 10989748);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -1404643822);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -335483076);

INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 942162931);

--connect con1,localhost,root

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 );

--connect con2,localhost,root

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 );

--connection con1

DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 );

--connection con2

DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 );

COMMIT;

--disconnect con2

--connection con1

COMMIT;

--disconnect con1

--connection default

DROP TABLE tYv10enE;

mysqltest: At line 21: query 'DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 )' failed: ER_LOCK_WAIT_TIMEOUT (1205): Lock wait timeout exceeded; try restarting transaction

Last, please note that if you are testing transaction isolation, you’d better enable innodb_snapshot_isolation=ON (~~MDEV-35124~~).

The behaviour of START TRANSACTION or BEGIN seems to be different from the one that is described in MDEV-6905. If the previous transaction had been committed by the second BEGIN or START TRANSACTION, then also the isolation level change should have taken effect. I think that this anomaly would qualify as a SQL layer bug. I hope that serg can comment on this.

Marko Mäkelä added a comment - 2024-11-04 14:04 I converted the input to the .test file format of the mtr tool: --source include/have_innodb.inc CREATE TABLE tYv10enE (ID INT AUTO_INCREMENT UNIQUE , VAL INT , c0 INT NULL UNIQUE KEY ) ENGINE=InnoDB; INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 10989748); INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -1404643822); INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -335483076); INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 942162931); --connect con1,localhost,root BEGIN ; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; BEGIN ; SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 ); --connect con2,localhost,root BEGIN ; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; BEGIN ; SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 ); --connection con1 DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 ); --connection con2 DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 ); COMMIT ; --disconnect con2 --connection con1 COMMIT ; --disconnect con1 --connection default DROP TABLE tYv10enE; With this test I can reproduce the reported phenomenon. But: BEGIN or START TRANSACTION does not actually do anything in InnoDB; the transaction would not be started before the first row operation. There is a START TRANSACTION WITH CONSISTENT SNAPSHOT variant that would immediately create a read view within InnoDB. SET SESSION TRANSACTION ISOLATION LEVEL only takes effect before the transaction is started. Unfortunately, no error is being flagged for a duplicated BEGIN or START TRANSACTION . If I ensure that the transactions will be created at the expected isolation level, there will be a locking conflict, just as expected: --source include/have_innodb.inc CREATE TABLE tYv10enE (ID INT AUTO_INCREMENT UNIQUE , VAL INT , c0 INT NULL UNIQUE KEY ) ENGINE=InnoDB; INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 10989748); INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -1404643822); INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), -335483076); INSERT INTO tYv10enE (VAL, c0) VALUES ((ROUND (RAND() * 100000)), 942162931); --connect con1,localhost,root SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; START TRANSACTION ; SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 ); --connect con2,localhost,root SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; START TRANSACTION ; SELECT * FROM tYv10enE WHERE (! ((1) | (1))) OR ( c0 <= 1 ); --connection con1 DELETE FROM tYv10enE WHERE FALSE OR ( c0 >= 889101988 ); --connection con2 DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 ); COMMIT ; --disconnect con2 --connection con1 COMMIT ; --disconnect con1 --connection default DROP TABLE tYv10enE; mysqltest: At line 21: query 'DELETE FROM tYv10enE WHERE FALSE OR ( c0 <= 1 )' failed: ER_LOCK_WAIT_TIMEOUT (1205): Lock wait timeout exceeded; try restarting transaction Last, please note that if you are testing transaction isolation, you’d better enable innodb_snapshot_isolation=ON ( MDEV-35124 ). The behaviour of START TRANSACTION or BEGIN seems to be different from the one that is described in MDEV-6905 . If the previous transaction had been committed by the second BEGIN or START TRANSACTION , then also the isolation level change should have taken effect. I think that this anomaly would qualify as a SQL layer bug. I hope that serg can comment on this.

huicong xu added a comment - 2024-11-05 01:44

Thanks for your reply.

According to your explanation, this anomaly appears on duplicated BEGIN and ISOLATION LEVEL settings.
I'm curious as to why this is happening.

I also check the effect of SET SESSION TRANSACTION ISOLATION LEVEL. I found that it does modify the isolation level.

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;

+----------------+

| @@tx_isolation |

+----------------+

| SERIALIZABLE   |

+----------------+

1 row in set (0.00 sec)

huicong xu added a comment - 2024-11-05 01:44 Thanks for your reply. According to your explanation, this anomaly appears on duplicated BEGIN and ISOLATION LEVEL settings. I'm curious as to why this is happening. I also check the effect of SET SESSION TRANSACTION ISOLATION LEVEL. I found that it does modify the isolation level. mysql> BEGIN ; Query OK, 0 rows affected (0.00 sec) mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE ; Query OK, 0 rows affected (0.00 sec) mysql> BEGIN ; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@tx_isolation; + ----------------+ | @@tx_isolation | + ----------------+ | SERIALIZABLE | + ----------------+ 1 row in set (0.00 sec)

Marko Mäkelä added a comment - 2024-11-07 11:53

Some settings are being copied from a THD object into an InnoDB trx_t object at the start of a transaction. This logic appears to be broken in the case of a duplicated BEGIN or START TRANSACTION statement. I did not debug it in detail. In any case, that bug needs to be fixed outside InnoDB, which is my area of responsibility.

You may be interested in this blog post: https://mariadb.com/resources/blog/isolation-level-violation-testing-and-debugging-in-mariadb/

Marko Mäkelä added a comment - 2024-11-07 11:53 Some settings are being copied from a THD object into an InnoDB trx_t object at the start of a transaction. This logic appears to be broken in the case of a duplicated BEGIN or START TRANSACTION statement. I did not debug it in detail. In any case, that bug needs to be fixed outside InnoDB, which is my area of responsibility. You may be interested in this blog post: https://mariadb.com/resources/blog/isolation-level-violation-testing-and-debugging-in-mariadb/

Sergei Golubchik made changes - 2024-11-13 19:21

Assignee

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2024-11-13 19:25

Summary

Write skew exception at serializable isolation level

implicit commit at START TRANSACTION doesn't reset characteristics

Sergei Golubchik made changes - 2024-11-13 19:33

Fix Version/s

10.6 [ 24028 ]

Sergei Golubchik made changes - 2024-11-13 19:35

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2024-11-13 19:35

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2024-11-14 18:30

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Marko Mäkelä made changes - 2024-11-25 14:48

Link

This issue relates to MDEV-32652 [ MDEV-32652 ]

Sergei Golubchik made changes - 2025-01-09 11:18

Component/s		Server [ 13907 ]
Fix Version/s		10.6.21 [ 29953 ]
Fix Version/s		10.11.11 [ 29954 ]
Fix Version/s		11.4.5 [ 29956 ]
Fix Version/s		11.7.2 [ 29914 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

People

Assignee:: Sergei Golubchik

Reporter:: huicong xu

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2024-11-04 09:15

Updated:: 2025-01-09 11:18

Resolved:: 2025-01-09 11:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration