49 lines
999 B
Plaintext
49 lines
999 B
Plaintext
|
# connection default
|
||
|
|
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
||
|
|
CREATE TABLE t1
|
||
|
|
(
|
||
|
|
id SMALLINT NOT NULL,
|
||
|
|
PRIMARY KEY (id)
|
||
|
|
) ENGINE=innodb
|
||
|
|
PARTITION BY RANGE (id)
|
||
|
|
(
|
||
|
|
PARTITION p1 VALUES LESS THAN (2),
|
||
|
|
PARTITION p2 VALUES LESS THAN (4),
|
||
|
|
PARTITION p3 VALUES LESS THAN (10)
|
||
|
|
);
|
||
|
|
INSERT INTO t1 VALUES (1),(2),(3);
|
||
|
|
# Test READ COMMITTED -> REPEATABLE READ
|
||
|
|
FLUSH TABLES;
|
||
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
||
|
|
BEGIN;
|
||
|
|
SELECT * FROM t1;
|
||
|
|
id
|
||
|
|
1
|
||
|
|
2
|
||
|
|
3
|
||
|
|
#connection con1
|
||
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
||
|
|
BEGIN;
|
||
|
|
INSERT INTO t1 VALUES(7);
|
||
|
|
COMMIT;
|
||
|
|
# connection default
|
||
|
|
COMMIT;
|
||
|
|
FLUSH TABLES;
|
||
|
|
# Test REPEATABLE READ -> READ COMMITTED
|
||
|
|
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
|
||
|
|
BEGIN;
|
||
|
|
SELECT * FROM t1;
|
||
|
|
id
|
||
|
|
1
|
||
|
|
2
|
||
|
|
3
|
||
|
|
7
|
||
|
|
# connection con1
|
||
|
|
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
|
||
|
|
BEGIN;
|
||
|
|
INSERT INTO t1 VALUES(9);
|
||
|
|
ERROR HY000: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
|
||
|
|
COMMIT;
|
||
|
|
COMMIT;
|
||
|
|
DROP TABLE t1;
|