MySQL 8.0 - As stated in MySQL Official Doc and MySQL Server Team, Alter table with Algorithm=INSTANT adds the column instantly without taking any lock.
But, It worked out different.
Test Setup - Table with 40M rows. Reads and writes (1000 TPS) on the table. Table schema is very simple.
field,type,null,key,default,extra
id,bigint(20),NO,PRI,,auto_increment
t_a,bigint(20),NO,MUL,,""
t_b,bigint(20),NO,MUL,,""
t_c,int(11),NO,"",1,""
Infra: AWS RDS MySQL 8 - Engine Version - 8.0.17
Test case: Adding a new column to the table.
Statement:
alter table table_name add column_name int, algorithm=instant;
Result:
mysql> alter table test_table add test_column int, algorithm=instant;
Query OK, 0 rows affected (36.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
It has taken ~40 secs. It has Blocked both Reads and Writes during the 40secs
Is this expected? Is there something I'm missing?