CODEWITHSUNDEEP
mysqlalter-table
Rahul
Rahul

Reputation: 870

Does mysql lock table for writes while adding new column with default value

If I execute below sql, will mysql lock complete table (no write can take place till the below operation completes)? in my opinion yes, as it has to modify all rows and add default values. However this operation took only 4 second on a table with 1 million rows. 

ALTER TABLE `user` ADD COLUMN `col1` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1;

However when I tried to break the operation into 2 separate queries (trying to reduce table lock time based on my incomplete understanding) the second query ran for more than 100 seconds

ALTER TABLE `user` ADD COLUMN `col1` TINYINT(1) UNSIGNED; // happens instantly
UPDATE user set col1 = true ; // takes > 100 seconds

So I wanted to know why so much difference in the execution time when the ultimate result is same.

Upvotes: 3

Views: 5425

Answers (2)

Rahul
Rahul

Reputation: 870

Found the answer on https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ with INSTANT algorithm, innoDB just adds new column into in table metadata along with default value. Individual rows are not modified, thus saving time in the first query that I mentioned. Al read operation uses the default value from table metadata, any write operation reconstruct the row and adds newly added column in the row itself. This breaking the single ALTER operation into 2 was slow as the latter was modifying each individual row

Upvotes: 2

Paul Spiegel
Paul Spiegel

Reputation: 31812

I can't say for sure, but here is my guess (for the InnoDB engine and DYNAMIC/COMPACT row format):

How are NULL stored? This is what the documentation says:

The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 16 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. [...]

If you already have (say) three nullable columns, then five bits in the mentioned bit-vector are unused. We can assume that they are preset to represent NULLs (that would make sense). When you now add a nullable column with DEAFAULT NULL (which is default setting for nullable columns), then no data have to be changed in the table - only table meta data. This would explain, why the ALTER operation is "instant".

The second part (the UPDATE statement) will not just change the content of every row in the table, but also their size. That means, the engine has to find a new space for every row. If you ever have watched a defragmentation process, you might know that it would often be much faster just to rewrite the entire data.

Upvotes: 0

Related Questions