10

I have a question about the ALTER TABLE ... ADD COLUMN DDL statement.

On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Shouldn't any DML statement that performs a write be queued until the ALTER TABLE operation finishes?

I'm posting this question because I've been asked to perform some test to verify whether it is possible to run ALTER TABLE ... ADD COLUMN on a live Production database in business hours, on a heavily-used database, on tables with several million rows -- which I find very ill-advised. Even if ALTER TABLE does not place a lock on the table, it will have to wait until any connection is not using the table anymore (due to the connection placing a metadata lock), which may happen much much later.
EDIT: Apparently this evaluation was too pessimistic. I've been doing several tests with mysqlslap performing heavy operations on the table (INSERT, UPDATE, DELETE statements, and SELECT statements with LIKE to avoid using indexes) on 150 simulated concurrent connections while ALTER TABLE ... ADD COLUMN runs; profiling shows metadata locks but with short waiting times (1 sec each), and table alteration completes in around 30 minutes, compared to 10 minutes with no SQL statements running. While this is satisfying, on the other hand I'd like to know whether it is safe to assume that DDL statements are non-blocking.

(It is probably worth of note that there is an Instant ADD COLUMN feature on InnoDB, which allows instant addition of a column to the table (under specific constraints), but it is not available before v10.3.2.)

1
  • I don't know about read operations. MyISAM is no better than InnoDB -- probably worse.
    – Rick James
    Jul 5, 2018 at 0:41

2 Answers 2

4
+100

Yes, it locks the table. From the docs on MySQL 8,

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

And from the docs you linked, it's pretty explicit

With instant ADD COLUMN, you can enjoy all the benefits of structured storage without the drawback of having to rebuild the table.

As you've stated, you're on 10.2. So it looks like adding a column will require rebuilding the whole table.

As to what happens when you can't receive a lock,

I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Yes, that's generally what happens during a lock, but be aware that's not always what happens. Sometimes statements and transactions give up waiting. Sometimes backends and pools get reaped when they're stuck waiting. It's always safer to do this during downtime, to have timeouts, and to catch errors from libraries when the timeouts expire. So long as you're using transactions, things rollback if something is triggered and can't get it's lock before timeout -- all will be kosher.

5
  • I'm confused by your sentence "Yes, that's generally what happens during a lock". Are you saying that, generally, INSERTs complete successfully when a table is locked? Isn't this a contradiction?
    – dr_
    Jul 9, 2018 at 7:43
  • On the other hand, I've noticed that some INSERTs hang when ALTER TABLE is running. This makes sense -- however, I'd have expected all of them hang, not just a few.
    – dr_
    Jul 9, 2018 at 8:50
  • They don't complete, they wait. Lock a table and try to INSERT -- I would expect the INSERT to wait in a queue and for a timeout countdown to start. If it never gets the lock, we would say that it's "deadlocked" waiting. (Usually at some point it'll time out) Jul 9, 2018 at 15:25
  • So, an ALTER TABLE is not atomic, and releases the lock to allow for INSERTs to be executed in between?
    – dr_
    Jul 9, 2018 at 15:30
  • No, it is atomic. It locks the table. Then the it unlocks the table. Then other things waiting for a lock, row level or table level can obtain their locks and process. Jul 9, 2018 at 16:21
4

it actually depends on the specific alter table, but in any case I would suggest looking into online schema change tools like ghost (from GitHub) or pt-online-schema-change (by percona).

Both tools do the change on a separate table and switch with the original at the end - you can even keep the old one for fast rollback.

It’s a much safer route especially for heavy loaded tables

2
  • 1
    I've tried pt-online-schema-change but it doesn't manage concurrency very well -- either it exits because the number of threads exceeds the threshold, or (increasing the number of threads) causes mysqlslap to go into deadlock. I found the standard ALTER TABLE behaving better.
    – dr_
    Jul 17, 2018 at 9:46
  • 2
    For really loaded ghost has a better behavior - there are many things you just can’t do with regular alter - without locking the table for a long period.
    – cohenjo
    Jul 17, 2018 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.