1################################################################################ 2# 3# Purpose: Execute changes to t1 and roll back to potentially mess up 4# REPEATABLE READ queries in other transactions. 5# 6# - Start transaction 7# - Do "bogus" updates 8# - ROLLBACK 9# - Do "bogus" inserts 10# - Do bogus deletes 11# - ROLLBACK 12# 13# Handles rows with pk between 500 and 550. 14# 15################################################################################ 16 17# TODO: Handle tx errors (possible deadlocks?) 18 19SET autocommit = 0; 20 21START TRANSACTION; 22--echo *** All changes done in this test / transaction will be rolled back. 23 24let $conn_id= `SELECT CONNECTION_ID()`; 25 26# "Bogus" updates: 27 28# Disabling query log to avoid complex filtering of output in order to keep 29# result file diffing clean. 30--echo *** Disabling query log. 31--disable_query_log 32 33--echo *** Executing UPDATE on rows 501 -- 549 with id = 50, single statement. 34--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD 35eval UPDATE t1 SET `int1` = 50, 36 `int1_key` = `int1_key` + 50, 37 `int2` = `int2` - 50, 38 `id` = 50, 39 `is_consistent` = 0, 40 `is_uncommitted` = 1, 41 `connection_id` = $conn_id 42 WHERE `pk` > 500 AND `pk` < 550; 43 44--echo *** Executing UPDATE on rows 526 -- 549 with id = 50, single statement. 45--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD 46eval UPDATE t1 SET `int2_key` = 50, 47 `id` = 50, 48 `is_consistent` = 0, 49 `is_uncommitted` = 1, 50 `connection_id` = $conn_id 51 WHERE `pk` > 525 AND `pk` < 550; 52 53# Still "bogus" updates... Now update 25 rows in succession, single field: 54--echo *** Executing UPDATE on rows 501 -- 525 with id = 50, multiple statements. 55 56let $updates= 25; 57while ($updates) 58{ 59 --error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD 60 eval UPDATE t1 SET `int2_key` = 50, 61 `id` = 50, 62 `is_consistent` = 0, 63 `is_uncommitted` = 1, 64 `connection_id` = $conn_id 65 WHERE `pk` = (SELECT 526 - $updates); 66 dec $updates; 67} 68 69--echo *** ROLLBACK 70ROLLBACK; 71 72--echo *** START TRANSACTION 73START TRANSACTION; 74# Now do some "bogus" inserts: 75--echo *** Executing INSERTs of rows with id = 50, 2 statements. 76 77--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY 78eval INSERT INTO t1 (`id`, `int1`, `int1_key`, `int1_unique`, `connection_id`) 79 VALUES (50, 505050, -505050, 505050, $conn_id); 80 81--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY 82eval INSERT INTO t1 (`id`, `int2`, `int2_key`, `int2_unique`, `connection_id`) 83 VALUES (50, -505050, 505050, -505050, $conn_id); 84 85# And some "bogus" deletes: 86--echo *** Executing DELETE of rows with pk between 449 and 540, single statement. 87--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD 88DELETE FROM t1 WHERE `pk` BETWEEN 449 AND 540; 89 90--echo *** Enabling query log. 91--enable_query_log 92 93ROLLBACK; 94 95