maria-developers team mailing list archive

[Elena Stepanova <elenst@xxxxxxxxxxxxxxxx>]

Hi,

On 8/14/2013 3:49 PM, Sergei Golubchik wrote:
> Hi!
>
> On Jul 10, Sergei Golubchik wrote:
> > In 5.5 we have ALTER ONLINE TABLE syntax, where ONLINE de facto means
> > "without copying the data".
> >
> > Now, after 5.6 merge we also have ALGORITHM=COPY|INPLACE and
> > LOCK=NONE|SHARED|EXCLUSIVE.
> >
> > I've mapped ONLINE to ALGORITHM=INPLACE, because that's what it means.
> >
> > But it's confusing, one can have an "inplace" operation under an
> > exclusive lock, if the engine wants it that way. And it won't be
> > "online" by any stretch of imagination. One can also technically have a
> > "copy" operation with a very permissive write-allow-write lock, and
> > it'll be as online as one can get it.
>
> So, I'm trying to make ONLINE to mean LOCK=NONE.
>
> And as I've realized, there can no possibly be a truly online, LOCK=NONE
> ALTER TABLE operation. For example,
>
>    create table t1 (b int);
>    alter table t1 modify b int default 5;
>
> is not online in MyISAM. Which, kind of, makes sense, because the server
> have to take an exclusive metadata lock to update the table definition.
>
> But for an InnoDB table such an operation is claimed to be online. Which
> is wrong, frm file still have to be updated, and an exclusive metadata
> lock is still needed:

I found it to be a documented limitation, although it took some browsing 
because the page has a rather strange name:

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

Inside of it, however, hides the section "Limitations of Online DDL", 
which, among other things, says:

"During each online DDL ALTER TABLE statement, regardless of the LOCK 
clause, there are brief periods at the beginning and end requiring an 
exclusive lock on the table (the same kind of lock specified by the 
LOCK=EXCLUSIVE clause). "

Of course, the definition of the word "brief" might also require 
reconsidering.

Regards,
/E

>    create table t1 (b int) engine=myisam;
>    alter table t1 modify b int default 5, lock=none;
>    ^^^ error, use LOCK=EXCLUSIVE
>
>    create table t1 (b int) engine=innodb;
>    start transaction;
>    insert t1 values (1);
>    -- in another connection
>    alter table t1 modify b int default 5, lock=none;
>    ^^^ no error. lock=none is accepted, but the statement hangs
>    -- in yet another connection
>    insert t1 values (2);
>    ^^^ hangs, blocked by the alter table, that is not quite "online"
>
> So, at best LOCK=NONE is inconsistent. But really, almost any ALTER will
> need an exclusive metadata lock, even if for an instant. So no ALTER
> TABLE can ever be really LOCK=NONE or ONLINE.
>
> Unless we redefine what "online" mean. If not, there's no choice but
> stop claming an "online alter" support and remove ONLINE and LOCK=NONE
> from the syntax.
>
> Opinions?
>
> Regards,
> Sergei
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-developers
> Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp