ALTER TABLE without locking the table?

by

The only other option is to do manually what many RDBMS systems do anyway…
– Create a new table

You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would cause a slow down, it’s not a lock…)

Once finished, change the name of the source table, then change the name of the new table. Preferably in a transaction.

Once finished, recompile any stored procedures, etc that use that table. The execution plans will likely no longer be valid.

EDIT:

Some comments have been made about this limitation being a bit poor. So I thought I’d put a new perspective on it to show why it’s how it is…

  • Adding a new field is like changing one field on every row.
  • Field Locks would be much harder than Row locks, never mind table locks.
  • You’re actually changing the physical structure on the disk, every record moves.
  • This really is like an UPDATE on the Whole table, but with more impact…

More Related Contents:

  1. Alter a MySQL column to be AUTO_INCREMENT
  2. You can’t specify target table for update in FROM clause
  3. How to get next/previous record in MySQL?
  4. How do I make a row generator in MySQL?
  5. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  6. MySQL number of items within “in clause”
  7. Error 1046 No database Selected, how to resolve?
  8. What’s the difference between VARCHAR and CHAR?
  9. Safely rename tables using serial primary key columns
  10. Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?
  11. Delete column from SQLite table
  12. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  13. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  14. single fixed table with multiple columns vs flexible abstract tables
  15. MySQL remove duplicates from big database quick
  16. mysql, iterate through column names
  17. Postgres manually alter sequence
  18. How to delete from select in MySQL?
  19. How to delete Duplicates in MySQL table
  20. How to ORDER BY a SUM() in MySQL?
  21. mySQL Returning the top 5 of each category
  22. Selecting Random Rows in MySQL
  23. Error: The used SELECT statements have a different number of columns
  24. How to Optimize Queries in a Database – The Basics
  25. Any way to achieve fulltext-like search on InnoDB
  26. Can I use a MySQL database with an App Engine application
  27. How to combine aggregate functions in MySQL?
  28. I want to copy table contained from one database and insert onto another database table
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment