ALTER TABLE without locking the table?

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.


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…

Leave a Comment