From: Claudio Nanni
Date: March 16 2009 11:24am
Subject: Re: InnoDB - CREATE INDEX - Locks table for too long
List-Archive: http://lists.mysql.com/mysql/216739
Message-Id: <53bcf3a60903160424q67bdf64eqb9e38f5092c5fc2c@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=001636c5bcf92e811904653ab399

--001636c5bcf92e811904653ab399
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi,

I am using your procedure on MyISAM tables now and works but RENAME does not
work with locked tables,

(anyway it is already an atomic operation)

=BARON================================================

Try something like this:

create table new_table like old_table;
alter table new_table add key(.....);
insert into new_table select * from old_table where <primary_key>
between ? and ?;

Repeat the last statement until nearly finished, then:

lock tables new_table write, old_table write;
insert into new_table select * from old_table where <primary_key> > ?;
rename table old_table backup, new_table old_table;
unlock tables;

================================================

so the above procedure fails unless I swap like this:

---------------------

...

...

unlock tables;

rename table old_table backup, new_table old_table;

---------------------

The problem is of course that it is no more atomic.

I also thought of "SELECT for UPDATE" that locks the records but does not
prevent from inserting new ones,

any suggestions?

Thanks

Clauido

--001636c5bcf92e811904653ab399--