Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Alter table without locking the entire table

Tags:

mysql

Does

ALTER TABLE sample ADD COLUMN `hasItem` tinyint(1) DEFAULT NULL 

lock the entire table?

like image 349
Manoj Avatar asked Feb 16 '16 05:02

Manoj


People also ask

How can I alter a table without locking it?

Adding a column to a table will no longer require table locks except possibly brief exclusive locks at the start and end of the operation. It should happen automatically, but to be sure set ALGORITHM=inplace and LOCK=none to your ALTER TABLE statement.

Does ALTER TABLE lock the table?

In MySQL 5.6, LOCK is not supported for ALTER TABLE operations on partitioned tables. To force use of the COPY algorithm for an ALTER TABLE operation that would otherwise not use it, enable the old_alter_table system variable or specify ALGORITHM=COPY .

Does view lock the table?

Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically.


1 Answers

Short answer: For MySQL < 5.6 locks are required. From 5.6 on, and using InnoDB, locks are not required for many ALTER TABLE operations including adding a column.


If you're using MySQL 5.5 or older, it will get a read lock for the whole operation and then a brief write lock at the end.

From the MySQL documentation for ALTER TABLE...

In most cases, ALTER TABLE makes a temporary copy of the original table... While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready...

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock.

Which is to say, when adding a column it read locks the table for most of the operation, then gets a write lock at the end.


MySQL 5.6 added the Online DDL to InnoDB which speeds up and improves many things such as altering tables and indexes. Adding a column to a table will no longer require table locks except possibly brief exclusive locks at the start and end of the operation.

It should happen automatically, but to be sure set ALGORITHM=inplace and LOCK=none to your ALTER TABLE statement.

There is one exception...

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY.

like image 174
Schwern Avatar answered Sep 20 '22 03:09

Schwern