| 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 |
|
| 19 |
SET autocommit = 0; |
| 20 |
|
| 21 |
START TRANSACTION; |
| 22 |
--echo *** All changes done in this test / transaction will be rolled back. |
| 23 |
|
| 24 |
let $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 |
| 35 |
eval 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 |
| 46 |
eval 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 |
|
| 56 |
let $updates= 25; |
| 57 |
while ($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 |
| 70 |
ROLLBACK; |
| 71 |
|
| 72 |
--echo *** START TRANSACTION |
| 73 |
START 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 |
| 78 |
eval 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 |
| 82 |
eval 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 |
| 88 |
DELETE FROM t1 WHERE `pk` BETWEEN 449 AND 540; |
| 89 |
|
| 90 |
--echo *** Enabling query log. |
| 91 |
--enable_query_log |
| 92 |
|
| 93 |
ROLLBACK; |
| 94 |
|