Change lock-free structures

This topic introduces how to submit a lock-free change ticket to implement lock-free structure changes to a database.

Background information

ODC V4.2.0 provides the lock-free structure change feature to solve the table locking issue when you change the structure of a database table.

The following table describes the online structure changes that ODC supports by the database version.

Prerequisites

• In MySQL mode of OceanBase Database, a table name cannot exceed 56 characters. In Oracle mode, a table name cannot exceed 120 characters.

• A table must contain a primary key or a unique key.

Considerations

• When the destination table contains a primary key or unique key, in a lock-free structure change, the primary key or unique key is used for table segmentation in a full table copy and subsequent incremental updates.

• Ensure that the database disk space is sufficient.

• When you perform other DDL operations on a table during the execution of a lock-free schema change on the same table, the task fails.

Principle

1. Create a temporary table.

2. Change the structure of the temporary table.

3. Copy full data of the table.

4. Synchronize incremental data.

5. Verify data consistency.

6. Kill the session.

7. Switch between the old and new tables.

Execution process

1. A user initiates a lock-free structure change task.

2. ODC performs a precheck on the SQL statement entered by the user.

3. Roles on the approval nodes approve the task.

4. ODC executes the lock-free structure change task.

Procedure

Assume that you want to change the type of the birth column in the student table to datetime.

1. In the SQL window, edit the SQL statement to create a table named student in the osc_test database.

2. On the Tasks tab, choose Lock-free Schema Change > Create Lock-free Schema Change.

3. In the Create Lock-free Schema Change dialog box, specify the following parameters.

   Parameter	Description
   Database	The database to be changed.
   Change Definitions	CREATE TABLE: The CREATE statement is required for syntax that is not supported by OceanBase Database. 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 area.
   Table Switch Settings	The original table is replaced by the destination table after the data is consistent. Table Lock Timeout: The table is locked during the table switching process. If the table is not switched within the timeout period, the execution may fail. Number of Retries: the number of retries to be automatically performed if the table is not switched within the timeout period. Source Table Clearing Policy: Select Ignore to rename and retain the original table, or select Delete Now to delete the original table after the lock-free structure change is completed.
   Task Settings	Select Execute immediately or Timed execution to set the task execution mode. The lock-free structure change tool provides two methods to handle task errors: 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 statement where an error occurs and continues to execute other statements in the script.
   Remarks	The description of the change. The description can contain up to 200 characters. This parameter is optional.

4. Click Create to create the lock-free structure change.

5. After the task is generated, you can view the task information in the Lock-free Structure Change list on the Tasks page.

View lock-free structure change tasks

Task information

1. In the lock-free structure change list on the Task Center page, click View in the Actions column.

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

3. You can click Initiate Again to re-initiate the lock-free structure change task.

Task process

In the Task Details panel, click the Task Process tab to view the task status, approval status, execution status, and execution result of the task.

Execution records

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

Task logs

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

References

• Edit and execute SQL statements

• PL compilation and debugging

• Command-line window

• Edit and export results

• Analyze SQL statements

• Generate mock data

• Manage database changes

• Manage partitions

• Synchronize shadow tables