--source include/have_innodb.inc
--source include/count_sessions.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc

--disable_query_log
call mtr.add_suppression("InnoDB: Transaction was aborted due to ");
--enable_query_log

--connect disable_purging,localhost,root
START TRANSACTION WITH CONSISTENT SNAPSHOT;

--connection default
--echo #
--echo # MDEV-26642 Weird SELECT view when a record is
--echo #            modified to the same value by two transactions
--echo # MDEV-32898 Phantom rows caused by updates of PRIMARY KEY
--echo #

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;
--connect con_weird,localhost,root
SET innodb_snapshot_isolation=OFF;
BEGIN;
SELECT * FROM t;
--connect consistent,localhost,root
SET innodb_snapshot_isolation=ON;
BEGIN;
SELECT * FROM t;
--connection default
UPDATE t SET a=3 WHERE b=2;
COMMIT;
--connection consistent
--error ER_CHECKREAD
UPDATE t SET b=3;
SELECT * FROM t;
COMMIT;
--connection con_weird
UPDATE t SET b=3;
SELECT * FROM t;
COMMIT;
--connection default
SELECT * FROM t;
DROP TABLE t;

--echo #
--echo # MDEV-26643 Inconsistent behaviors of UPDATE under
--echo #            READ UNCOMMITTED and READ COMMITTED isolation level
--echo #

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";
--send UPDATE t SET b = 20 WHERE a

--connection default
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
COMMIT;

--connection consistent
--reap
SELECT * FROM t;

--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";
--send UPDATE t SET b = 20 WHERE a

--connection default
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
COMMIT;

--connection consistent
--reap
SELECT * FROM t;

--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";
send UPDATE t SET b = 20 WHERE a;

--connection default
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
SELECT * FROM t;
COMMIT;

--connection con_weird
--reap
COMMIT;

--connection default
SELECT * FROM t;
TRUNCATE TABLE t;

--echo #
--echo # MDEV-34108 Inappropriate semi-consistent read in snapshot isolation
--echo #
INSERT INTO t VALUES(NULL, 1), (1, 1);
BEGIN;
UPDATE t SET b = 3;

--connection consistent
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
# As semi-consistent read is disabled for innodb_snapshot_isolation=ON, the
# following UPDATE must be blocked on the first record.
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
--send 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
# If the bug wouldn't be fixed, the result would be (1,3),(1,2), because
# "UPDATE t SET b = 2 WHERE a" would be blocked on the second (1,3) record,
# as semi-consistent read would filter out the first (null,3) record without
# blocking.
--reap
COMMIT;

--connection default
SELECT * FROM t;
DROP TABLE t;

--echo #
--echo # MDEV-33802 Weird read view after ROLLBACK of other transactions
--echo #

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;
--disable_ps2_protocol
--error ER_CHECKREAD
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
--enable_ps2_protocol
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";
--send SELECT * FROM t FORCE INDEX (b) FOR UPDATE

--connection default
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
ROLLBACK;

--connection con_weird
--reap
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
COMMIT;

--connection consistent
SELECT * FROM t FORCE INDEX (b) FOR UPDATE;
COMMIT;

--connection default
TRUNCATE TABLE t;

--echo #
--echo # MDEV-36639 innodb_snapshot_isolation=1 gives error for not comitted row changes
--echo #
INSERT INTO t VALUES (1,1),(2,2);

--connection default
--echo # Case 1: Transaction A modifies a record, transaction B with snapshot
--echo # isolation level is blocked by A, then A is committed.
--echo # 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;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
--send SELECT * FROM t WHERE a=1 FOR UPDATE

--connection default
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
COMMIT;

--connection consistent
--error ER_CHECKREAD
--reap

--echo # Case 2: Transaction A modifies a record, transaction B with snapshot
--echo # isolation level is blocked by A, then A is rolled back.
--echo # Expected behaviour: B continues execution.

--connection default
BEGIN;
UPDATE t SET b=4 WHERE a=1;

--connection consistent
BEGIN;
SELECT * FROM t;
SET DEBUG_SYNC="lock_wait_before_suspend SIGNAL select_blocked";
--send SELECT * FROM t WHERE a=1 FOR UPDATE

--connection default
SET DEBUG_SYNC="now WAIT_FOR select_blocked";
ROLLBACK;

--connection consistent
--reap
ROLLBACK;

--echo # Case 3: Transaction B with snapshot isolation level started with
--echo # consistent snapshot. Transaction A modifies a record and is committed.
--echo # Both B tries to read modified by A record.
--echo # 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
--error ER_CHECKREAD
SELECT * FROM t WHERE a=1 FOR UPDATE;
--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;
COMMIT;
--connection consistent
--disable_ps2_protocol
--error ER_CHECKREAD
SELECT * FROM t1;
--enable_ps2_protocol
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;
COMMIT;
--disconnect con_weird
--connection consistent
--disable_ps2_protocol
--error ER_CHECKREAD
SELECT * FROM t1;
--enable_ps2_protocol
COMMIT;
--disconnect consistent
--connection default
DROP TABLE t1,t2;

--echo #
--echo # MDEV-37215 SELECT...FOR UPDATE crash under SERIALIZABLE
--echo #

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB;
SELECT * FROM t1 FOR UPDATE;
DROP TABLE t1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

--source include/wait_until_count_sessions.inc
--echo # End of 10.6 tests