1
DROP TABLE IF EXISTS t1, t2;
5
CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=NDB;
6
INSERT INTO t1 VALUES (1,123,1,123);
7
INSERT INTO t1 VALUES (2,124,2,124);
8
INSERT INTO t1 VALUES (3,125,3,125);
9
INSERT INTO t1 VALUES (4,126,4,126);
10
CREATE INDEX ixi ON t1 (i);
11
CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=NDB;
12
INSERT INTO t2 VALUES (1,123,1,123);
13
INSERT INTO t2 VALUES (2,124,2,124);
14
INSERT INTO t2 VALUES (3,125,3,125);
15
INSERT INTO t2 VALUES (4,126,4,126);
16
CREATE INDEX ixi ON t2 (i);
18
SELECT @@global.tx_isolation;
21
EXPLAIN SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i<125 AND t2.i=t1.i FOR UPDATE;
22
id select_type table type possible_keys key key_len ref rows Extra
23
1 SIMPLE t1 range ixi ixi 5 NULL 10 Using where
24
1 SIMPLE t2 ref ixi ixi 5 test.t1.i 1 Using where
25
SELECT t1.i,t2.i FROM t1,t2 WHERE t1.i<125 AND t2.i=t1.i FOR UPDATE;
30
UPDATE t1,t2 SET t1.i=225,t2.i=225 WHERE t1.i=125 AND t2.i=t1.i;
31
SELECT * FROM t1 ORDER BY t1.k;
37
SELECT * FROM t2 ORDER BY t2.k;
44
UPDATE t1,t2 SET t1.i=223,t2.i=223 WHERE t1.i=123 AND t2.i=t1.i;
45
SELECT * FROM t1 ORDER BY t1.k;
51
SELECT * FROM t2 ORDER BY t2.k;
58
UPDATE t1,t2 SET t1.i=226,t2.i=226 WHERE t1.i=126 AND t2.i=t1.i;
59
SELECT * FROM t1 ORDER BY t1.k;
65
SELECT * FROM t2 ORDER BY t2.k;
72
UPDATE t1,t2 SET t1.i=224,t2.i=224 WHERE t1.i=124 AND t2.i=t1.i;
73
SELECT * FROM t1 ORDER BY t1.k;
79
SELECT * FROM t2 ORDER BY t2.k;
86
DELETE FROM t1 WHERE t1.i=226;
87
SELECT * FROM t1 ORDER BY t1.k;
92
SELECT * FROM t2 ORDER BY t2.k;
99
DELETE FROM t1 WHERE t1.i=224;
100
SELECT * FROM t1 ORDER BY t1.k;
105
SELECT * FROM t2 ORDER BY t2.k;
115
SELECT * FROM t1 ORDER BY t1.k;
120
SELECT * FROM t2 ORDER BY t2.k;
127
SELECT * FROM t1 ORDER BY t1.k;
132
SELECT * FROM t2 ORDER BY t2.k;