1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v1;
SET autocommit=0;
SET autocommit=0;
connection default;
CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=NDB;
INSERT INTO t1 VALUES (1,123,1,123);
INSERT INTO t1 VALUES (2,124,2,124);
INSERT INTO t1 VALUES (3,125,3,125);
INSERT INTO t1 VALUES (4,126,4,126);
CREATE INDEX ixi ON t1 (i);
CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=NDB;
INSERT INTO t2 VALUES (1,123,1,123);
INSERT INTO t2 VALUES (2,124,2,124);
INSERT INTO t2 VALUES (3,125,3,125);
INSERT INTO t2 VALUES (4,126,4,126);
CREATE INDEX ixi ON t2 (i);
CREATE TRIGGER trig_t2 AFTER UPDATE ON t2
FOR EACH ROW BEGIN
UPDATE t1 SET l = NEW.i WHERE i = OLD.i;
END;
|
COMMIT;
SELECT @@global.tx_isolation;
@@global.tx_isolation
REPEATABLE-READ
EXPLAIN SELECT t1.i FROM t1 WHERE t1.i< 125 FOR UPDATE;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range ixi ixi 5 NULL 10 Using where
SELECT t1.i FROM t1 WHERE t1.i< 125 FOR UPDATE;
i
123
124
connection root1;
UPDATE t2 SET t2.i=225 WHERE t2.i=125;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 123
2 124 2 124
3 125 3 225
4 126 4 126
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 123 1 123
2 124 2 124
3 225 3 125
4 126 4 126
connection default;
UPDATE t2 SET t2.i=223 WHERE t2.i=123;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 223
2 124 2 124
3 125 3 125
4 126 4 126
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 223 1 123
2 124 2 124
3 125 3 125
4 126 4 126
connection root1;
UPDATE t2 SET t2.i=226 WHERE t2.i=126;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 123
2 124 2 124
3 125 3 225
4 126 4 226
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 123 1 123
2 124 2 124
3 225 3 125
4 226 4 126
connection default;
UPDATE t2 SET t2.i=224 WHERE t2.i=124;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 223
2 124 2 224
3 125 3 125
4 126 4 126
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 223 1 123
2 224 2 124
3 125 3 125
4 126 4 126
connection root1;
DELETE FROM t1 WHERE t1.i=226;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 123
2 124 2 124
3 125 3 225
4 126 4 226
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 123 1 123
2 124 2 124
3 225 3 125
4 226 4 126
connection default;
DELETE FROM t1 WHERE t1.i=224;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 223
2 124 2 224
3 125 3 125
4 126 4 126
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 223 1 123
2 224 2 124
3 125 3 125
4 126 4 126
COMMIT;
connection root1;
ROLLBACK;
connection default;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 223
2 124 2 224
3 125 3 125
4 126 4 126
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 223 1 123
2 224 2 124
3 125 3 125
4 126 4 126
connection root1;
SELECT * FROM t1 ORDER BY t1.k;
k i j l
1 123 1 223
2 124 2 224
3 125 3 125
4 126 4 126
SELECT * FROM t2 ORDER BY t2.k;
k i j l
1 223 1 123
2 224 2 124
3 125 3 125
4 126 4 126
connection default;
DROP TABLE t1, t2;
|
