connect disable_purging,localhost,root;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
#
# MDEV-26642 Weird SELECT view when a record is
#            modified to the same value by two transactions
# MDEV-32898 Phantom rows caused by updates of PRIMARY KEY
#
CREATE TABLE t(a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
INSERT INTO t VALUES (1,1),(2,2);
BEGIN;
SELECT * FROM t LOCK IN SHARE MODE;
a	b
1	1
2	2
connect con_weird,localhost,root;
SET innodb_snapshot_isolation=OFF;
BEGIN;
SELECT * FROM t;
a	b
1	1
2	2
connect consistent,localhost,root;
SET innodb_snapshot_isolation=ON;
BEGIN;
SELECT * FROM t;
a	b
1	1
2	2
connection default;
UPDATE t SET a=3 WHERE b=2;
COMMIT;
connection consistent;
UPDATE t SET b=3;
ERROR HY000: Record has changed since last read in table 't'
SELECT * FROM t;
a	b
1	1
3	2
COMMIT;
connection con_weird;
UPDATE t SET b=3;
SELECT * FROM t;
a	b
1	3
2	2
3	3
COMMIT;
connection default;
SELECT * FROM t;
a	b
1	3
3	3
DROP TABLE t;
#
# MDEV-26643 Inconsistent behaviors of UPDATE under
#            READ UNCOMMITTED and READ COMMITTED isolation level
#
CREATE TABLE t(a INT, b INT) ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO t VALUES(NULL, 1), (2, 2);
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
UPDATE t SET a = 10;
connection consistent;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
UPDATE t SET b = 20 WHERE a;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
COMMIT;
connection consistent;
SELECT * FROM t;
a	b
10	20
10	20
connection default;
TRUNCATE TABLE t;
INSERT INTO t VALUES(NULL, 1), (2, 2);
BEGIN;
UPDATE t SET a = 10;
connection consistent;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
UPDATE t SET b = 20 WHERE a;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
COMMIT;
connection consistent;
SELECT * FROM t;
a	b
10	20
10	20
connection default;
TRUNCATE TABLE t;
INSERT INTO t VALUES(NULL, 1), (2, 2);
BEGIN;
UPDATE t SET a = 10;
connection con_weird;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
UPDATE t SET b = 20 WHERE a;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
SELECT * FROM t;
a	b
10	1
10	2
COMMIT;
connection con_weird;
COMMIT;
connection default;
SELECT * FROM t;
a	b
10	1
10	20
TRUNCATE TABLE t;
#
# MDEV-34108 Inappropriate semi-consistent read in snapshot isolation
#
INSERT INTO t VALUES(NULL, 1), (1, 1);
BEGIN;
UPDATE t SET b = 3;
connection consistent;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
UPDATE t SET b = 2 WHERE a;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
UPDATE t SET a = 1;
COMMIT;
connection consistent;
COMMIT;
connection default;
SELECT * FROM t;
a	b
1	2
1	2
DROP TABLE t;
#
# MDEV-33802 Weird read view after ROLLBACK of other transactions
#
CREATE TABLE t(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB STATS_PERSISTENT=0;
connection consistent;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
INSERT INTO t SET a=1;
connection consistent;
SAVEPOINT sp1;
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
ERROR HY000: Record has changed since last read in table 't'
SAVEPOINT sp1;
connection default;
BEGIN;
INSERT INTO t SET a=2;
connection con_weird;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
ROLLBACK;
connection con_weird;
a	b
1	NULL
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
a	b
1	NULL
COMMIT;
connection consistent;
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
a	b
1	NULL
COMMIT;
connection default;
TRUNCATE TABLE t;
#
# MDEV-36639 innodb_snapshot_isolation=1 gives error for not comitted row changes
#
INSERT INTO t VALUES (1,1),(2,2);
connection default;
# Case 1: Transaction A modifies a record, transaction B with snapshot
# isolation level is blocked by A, then A is committed.
# Expected behaviour: B gets ER_CHECKREAD.
BEGIN;
UPDATE t SET b=3 WHERE a = 1;
connection consistent;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM t;
a	b
1	1
2	2
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
SELECT * FROM t WHERE a=1 FOR UPDATE;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
COMMIT;
connection consistent;
ERROR HY000: Record has changed since last read in table 't'
# Case 2: Transaction A modifies a record, transaction B with snapshot
# isolation level is blocked by A, then A is rolled back.
# Expected behaviour: B continues execution.
connection default;
BEGIN;
UPDATE t SET b=4 WHERE a=1;
connection consistent;
BEGIN;
SELECT * FROM t;
a	b
2	2
1	3
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
SELECT * FROM t WHERE a=1 FOR UPDATE;
connection default;
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
ROLLBACK;
connection consistent;
a	b
1	3
ROLLBACK;
# Case 3: Transaction B with snapshot isolation level started with
# consistent snapshot. Transaction A modifies a record and is committed.
# Both B tries to read modified by A record.
# Expected behavior: B gets ER_CHECKREAD.
connection consistent;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connection default;
UPDATE t SET b=4 WHERE a=1;
connection consistent;
SELECT * FROM t WHERE a=1 FOR UPDATE;
ERROR HY000: Record has changed since last read in table 't'
disconnect disable_purging;
connection default;
SET DEBUG_SYNC="RESET";
DROP TABLE t;
CREATE TABLE t1(a INT) ENGINE=InnoDB STATS_PERSISTENT=0;
CREATE TABLE t2(a INT) ENGINE=InnoDB STATS_PERSISTENT=0;
BEGIN;
INSERT INTO t1 SET a=1;
connection con_weird;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
INSERT INTO t2 SET a=1;
connection consistent;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
INSERT INTO t2 SET a=2;
connection default;
COMMIT;
connection con_weird;
SELECT * FROM t1;
a
1
COMMIT;
connection consistent;
SELECT * FROM t1;
ERROR HY000: Record has changed since last read in table 't1'
COMMIT;
connection default;
BEGIN;
INSERT INTO t1 SET a=2;
connection con_weird;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
INSERT INTO t2 SET a=3;
connection consistent;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
INSERT INTO t2 SET a=2;
connection default;
COMMIT;
connection con_weird;
SELECT * FROM t1;
a
1
2
COMMIT;
disconnect con_weird;
connection consistent;
SELECT * FROM t1;
ERROR HY000: Record has changed since last read in table 't1'
COMMIT;
disconnect consistent;
connection default;
DROP TABLE t1,t2;
#
# MDEV-37215 SELECT...FOR UPDATE crash under SERIALIZABLE
#
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
SELECT * FROM t1 FOR UPDATE;
a
DROP TABLE t1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# End of 10.6 tests