ApsaraDB for OceanBase:Lock-free schema changes

Background information

Generally, when you change the schema of a table, the table must be locked, which will block business requests. To resolve this issue, ODC V4.2.0 and later provide the lock-free schema change feature. This topic describes how to submit a ticket to perform a lock-free schema change task.

Technical mechanism

1. Create a temporary table.

2. Change the schema of the temporary table.

3. Copy all data.

4. Synchronize incremental data.

5. Verify the data consistency.

6. Kill the session.

7. Switch to the new table.

Execution process

1. Initiate a lock-free schema change ticket.

2. Perform a precheck on the entered SQL statement.

3. Approve the task process.

4. Execute the lock-free schema change task.

Operations supported for lock-free schema changes

Considerations

• Make sure that the database has sufficient disk space.

• The table contains a primary key or a non-null unique key, and does not contain a foreign key.

• If you execute other DDL operations on a table on which a lock-free schema change task is being performed, the task will fail.

• The table name does not exceed 54 characters in length in MySQL mode of OceanBase Database.

• When ODC connects to an instance of OceanBase Database Community Edition, the data source configuration information must contain the cluster name and a sys tenant account.

• In MySQL mode of OceanBase Database of a version earlier than V4.3.0, ODC locks the user and closes the session to prevent data writes during table name switching.

• If you execute other DDL operations on a table on which a lock-free schema change task is being performed, the task will fail.

Procedure

Create a lock-free schema change task

This topic uses OceanBase community edition and enterprise edition of OMS as examples, demonstrating the scenario of changing the column type of birth in the table student to date. The project name used in this example is odc_4.2.0, the database name is odc_test, and the table name is student.

1. In the SQL window, write an SQL statement to create a table named student in the odc_test database.

   

   CREATE TABLE `odc_test`.`student` (
   `name` varchar(120) NULL,
   `id` int NOT NULL,
   `birthday` date NULL,
   `create_time` date NULL,
   CONSTRAINT `key_id` PRIMARY KEY (`id`)
   ) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci  PARTITION BY RANGE(`id`) (
   PARTITION `p1` VALUES LESS THAN (10000),
   PARTITION `p2` VALUES LESS THAN (20000)
   );

2. Create a lock-free schema change task.

   a. On the Ticket tab, choose Lock-free Schema Change > Create New.

   

   b. In the Create Online Schema Change panel, configure the following parameters.

   

   Parameter	Description
   Database	Select the database to which the database change task belongs.
   Lock User	If you specify to lock a user, ODC will lock the specified database account and close all sessions of this account before table name switching. This is to reduce the impact on businesses while ensuring data consistency during table name switching. If no account is specified, ODC will not lock any account or close any session. In this case, you must manually ensure data consistency during table name switching. Note You can not execute the lock-free schema change tasks of a table and its related table simultaneously.
   Change Definition	CREATE TABLE: If other syntaxes are not supported in OceanBase Database, you need to use the CREATE statement. ALTER TABLE: In OceanBase Database V4.0.0 and later, you can use the ALTER statement in ALTER TABLE OFFLINE mode.
   SQL Content	Enter the SQL script in the editing section.
   Switch Table Settings	Switch to the target table after data consistency is verified. Table Locking Timeout Period: The table is locked during table switching. If table switching is not completed within the timeout period, the switching fails. Retries: If table switching is not completed within the specified period of time, a retry can be automatically initiated. Cleanup Strategy for Source Table Upon Completion: You can select Renaming tasks are not handled to rename the source table without deleting it, or select Delete Now to delete the source table after the lock-free schema change task is completed.
   Task Settings	Select Execute Now or Scheduled Execution. The lock-free schema change tool provides two error handling methods: Abort Task: This is the default option. When you select this option, the task is aborted if an error occurs when you run the script. Ignore Error and Continue: When you select this option, the system skips the erroneous statement where an error occurs and continues to execute other statements in the script. Select Automatic Switch or Manual Switch.
   Remarks	Optional. Additional information about the task, which cannot exceed 200 characters in length.

   c. Click Create.

   d. After the lock-free schema change task is generated, choose Tickets > Lock-free Schema Change and view the task information in the task list.

View a lock-free schema change task

View task information

1. In the lock-free schema change task list on the Ticket tab, click View in the Actions column of the target task.

2. In the Task Details panel, click the Task Information tab to view the database, type, risk level, SQL content, and rollback statement of the task.

3. Click Initiate Again to initiate the lock-free schema change task again.

View task process

In the Task Details panel, click the Task Process tab and view information such as the task initiation status, approval status, execution status, and execution result.

View execution records

In the Task Details panel, click the Execution Records tab to view the DDL statements of the new and source tables.

View task logs

In the Task Details panel, click the Task Logs tab to view all logs and alert logs of the task.

References

• Users and roles

• Automatic authorization

• Project collaboration management

• Risk levels, risk identification rules, and approval processes

• SQL check specifications

• SQL window specification

• Database change management

• Synchronize shadow tables

• Schema comparison

• Message notification management