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.)