Bug #49891 View DDL breaks REPEATABLE READ

The problem was that if a query accessing a view was blocked due to
conflicting locks on tables in the view definition, it would be possible
for a different connection to alter the view definition before the view
query completed. When the view query later resumed, it used the old view
definition. This meant that if the view query was later repeated inside
the same transaction, the two executions of the query would give different
results, thus breaking repeatable read. (The first query used the old
view definition, the second used the new view definition).

This bug is no longer repeatable with the recent changes to the metadata
locking subsystem (revno: 3040). The view query will no longer back-off
and release the lock on the view definiton. Instead it will wait for
the conflicting lock(s) to go away while keeping the view definition lock.
This means that it is no longer possible for a concurrent connection to
alter the view definition. Instead, any such attempt will be blocked.

In the case from the bug report where the same view query was executed
twice inside the same transaction, any ALTER VIEW from other connections
will now be blocked until the transaction has completed (or aborted).
The view queries will therefore use the same view definition and we will
have repeatable read.

Test case added to innodb_mysql_lock.test.
This patch contains no code changes.

1 files changed, 68 insertions, 0 deletions

diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index 36d09b4c411..c8ece729b19 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -209,6 +209,74 @@ disconnect con1;
 DROP TABLE t1;
 
 
+--echo #
+--echo # Bug#49891 View DDL breaks REPEATABLE READ
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v2;
+--enable_warnings
+
+CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb;
+CREATE TABLE t2 ( f1 INTEGER );
+CREATE VIEW v1 AS SELECT 1 FROM t1;
+
+connect (con2, localhost, root);
+connect (con3, localhost, root);
+
+--echo # Connection con3
+connection con3;
+LOCK TABLE t1 WRITE;
+
+--echo # Connection default
+connection default;
+START TRANSACTION;
+# This should block due to t1 being locked.
+--echo # Sending:
+--send SELECT * FROM v1
+
+--echo # Connection con2
+connection con2;
+--echo # Waiting for 'SELECT * FROM v1' to sync in.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table" AND info = "SELECT * FROM v1";
+--source include/wait_condition.inc
+# This should block due to v1 being locked.
+--echo # Sending:
+--send ALTER VIEW v1 AS SELECT 2 FROM t2
+
+--echo # Connection con3
+connection con3;
+--echo # Waiting for 'ALTER VIEW v1 AS SELECT 2 FROM t2' to sync in.
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM information_schema.processlist
+  WHERE state = "Waiting for table" AND info = "ALTER VIEW v1 AS SELECT 2 FROM t2";
+--source include/wait_condition.inc
+# Unlock t1 allowing SELECT * FROM v1 to proceed.
+UNLOCK TABLES;
+
+--echo # Connection default;
+connection default;
+--echo # Reaping: SELECT * FROM v1
+--reap
+SELECT * FROM v1;
+COMMIT;
+
+--echo # Connection con2
+connection con2;
+--echo # Reaping: ALTER VIEW v1 AS SELECT 2 FROM t2
+--reap
+
+--echo # Connection default
+connection default;
+DROP TABLE t1, t2;
+DROP VIEW v1;
+disconnect con2;
+disconnect con3;
+
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc