Does
ALTER TABLE sample ADD COLUMN `hasItem` tinyint(1) DEFAULT NULL
lock the entire table?
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.
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 .
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With