Table Alters in Calgary and later releases

[Lane Roberts]

Back in earlier versions of the platform (Pre-Calgary), whenever a customer admin would need to make an alter against a table, such as when adding a new column on an instance using a MySQL DB (Oracle databases do not lock tables by default and do not use online schema changes), these changes to the database would fully lock write access to the table during execution. In the case of sub-production systems or smaller production system that did not have much 24hr global activity, these full table locks were rarely a significant issue. However, with larger global customers and instances where many hundreds of users were using the live production instance on a 24/7 basis, fully locking a table against any additional traffic could create major problems as all the traffic continues to queue up, eventually running a node out of memory, and often causing an outage.

The short term solution to this problem was to pre-load any new columns directly into the database, before an update set was scheduled to be run, this would prevent any long running alters from locking the intended table for an extensive duration, and as a result prevented potential outages from occurring. However, this workaround was not only labor intensive and time consuming but could also only be performed internally by ServiceNow staff, making the workaround highly inconvenient for the customers affected by this issue.

As result of the above complications with performing table alters via the old 'Full Lock' method, a new way to implement table changes was introduced, that being onlineAlter. Unlike its predecessor, onlineAlter provides a nearly lock-free process when adding, modifying, or removing columns and when adding or dropping indexes.

The onlineAlter dictionary attribute is available with the Calgary release, and is enabled by default for all tables.

How the onlineAlter process works in Calgary and forward:

1. A new empty table is created with the same schema as the table that is to be altered. The 'onlineAlter' operation is performed on this new table.
2. Triggers are added on the live table to copy any changes that are made against the live table to the new table.
3. All data is copied in chunks, from the live table to the new table.
4. Once all of the data is copied, the tables are swapped. This requires a very very brief table-lock.
5. The old unused table is dropped.

While the new process is safer and far more stable it does use additional resources as a result, because of this, users logged into the node that is committing the update set may experience some decreased performance during the update set commit process. It is because of this that it is highly recommended for update sets that include any new columns to the Task or Children of Task tables, should be committed outside of peak business hours. Also, because of the implementation of flattening on the Task table starting with the Dublin release, any alters against Task and Children of Task will now take extensive amounts of time given the size of the flattened task table. However, it is good to note that an enhancement was included within Eureka that allows multiple alters to be batched as a single event, which will drastically decrease the commit times vs a Dublin instance.