Skip to content

Instantly share code, notes, and snippets.

@ralexandr

ralexandr/non_blocking_alter_table.sql

Last active September 2, 2020 06:00
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ralexandr/18e4648fffd5520f9e5fcde4fd6a900e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ralexandr/18e4648fffd5520f9e5fcde4fd6a900e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ralexandr revised this gist Sep 2, 2020. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion non_blocking_alter_table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1 +1,3 @@
    ALTER TABLE `my_awesome_database`.`user_transaction` ADD FOREIGN KEY (`userId`) REFERENCES `my_awesome_database`.`user` (`id`), ALGORITHM=INPLACE, LOCK=NONE;
    ALTER TABLE `my_awesome_database`.`user_transaction`
    ADD FOREIGN KEY (`userId`) REFERENCES `my_awesome_database`.`user` (`id`),
    ALGORITHM=INPLACE, LOCK=NONE;
  2. ralexandr revised this gist Sep 2, 2020. 1 changed file with 0 additions and 8 deletions.
    8 changes: 0 additions & 8 deletions non_blocking_alter_table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1 @@
    Problem:

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

    In order to avoid blocking as much as possible, innoDB tables could be altered with 'inplace' algorithm and lock=none flag

    For example:

    ALTER TABLE `my_awesome_database`.`user_transaction` ADD FOREIGN KEY (`userId`) REFERENCES `my_awesome_database`.`user` (`id`), ALGORITHM=INPLACE, LOCK=NONE;
  3. ralexandr created this gist Sep 2, 2020.
    9 changes: 9 additions & 0 deletions non_blocking_alter_table.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,9 @@
    Problem:

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

    In order to avoid blocking as much as possible, innoDB tables could be altered with 'inplace' algorithm and lock=none flag

    For example:

    ALTER TABLE `my_awesome_database`.`user_transaction` ADD FOREIGN KEY (`userId`) REFERENCES `my_awesome_database`.`user` (`id`), ALGORITHM=INPLACE, LOCK=NONE;