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
INSERT INTO t1 VALUES (1,123,1,123);
10
INSERT INTO t1 VALUES (2,124,2,124);
11
INSERT INTO t1 VALUES (3,125,3,125);
12
INSERT INTO t1 VALUES (4,126,4,126);
13
CREATE INDEX ixi ON t1 (i);
14
CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=InnoDB;
15
INSERT INTO t2 VALUES (1,123,1,123);
16
INSERT INTO t2 VALUES (2,124,2,124);
17
INSERT INTO t2 VALUES (3,125,3,125);
18
INSERT INTO t2 VALUES (4,126,4,126);
19
CREATE INDEX ixi ON t2 (i);
20
CREATE VIEW v1 AS SELECT t1.i, t2.l from t1,t2;
22
SELECT @@global.tx_isolation;
25
EXPLAIN SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i<125 AND t2.i=t1.i FOR UPDATE;
26
id select_type table type possible_keys key key_len ref rows Extra
27
1 SIMPLE t1 index ixi ixi 5 NULL 4 Using where; Using index
28
1 SIMPLE t2 ref ixi ixi 5 test.t1.i 2 Using where; Using index
29
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i<125 AND t2.i=t1.i FOR UPDATE;
34
UPDATE t1,t2 SET t1.i=225,t2.i=225 WHERE t1.i=125 AND t2.i=t1.i;
35
SELECT * FROM t1 ORDER BY t1.k;
41
SELECT * FROM t2 ORDER BY t2.k;
48
UPDATE v1 SET i=323 where i=123;