From: Dmitry Lenev Date: May 6 2010 7:28pm Subject: Re: transactions and alter table in 5.5.4 v. 5.1 List-Archive: http://lists.mysql.com/internals/37902 Message-Id: <20100506192836.GB21688@bandersnatch> MIME-Version: 1.0 Content-Type: text/plain; CHARSET=US-ASCII Content-Transfer-Encoding: 7BIT Hello Zardosht! * Zardosht Kasheff [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, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification