List:Internals« Previous MessageNext Message »
From:Dmitry Lenev Date:May 6 2010 7:28pm
Subject:Re: transactions and alter table in 5.5.4 v. 5.1
View as plain text  
Hello Zardosht!

* Zardosht Kasheff <zardosht@stripped> [10/05/06 21:35]:
> Hello all,
> I have noticed the following behavior to be different in 5.5.4 vs.
> 5.1. I would just like to confirm that it is intended.
> Suppose one thread does:
> begin;
> insert into foo values (1);
> and another thread does:
> alter table foo add index...
> In 5.1, the alter table statement would proceed to make calls into the
> handler of the storage engine, and the storage engine would handle
> issues. In 5.5.4, the alter table statement blocks until thread one's
> transaction commits or aborts.
> Is this the intended behavior?

Yes, behavior in 5.5.4 is intended. This change was introduced in
order to fix bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock"
and bug #37346 "innodb does not detect deadlock between update and
alter table".

Also note that in 5.1 for transactional table 'foo' ALTER statement
also has to wait for the transaction. It just happens later (than
in 5.5.4) during ALTER execution at the moment when it tries to read
row which was inserted by the first transaction.

Dmitry Lenev, Software Developer
Sun Microsystems DBG/MySQL,

Are you MySQL certified?
transactions and alter table in 5.5.4 v. 5.1Zardosht Kasheff6 May
  • Re: transactions and alter table in 5.5.4 v. 5.1Dmitry Lenev6 May