Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_parallel_locking.inc

raw
1 --echo #
2 --echo # Check consistency of delete-ref-check
3 --echo #
4
5 --echo # Create procedure to transactionally move a child->parent
6 --echo # ref between several child rows.
7 --echo # At all times there is a reference, but the reference
8 --echo # is from rows with different pks at different times.
9 --echo # The transaction to move the ref to a different child row
10 --echo # might some time get a Lock wait timeout error if the other
11 --echo # procedure's transaction has acquired the SimpleRead lock
12 --echo # for the same parent row at the same time.
13 --echo # We simply ignore those errors and move on.
14 --echo # At no time should it be possible to delete the parent row.
15 --echo #
16
17 delimiter %;
18
19 create procedure updateref(iterations int)
20 ur:begin
21 declare continue handler for 1205 SET @dummy__ = 0;
22 set @x = 1;
23 repeat
24 start transaction;
25 delete from child;
26 insert into child values (@x, 42);
27 commit;
28 set @x=@x+1;
29 until @x = iterations
30 end repeat;
31 end%
32
33 delimiter ;%
34
35 --echo #
36 --echo # Create procedure to continually attempt to delete
37 --echo # a parent row. This should always fail due to child
38 --echo # references. The error return codes would vary between
39 --echo # 1205(Lock wait time out) and 1451(child row exists)
40 --echo # depending on the time at which the delete is trying
41 --echo # acquire the child row lock w.r.to the other procedure.
42 --echo #
43
44 drop procedure if exists removeparent;
45
46 delimiter %;
47
48 create procedure removeparent(iterations int)
49 rp:begin
50 declare continue handler for 1205, 1451 SET @err = @err+1;
51 set @x = 0;
52 set @err = 0;
53 repeat
54 delete from parent where id=42;
55 set @x=@x+1;
56 if @x != @err then
57 leave rp;
58 end if;
59 until @x = iterations
60 end repeat;
61 end%
62
63 delimiter ;%
64
65 --echo # Setting up initial values
66 insert into parent values (42);
67 insert into child values (0,42);
68
69 --echo # Sanity check, delete must fail
70 --error ER_ROW_IS_REFERENCED_2
71 delete from parent where id=42;
72
73 connection con1;
74 --echo # Start procedure moving child reference around
75 --send call updateref(100);
76
77 connection con2;
78 --echo # Start procedure attempting to delete parent
79 --send call removeparent(100);
80
81 --echo # Wait for parent delete to finish,
82 --echo # FK error is masked by the SP
83 --reap
84
85 --echo # Now let the child ref transfers complete.
86 connection con1;
87 --reap
88
89 --echo # Verify that the parent row is intact.
90 select * from parent;
91
92 --echo # Verify that we can add a new child row ref.
93 insert into child values (100,42);
94
95 --echo # Drop the procedures and delete the rows from tables
96 drop procedure updateref;
97 drop procedure removeparent;
98 delete from child;
99 delete from parent;