CODEWITHSUNDEEP
mysqlmariadb
Tulvan
Tulvan

Reputation: 55

mariadb alter table lock strategy

I am using MariaDB 10.1.9. Short version: What I really want to know for certain is if I can modify an indexed auto_increment field on an innodb table from int to bigint without locking the table?

Long version: Is there a detailed explanation of which ALTER TABLE operations require which lock level? The documentation just says "Depending on the required operation and the used storage engine, different lock strategies can be used for ALTER TABLE.". It doesn't provide a link to any details and each operation on the ALTER TABLE page does not specify it's required level.

From experimentation, I know ADD COLUMN does not require a lock. MODIFY COLUMN allows reads, but can it be manually set to allow writes? The MariaDB documentation says you can set the lock level, but if you don't set it restrictive enough, it will give an error - but it doesn't say what that error is. The current table column definition looks like

`Id` int(10) NOT NULL AUTO_INCREMENT
KEY `Id` (`Id`)

When I try

ALTER TABLE MyTable MODIFY MyField bigint AUTO_INCREMENT LOCK=NONE;

I just get a generic SQL syntax error. Even if I specify DEFAULT, I get an error, so I'm not sure how to use the LOCK - which I would expect the proper error to tell me when I have chosen an improper lock level.

Upvotes: 1

Views: 1880

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

The syntax...

alter_specification [, alter_specification] ...

... requires a comma

ALTER TABLE MyTable 
  MODIFY COLUMN MyField BIGINT AUTO_INCREMENT, -- comma here
  LOCK=NONE;

I'm guessing the error was not all that "generic" -- it should have said something about the right syntax to use near 'LOCK... which is your hint not that the quoted term is the beginning of the error, but rather that that the parser/lexer expected something other than the quoted value to occur at that position (because it was looking for the comma).

If the column you are altering is the primary key, a lock seems inevitable -- because the entire table should need rebuilding, including all the indexes, since the primary key "rides free" in all indexes, as it is what's used after a non-covering index lookup to actually find the rows matched by the index.

Upvotes: 2

Related Questions