1# 2# Ensure that the number of locks (SELECT FOR UPDATE for example) is 3# added to the number of altered rows when choosing the smallest 4# transaction to kill as a victim when a deadlock is detected. 5# Also transactions what had edited non-transactional tables should 6# be heavier than ones that had not. 7# 8 9-- source include/have_innodb.inc 10 11SET default_storage_engine=InnoDB; 12 13# we do not really care about what gets printed, we are only 14# interested in getting the deadlock resolved according to our 15# expectations 16-- disable_query_log 17-- disable_result_log 18 19# we want to use "-- eval statement1; statement2" which does not work with 20# prepared statements. Because this test should not behave differently with 21# or without prepared statements we disable them so the test does not fail 22# if someone runs ./mysql-test-run.pl --ps-protocol 23-- disable_ps_protocol 24 25-- disable_warnings 26DROP TABLE IF EXISTS t1, t2, t3, t4, t5_nontrans; 27-- enable_warnings 28 29# we will create a simple deadlock with t1, t2 and two connections 30CREATE TABLE t1 (a INT); 31CREATE TABLE t2 (a INT); 32 33# auxiliary table with a bulk of rows which will be locked by a 34# transaction to increase its weight 35CREATE TABLE t3 (a INT); 36 37# auxiliary empty table which will be inserted by a 38# transaction to increase its weight 39CREATE TABLE t4 (a INT); 40 41# auxiliary non-transactional table which will be edited by a 42# transaction to tremendously increase its weight 43CREATE TABLE t5_nontrans (a INT) ENGINE=MyISAM; 44 45INSERT INTO t1 VALUES (1); 46INSERT INTO t2 VALUES (1); 47# insert a lot of rows in t3 48INSERT INTO t3 VALUES (1); 49INSERT INTO t3 SELECT * FROM t3; 50INSERT INTO t3 SELECT * FROM t3; 51INSERT INTO t3 SELECT * FROM t3; 52INSERT INTO t3 SELECT * FROM t3; 53INSERT INTO t3 SELECT * FROM t3; 54INSERT INTO t3 SELECT * FROM t3; 55INSERT INTO t3 SELECT * FROM t3; 56INSERT INTO t3 SELECT * FROM t3; 57INSERT INTO t3 SELECT * FROM t3; 58INSERT INTO t3 SELECT * FROM t3; 59INSERT INTO t3 SELECT * FROM t3; 60 61# test locking weight 62 63-- let $con1_extra_sql = INSERT INTO t4 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) 64-- let $con1_extra_sql_present = 1 65-- let $con2_extra_sql = SELECT * FROM t3 FOR UPDATE 66-- let $con2_extra_sql_present = 1 67-- let $con1_should_be_rolledback = 0 68-- source include/innodb_trx_weight.inc 69 70# insert more rows for 64k page size. 71INSERT INTO t3 SELECT * FROM t3; 72 73-- let $con1_extra_sql = INSERT INTO t4 VALUES (1), (1) 74-- let $con1_extra_sql_present = 1 75-- let $con2_extra_sql = SELECT * FROM t3 FOR UPDATE 76-- let $con2_extra_sql_present = 1 77-- let $con1_should_be_rolledback = 1 78-- source include/innodb_trx_weight.inc 79 80-- let $con1_extra_sql = 81-- let $con1_extra_sql_present = 0 82-- let $con2_extra_sql = SELECT * FROM t3 FOR UPDATE 83-- let $con2_extra_sql_present = 1 84-- let $con1_should_be_rolledback = 1 85-- source include/innodb_trx_weight.inc 86 87# test weight when non-transactional tables are edited 88 89-- let $con1_extra_sql = INSERT INTO t4 VALUES (1), (1), (1) 90-- let $con1_extra_sql_present = 1 91-- let $con2_extra_sql = 92-- let $con2_extra_sql_present = 0 93-- let $con1_should_be_rolledback = 0 94-- source include/innodb_trx_weight.inc 95 96-- let $con1_extra_sql = INSERT INTO t4 VALUES (1), (1), (1) 97-- let $con1_extra_sql_present = 1 98-- let $con2_extra_sql = INSERT INTO t5_nontrans VALUES (1) 99-- let $con2_extra_sql_present = 1 100-- let $con1_should_be_rolledback = 1 101-- source include/innodb_trx_weight.inc 102 103-- let $con1_extra_sql = INSERT INTO t4 VALUES (1), (1), (1) 104-- let $con1_extra_sql = $con1_extra_sql; INSERT INTO t5_nontrans VALUES (1) 105-- let $con1_extra_sql_present = 1 106-- let $con2_extra_sql = INSERT INTO t5_nontrans VALUES (1) 107-- let $con2_extra_sql_present = 1 108-- let $con1_should_be_rolledback = 0 109-- source include/innodb_trx_weight.inc 110 111DROP TABLE t1, t2, t3, t4, t5_nontrans; 112