1
SELECT @@global.innodb_table_locks into @table_locks;
2
SET @@global.innodb_table_locks= OFF;
3
DROP TABLE IF EXISTS t1, t2;
4
DROP VIEW IF EXISTS v1;
8
CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=InnoDB;
9
CREATE INDEX ixi ON t1 (i);
10
CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=InnoDB;
11
CREATE INDEX ixi ON t2 (i);
12
CREATE PROCEDURE fill_t1 (IN upb int)
14
DECLARE cnt int DEFAULT 0;
16
INSERT INTO t1 VALUES (cnt, cnt+100, cnt, cnt+100);
21
CREATE FUNCTION half_t1() RETURNS int
23
DECLARE res int DEFAULT 0;
24
SELECT count(*)/2 INTO res FROM t1;
28
CREATE PROCEDURE fill_t2 (IN upb int)
30
DECLARE cnt int DEFAULT 0;
32
INSERT INTO t2 VALUES (cnt, cnt+100, cnt, cnt+100);
37
CREATE FUNCTION half_t2() RETURNS int
39
DECLARE res int DEFAULT 0;
40
SELECT count(*)/2 INTO res FROM t2;
47
SELECT @@global.tx_isolation;
50
EXPLAIN SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i < half_t1() AND t2.i=t1.i LOCK IN SHARE MODE;
51
id select_type table type possible_keys key key_len ref rows Extra
52
1 SIMPLE t1 index ixi ixi 5 NULL 10 Using where; Using index
53
1 SIMPLE t2 ref ixi ixi 5 test.t1.i 1 Using where; Using index
54
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i < half_t1() AND t2.i=t1.i LOCK IN SHARE MODE;
57
SELECT k from t1 WHERE k < half_t1();
64
SELECT k from t1 WHERE k >= half_t1();
71
UPDATE t1,t2 SET t1.i=1111,t2.i=2222 WHERE t1.k < half_t1() AND t2.i=t1.i;
72
SELECT * FROM t1 ORDER BY t1.k;
84
SELECT * FROM t2 ORDER BY t2.k;
97
UPDATE t1,t2 SET t1.i=3333,t2.i=4444 WHERE t1.k >= half_t1() AND t2.i=t1.i;