Merge lp:~laurynas-biveinis/percona-server/bug1064326-5.1 into lp:percona-server/5.1

Proposed by Laurynas Biveinis
Status: Merged
Approved by: Stewart Smith
Approved revision: 486
Merged at revision: 501
Proposed branch: lp:~laurynas-biveinis/percona-server/bug1064326-5.1
Merge into: lp:percona-server/5.1
Prerequisite: lp:~laurynas-biveinis/percona-server/bug890404-5.1
Diff against target: 333 lines (+203/-14)
8 files modified
Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result (+68/-4)
Percona-Server/mysql-test/r/percona_server_variables_debug.result (+1/-0)
Percona-Server/mysql-test/r/percona_server_variables_release.result (+1/-0)
Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test (+103/-6)
Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc (+8/-0)
Percona-Server/storage/innodb_plugin/include/srv0srv.h (+4/-0)
Percona-Server/storage/innodb_plugin/lock/lock0lock.c (+14/-4)
Percona-Server/storage/innodb_plugin/srv/srv0srv.c (+4/-0)
To merge this branch: bzr merge lp:~laurynas-biveinis/percona-server/bug1064326-5.1
Reviewer Review Type Date Requested Status
Stewart Smith (community) Approve
Review via email: mp+129423@code.launchpad.net

Description of the change

Fix bug 1064326 (Fake changes blocking real changes due to locking).

The issue is that, although fake change transactions downgrade the
requested X row locks to S locks, these S locks prevent X locks from
being taken.

Fixed by porting lp:mysqlatfacebook/51 revisions 3771 and 3775. This
fix introduces a new option innodb_locking_fake_changes, which, when
set to FALSE, makes fake transactions not to take any row locks.
However, this option is not safe to set to FALSE by default, because
the fake changes implementation is not ready for lock-less operation
for all workloads. Namely, if a real transacation will remove a row
that a fake transaction is doing a secondary index maintenance for,
the latter will fail. The new option is considered experimental and
might be removed in the future if lockless operation mode fixes are
implemented.

The name of variable is different from FB innodb_fake_changes_locks to
avoid making innodb_fake_changes a prefix of it.

Adjust percona_innodb_fake_changes_locks testcase to test
innodb_locking_fake_changes=0 operation and that fake changes do not
cause any real data changes to the tables.

Re-record percona_server_variables_debug and
percona_server_variables_release tests.

http://jenkins.percona.com/job/percona-server-5.1-param/442/

26611

To post a comment you must log in.
Stewart Smith (stewart) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result'
2--- Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result 2011-11-24 02:00:54 +0000
3+++ Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result 2012-10-12 13:06:39 +0000
4@@ -1,7 +1,9 @@
5-DROP TABLE IF EXISTS t1;
6+DROP TABLE IF EXISTS t1, t2;
7+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
8+INSERT INTO t1 VALUES (1);
9+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
10+INSERT INTO t2 VALUES (1, 1);
11 # Verifying that X_LOCK not acquired
12-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
13-INSERT INTO t1 VALUES (1);
14 SET autocommit=0;
15 SET innodb_fake_changes=1;
16 BEGIN;
17@@ -15,5 +17,67 @@
18 a
19 1
20 ROLLBACK;
21+# Confirm no lock wait timeouts when innodb_locking_fake_changes=0
22+SET GLOBAL innodb_locking_fake_changes=0;
23+BEGIN;
24+SELECT * FROM t1 FOR UPDATE;
25+a
26+1
27+BEGIN;
28+UPDATE t1 SET a=3 WHERE a=1;
29+SELECT * FROM t1 LOCK IN SHARE MODE;
30+a
31+3
32+ROLLBACK;
33+ROLLBACK;
34+# Confirm that fake UPDATE does not hold a lock
35+BEGIN;
36+SELECT * FROM t1 FOR UPDATE;
37+a
38+1
39+BEGIN;
40+UPDATE t1 SET a=3 WHERE a=1;
41+ROLLBACK;
42+ROLLBACK;
43+BEGIN;
44+UPDATE t1 SET a=3 WHERE a=1;
45+BEGIN;
46+UPDATE t1 SET a=3 WHERE a=1;
47+ROLLBACK;
48+ROLLBACK;
49+# Confirm that fake INSERT does not hold a lock
50+BEGIN;
51+INSERT INTO t1 VALUES(4);
52+BEGIN;
53+INSERT INTO t1 VALUES(4);
54+ROLLBACK;
55+ROLLBACK;
56+# Confirm that fake DELETE does not hold a lock
57+BEGIN;
58+DELETE FROM t1 WHERE a=1;
59+BEGIN;
60+DELETE FROM t1 WHERE a=1;
61+ROLLBACK;
62+ROLLBACK;
63+# Confirm that fake REPLACE does not hold a lock
64+BEGIN;
65+REPLACE INTO t2 VALUES(1, 2);
66+BEGIN;
67+REPLACE INTO t2 VALUES(1, 2);
68+ROLLBACK;
69+ROLLBACK;
70 SET innodb_fake_changes=default;
71-DROP TABLE t1;
72+SET GLOBAL innodb_locking_fake_changes=default;
73+# Verify that the fake changes to t1 did not leak through
74+CHECK TABLE t1;
75+Table Op Msg_type Msg_text
76+test.t1 check status OK
77+should_be_1
78+1
79+# Verify that the fake changes to t2 did not leak through
80+CHECK TABLE t2;
81+Table Op Msg_type Msg_text
82+test.t2 check status OK
83+should_be_1
84+1
85+DROP TABLE t1, t2;
86
87=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_debug.result'
88--- Percona-Server/mysql-test/r/percona_server_variables_debug.result 2012-10-12 01:13:44 +0000
89+++ Percona-Server/mysql-test/r/percona_server_variables_debug.result 2012-10-12 13:06:39 +0000
90@@ -121,6 +121,7 @@
91 INNODB_IO_CAPACITY
92 INNODB_KILL_IDLE_TRANSACTION
93 INNODB_LAZY_DROP_TABLE
94+INNODB_LOCKING_FAKE_CHANGES
95 INNODB_LOCKS_UNSAFE_FOR_BINLOG
96 INNODB_LOCK_WAIT_TIMEOUT
97 INNODB_LOG_BLOCK_SIZE
98
99=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_release.result'
100--- Percona-Server/mysql-test/r/percona_server_variables_release.result 2012-09-05 22:27:47 +0000
101+++ Percona-Server/mysql-test/r/percona_server_variables_release.result 2012-10-12 13:06:39 +0000
102@@ -118,6 +118,7 @@
103 INNODB_IO_CAPACITY
104 INNODB_KILL_IDLE_TRANSACTION
105 INNODB_LAZY_DROP_TABLE
106+INNODB_LOCKING_FAKE_CHANGES
107 INNODB_LOCKS_UNSAFE_FOR_BINLOG
108 INNODB_LOCK_WAIT_TIMEOUT
109 INNODB_LOG_BLOCK_SIZE
110
111=== modified file 'Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test'
112--- Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test 2011-11-24 02:00:54 +0000
113+++ Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test 2012-10-12 13:06:39 +0000
114@@ -1,24 +1,121 @@
115 --source include/have_innodb_plugin.inc
116
117 --disable_warnings
118-DROP TABLE IF EXISTS t1;
119+DROP TABLE IF EXISTS t1, t2;
120 --enable_warnings
121
122---echo # Verifying that X_LOCK not acquired
123-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
124+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
125 INSERT INTO t1 VALUES (1);
126+let $t1_checksum_1= `CHECKSUM TABLE t1 EXTENDED`;
127+
128+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
129+INSERT INTO t2 VALUES (1, 1);
130+let $t2_checksum_1= `CHECKSUM TABLE t2 EXTENDED`;
131+
132 --connect (conn1,localhost,root,,)
133 --connection conn1
134+--echo # Verifying that X_LOCK not acquired
135 SET autocommit=0;
136 SET innodb_fake_changes=1;
137 BEGIN;
138 SELECT * FROM t1 FOR UPDATE;
139+
140 --connection default
141 SET innodb_lock_wait_timeout=3;
142 --error 1205
143 UPDATE t1 SET a=2;
144 SELECT * FROM t1 LOCK IN SHARE MODE;
145---connection conn1
146-ROLLBACK;
147+
148+--connection conn1
149+ROLLBACK;
150+
151+--echo # Confirm no lock wait timeouts when innodb_locking_fake_changes=0
152+SET GLOBAL innodb_locking_fake_changes=0;
153+BEGIN;
154+SELECT * FROM t1 FOR UPDATE;
155+
156+--connect (conn2,localhost,root,,)
157+--connection conn2
158+BEGIN;
159+UPDATE t1 SET a=3 WHERE a=1;
160+SELECT * FROM t1 LOCK IN SHARE MODE;
161+ROLLBACK;
162+
163+--connection conn1
164+ROLLBACK;
165+
166+--echo # Confirm that fake UPDATE does not hold a lock
167+BEGIN;
168+SELECT * FROM t1 FOR UPDATE;
169+
170+--connection conn2
171+BEGIN;
172+UPDATE t1 SET a=3 WHERE a=1;
173+ROLLBACK;
174+
175+--connection conn1
176+ROLLBACK;
177+BEGIN;
178+UPDATE t1 SET a=3 WHERE a=1;
179+
180+--connection conn2
181+BEGIN;
182+UPDATE t1 SET a=3 WHERE a=1;
183+ROLLBACK;
184+
185+--connection conn1
186+ROLLBACK;
187+--echo # Confirm that fake INSERT does not hold a lock
188+BEGIN;
189+INSERT INTO t1 VALUES(4);
190+
191+--connection conn2
192+BEGIN;
193+INSERT INTO t1 VALUES(4);
194+ROLLBACK;
195+
196+--connection conn1
197+ROLLBACK;
198+--echo # Confirm that fake DELETE does not hold a lock
199+BEGIN;
200+DELETE FROM t1 WHERE a=1;
201+
202+--connection conn2
203+BEGIN;
204+DELETE FROM t1 WHERE a=1;
205+ROLLBACK;
206+
207+--connection conn1
208+ROLLBACK;
209+--echo # Confirm that fake REPLACE does not hold a lock
210+BEGIN;
211+REPLACE INTO t2 VALUES(1, 2);
212+
213+--connection conn2
214+BEGIN;
215+REPLACE INTO t2 VALUES(1, 2);
216+ROLLBACK;
217+
218+--connection conn1
219+ROLLBACK;
220+
221 SET innodb_fake_changes=default;
222-DROP TABLE t1;
223+SET GLOBAL innodb_locking_fake_changes=default;
224+--echo # Verify that the fake changes to t1 did not leak through
225+CHECK TABLE t1;
226+let $t1_checksum_2= `CHECKSUM TABLE t1 EXTENDED`;
227+--disable_query_log
228+eval SELECT "$t1_checksum_1" LIKE "$t1_checksum_2" AS should_be_1;
229+--enable_query_log
230+
231+--echo # Verify that the fake changes to t2 did not leak through
232+CHECK TABLE t2;
233+let $t2_checksum_2= `CHECKSUM TABLE t2 EXTENDED`;
234+--disable_query_log
235+eval SELECT "$t2_checksum_1" LIKE "$t2_checksum_2" AS should_be_1;
236+--enable_query_log
237+
238+--connection default
239+--disconnect conn1
240+--disconnect conn2
241+DROP TABLE t1, t2;
242
243=== modified file 'Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc'
244--- Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc 2012-09-14 12:19:29 +0000
245+++ Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc 2012-10-12 13:06:39 +0000
246@@ -12138,6 +12138,13 @@
247 NULL, NULL, 0, 0, 1024, 0);
248 #endif /* UNIV_DEBUG */
249
250+static MYSQL_SYSVAR_BOOL(locking_fake_changes, srv_fake_changes_locks,
251+ PLUGIN_VAR_NOCMDARG,
252+ "###EXPERIMENTAL### if enabled, transactions will get S row locks instead "
253+ "of X locks for fake changes. If disabled, fake change transactions will "
254+ "not take any locks at all.",
255+ NULL, NULL, TRUE);
256+
257 static struct st_mysql_sys_var* innobase_system_variables[]= {
258 MYSQL_SYSVAR(page_size),
259 MYSQL_SYSVAR(log_block_size),
260@@ -12239,6 +12246,7 @@
261 MYSQL_SYSVAR(pass_corrupt_table),
262 MYSQL_SYSVAR(lazy_drop_table),
263 MYSQL_SYSVAR(fake_changes),
264+ MYSQL_SYSVAR(locking_fake_changes),
265 #ifdef UNIV_DEBUG
266 MYSQL_SYSVAR(trx_rseg_n_slots_debug),
267 #endif /* UNIV_DEBUG */
268
269=== modified file 'Percona-Server/storage/innodb_plugin/include/srv0srv.h'
270--- Percona-Server/storage/innodb_plugin/include/srv0srv.h 2012-09-14 12:19:29 +0000
271+++ Percona-Server/storage/innodb_plugin/include/srv0srv.h 2012-10-12 13:06:39 +0000
272@@ -369,6 +369,10 @@
273 /** Whether startup should be blocked until buffer pool is fully restored */
274 extern ibool srv_blocking_lru_restore;
275
276+/** When TRUE, fake change transcations take S rather than X row locks.
277+When FALSE, row locks are not taken at all. */
278+extern my_bool srv_fake_changes_locks;
279+
280 /** Status variables to be passed to MySQL */
281 typedef struct export_var_struct export_struc;
282
283
284=== modified file 'Percona-Server/storage/innodb_plugin/lock/lock0lock.c'
285--- Percona-Server/storage/innodb_plugin/lock/lock0lock.c 2012-05-09 04:14:12 +0000
286+++ Percona-Server/storage/innodb_plugin/lock/lock0lock.c 2012-10-12 13:06:39 +0000
287@@ -5447,8 +5447,13 @@
288 return(DB_SUCCESS);
289 }
290
291- if (thr && thr_get_trx(thr)->fake_changes && mode == LOCK_X) {
292- mode = LOCK_S;
293+ if (UNIV_UNLIKELY((thr && thr_get_trx(thr)->fake_changes))) {
294+ if (!srv_fake_changes_locks) {
295+ return(DB_SUCCESS);
296+ }
297+ if (mode == LOCK_X) {
298+ mode = LOCK_S;
299+ }
300 }
301
302 heap_no = page_rec_get_heap_no(rec);
303@@ -5528,8 +5533,13 @@
304 return(DB_SUCCESS);
305 }
306
307- if (thr && thr_get_trx(thr)->fake_changes && mode == LOCK_X) {
308- mode = LOCK_S;
309+ if (UNIV_UNLIKELY((thr && thr_get_trx(thr)->fake_changes))) {
310+ if (!srv_fake_changes_locks) {
311+ return(DB_SUCCESS);
312+ }
313+ if (mode == LOCK_X) {
314+ mode = LOCK_S;
315+ }
316 }
317
318 heap_no = page_rec_get_heap_no(rec);
319
320=== modified file 'Percona-Server/storage/innodb_plugin/srv/srv0srv.c'
321--- Percona-Server/storage/innodb_plugin/srv/srv0srv.c 2012-09-14 12:19:29 +0000
322+++ Percona-Server/storage/innodb_plugin/srv/srv0srv.c 2012-10-12 13:06:39 +0000
323@@ -166,6 +166,10 @@
324
325 UNIV_INTERN ulonglong srv_changed_pages_limit = 0;
326
327+/** When TRUE, fake change transcations take S rather than X row locks.
328+ When FALSE, row locks are not taken at all. */
329+UNIV_INTERN my_bool srv_fake_changes_locks = TRUE;
330+
331 /* if TRUE, then we auto-extend the last data file */
332 UNIV_INTERN ibool srv_auto_extend_last_data_file = FALSE;
333 /* if != 0, this tells the max size auto-extending may increase the

Subscribers

People subscribed via source and target branches