Debian Codegraph @

mysql-test/suite/engines/rr_trx/t/rr_id_900.test

raw
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