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

[Laurynas Biveinis]

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

[Stewart Smith (stewart)]

review: Approve

=== modified file 'Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result'
--- Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result	2011-11-24 02:00:54 +0000
+++ Percona-Server/mysql-test/r/percona_innodb_fake_changes_locks.result	2012-10-12 13:06:39 +0000
@@ -1,7 +1,9 @@
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 1);
 # Verifying that X_LOCK not acquired
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1);
 SET autocommit=0;
 SET innodb_fake_changes=1;
 BEGIN;
@@ -15,5 +17,67 @@
 a
 1
 ROLLBACK;
+# Confirm no lock wait timeouts when innodb_locking_fake_changes=0
+SET GLOBAL innodb_locking_fake_changes=0;
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+a
+1
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+SELECT * FROM t1 LOCK IN SHARE MODE;
+a
+3
+ROLLBACK;
+ROLLBACK;
+# Confirm that fake UPDATE does not hold a lock
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+a
+1
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+ROLLBACK;
+ROLLBACK;
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+ROLLBACK;
+ROLLBACK;
+# Confirm that fake INSERT does not hold a lock
+BEGIN;
+INSERT INTO t1 VALUES(4);
+BEGIN;
+INSERT INTO t1 VALUES(4);
+ROLLBACK;
+ROLLBACK;
+# Confirm that fake DELETE does not hold a lock
+BEGIN;
+DELETE FROM t1 WHERE a=1;
+BEGIN;
+DELETE FROM t1 WHERE a=1;
+ROLLBACK;
+ROLLBACK;
+# Confirm that fake REPLACE does not hold a lock
+BEGIN;
+REPLACE INTO t2 VALUES(1, 2);
+BEGIN;
+REPLACE INTO t2 VALUES(1, 2);
+ROLLBACK;
+ROLLBACK;
 SET innodb_fake_changes=default;
-DROP TABLE t1;
+SET GLOBAL innodb_locking_fake_changes=default;
+# Verify that the fake changes to t1 did not leak through
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+should_be_1
+1
+# Verify that the fake changes to t2 did not leak through
+CHECK TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	check	status	OK
+should_be_1
+1
+DROP TABLE t1, t2;

=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_debug.result'
--- Percona-Server/mysql-test/r/percona_server_variables_debug.result	2012-10-12 01:13:44 +0000
+++ Percona-Server/mysql-test/r/percona_server_variables_debug.result	2012-10-12 13:06:39 +0000
@@ -121,6 +121,7 @@
 INNODB_IO_CAPACITY
 INNODB_KILL_IDLE_TRANSACTION
 INNODB_LAZY_DROP_TABLE
+INNODB_LOCKING_FAKE_CHANGES
 INNODB_LOCKS_UNSAFE_FOR_BINLOG
 INNODB_LOCK_WAIT_TIMEOUT
 INNODB_LOG_BLOCK_SIZE

=== modified file 'Percona-Server/mysql-test/r/percona_server_variables_release.result'
--- Percona-Server/mysql-test/r/percona_server_variables_release.result	2012-09-05 22:27:47 +0000
+++ Percona-Server/mysql-test/r/percona_server_variables_release.result	2012-10-12 13:06:39 +0000
@@ -118,6 +118,7 @@
 INNODB_IO_CAPACITY
 INNODB_KILL_IDLE_TRANSACTION
 INNODB_LAZY_DROP_TABLE
+INNODB_LOCKING_FAKE_CHANGES
 INNODB_LOCKS_UNSAFE_FOR_BINLOG
 INNODB_LOCK_WAIT_TIMEOUT
 INNODB_LOG_BLOCK_SIZE

=== modified file 'Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test'
--- Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test	2011-11-24 02:00:54 +0000
+++ Percona-Server/mysql-test/t/percona_innodb_fake_changes_locks.test	2012-10-12 13:06:39 +0000
@@ -1,24 +1,121 @@
 --source include/have_innodb_plugin.inc
 
 --disable_warnings
-DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t1, t2;
 --enable_warnings
 
---echo # Verifying that X_LOCK not acquired
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
 INSERT INTO t1 VALUES (1);
+let $t1_checksum_1= `CHECKSUM TABLE t1 EXTENDED`;
+
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1, 1);
+let $t2_checksum_1= `CHECKSUM TABLE t2 EXTENDED`;
+
 --connect (conn1,localhost,root,,)
 --connection conn1
+--echo # Verifying that X_LOCK not acquired
 SET autocommit=0;
 SET innodb_fake_changes=1;
 BEGIN;
 SELECT * FROM t1 FOR UPDATE;
+
 --connection default
 SET innodb_lock_wait_timeout=3;
 --error 1205
 UPDATE t1 SET a=2;
 SELECT * FROM t1 LOCK IN SHARE MODE;
---connection conn1
-ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+
+--echo # Confirm no lock wait timeouts when innodb_locking_fake_changes=0
+SET GLOBAL innodb_locking_fake_changes=0;
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+
+--connect (conn2,localhost,root,,)
+--connection conn2
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+SELECT * FROM t1 LOCK IN SHARE MODE;
+ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+
+--echo # Confirm that fake UPDATE does not hold a lock
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+
+--connection conn2
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+
+--connection conn2
+BEGIN;
+UPDATE t1 SET a=3 WHERE a=1;
+ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+--echo # Confirm that fake INSERT does not hold a lock
+BEGIN;
+INSERT INTO t1 VALUES(4);
+
+--connection conn2
+BEGIN;
+INSERT INTO t1 VALUES(4);
+ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+--echo # Confirm that fake DELETE does not hold a lock
+BEGIN;
+DELETE FROM t1 WHERE a=1;
+
+--connection conn2
+BEGIN;
+DELETE FROM t1 WHERE a=1;
+ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+--echo # Confirm that fake REPLACE does not hold a lock
+BEGIN;
+REPLACE INTO t2 VALUES(1, 2);
+
+--connection conn2
+BEGIN;
+REPLACE INTO t2 VALUES(1, 2);
+ROLLBACK;
+
+--connection conn1
+ROLLBACK;
+
 SET innodb_fake_changes=default;
-DROP TABLE t1;
+SET GLOBAL innodb_locking_fake_changes=default;
+--echo # Verify that the fake changes to t1 did not leak through
+CHECK TABLE t1;
+let $t1_checksum_2= `CHECKSUM TABLE t1 EXTENDED`;
+--disable_query_log
+eval SELECT "$t1_checksum_1" LIKE "$t1_checksum_2" AS should_be_1;
+--enable_query_log
+
+--echo # Verify that the fake changes to t2 did not leak through
+CHECK TABLE t2;
+let $t2_checksum_2= `CHECKSUM TABLE t2 EXTENDED`;
+--disable_query_log
+eval SELECT "$t2_checksum_1" LIKE "$t2_checksum_2" AS should_be_1;
+--enable_query_log
+
+--connection default
+--disconnect conn1
+--disconnect conn2
+DROP TABLE t1, t2;

=== modified file 'Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc'
--- Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc	2012-09-14 12:19:29 +0000
+++ Percona-Server/storage/innodb_plugin/handler/ha_innodb.cc	2012-10-12 13:06:39 +0000
@@ -12138,6 +12138,13 @@
   NULL, NULL, 0, 0, 1024, 0);
 #endif /* UNIV_DEBUG */
 
+static MYSQL_SYSVAR_BOOL(locking_fake_changes, srv_fake_changes_locks,
+  PLUGIN_VAR_NOCMDARG,
+  "###EXPERIMENTAL### if enabled, transactions will get S row locks instead "
+  "of X locks for fake changes.  If disabled, fake change transactions will "
+  "not take any locks at all.",
+  NULL, NULL, TRUE);
+
 static struct st_mysql_sys_var* innobase_system_variables[]= {
   MYSQL_SYSVAR(page_size),
   MYSQL_SYSVAR(log_block_size),
@@ -12239,6 +12246,7 @@
   MYSQL_SYSVAR(pass_corrupt_table),
   MYSQL_SYSVAR(lazy_drop_table),
   MYSQL_SYSVAR(fake_changes),
+  MYSQL_SYSVAR(locking_fake_changes),
 #ifdef UNIV_DEBUG
   MYSQL_SYSVAR(trx_rseg_n_slots_debug),
 #endif /* UNIV_DEBUG */

=== modified file 'Percona-Server/storage/innodb_plugin/include/srv0srv.h'
--- Percona-Server/storage/innodb_plugin/include/srv0srv.h	2012-09-14 12:19:29 +0000
+++ Percona-Server/storage/innodb_plugin/include/srv0srv.h	2012-10-12 13:06:39 +0000
@@ -369,6 +369,10 @@
 /** Whether startup should be blocked until buffer pool is fully restored */
 extern ibool srv_blocking_lru_restore;
 
+/** When TRUE, fake change transcations take S rather than X row locks.
+When FALSE, row locks are not taken at all. */
+extern my_bool srv_fake_changes_locks;
+
 /** Status variables to be passed to MySQL */
 typedef struct export_var_struct export_struc;
 

=== modified file 'Percona-Server/storage/innodb_plugin/lock/lock0lock.c'
--- Percona-Server/storage/innodb_plugin/lock/lock0lock.c	2012-05-09 04:14:12 +0000
+++ Percona-Server/storage/innodb_plugin/lock/lock0lock.c	2012-10-12 13:06:39 +0000
@@ -5447,8 +5447,13 @@
 		return(DB_SUCCESS);
 	}
 
-	if (thr && thr_get_trx(thr)->fake_changes && mode == LOCK_X) {
-		mode = LOCK_S;
+	if (UNIV_UNLIKELY((thr && thr_get_trx(thr)->fake_changes))) {
+		if (!srv_fake_changes_locks) {
+			return(DB_SUCCESS);
+		}
+		if (mode == LOCK_X) {
+			mode = LOCK_S;
+		}
 	}
 
 	heap_no = page_rec_get_heap_no(rec);
@@ -5528,8 +5533,13 @@
 		return(DB_SUCCESS);
 	}
 
-	if (thr && thr_get_trx(thr)->fake_changes && mode == LOCK_X) {
-		mode = LOCK_S;
+	if (UNIV_UNLIKELY((thr && thr_get_trx(thr)->fake_changes))) {
+		if (!srv_fake_changes_locks) {
+			return(DB_SUCCESS);
+		}
+		if (mode == LOCK_X) {
+			mode = LOCK_S;
+		}
 	}
 
 	heap_no = page_rec_get_heap_no(rec);

=== modified file 'Percona-Server/storage/innodb_plugin/srv/srv0srv.c'
--- Percona-Server/storage/innodb_plugin/srv/srv0srv.c	2012-09-14 12:19:29 +0000
+++ Percona-Server/storage/innodb_plugin/srv/srv0srv.c	2012-10-12 13:06:39 +0000
@@ -166,6 +166,10 @@
 
 UNIV_INTERN ulonglong	srv_changed_pages_limit = 0;
 
+/** When TRUE, fake change transcations take S rather than X row locks.
+    When FALSE, row locks are not taken at all. */
+UNIV_INTERN my_bool	srv_fake_changes_locks = TRUE;
+
 /* if TRUE, then we auto-extend the last data file */
 UNIV_INTERN ibool	srv_auto_extend_last_data_file	= FALSE;
 /* if != 0, this tells the max size auto-extending may increase the