mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +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.
162 lines
3.4 KiB
Text
162 lines
3.4 KiB
Text
drop table if exists t1;
|
|
connect a,localhost,root,,;
|
|
connect b,localhost,root,,;
|
|
connection a;
|
|
set binlog_format=mixed;
|
|
set session transaction isolation level repeatable read;
|
|
create table t1(a int not null)
|
|
engine=innodb
|
|
DEFAULT CHARSET=latin1
|
|
PARTITION BY RANGE(a)
|
|
(PARTITION p0 VALUES LESS THAN (20),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
|
|
set autocommit=0;
|
|
select * from t1 where a=3 lock in share mode;
|
|
a
|
|
3
|
|
connection b;
|
|
set binlog_format=mixed;
|
|
set innodb_snapshot_isolation=OFF;
|
|
set session transaction isolation level repeatable read;
|
|
set autocommit=0;
|
|
update t1 set a=10 where a=5;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
commit;
|
|
connection a;
|
|
commit;
|
|
connection b;
|
|
set session transaction isolation level read committed;
|
|
update t1 set a=10 where a=5;
|
|
connection a;
|
|
select * from t1 where a=2 for update;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
select * from t1 where a=2 limit 1 for update;
|
|
a
|
|
2
|
|
connection b;
|
|
update t1 set a=11 where a=6;
|
|
update t1 set a=12 where a=2;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
update t1 set a=13 where a=1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
connection a;
|
|
commit;
|
|
connection b;
|
|
update t1 set a=14 where a=1;
|
|
commit;
|
|
connection a;
|
|
select * from t1;
|
|
a
|
|
10
|
|
11
|
|
14
|
|
2
|
|
3
|
|
4
|
|
7
|
|
drop table t1;
|
|
connection default;
|
|
disconnect a;
|
|
disconnect b;
|
|
connect con1,localhost,root,,;
|
|
connect con2,localhost,root,,;
|
|
SET SESSION AUTOCOMMIT = 0;
|
|
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
|
set binlog_format=mixed;
|
|
connection con1;
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
|
|
ENGINE = InnoDB
|
|
PARTITION BY RANGE (a)
|
|
(PARTITION p0 VALUES LESS THAN (300),
|
|
PARTITION p1 VALUES LESS THAN MAXVALUE);
|
|
INSERT INTO t1 VALUES (1,2);
|
|
# 1. test for locking:
|
|
BEGIN;
|
|
UPDATE t1 SET b = 12 WHERE a = 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 12
|
|
connection con2;
|
|
UPDATE t1 SET b = 21 WHERE a = 1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
ROLLBACK;
|
|
connection con1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 12
|
|
ROLLBACK;
|
|
# 2. test for serialized update:
|
|
CREATE TABLE t2 (a INT);
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (1,'init');
|
|
CREATE PROCEDURE p1()
|
|
BEGIN
|
|
# retry the UPDATE in case it times out the lock before con1 has time
|
|
# to COMMIT.
|
|
DECLARE do_retry INT DEFAULT 0;
|
|
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
|
|
retry_loop:LOOP
|
|
UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
|
|
IF do_retry = 0 THEN
|
|
LEAVE retry_loop;
|
|
END IF;
|
|
SET do_retry = 0;
|
|
END LOOP;
|
|
INSERT INTO t2 VALUES ();
|
|
END|
|
|
BEGIN;
|
|
UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 init+con1
|
|
connection con2;
|
|
CALL p1;;
|
|
connection con1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 init+con1
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 init+con1
|
|
connection con2;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 init+con1+con2
|
|
COMMIT;
|
|
connection con1;
|
|
# 3. test for updated key column:
|
|
TRUNCATE t1;
|
|
DELETE FROM t2;
|
|
INSERT INTO t1 VALUES (1,'init');
|
|
BEGIN;
|
|
UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
|
|
affected rows: 1
|
|
info: Rows matched: 1 Changed: 1 Warnings: 0
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 init+con1
|
|
connection con2;
|
|
CALL p1;;
|
|
connection con1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 init+con1
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 init+con1
|
|
connection con2;
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 init+con1
|
|
connection default;
|
|
disconnect con1;
|
|
disconnect con2;
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1, t2;
|