Clearing DDL Table Locks with the Admin Console

Data Definition Language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. When an admin performs a DDL operation on a table, such as adding a field, modifying a linked field, or enabling history, a DDL table lock prevents other users from making changes until the current change is complete. This lock maintains the integrity of the table's structure and data.

Most DDL table locks will clear without intervention, so using the admin console to clear it with the steps below should be a last resort. There are cases, however, where table remains locked for longer than expected, such as more than a couple of hours.

Consider these best practices to avoid DDL table locks that remain longer than expected:

  • Avoid adding a new field to a table during business hours or any time when server load is high. This is especially true if the table has many records.
  • Avoid adding a field to history at the same time that you add it to the table, especially if the table has many records or when there is high server load. Note that new fields added to a table are automatically added to the History record table by default. You can edit the History field for a table and deselect the "Auto-add new fields to History" checkbox on the Options tab; this allows you to schedule the History update and minimize database workload.

For a lock that does require intervention to clear, complete the following steps:

  1. Take a thread dump and contact Support so a developer can evaluate the log and validate that no DDL operation is in progress. To take a thread dump, run the following command on the server as root or an administrator, substituting the directory where  Agiloft is installed for [AGILOFT_HOME]: [AGILOFT_HOME]/bin/ew-control -d tr

  2. Log into the Admin Console for the server and the run the following script through the bean shell, replacing Table with the knowledgebase table name:

    import com.supportwizard.db.ddll.*;

    DDLLockManager mgr = DDLLockManagerFactory.getDDLLockManager();

    mgr.clearAllLocks(new String[] {"Table"});

    The Table Name is different from the Table Label—it is the internal knowledgebase name of the table. Navigate to Setup > Table to determine the Table Name.

  • No labels