All Products
Search
Document Center

Change schemas without locking tables

Last Updated: Sep 30, 2020

MySQL databases are a common type of databases. They are easy to use and manage.
Many enterprises use MySQL databases to manage business data at the early stage of development. However, as enterprises’ business develops, online business data accumulates and becomes difficult to manage.
For example, it becomes more difficult to change schemas, such as adding fields, modifying attributes of fields, adding indexes, and modifying indexes. But you may often need to change schemas, and changing schemas of large tables in an online environment involves various risks. One of the risks is that tables are locked when being changed. When a table is locked, data cannot be written to the table.
For online transaction processing (OLTP) databases, table locking caused by schema changes will affect online business, especially business that features high concurrency.

Table locking caused by using MySQL native DDL operations to change schemas

In MySQL 5.5 and earlier, only the Table-Copy and In-Place algorithms can be used in data definition language (DDL) statements to change schemas. The In-Place algorithm is provided in MySQL 5.5 and later.

  • Table-Copy: generates an empty copy of the original table, changes the schema of the copy, copies data from the original table to the copy, deletes or renames the original table, and then renames the copy after the original table. During this process, the original table is locked and you cannot write data to the original table.
  • In-Place: avoids copying table data and only allows you to add or modify indexes in place. When you add or modify indexes, you can still read data from and write data to the table.

Therefore, it is extremely risky to perform DDL operations to change schemas in MySQL 5.5 and earlier.

In MySQL 5.6 and later, online DDL operations can be performed on InnoDB tables. For more information, see Online DDL Operations. The online DDL operations include various types of DDL operations. For example, you can use online DDL operations to add, delete, and rename columns. You can also add and modify indexes. However, certain common DDL operations, such as changing column data types, column lengths, and character sets, are not supported. These DDL operations may still cause tables to be locked. Against this background, Data Management Service (DMS) provides the following feature: changing schemas without locking tables.

Comparison of changing schemas without locking tables in MySQL and DMS

Operation MySQL 5.5 and earlier MySQL 5.6 and later 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

Benefits

  • When schemas are changed by using MySQL native online DDL operations, latency may occur in synchronization between primary and secondary databases. In DMS, the feature of changing schemas without locking tables can control the rate of synchronization between primary and secondary databases and avoid latency.
  • If a Data Transmission Service (DTS) table copy link is configured for the table whose schema is to be changed, table copy will not be interrupted while the schema of the table is being changed. To achieve this, the DTS table copy link must have been restarted after February 14, 2020.

Application scope

  • Database engines: MySQL and ApsaraDB for PolarDB
  • Storage engines: InnoDB and RocksDB
  • Instances and networks: ApsaraDB for RDS instances on classic networks and in Virtual Private Clouds (VPCs), user-built databases hosted on Elastic Compute Service (ECS) instances on classic networks and in VPCs, and user-built databases on the Internet

Scenarios

  • This feature can be used to change schemas without affecting business.
  • This feature can avoid latency in synchronization between primary and secondary databases that occurs when schemas are changed by using native online DDL operations.
  • This feature can be used to reclaim tablespaces and reduce fragmentation rates without locking tables. You no longer need to use OPTIMIZE TABLE clauses that cause tables to be locked. For more information, see Defragment and reclaim tablespaces without locking tables.
  • This feature can be used to modify character sets and collations for tables without locking the tables.
  • This feature can be used to change time zones for tables without locking the tables.

Procedure of enabling this feature

  1. Log on to the DMS console.

  2. In the top navigation bar, choose System Management > Instance. The Instance page appears.

  3. Find the target instance, choose More > Edit instance in the Actions column. The Edit instance dialog box appears.Open the Edit instance dialog box

  4. Click the Advanced information tab.

  5. On this tab, select Open(DMS OnlineDDL first) or Open(MySQL Native OnlineDDL first) from the OnlineDDL drop-down list.
    Select Open(DMS OnlineDDL first) or Open(MySQL Native OnlineDDL first)

    • Close: If you select Close, DMS routes SQL statements directly to the MySQL instance for execution without processing the statements.
    • Open(DMS OnlineDDL first): DMS uses the self-developed feature to change schemas without locking tables. The execution is slightly slower, but parallel threads for replica transactions are not affected and no latency occurs in synchronization between primary and secondary databases.
    • Open(MySQL Native OnlineDDL first): DMS preferentially uses MySQL native online DDL operations to change schemas. If tables are locked in this case, DMS uses the self-developed feature to change schemas without locking tables. The execution is faster, but parallel threads for replica transactions may become serial threads, which may cause latency in synchronization between primary and secondary databases.
  6. Click Submit.

Access to the feature of changing schemas without locking tables

Prerequisites: This feature is enabled. You selected Open(DMS OnlineDDL first) for the instance. For more information about how to enable the feature of changing schemas without locking tables, see Procedure of enabling this feature.

  • Change schemas without locking tables for instances in the Stable Change mode
    • Schema Design: To use the Schema Design feature for instances in the Stable Change mode, submit a ticket.
      • Normal Data Modify: To submit Normal Data Modify tickets to change data without locking tables, you need to enable the All DDL must execute by order rule under the SQL execution rules checkpoint. For more information, see Data change.
    • Task Management: Database administrators (DBAs) and DMS administrators can create SQL tasks to change schemas in specified databases. The tasks will be run without locking tables.
  • Change schemas without locking tables for instances in the Secure Collaboration mode
    • Schema Design: You can use the Schema Design feature to visually change the schema of a table and then perform changes to the base or target database without locking the table. For more information, see Schema design.
    • Normal Data Modify: To submit Normal Data Modify tickets to change data without locking tables, you need to enable the All DDL must execute by order rule under the SQL execution rules checkpoint. For more information, see Data change.
    • Task Management: DBAs and DMS administrators can create SQL tasks to change schemas in specified databases. The tasks will be run without locking tables.

Common statements

  • Create a temporary table that copies the schema of the original table to be changed: CREATE TABLE tmp_table_name LIKE table_name
  • Change the schema of the temporary table: ALTER TABLE tmp_table_name XXXX
  • Copy all data from the original table to the temporary table: INSERT IGNORE INTO tmp_table_name (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx
  • Synchronize incremental data by using binlogs: UPDATE/INSERT/DELETE tmp_table_name
  • Rename the temporary table after the original table: RENAME TABLE table_name to old_tmp_table_name, tmp_table_name to table_name

Make sure that the original table contains a primary key or unique index, which is used to copy data from the original table to the temporary table and synchronize incremental data.

Schema changes must not involve the primary key or unique index that is used to copy and synchronize data. Otherwise, schema changes will fail.

The feature of changing schemas without locking tables in DMS does not need triggers. The execution of parallel threads for replica transactions remains concurrent and does not become serial. This avoids latency in synchronization between primary and secondary databases.

By contrast, MySQL native online DDL operations convert parallel threads to serial threads, which may cause synchronization latency.

Limits

  • Account: To use the feature of changing schemas without locking tables, you must have logged in with one of the following accounts:

    • Privileged account
    • Account with the logging permission
    • Account with the read/write permission
  • Disk space: Make sure that the target database has sufficient disk space for you to copy the table to be changed. Otherwise, the RDS instance will be locked.

  • Log format: For ApsaraDB for PolarDB instances, binlogging is disabled by default. To use the feature of changing schemas without locking tables, you need to enable binlogging and set the log format to ROW. For more information, see Enable binlogging.

  • When the feature of changing schemas without locking tables is used, schema changes must not include any updates of primary keys or unique indexes.

    • If the table to be changed contains a primary key, make sure that the primary key will not be updated.
    • If the table to be changed contains no primary key but a unique index, make sure that the unique index will not be updated.
    • This feature cannot be used to change tables that contain no primary key or unique index. In this case, you need to disable this feature for the instance, or if you want to change the schema of a table in this instance, you need to specify a primary key or unique index for the table.

Appendix

For more information, see Appendix to changing schemas without locking tables.