Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
833 views
in Technique[技术] by (71.8m points)

mysql - Alter table without locking the entire table

Does

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

lock the entire table?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...