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 MOD(k,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 MOD(k,2) INTO res FROM t2;
47
SELECT @@global.tx_isolation;
50
EXPLAIN SELECT t1.i,t2.i FROM t1,t2 WHERE t1.k % 2= 0 AND t1.k = t2.k LOCK IN SHARE MODE;
51
id select_type table type possible_keys key key_len ref rows Extra
52
1 SIMPLE t1 index PRIMARY ixi 5 NULL 40 Using where; Using index
53
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.k 1
54
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.k % 2= 0 AND t1.k = t2.k LOCK IN SHARE MODE;
77
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.k % 2= 1 AND t1.k = t2.k LOCK IN SHARE MODE;
99
UPDATE t1,t2 SET t1.i=1111,t2.i=2222 WHERE t1.k % 2 = 1 AND t1.k = t2.k;
100
SELECT * FROM t1 WHERE k < 20 ORDER BY t1.k;
122
SELECT * FROM t2 WHERE k < 20 ORDER BY t2.k;
145
UPDATE t1,t2 SET t1.i=3333,t2.i=4444 WHERE t1.k % 2 = 0 AND t1.k = t2.k;