| 1 |
################################################################################ |
| 2 |
# |
| 3 |
# DELETE |
| 4 |
# |
| 5 |
# DELETE existing rows and commit. Above 900 (initial insert inserted 1000 pk's). |
| 6 |
# |
| 7 |
# DO NOT DELETE ROWS WITH (PK MOD 5) = 0 (PK's evenly divisible by 5). We |
| 8 |
# count these as a consistency check in other tests. |
| 9 |
# |
| 10 |
# This test runs a number of consecutive transactions (to allow for high |
| 11 |
# concurrency): |
| 12 |
# |
| 13 |
# Tx 1: |
| 14 |
# - DELETE a row and INSERT it immediately with same pk. |
| 15 |
# Tx 2: |
| 16 |
# - DELETE a row and INSERT it immediately with pk = NULL. |
| 17 |
# |
| 18 |
# Tx 3: |
| 19 |
# - DELETE two rows so that the total table sum does not change. |
| 20 |
# |
| 21 |
# Net effect: 2 fewer rows (if no errors) |
| 22 |
# |
| 23 |
# Roll back the entire transaction if a statement upon subsequent statements |
| 24 |
# depend result in error. This is to maintain consistency (zero-sum tx). |
| 25 |
# |
| 26 |
################################################################################ |
| 27 |
SET autocommit = 0; |
| 28 |
START TRANSACTION; |
| 29 |
--echo |
| 30 |
--echo *** Delete a row and re-insert with same `pk`: |
| 31 |
--echo |
| 32 |
# |
| 33 |
# Get pk and unique ints of an existing row that is internally consistent. |
| 34 |
# (Note: If any int field values may be NULL, handle this by e.g. using COALESCE) |
| 35 |
# Re-using unique values in an effort to avoid rollbacks due to duplicate keys. |
| 36 |
# |
| 37 |
# NOTE: Because we maintain 0-sum consistency by "cancelling out" the deleted row with a new |
| 38 |
# row later, we need to make sure we knoe exactly what we are deleting, thus using |
| 39 |
# FOR UPDATE clause. |
| 40 |
--echo *** Disabling result log |
| 41 |
--disable_result_log |
| 42 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 43 |
SELECT @pk:=`pk`, |
| 44 |
@unique1:=`int1_unique`, |
| 45 |
@unique2:=`int2_unique` |
| 46 |
FROM t1 WHERE `pk` MOD 5 = 4 AND `pk` > 900 AND `is_consistent` = 1 LIMIT 1 FOR UPDATE; |
| 47 |
--echo *** Enabling result log |
| 48 |
--enable_result_log |
| 49 |
|
| 50 |
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc |
| 51 |
|
| 52 |
# Delete the row. We maintain 0-sum tx consistency by inserting ints that will cancel out |
| 53 |
# the unique values (which we are keeping) later in this transaction. |
| 54 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 55 |
DELETE FROM t1 WHERE `pk` = @pk; |
| 56 |
|
| 57 |
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc |
| 58 |
|
| 59 |
--echo *** Doing insert of row with pk = @pk if above statement succeeded (query log disabled)... |
| 60 |
# Note that affected rows may be 0 if some other thread changed the row in the meantime - still |
| 61 |
# we get no error. We work around this by using FOR UPDATE to lock the row (see above). |
| 62 |
|
| 63 |
if(!$error) |
| 64 |
{ |
| 65 |
# Insert a new row with the same sum of integers. |
| 66 |
# This is conditional, so skip it the query log. |
| 67 |
--disable_query_log |
| 68 |
|
| 69 |
# If some other thread is doing the same thing at the same time, we may get duplicate key error |
| 70 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY |
| 71 |
INSERT INTO t1 (`pk`, `id`, `int1`, `int1_key`, `int1_unique`, |
| 72 |
`int2`, `int2_key`, `int2_unique`, |
| 73 |
`for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`) |
| 74 |
VALUES (@pk, 900, 900, -900, @unique1, |
| 75 |
-(@unique1+@unique2), 0, @unique2, |
| 76 |
0, CONNECTION_ID(), 0, 0, 1); |
| 77 |
|
| 78 |
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc |
| 79 |
--enable_query_log |
| 80 |
} |
| 81 |
|
| 82 |
COMMIT; |
| 83 |
|
| 84 |
######################## |
| 85 |
# TRANSACTION 2 |
| 86 |
######################## |
| 87 |
|
| 88 |
START TRANSACTION; |
| 89 |
|
| 90 |
--echo |
| 91 |
--echo *** Delete a row and re-insert with `pk` = NULL: |
| 92 |
--echo |
| 93 |
--echo *** Disabling result log |
| 94 |
--disable_result_log |
| 95 |
# In order not to pick the same row as above, try to pick a different pk. |
| 96 |
# Use FOR UPDATE to make sure we maintain 0-sum consistency througout the transaction. |
| 97 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 98 |
SELECT @pk:=`pk`, |
| 99 |
@unique1:=`int1_unique`, |
| 100 |
@unique2:=`int2_unique` |
| 101 |
FROM t1 WHERE `pk` MOD 5 = 4 AND `pk` > 901 AND `is_consistent` = 1 LIMIT 1 FOR UPDATE; |
| 102 |
--echo *** Enabling result log |
| 103 |
--enable_result_log |
| 104 |
|
| 105 |
--source suite/engines/rr_trx/include/check_for_error_rollback_skip.inc |
| 106 |
|
| 107 |
# Delete the row. We maintain 0-sum tx consistency by inserting ints that will cancel out |
| 108 |
# the unique values (which we are keeping) later in this transaction. |
| 109 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 110 |
DELETE FROM t1 WHERE `pk` = @pk; |
| 111 |
|
| 112 |
--source suite/engines/rr_trx/include/check_for_error_rollback.inc |
| 113 |
|
| 114 |
--echo *** Doing insert of row with pk = NULL if above statement succeeded (query log disabled)... |
| 115 |
|
| 116 |
if(!$error) |
| 117 |
{ |
| 118 |
# Insert a new row with the same sum of integers. |
| 119 |
# This is conditional, so skip it the query log. |
| 120 |
--disable_query_log |
| 121 |
|
| 122 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_DUP_ENTRY |
| 123 |
INSERT INTO t1 (`pk`, `id`, `int1`, `int1_key`, `int1_unique`, |
| 124 |
`int2`, `int2_key`, `int2_unique`, |
| 125 |
`for_update`, `connection_id`, `thread_id`, `is_uncommitted`, `is_consistent`) |
| 126 |
VALUES (NULL, 901, 901, -901, @unique1, |
| 127 |
-(@unique1+@unique2), 0, @unique2, |
| 128 |
0, CONNECTION_ID(), 0, 0, 1); |
| 129 |
--source suite/engines/rr_trx/include/check_for_error_rollback.inc |
| 130 |
--enable_query_log |
| 131 |
} |
| 132 |
COMMIT; |
| 133 |
|
| 134 |
######################## |
| 135 |
# TRANSACTION 3 |
| 136 |
######################## |
| 137 |
|
| 138 |
START TRANSACTION; |
| 139 |
|
| 140 |
# By identifying rows with total row sum = 0, we know that deleting such rows |
| 141 |
# won't affect the total table sum (used for consistency check). |
| 142 |
# Such rows should have been marked with `is_consistent` = 0; |
| 143 |
|
| 144 |
--echo |
| 145 |
--echo *** Delete up to two (2) "consistent" rows (zero-sum; table sum unchanged) |
| 146 |
--echo |
| 147 |
# We get Warning "1592: Statement is not safe to log in statement mode" with server 5.1 |
| 148 |
# due to LIMIT (see Bug#42415 and Bug#42851). |
| 149 |
--disable_warnings |
| 150 |
--error 0, ER_LOCK_DEADLOCK, ER_LOCK_WAIT_TIMEOUT, ER_CHECKREAD |
| 151 |
DELETE FROM t1 WHERE `pk` > 902 AND `pk` MOD 5 = 3 AND `is_consistent` = 1 LIMIT 2; |
| 152 |
--enable_warnings |
| 153 |
|
| 154 |
COMMIT; |
| 155 |
|