MySQL Bugs: #45701: Falcon doesn't allow ALTER TABLE to lock more than one table.

Bug #45701	Falcon doesn't allow ALTER TABLE to lock more than one table.
Submitted:	24 Jun 2009 12:57	Modified:	26 May 2010 17:48
Reporter:	Dmitry Lenev	Email Updates:
Status:	Unsupported
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.1-fk	OS:	Any
Assigned to:	Kevin Lewis	Target Version:
Tags:	F_HANDLER
Triage:	Needs Triage: D2 (Serious)

Description:
Unlike other storage engines Falcon assumes that ALTER TABLE can't lock more than one
table. StorageInterface::external_lock() method emits error when ALTER TABLE is changed
in such way that it starts doing this.

Unfortunately new foreign keys implementation requires such functionality to be supported
by engine, e.g. in new implementation ALTER TABLE ... ADD CONSTRAINT will try to open and
lock both child (altered) and parent tables.

How to repeat:
Change ALTER TABLE code to lock several tables and observe error emitted.
Alternatively inspect code in StorageInterface::external_lock().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77257

2746 Kevin Lewis	2009-06-25
      Bug#45701 - If a table has two foreign key relationships with one other table, an
alter table that drops one of the FKs will call external_lock twice for each table. And
if a table has a foreign key relationship with another table, an alter that creates
another foreign key relationship between the two tables will also call external_lock
twice for each table.
      
      So it is necessary for Falcon to support multiple calls to external_lock by the
same connection to the same table.  This was not needed before the Foriegn Key
implementation.  
      
      So the current Connection pointer is sent into Table::SetAlter() and stored in
Table::alterConnection.   And alterIsActive is now an int instead of a boolean.  If the
same connection locks the table a second time, alterIsActive is incremented and
alterConnection remains set.  It is not set to NULL until alterIsActive is decremented to
zero.

Dmitry Lenev wrote; 
I have applied your patch to mysql-6.1-fk-stage tree, built
server (I am on Linux so I am not affected by the above problem
with InnoDB), and re-run my tests for Falcon engine.

I can confirm that now it successfully passes most of the
tests for milestone 13 of WL#148 "Foreign keys".

Patch pushed to mysql-6.0-falcon-team

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77699

2723 Dmitry Lenev	2009-07-02
      Bug#45701 - If a table has two foreign key relationships with one other table, an
      alter table that drops one of the FKs will call external_lock twice for each table.
      And if a table has a foreign key relationship with another table, an alter that
      creates another foreign key relationship between the two tables will also call
      external_lock twice for each table.
      
      So it is necessary for Falcon to support multiple calls to external_lock by the
      same connection to the same table.  This was not needed before the Foreign Key
      implementation.
      
      So the current Connection pointer is sent into Table::SetAlter() and stored in
      Table::alterConnection.   And alterIsActive is now an int instead of a boolean.
      If the same connection locks the table a second time, alterIsActive is incremented
      and alterConnection remains set.  It is not set to NULL until alterIsActive is
      decremented to zero.
      
      Manually applied patch from Falcon team tree to mysql-6.1-fk tree.