1
DROP TABLE IF EXISTS t1, t2;
2
DROP VIEW IF EXISTS v1;
6
CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=NDB;
7
INSERT INTO t1 VALUES (1,123,1,123);
8
INSERT INTO t1 VALUES (2,124,2,124);
9
INSERT INTO t1 VALUES (3,125,3,125);
10
INSERT INTO t1 VALUES (4,126,4,126);
11
CREATE INDEX ixi ON t1 (i);
12
CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=NDB;
13
INSERT INTO t2 VALUES (1,123,1,123);
14
INSERT INTO t2 VALUES (2,124,2,124);
15
INSERT INTO t2 VALUES (3,125,3,125);
16
INSERT INTO t2 VALUES (4,126,4,126);
17
CREATE INDEX ixi ON t2 (i);
18
CREATE TRIGGER trig_t2 AFTER UPDATE ON t2
20
UPDATE t1 SET l = NEW.i WHERE i = OLD.i;
24
SELECT @@global.tx_isolation;
27
EXPLAIN SELECT t1.i FROM t1 WHERE t1.i< 125 FOR UPDATE;
28
id select_type table type possible_keys key key_len ref rows Extra
29
1 SIMPLE t1 range ixi ixi 5 NULL 10 Using where
30
SELECT t1.i FROM t1 WHERE t1.i< 125 FOR UPDATE;
35
UPDATE t2 SET t2.i=225 WHERE t2.i=125;
36
SELECT * FROM t1 ORDER BY t1.k;
42
SELECT * FROM t2 ORDER BY t2.k;
49
UPDATE t2 SET t2.i=223 WHERE t2.i=123;
50
SELECT * FROM t1 ORDER BY t1.k;
56
SELECT * FROM t2 ORDER BY t2.k;
63
UPDATE t2 SET t2.i=226 WHERE t2.i=126;
64
SELECT * FROM t1 ORDER BY t1.k;
70
SELECT * FROM t2 ORDER BY t2.k;
77
UPDATE t2 SET t2.i=224 WHERE t2.i=124;
78
SELECT * FROM t1 ORDER BY t1.k;
84
SELECT * FROM t2 ORDER BY t2.k;
91
DELETE FROM t1 WHERE t1.i=226;
92
SELECT * FROM t1 ORDER BY t1.k;
98
SELECT * FROM t2 ORDER BY t2.k;
105
DELETE FROM t1 WHERE t1.i=224;
106
SELECT * FROM t1 ORDER BY t1.k;
112
SELECT * FROM t2 ORDER BY t2.k;
122
SELECT * FROM t1 ORDER BY t1.k;
128
SELECT * FROM t2 ORDER BY t2.k;
135
SELECT * FROM t1 ORDER BY t1.k;
141
SELECT * FROM t2 ORDER BY t2.k;