Debian Codegraph @

mysql-test/suite/galera/r/MDEV-20616.result

raw
1 connection node_2;
2 connection node_1;
3
4 Test phase 1 to make sure that natral deadlock in trigger SP execution is
5 handled correctly
6
7 CREATE TABLE t1(a INT);
8 CREATE TABLE t2(f1 INT, f2 INT, f3 INT);
9 CREATE PROCEDURE proc()
10 BEGIN
11 INSERT INTO t2 VALUES(100, 200, 300);
12 UPDATE t2 SET f3 = f3 + 100;
13 END|
14 CREATE TRIGGER t1 BEFORE INSERT ON t1 FOR EACH ROW CALL proc();
15 INSERT INTO t1 VALUES(2);;
16 connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1;
17 INSERT INTO t1 VALUES(1);;
18 connection node_1;
19 connection node_1a;
20 connection node_1;
21 wsrep__bf_aborts
22 0
23 DROP TABLE t1;
24 DROP TABLE t2;
25 DROP PROCEDURE proc;
26
27 Test phase 2 to make sure that BF abort for SP execution is
28 handled correctly
29
30 connection node_1;
31 SET SESSION wsrep_retry_autocommit = 0;
32 SET SESSION wsrep_sync_wait = 0;
33 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1));
34 connection node_1a;
35 SET SESSION wsrep_retry_autocommit = 0;
36 SET SESSION wsrep_sync_wait = 0;
37 CREATE PROCEDURE proc_update()
38 BEGIN
39 UPDATE t1 SET f2 = 'b';
40 END|
41 INSERT INTO t1 VALUES(1, 'a');
42 connection node_1;
43 SET debug_sync='wsrep_before_certification SIGNAL ready WAIT_FOR cont';
44 CALL proc_update;
45 connection node_1a;
46 SET debug_sync='now WAIT_FOR ready';
47 connection node_2;
48 UPDATE t1 SET f2='c';
49 connection node_1a;
50 SET debug_sync='now SIGNAL cont';
51 connection node_1;
52 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
53 connection node_1a;
54 SET debug_sync='RESET';
55 DROP PROCEDURE proc_update;
56 connection node_1;
57
58 Test phase 3 to make sure natural deadlock is not treated as BF abort
59
60 TRUNCATE t1;
61 INSERT INTO t1 VALUES (1, 'a'), (2, 'a');
62 connection node_1a;
63 START TRANSACTION;
64 UPDATE t1 SET f2 = 'b' WHERE f1 = 1;
65 connection node_1;
66 START TRANSACTION;
67 UPDATE t1 SET f2 = 'c' WHERE f1 = 2;
68 connection node_1a;
69 UPDATE t1 SET f2 = 'b' WHERE f1 = 2;
70 connection node_1;
71 UPDATE t1 SET f2 = 'c' WHERE f1 = 1;
72 connection node_1a;
73 COMMIT;
74 wsrep__bf_aborts
75 0
76 connection node_1;
77 ROLLBACK;
78
79 Test phase 4 to make sure natural deadlock inside SP execution
80 is not treated as BF abort
81
82 connection node_1a;
83 TRUNCATE t1;
84 INSERT INTO t1 VALUES (1, 'a'), (2, 'a');
85 CREATE PROCEDURE proc_update_1()
86 BEGIN
87 START TRANSACTION;
88 UPDATE t1 SET f2 = 'b' WHERE f1 = 1;
89 SELECT SLEEP(5);
90 UPDATE t1 SET f2 = 'b' WHERE f1 = 2;
91 COMMIT;
92 END|
93 CREATE PROCEDURE proc_update_2()
94 BEGIN
95 START TRANSACTION;
96 UPDATE t1 SET f2 = 'c' WHERE f1 = 2;
97 SELECT SLEEP(5);
98 UPDATE t1 SET f2 = 'c' WHERE f1 = 1;
99 COMMIT;
100 END|
101 connection node_1;
102 CALL proc_update_1;
103 connection node_1a;
104 CALL proc_update_2;
105 SLEEP(5)
106 0
107 wsrep__bf_aborts
108 0
109 connection node_1;
110 SLEEP(5)
111 0
112 DROP PROCEDURE proc_update_1;
113 DROP PROCEDURE proc_update_2;
114 DROP TABLE t1;