1# 2# Test different deadlock scenarios in innodb and make sure that 3# wsrep patch does not handle them as BF aborts. 4# 5 6--source include/galera_cluster.inc 7--source include/have_debug.inc 8--source include/have_debug_sync.inc 9 10############################################################################## 11# test case to verify that natural deadlock of trigger SP execution is 12# handled correctly 13############################################################################## 14 15--echo 16--echo Test phase 1 to make sure that natral deadlock in trigger SP execution is 17--echo handled correctly 18--echo 19--let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 20 21CREATE TABLE t1(a INT); 22CREATE TABLE t2(f1 INT, f2 INT, f3 INT); 23--disable_query_log 24let $run=1000; 25while($run) 26{ 27 INSERT INTO t2 VALUES (1, 2, 3); 28 dec $run; 29} 30--enable_query_log 31 32DELIMITER |; 33CREATE PROCEDURE proc() 34BEGIN 35 INSERT INTO t2 VALUES(100, 200, 300); 36 UPDATE t2 SET f3 = f3 + 100; 37END| 38DELIMITER ;| 39 40CREATE TRIGGER t1 BEFORE INSERT ON t1 FOR EACH ROW CALL proc(); 41 42--send INSERT INTO t1 VALUES(2); 43 44--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 45--send INSERT INTO t1 VALUES(1); 46 47--connection node_1 48--error 0,ER_LOCK_DEADLOCK 49--reap 50 51--connection node_1a 52--error 0,ER_LOCK_DEADLOCK 53--reap 54 55--connection node_1 56--let $aborts_new = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 57--disable_query_log 58--eval SELECT $aborts_new - $aborts_old AS wsrep__bf_aborts; 59--enable_query_log 60DROP TABLE t1; 61DROP TABLE t2; 62DROP PROCEDURE proc; 63 64############################################################################## 65# 66# test case to verify that BF abort for SP execution is handled correctly 67# 68############################################################################## 69 70--echo 71--echo Test phase 2 to make sure that BF abort for SP execution is 72--echo handled correctly 73--echo 74--connection node_1 75SET SESSION wsrep_retry_autocommit = 0; 76SET SESSION wsrep_sync_wait = 0; 77CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)); 78 79# Control connection for Galera sync point management 80--connection node_1a 81 82SET SESSION wsrep_retry_autocommit = 0; 83SET SESSION wsrep_sync_wait = 0; 84--let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 85 86DELIMITER |; 87CREATE PROCEDURE proc_update() 88BEGIN 89 UPDATE t1 SET f2 = 'b'; 90END| 91DELIMITER ;| 92 93INSERT INTO t1 VALUES(1, 'a'); 94 95--connection node_1 96SET debug_sync='wsrep_before_certification SIGNAL ready WAIT_FOR cont'; 97--send CALL proc_update 98 99--connection node_1a 100SET debug_sync='now WAIT_FOR ready'; 101 102--connection node_2 103UPDATE t1 SET f2='c'; 104 105--connection node_1a 106# wait for BF to happen 107--let $wait_condition = SELECT VARIABLE_VALUE = $aborts_old + 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts' 108--source include/wait_condition.inc 109 110SET debug_sync='now SIGNAL cont'; 111 112--connection node_1 113--error ER_LOCK_DEADLOCK 114--reap 115 116--connection node_1a 117SET debug_sync='RESET'; 118 119DROP PROCEDURE proc_update; 120 121 122############################################################################## 123# 124# test case to verify that natural deadlock does not cause BF abort 125# 126############################################################################## 127 128--connection node_1 129--echo 130--echo Test phase 3 to make sure natural deadlock is not treated as BF abort 131--echo 132TRUNCATE t1; 133INSERT INTO t1 VALUES (1, 'a'), (2, 'a'); 134 135--connection node_1a 136--let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 137 138START TRANSACTION; 139UPDATE t1 SET f2 = 'b' WHERE f1 = 1; 140 141--connection node_1 142START TRANSACTION; 143UPDATE t1 SET f2 = 'c' WHERE f1 = 2; 144 145--connection node_1a 146# this hangs for lock wait 147--send UPDATE t1 SET f2 = 'b' WHERE f1 = 2 148 149# 150# classic deadlock happens here 151# 152--connection node_1 153--error 0, ER_LOCK_DEADLOCK 154UPDATE t1 SET f2 = 'c' WHERE f1 = 1; 155 156--connection node_1a 157--error 0, ER_LOCK_DEADLOCK 158--reap 159COMMIT; 160 161# 162# either one of SP executions was aborted because of natural deadlock, or in worst case 163# they were ordered seqeuntailly, and both succeeded. 164# anyways, we just check here that no BF aborts happened 165# 166--let $aborts_new = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 167--disable_query_log 168--eval SELECT $aborts_new - $aborts_old AS wsrep__bf_aborts; 169--enable_query_log 170 171--connection node_1 172ROLLBACK; 173 174############################################################################## 175# 176# test case to verify that natural deadlock within SP exceution 177# does not cause BF abort 178# 179############################################################################## 180 181--echo 182--echo Test phase 4 to make sure natural deadlock inside SP execution 183--echo is not treated as BF abort 184--echo 185 186--connection node_1a 187TRUNCATE t1; 188INSERT INTO t1 VALUES (1, 'a'), (2, 'a'); 189 190--let $aborts_old = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 191 192DELIMITER |; 193CREATE PROCEDURE proc_update_1() 194BEGIN 195 START TRANSACTION; 196 UPDATE t1 SET f2 = 'b' WHERE f1 = 1; 197 SELECT SLEEP(5); 198 UPDATE t1 SET f2 = 'b' WHERE f1 = 2; 199 COMMIT; 200END| 201DELIMITER ;| 202 203DELIMITER |; 204CREATE PROCEDURE proc_update_2() 205BEGIN 206 START TRANSACTION; 207 UPDATE t1 SET f2 = 'c' WHERE f1 = 2; 208 SELECT SLEEP(5); 209 UPDATE t1 SET f2 = 'c' WHERE f1 = 1; 210 COMMIT; 211END| 212DELIMITER ;| 213 214--connection node_1 215--send CALL proc_update_1 216 217--connection node_1a 218# 219# calling proc_update_2 should cause a natural deadlock 220# however, this test is not deterministic, and depends on the sleep() to 221# cause expected ordering for update statement execution within SPs 222# We therefore, allow both success and deadlock error for the result 223# 224--error 0, ER_LOCK_DEADLOCK 225CALL proc_update_2; 226 227# 228# either one of SP executions was aborted because of natural deadlock, or in worst case 229# they were ordered seqeuntailly, and both succeeded. 230# anyways, we just check here that no BF aborts happened 231# 232--let $aborts_new = `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_local_bf_aborts'` 233--disable_query_log 234--eval SELECT $aborts_new - $aborts_old AS wsrep__bf_aborts; 235--enable_query_log 236 237 238--connection node_1 239--error 0, ER_LOCK_DEADLOCK 240--reap 241 242DROP PROCEDURE proc_update_1; 243DROP PROCEDURE proc_update_2; 244DROP TABLE t1; 245