Using Lock-Free Schema Change

Scenarios

You can enable the lock-free schema change feature on the console. Once it is enabled, you can query, stop, and clean up lock-free schema change tasks and download task logs to support automated change management and efficient troubleshooting. Logs of lock-free schema change tasks are stored on OBS and retained for 30 days. Be sure to download required logs within this period, as expired logs will be automatically deleted.

During a lock-free schema change, temporary tables are generated and occupy storage space. To avoid unnecessary storage costs, verify that your workloads are running properly after the change and delete the temporary tables promptly. If you terminate a lock-free schema change task, the task may have already completed successfully. Always check whether the change was applied and verify if any temporary tables remain. Delete any leftover temporary tables to avoid additional storage fees.

Prerequisites

• DBA Assistant is enabled.
• SQL audit is enabled. For details, see Enabling SQL Audit.
• The value of the instance parameter rds_global_sql_log_bin has been changed to ON. For details, see Modifying Instance Parameters.

Constraints

• Instance changes and instance backups are not allowed during lock-free changes.
• Ensure that your instance has enough available storage.
• Running lock-free changes under heavy workloads can increase the risk of failure. Perform the change during off-peak hours.
• The table selected for a lock-free change cannot contain foreign keys. Tables with names that differ only in case are not supported. For other constraints, see Overview of Lock-Free Schema Change.

Procedure

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region.
3. On the Instances page, click the target instance name.
4. In the navigation pane, choose Lock-Free Schema Change under DBA Assistant.
5. Click Create Lock-Free Change Task.
6. In the displayed dialog box, select a database, enter change SQL statements, and select Clean Up Temporary Tables as needed.
   Figure 1 Creating a lock-free change task
   

   Table 1 Lock-free change task parameters

   Parameter	Description
   Database Name	Name of the database where target DDL statements will be executed.
   Schema Change SQL	A maximum of 10 DDL statements can be executed in a batch. Separate DDL statements with semicolons (;). Multiple ALTER statements cannot modify the same table.
   Clean Up Temporary Tables	After the task is complete, the system creates a temporary table and does not delete it. Select this option to automatically delete the temporary table once the task finishes.

7. Click OK.
8. After the task is initiated, you can view the execution status of these DDL statements in the lock-free change task list. You can also perform the following operations:
   • Terminating the task

     To terminate an ongoing change task, click Terminate in the Operation column. In the displayed dialog box, confirm the task information, enter YES, and click OK to terminate the task.

   • Deleting the task

     Click Delete in the Operation column. In the displayed dialog box, confirm the task information, enter YES, and click OK to delete the task.

   • Downloading logs

     Choose More > Download Logs in the Operation column to download the log file of the task.

   • Cleaning up temporary tables

     Choose More > Clean Up Temporary Tables in the Operation column. In the displayed dialog box, confirm the task information, enter YES, and click OK to delete the temporary tables generated by the task.