Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_locking.result

raw
1 CREATE TABLE parent (
2 id INT NOT NULL AUTO_INCREMENT,
3 bogus_column char(32),
4 PRIMARY KEY (id)
5 ) ENGINE=ndb;
6 CREATE TABLE child (
7 id INT NOT NULL AUTO_INCREMENT,
8 parent_id INT NOT NULL,
9 bogus_column char(32),
10 PRIMARY KEY (id),
11 KEY (parent_id)
12 ) ENGINE=ndb;
13 alter table child add constraint fkname foreign key (parent_id) references parent (id) on update restrict on delete restrict;
14 INSERT INTO parent (bogus_column)
15 VALUES ('aaa'), ('bbb'), ('ccc'), ('ddd'), ('eee');
16 INSERT INTO child (parent_id,bogus_column) VALUES
17 (1, 'aaa'), (2,'bbb'), (3, 'ccc'),
18 (4, 'ddd'), (5, 'eee');
19 START TRANSACTION;
20 START TRANSACTION;
21 UPDATE child SET parent_id = 5 WHERE parent_id = 4;
22 UPDATE parent SET bogus_column = 'new!' WHERE id = 4;
23 UPDATE parent SET bogus_column = 'new!' WHERE id = 5;
24 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
25 rollback;
26 rollback;
27 alter table child drop foreign key fkname;
28 alter table child add constraint fkname foreign key (parent_id) references parent (id);
29 START TRANSACTION;
30 START TRANSACTION;
31 UPDATE child SET parent_id = 5 WHERE parent_id = 4;
32 UPDATE parent SET bogus_column = 'new!' WHERE id = 4;
33 UPDATE parent SET bogus_column = 'new!' WHERE id = 5;
34 # Commit now tries to acquire a SimpleRead lock on the parent table row
35 # to verify the FK constraint. But since T2 already has the exclusive
36 # lock on the same parent table row, this commit should fail.
37 commit;
38 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
39 # Now T2's commit should be able to get locks on child table row
40 # as T1 has ended and locks on child table have been released.
41 commit;
42 drop table child, parent;
43 CREATE TABLE t1 (
44 id int(11) NOT NULL auto_increment,
45 ref_id int(11) default NULL,
46 PRIMARY KEY (id),
47 KEY ref_id (ref_id),
48 CONSTRAINT ref_id FOREIGN KEY (ref_id) REFERENCES t1 (id)
49 ) ENGINE=ndb;
50 Warnings:
51 Warning 1681 Integer display width is deprecated and will be removed in a future release.
52 Warning 1681 Integer display width is deprecated and will be removed in a future release.
53 INSERT INTO t1 VALUES (1, 1);
54 DELETE FROM t1 WHERE id = 1;
55 drop table t1;
56 #
57 # Bug#22180583 - Incorrect locking of FK Child table scan check.
58 #
59 # Create parent and child tables with fks
60 create table parent(
61 id int primary key
62 )engine = ndb;
63 create table child(
64 id int primary key auto_increment,
65 ref_oi int,
66 ref_uk int unique key,
67 foreign key fk1(ref_oi) references parent(id),
68 foreign key fk2(ref_uk) references parent(id)
69 )engine = ndb;
70 insert into parent values (1);
71 Test 1: Test FK locking with ordered index on child column
72 # Transaction T1 inserts child row
73 start transaction;
74 insert into child(ref_oi) values(1);
75 # Delete parent from parallel transaction T2.
76 # This should fail because T2 tries to take a SimpleRead on child row.
77 # T2 cannot acquire lock because T1 has not yet unlocked child row.
78 delete from parent where id = 1;
79 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
80 commit;
81 # Show that FK reference intact
82 select * from parent;
83 id
84 1
85 select * from child;
86 id ref_oi ref_uk
87 1 1 NULL
88 delete from child;
89 Test 2: Test FK locking with unique index on child column
90 # Transaction T1 inserts child row with delayed commit (error insert 5012)
91 start transaction;
92 insert into child(ref_uk) values(1);
93 # Delete parent from parallel transaction T2.
94 delete from parent where id = 1;
95 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
96 commit;
97 # Show that FK reference intact
98 select * from parent;
99 id
100 1
101 select * from child;
102 id ref_oi ref_uk
103 2 NULL 1
104 drop table child, parent;