mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
4e1e9ea6f3
From the very beginning, the default InnoDB transaction isolation level
REPEATABLE READ does not correspond to any well formed definition.
The main issue is the lack of write/write conflict detection.
To fix that and to make REPEATABLE READ correspond to Snapshot Isolation,
b8a6719889 introduced the Boolean
session variable innodb_snapshot_isolation. It was disabled by default
in order not to break any user applications.
In a new major version of MariaDB Server, we had better enable this
parameter by default.
47 lines
1.4 KiB
Text
47 lines
1.4 KiB
Text
connect cancel_purge,localhost,root,,;
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
connection default;
|
|
CREATE TABLE t (a int PRIMARY KEY, b int) engine = InnoDB;
|
|
CREATE TABLE t2 (a int PRIMARY KEY) engine = InnoDB;
|
|
INSERT INTO t VALUES (10, 10), (20, 20), (30, 30);
|
|
INSERT INTO t2 VALUES (10), (20), (30);
|
|
BEGIN;
|
|
SELECT * FROM t WHERE a = 20 FOR UPDATE;
|
|
a b
|
|
20 20
|
|
connect con_2,localhost,root,,;
|
|
SET innodb_snapshot_isolation=OFF;
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
BEGIN;
|
|
SET DEBUG_SYNC = 'lock_trx_handle_wait_enter SIGNAL upd_locked WAIT_FOR upd_cont EXECUTE 2';
|
|
UPDATE t SET b = 100;
|
|
connect con_3,localhost,root,,;
|
|
BEGIN;
|
|
UPDATE t2 SET a = a + 100;
|
|
SELECT * FROM t WHERE a = 30 FOR UPDATE;
|
|
a b
|
|
30 30
|
|
SET DEBUG_SYNC='now WAIT_FOR upd_locked';
|
|
SET DEBUG_SYNC = 'lock_wait_start SIGNAL sel_locked';
|
|
SELECT * FROM t WHERE a = 20 FOR UPDATE;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR sel_locked';
|
|
ROLLBACK;
|
|
SET DEBUG_SYNC='now SIGNAL upd_cont';
|
|
SET DEBUG_SYNC="now WAIT_FOR upd_locked";
|
|
SET SESSION innodb_lock_wait_timeout=1;
|
|
SELECT * FROM t WHERE a = 10 FOR UPDATE;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC="now SIGNAL upd_cont";
|
|
connection con_3;
|
|
a b
|
|
20 20
|
|
connection con_2;
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
disconnect con_3;
|
|
disconnect con_2;
|
|
connection default;
|
|
SET DEBUG_SYNC = 'RESET';
|
|
DROP TABLE t;
|
|
DROP TABLE t2;
|
|
disconnect cancel_purge;
|