Refactor the code that saves, updates and deletes rows from

vasild · vasild · commit 5eaf0f157d2a · 2012-05-10T15:43:57.000+03:00
mysql.innodb_table_stats and mysql.innodb_index_stats to prepare it
for being able to rename a table (update the corresponding rows).

* Remove the foreign key from innodb_index_stats because it is a source
  of various deadlocks. Also the absence of FK would allow users to
  temporarily store some rows in innodb_index_stats that do not have
  corresponding rows in innodb_table_stats.

* When saving table and its indexes stats - save the table stats and each
  index stat in a separate transaction to avoid deadlocks.

  The previous mode of operation for saving was:

  BEGIN;
  SELECT FOR UPDATE;
  if not found
    INSERT;
  else
    UPDATE;
  COMMIT;

  Which had the flaw that if there was no entry and two concurrent save
  functions were called, then one of them could get a deadlock, like this:

  t1: SELECT FOR UPDATE; (returns not found)
  t2: SELECT FOR UPDATE; (returns not found)
  t1: INSERT; (hangs, waiting on t2)
  t2: INSERT; (fails because of a deadlock)

  Another way of operation would be:

  BEGIN;
  INSERT;
  if duplicate
    UPDATE;
  COMMIT;

  but that could deadlock with a single DELETE of the corresponding entry in
  the table, like this:

  t1: INSERT; (returns duplicate)
  t2: DELETE; (hangs, waiting on t1)
  t1: UPDATE; (fails because of a deadlock)

  Another solution would have been to synchronize dict_stats_save() and
  dict_stats_delete_table_stats() with a simple mutex, but que_eval_sql()
  does not allow any latches to be held during its execution except
  dict_sys-&gt;mutex.

  So we change the save of table stats to:

  BEGIN;
  INSERT;
  COMMIT;
  if duplicate
    BEGIN;
    UPDATE;
    COMMIT;

  At worse, a row could get deleted after INSERT has returned a duplicate error
  and before UPDATE executes, but then the UPDATE will be noop because the
  WHERE clause will match nothing. The effect will be as if the delete executed
  after the end of the save operation.

* When deleting stats, delete the table and the index stats in separate
  transactions.
diff --git a/mysql-test/r/lowercase_table4.result b/mysql-test/r/lowercase_table4.result
@@ -30,17 +30,6 @@ Create Table	CREATE TABLE `Table2` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
 CONSTRAINT_CATALOG	def
-CONSTRAINT_SCHEMA	mysql
-CONSTRAINT_NAME	innodb_index_stats_ibfk_1
-UNIQUE_CONSTRAINT_CATALOG	def
-UNIQUE_CONSTRAINT_SCHEMA	mysql
-UNIQUE_CONSTRAINT_NAME	PRIMARY
-MATCH_OPTION	NONE
-UPDATE_RULE	RESTRICT
-DELETE_RULE	RESTRICT
-TABLE_NAME	innodb_index_stats
-REFERENCED_TABLE_NAME	innodb_table_stats
-CONSTRAINT_CATALOG	def
 CONSTRAINT_SCHEMA	test
 CONSTRAINT_NAME	fk1
 UNIQUE_CONSTRAINT_CATALOG	def
@@ -100,17 +89,6 @@ Create Table	CREATE TABLE `Customer` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
 CONSTRAINT_CATALOG	def
-CONSTRAINT_SCHEMA	mysql
-CONSTRAINT_NAME	innodb_index_stats_ibfk_1
-UNIQUE_CONSTRAINT_CATALOG	def
-UNIQUE_CONSTRAINT_SCHEMA	mysql
-UNIQUE_CONSTRAINT_NAME	PRIMARY
-MATCH_OPTION	NONE
-UPDATE_RULE	RESTRICT
-DELETE_RULE	RESTRICT
-TABLE_NAME	innodb_index_stats
-REFERENCED_TABLE_NAME	innodb_table_stats
-CONSTRAINT_CATALOG	def
 CONSTRAINT_SCHEMA	test
 CONSTRAINT_NAME	product_order_ibfk_1
 UNIQUE_CONSTRAINT_CATALOG	def
diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage.result b/mysql-test/suite/funcs_1/r/is_key_column_usage.result
@@ -100,8 +100,6 @@ def	mysql	PRIMARY	def	mysql	innodb_index_stats	database_name
 def	mysql	PRIMARY	def	mysql	innodb_index_stats	table_name
 def	mysql	PRIMARY	def	mysql	innodb_index_stats	index_name
 def	mysql	PRIMARY	def	mysql	innodb_index_stats	stat_name
-def	mysql	innodb_index_stats_ibfk_1	def	mysql	innodb_index_stats	database_name
-def	mysql	innodb_index_stats_ibfk_1	def	mysql	innodb_index_stats	table_name
 def	mysql	PRIMARY	def	mysql	innodb_table_stats	database_name
 def	mysql	PRIMARY	def	mysql	innodb_table_stats	table_name
 def	mysql	PRIMARY	def	mysql	ndb_binlog_index	epoch
diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result
@@ -70,7 +70,6 @@ def	mysql	PRIMARY	mysql	help_topic
 def	mysql	name	mysql	help_topic
 def	mysql	PRIMARY	mysql	host
 def	mysql	PRIMARY	mysql	innodb_index_stats
-def	mysql	innodb_index_stats_ibfk_1	mysql	innodb_index_stats
 def	mysql	PRIMARY	mysql	innodb_table_stats
 def	mysql	PRIMARY	mysql	ndb_binlog_index
 def	mysql	PRIMARY	mysql	plugin
diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result
@@ -19,7 +19,6 @@ def	mysql	PRIMARY	mysql	help_relation	PRIMARY KEY
 def	mysql	name	mysql	help_topic	UNIQUE
 def	mysql	PRIMARY	mysql	help_topic	PRIMARY KEY
 def	mysql	PRIMARY	mysql	host	PRIMARY KEY
-def	mysql	innodb_index_stats_ibfk_1	mysql	innodb_index_stats	FOREIGN KEY
 def	mysql	PRIMARY	mysql	innodb_index_stats	PRIMARY KEY
 def	mysql	PRIMARY	mysql	innodb_table_stats	PRIMARY KEY
 def	mysql	PRIMARY	mysql	ndb_binlog_index	PRIMARY KEY
diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result
@@ -19,7 +19,6 @@ def	mysql	PRIMARY	mysql	help_relation	PRIMARY KEY
 def	mysql	name	mysql	help_topic	UNIQUE
 def	mysql	PRIMARY	mysql	help_topic	PRIMARY KEY
 def	mysql	PRIMARY	mysql	host	PRIMARY KEY
-def	mysql	innodb_index_stats_ibfk_1	mysql	innodb_index_stats	FOREIGN KEY
 def	mysql	PRIMARY	mysql	innodb_index_stats	PRIMARY KEY
 def	mysql	PRIMARY	mysql	innodb_table_stats	PRIMARY KEY
 def	mysql	PRIMARY	mysql	ndb_binlog_index	PRIMARY KEY
@@ -55,7 +54,6 @@ def	mysql	PRIMARY	mysql	help_relation	PRIMARY KEY
 def	mysql	name	mysql	help_topic	UNIQUE
 def	mysql	PRIMARY	mysql	help_topic	PRIMARY KEY
 def	mysql	PRIMARY	mysql	host	PRIMARY KEY
-def	mysql	innodb_index_stats_ibfk_1	mysql	innodb_index_stats	FOREIGN KEY
 def	mysql	PRIMARY	mysql	innodb_index_stats	PRIMARY KEY
 def	mysql	PRIMARY	mysql	innodb_table_stats	PRIMARY KEY
 def	mysql	PRIMARY	mysql	ndb_binlog_index	PRIMARY KEY
diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result
@@ -19,11 +19,8 @@ INDEX_ID	NAME	POS
 14	POS	1
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 ID	FOR_NAME	REF_NAME	N_COLS	TYPE
-mysql/innodb_index_stats_ibfk_1	mysql/innodb_index_stats	mysql/innodb_table_stats	2	0
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
 ID	FOR_COL_NAME	REF_COL_NAME	POS
-mysql/innodb_index_stats_ibfk_1	database_name	database_name	0
-mysql/innodb_index_stats_ibfk_1	table_name	table_name	1
 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
 count(*)
 7
@@ -36,12 +33,9 @@ FOREIGN KEY (parent_id) REFERENCES parent(id)
 ON DELETE CASCADE) ENGINE=INNODB;
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 ID	FOR_NAME	REF_NAME	N_COLS	TYPE
-mysql/innodb_index_stats_ibfk_1	mysql/innodb_index_stats	mysql/innodb_table_stats	2	0
 test/constraint_test	test/child	test/parent	1	1
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
 ID	FOR_COL_NAME	REF_COL_NAME	POS
-mysql/innodb_index_stats_ibfk_1	database_name	database_name	0
-mysql/innodb_index_stats_ibfk_1	table_name	table_name	1
 test/constraint_test	parent_id	id	0
 INSERT INTO parent VALUES(1);
 SELECT name, num_rows, ref_count 
@@ -94,12 +88,9 @@ FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
 ON DELETE CASCADE) ENGINE=INNODB;
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
 ID	FOR_NAME	REF_NAME	N_COLS	TYPE
-mysql/innodb_index_stats_ibfk_1	mysql/innodb_index_stats	mysql/innodb_table_stats	2	0
 test/constraint_test	test/child	test/parent	2	1
 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
 ID	FOR_COL_NAME	REF_COL_NAME	POS
-mysql/innodb_index_stats_ibfk_1	database_name	database_name	0
-mysql/innodb_index_stats_ibfk_1	table_name	table_name	1
 test/constraint_test	id	id	0
 test/constraint_test	parent_id	newid	1
 INSERT INTO parent VALUES(1, 9);
diff --git a/mysql-test/suite/innodb/r/innodb_stats_drop_locked.result b/mysql-test/suite/innodb/r/innodb_stats_drop_locked.result
@@ -27,7 +27,7 @@ innodb_stats_drop_locked	CREATE TABLE `innodb_stats_drop_locked` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1
 DROP TABLE innodb_stats_drop_locked;
 Warnings:
-Warning	1205	Unable to delete statistics for table test.innodb_stats_drop_locked from mysql.innodb_table_stats or mysql.innodb_index_stats because the rows are locked: Lock wait timeout. They can be deleted later using DELETE FROM mysql.innodb_index_stats WHERE database_name = 'test' AND table_name = 'innodb_stats_drop_locked'; DELETE FROM mysql.innodb_table_stats WHERE database_name = 'test' AND table_name = 'innodb_stats_drop_locked';
+Warning	1205	Unable to delete statistics for table test.innodb_stats_drop_locked: Lock wait timeout. They can be deleted later using DELETE FROM mysql.innodb_index_stats WHERE database_name = 'test' AND table_name = 'innodb_stats_drop_locked'; DELETE FROM mysql.innodb_table_stats WHERE database_name = 'test' AND table_name = 'innodb_stats_drop_locked';
 SHOW TABLES;
 Tables_in_test
 COMMIT;
diff --git a/mysql-test/suite/perfschema/r/pfs_upgrade.result b/mysql-test/suite/perfschema/r/pfs_upgrade.result
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
diff --git a/storage/innobase/dict/dict0stats.cc b/storage/innobase/dict/dict0stats.cc
