Background

Just sharing the recent DB migration issue.
We are about to add 2 columns in a table, and set default value with NOT NULL.

Original sequelize migration code will be like below.

    await queryInterface.addColumn(
      'lines',
      'ad_insertion_type',
      {
        type: Sequelize.DataTypes.STRING,
        allowNull: false,
        defaultValue: 'CSAI',
      },
    );
    await queryInterface.addColumn(
      'lines',
      'product_type',
      {
        type: Sequelize.DataTypes.STRING,
        allowNull: false,
        defaultValue: 'VOD',
      },
    );

if translated in raw sql will be like below

ALTER TABLE `lines` ADD `ad_insertion_type` VARCHAR(255) NOT NULL DEFAULT 'CSAI';
ALTER TABLE `lines` ADD `product_type` VARCHAR(255) NOT NULL DEFAULT 'VOD';

Concern

What makes us worried is that this change may cause table lock. I checked mysql doc and basically no affect on this ADD column DEFAULT clause after online DDL released from version after Mysql5.6.
From the doc we can tell that only column data type change will take lock on the migrated table.

I'm still curious what will happen to our DB migration. So I did some experiments on my laptop.

data

• insert around 1m rows of data into the tested table

steps

• run migration with sequelize
• run SHOW OPEN TABLES WHERE In_use=1; in TablePlus cli to check if any table been locked
• run random select clause to check if been locked
• run random update clause to check if been locked

The result is not what I expected. It showed that the tested table lines had been locked, but none of the query been blocked.
I can only assume that this is row lock instead of table lock.

followed up

I tried another migration. I changed column row, and update one row with new connection. It's been blocked, which proves that data type change causes lock.

REF

https://juejin.cn/post/7002180864008257543