In Data Management (DMS), you can use data manipulation language (DML) statements or data definition language (DDL) statements to perform changes without locking tables.

Background information

  • Data changes for a large table

    You want to change the data of a large table by using SQL statements. If a single SQL statement affects multiple data rows and the number of log entries exceeds the upper limit, the SQL statement fails to be executed. If the SQL statement is executed based on no indexes, the table is locked.

  • Schema changes

    In versions earlier than MySQL 5.5, only the Table-Copy algorithm can be used in DDL statements for schema changes. In MySQL 5.5 and later, the In-Place algorithm is also provided. In MySQL 5.6 and later, online DDL operations can be performed on InnoDB tables. The online DDL operations contain various types of DDL operations. For example, you can perform online DDL operations to add, delete, and rename columns, or add and modify indexes. However, you cannot perform specific common DDL operations by using online DDL operations. For example, you cannot perform online DDL operations to change column data types, column lengths, or character sets.

To resolve these issues, DMS provides the lockless change feature. This feature allows you to use DML statements or DDL statements to perform changes without locking tables. This reduces risks in data change or schema change. For more information, see DML-based lockless change or DDL-based lockless change.

The following sections describe this feature and the working principles in detail.

DML-based lockless change

  • Background information
    As business grows, business data accumulates. In this case, you must separately manage online data and historical data or regularly clear table data. The following issues may be caused:
    • A single SQL statement affects a large number of data rows. In this case, the number of log entries exceeds the upper limit and the SQL statement fails to be executed.
    • The SQL statement is executed based on no indexes. This may cause table locks, increase database loads, and even cause business failures.
    To resolve these issues, you can divide the SQL statement into different batches for execution. However, this method may cause the following issues:
    • An improper method is used to divide the SQL statement. For example, you use a LIMIT clause to divide the SQL statement. This causes table locks. In some cases, the interval for executing the different batches is not configured as expected. This causes great latency in synchronization between primary and secondary databases when data is changed. These issues affect your databases and business.
    • The SQL statement is divided by a database administrator (DBA). The process of dividing the SQL statement is complex and takes great effort. In addition, this is prone to errors and difficult to make dynamic adjustments.
  • Benefits

    The DML-based lockless change feature is powered by the mighty engines in Data Management (DMS). You can use this feature to divide a single SQL statement into different batches for execution. After each batch is executed, the SQL execution enters a buffer period. You can use this feature to change a large amount of data. For example, you can use this feature to clear historical data or update all fields in a table. This improves data change efficiency and reduces the impact on database performance and capacity. In addition, business is not affected.

  • Working principles
    • A single SQL statement is executed in different batches.
    • The SQL execution enters a buffer period after each batch of the SQL statement is executed.
  • Scenarios
    • Clear historical data.
    • Update all fields in a table.

DDL-based lockless change

  • Background information

    MySQL databases are used to manage business data at the early stage of business development. However, as business develops and business data accumulates, MySQL databases become difficult to manage. Assume that a table contains a large amount of data and you want to change the table schema by adding fields, modifying field attributes, adding indexes, or modifying indexes. In this case, the table may be locked when you change the schema. As a result, business data fails to be written to the table.

    Native capabilities of MySQL:
    • In versions earlier than MySQL 5.5, only the Table-Copy algorithm can be used in DDL statements for schema changes. In MySQL 5.5 and later, the In-Place algorithm is also provided.
      • Table-Copy: You can change the schema of a table by generating a temporary table and copying data from the original table to the temporary table. In this process, the table is locked and you cannot write data to the table.
      • In-Place: When you add or modify indexes, you can still read data from and write data to a table in a database.
    • In MySQL 5.6 and later, online DDL operations can be performed on InnoDB tables. The online DDL operations contain various types of DDL operations. For example, you can perform online DDL operations to add, delete, and rename columns, or add and modify indexes. However, you cannot perform specific common DDL operations by using online DDL operations. For example, you cannot perform online DDL operations to change column data types, column lengths, or character sets.
  • Benefits
    • Compared with the native capabilities of databases, DMS allows you to change schemas at a managed speed. This avoids the latency in synchronization between primary and secondary databases and has less impact on database performance. In addition, you can apply this feature to various scenarios where tables may be locked when schemas are changed by using native online DDL operations.
    • Compared with other tools such as pt-online-schema-change (PT) and OnlineSchemaChange (OSC), DMS allows you to perform lock-free schema changes without a trigger. You can change schemas in an asynchronous manner, which has little impact on databases. You can interrupt schema changes at any time in a safe manner.
    • DMS allows you to use Data Transmission Service (DTS) synchronization tools to change schemas without locking tables. If a DTS copy link is configured for the table whose schema is to be changed, table copy will not be interrupted when the schema of the table is being changed.
      Note The DTS copy link must have been restarted since February 14, 2020.
    • The following table compares the online DDL feature of MySQL and the DDL-based lockless change feature of DMS.
      Operation MySQL 5.5 and earlier MySQL 5.6 and later DDL-based lockless change in DMS
      Add columns X
      Delete columns X
      Rename columns X
      Add indexes X
      Modify indexes X
      Defragment tables X
      Change column data types X X
      Change column lengths X X
      Change character sets X X
      Convert characters X X
      Partition tables X X
      Correct time zones X X
      Alleviate or eliminate latency in secondary databases X X
    • For more information, see Appendix to DDL-based lockless change.
  • Working principles
    1. Create a temporary table: CREATE TABLE tmp_table_table LIKE table_name
    2. Change the schema of the temporary table: ALTER TABLE tmp_table_table XXXX
    3. Copy all the data of the original table: INSERT IGNORE INTO tmp_table_table (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx
    4. Use binary logs to synchronize incremental data: UPDATE/INSERT/DELETE tmp_table_name
    5. Rename the temporary table after the original table: RENAME TABLE table_name to old_tmp_table_table, tmp_table_name to table_name
  • Scenarios
    • Change the schemas of databases.
    • Change character sets and collations for tables and adjust time zones.
    • Reclaim tablespaces and reduce fragmentation rates without locking tables. You no longer need to use the OPTIMIZE TABLE statement that causes tables to be locked. For more information, see Defragment space without locking a table.