Have you ever made a change to a table and the next thing you know you're facing a table lock, queries have stopped running, and you've got an outage on your hands? To help you out with this, we created online alter operations in Calgary which prevent these table locks from happening every time you make a change to a table, which in turn can prevent those dreaded outages. How is this possible? Well, online alter operations prevent queries from being dropped by only very briefly locking a table when a change is made. Here's how it works:
- An empty table is created with the same schema as the table that is to be altered.
- The ALTER operation is performed on the new, empty table.
- Triggers are added on the live table to copy any changes that are made against the live table to the new table.
- All data is copied in chunks, from the live table to the new table.
- Once all of the data is copied, the tables are swapped. This requires a very brief table lock.
- The old live table is dropped.
To learn more about how online alter operations work, check out our latest KB on the subject: KB0539427: Online alter changes.
On deck for next week: the latest top ten solutions column!
