mirror of
https://github.com/MariaDB/server.git
synced 2025-02-12 00:15:35 +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.
65 lines
1.4 KiB
Text
65 lines
1.4 KiB
Text
create table t1 (
|
|
rowid int,
|
|
f1 int,
|
|
f2 int,
|
|
key i1 (f1, f2),
|
|
key i2 (f2)) engine=innodb;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`rowid` int(11) DEFAULT NULL,
|
|
`f1` int(11) DEFAULT NULL,
|
|
`f2` int(11) DEFAULT NULL,
|
|
KEY `i1` (`f1`,`f2`),
|
|
KEY `i2` (`f2`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
insert into `t1` (rowid, f1, f2) values (1, 1, 10), (2, 1, NULL);
|
|
connect a,localhost,root,,;
|
|
connect b,localhost,root,,;
|
|
connection a;
|
|
set innodb_snapshot_isolation=OFF;
|
|
start transaction with consistent snapshot;
|
|
connection b;
|
|
start transaction;
|
|
update t1 set f2 = 4 where f1 = 1 and f2 is null;
|
|
(b) Number of rows updated:
|
|
select row_count();
|
|
row_count()
|
|
1
|
|
insert into t1 values (3, 1, null);
|
|
(b) After update and insert query.
|
|
select rowid, f1, f2 from t1;
|
|
rowid f1 f2
|
|
1 1 10
|
|
2 1 4
|
|
3 1 NULL
|
|
commit;
|
|
connection a;
|
|
(a) Before the update statement is executed.
|
|
select rowid, f1, f2 from t1;
|
|
rowid f1 f2
|
|
1 1 10
|
|
2 1 NULL
|
|
SET SESSION debug_dbug="+d,bug14007649";
|
|
update t1 set f2 = 6 where f1 = 1 and f2 is null;
|
|
(a) Number of rows updated:
|
|
select row_count();
|
|
row_count()
|
|
1
|
|
(a) After the update statement is executed.
|
|
select rowid, f1, f2 from t1;
|
|
rowid f1 f2
|
|
1 1 10
|
|
2 1 NULL
|
|
3 1 6
|
|
commit;
|
|
"The trx with consistent snapshot ended."
|
|
select rowid, f1, f2 from t1;
|
|
rowid f1 f2
|
|
1 1 10
|
|
2 1 4
|
|
3 1 6
|
|
connection default;
|
|
disconnect a;
|
|
disconnect b;
|
|
drop table t1;
|