Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_locking.test

raw
1 -- source include/have_ndb.inc
2
3 connect (con1,localhost,root,,test);
4 connect (con2,localhost,root,,test);
5
6 #
7 # found in http://www.mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/
8 #
9 CREATE TABLE parent (
10 id INT NOT NULL AUTO_INCREMENT,
11 bogus_column char(32),
12 PRIMARY KEY (id)
13 ) ENGINE=ndb;
14
15 CREATE TABLE child (
16 id INT NOT NULL AUTO_INCREMENT,
17 parent_id INT NOT NULL,
18 bogus_column char(32),
19 PRIMARY KEY (id),
20 KEY (parent_id)
21 ) ENGINE=ndb;
22
23 alter table child add constraint fkname foreign key (parent_id) references parent (id) on update restrict on delete restrict;
24
25 INSERT INTO parent (bogus_column)
26 VALUES ('aaa'), ('bbb'), ('ccc'), ('ddd'), ('eee');
27
28 INSERT INTO child (parent_id,bogus_column) VALUES
29 (1, 'aaa'), (2,'bbb'), (3, 'ccc'),
30 (4, 'ddd'), (5, 'eee');
31
32 connection con1;
33 START TRANSACTION; # session1
34
35 connection con2;
36 START TRANSACTION; # session2
37
38 # session1
39 connection con1;
40 UPDATE child SET parent_id = 5 WHERE parent_id = 4;
41
42 #session2
43 connection con2;
44 UPDATE parent SET bogus_column = 'new!' WHERE id = 4;
45
46 #session2
47 connection con2;
48 --error ER_LOCK_WAIT_TIMEOUT
49 UPDATE parent SET bogus_column = 'new!' WHERE id = 5;
50
51 connection con1;
52 rollback;
53
54 connection con2;
55 rollback;
56
57 alter table child drop foreign key fkname;
58
59 #
60 # But if you use noaction instead of restrict
61 # the fk constraints are checked only during the commit phase.
62 #
63 alter table child add constraint fkname foreign key (parent_id) references parent (id);
64
65 connection con1;
66 START TRANSACTION; # session1 - transaction T1
67
68 connection con2;
69 START TRANSACTION; # session2 - transaction T2
70
71 connection con1;
72 UPDATE child SET parent_id = 5 WHERE parent_id = 4;
73
74 connection con2;
75 UPDATE parent SET bogus_column = 'new!' WHERE id = 4;
76
77 connection con2;
78 UPDATE parent SET bogus_column = 'new!' WHERE id = 5;
79
80 connection con1;
81 --echo # Commit now tries to acquire a SimpleRead lock on the parent table row
82 --echo # to verify the FK constraint. But since T2 already has the exclusive
83 --echo # lock on the same parent table row, this commit should fail.
84 --error ER_LOCK_WAIT_TIMEOUT
85 commit;
86
87 connection con2;
88 --echo # Now T2's commit should be able to get locks on child table row
89 --echo # as T1 has ended and locks on child table have been released.
90 commit;
91
92 drop table child, parent;
93
94 #
95 # bug#7412
96 #
97 CREATE TABLE t1 (
98 id int(11) NOT NULL auto_increment,
99 ref_id int(11) default NULL,
100 PRIMARY KEY (id),
101 KEY ref_id (ref_id),
102 CONSTRAINT ref_id FOREIGN KEY (ref_id) REFERENCES t1 (id)
103 ) ENGINE=ndb;
104
105 INSERT INTO t1 VALUES (1, 1);
106 DELETE FROM t1 WHERE id = 1;
107 drop table t1;
108
109
110 --echo #
111 --echo # Bug#22180583 - Incorrect locking of FK Child table scan check.
112 --echo #
113
114 --echo # Create parent and child tables with fks
115 create table parent(
116 id int primary key
117 )engine = ndb;
118
119 create table child(
120 id int primary key auto_increment,
121 ref_oi int,
122 ref_uk int unique key,
123 foreign key fk1(ref_oi) references parent(id),
124 foreign key fk2(ref_uk) references parent(id)
125 )engine = ndb;
126
127 insert into parent values (1);
128
129 --echo Test 1: Test FK locking with ordered index on child column
130
131 --echo # Transaction T1 inserts child row
132 connection con1;
133 start transaction;
134 insert into child(ref_oi) values(1);
135
136 --echo # Delete parent from parallel transaction T2.
137 --echo # This should fail because T2 tries to take a SimpleRead on child row.
138 --echo # T2 cannot acquire lock because T1 has not yet unlocked child row.
139 connection con2;
140 --error 1205
141 delete from parent where id = 1;
142 connection con1;
143 commit;
144
145 --echo # Show that FK reference intact
146 select * from parent;
147 select * from child;
148
149 delete from child;
150
151 --echo Test 2: Test FK locking with unique index on child column
152
153 --echo # Transaction T1 inserts child row with delayed commit (error insert 5012)
154 start transaction;
155 insert into child(ref_uk) values(1);
156
157 --echo # Delete parent from parallel transaction T2.
158 connection con2;
159 --error 1205
160 delete from parent where id = 1;
161 connection con1;
162 commit;
163
164 --echo # Show that FK reference intact
165 select * from parent;
166 select * from child;
167
168 drop table child, parent;
169
170 #--echo # Test takes too long, so commented out
171 #--echo # Test 3: Run INSERT(child)/DELETE(parent) load
172 #--echo #
173 #
174 #--echo # Check for orphaned child rows with concurrent queries
175 #--echo # Run multiple concurrent INSERT(child) + DELETE(parent)
176 #--echo # to interleave parent deletion and child insertion ops
177 #
178 #--echo # Create parent and child tables
179 #create table parent (
180 # id int primary key
181 #) engine=ndb;
182 #
183 #create table child (
184 # id int primary key,
185 # ref int
186 #) engine=ndb;
187 #
188 #--echo # Create FK on ref referencing parent's id.
189 #alter table child add constraint fkref foreign key (ref) references parent (id) on update restrict on delete restrict;
190 #
191 #--source suite/ndb/t/ndb_fk_parallel_locking.inc
192 #
193 #--echo # Now create a key on the child column and repeat the tests
194 #create index ordered_idx on child(ref);
195 #delete from child;
196 #delete from parent;
197 #--source suite/ndb/t/ndb_fk_parallel_locking.inc
198 #
199 #create unique index unique_idx on child(ref);
200 #delete from child;
201 #delete from parent;
202 #--source suite/ndb/t/ndb_fk_parallel_locking.inc
203 #
204 #--echo # Cleanup tables
205 #drop table child;
206 #drop table parent;