Method, device, apparatus and medium for executing ddl statement

Abstract

The one or more embodiments of the specification provide a DDL statement execution method, device, equipment and medium. The specification sets up locking indication information in advance for indicating the locking mode when different types of database operations are implemented through DDL statements, to provide a unified locking mechanism, so that when a DDL statement for operating a target data table is received, target locking indication information matched with a first to-be-executed operation corresponding to the DDL statement is obtained from the locking indication information according to the first to-be-executed operation, so that the target data table is locked according to the target locking indication information, and the DDL statement is executed when the target data table is in a locked state, so that the DDL statement can be correctly executed and will not affect other DDL statements currently being executed, to improve the concurrency problem between multiple DDL statements.

Description

TECHNICAL FIELD

[0001] One or more embodiments of the present specification relate to the technical field of computer technology, and particularly relate to a DDL statement execution method, device, equipment and medium. BACKGROUND

[0002] Data Definition Language (DDL) is a database language used to define the structure of a data table, which can be used to create, delete, and modify the structure of a data table. However, a database is a shared resource that allows multiple users to use it, and when multiple users concurrently operate on a data table, there may be multiple DDL statement requests to operate on the same structure of the database table. If this concurrent operation is not controlled, it may damage the data consistency of the database. In order to correctly and concurrently execute DDL statements in the database, a unified locking mechanism is needed to deal with the concurrency problem between multiple DDL statements. SUMMARY

[0003] Therefore, one or more embodiments of the present specification provide a DDL statement execution method, device, equipment and medium.

[0004] To achieve the above-mentioned purpose, one or more embodiments of the present specification provide technical solutions as follows:

[0005] According to a first aspect of one or more embodiments of the present specification, a DDL statement execution method is provided, comprising:

[0006] In response to receiving a target DDL statement for operating on a target data table, determining target locking indication information matched with a first to-be-executed operation corresponding to the target DDL statement from pre-set locking indication information based on the first to-be-executed operation, the locking indication information being used to indicate a locking mode when different types of database operations are implemented through DDL statements;

[0007] According to the target locking indication information, locking the target data table to execute the target DDL statement in a case where the target data table is in a locked state.

[0008] According to a second aspect of one or more embodiments of the present specification, a DDL statement execution device is provided, comprising:

[0009] A determination module is configured to, in response to receiving a target DDL statement for operating on a target data table, determine target locking indication information matched with a first to-be-executed operation corresponding to the target DDL statement from pre-set locking indication information based on the first to-be-executed operation, the locking indication information being used to indicate a locking mode when different types of database operations are implemented through DDL statements;

[0010] The locking module is used to lock the target data table according to the target locking instruction information, so as to execute the target DDL statement while the target data table is in a locked state.

[0011] According to a third aspect of one or more embodiments of this specification, a computing device is provided, comprising:

[0012] processor;

[0013] Memory used to store processor-executable instructions;

[0014] The processor implements the method as described in any of the first aspects above by running executable instructions.

[0015] According to a fourth aspect of one or more embodiments of this specification, a computer-readable storage medium is provided that stores computer instructions thereon, which, when executed by a processor, implement the method as described in any of the first aspects above.

[0016] By pre-setting locking indication information to specify the locking methods for different types of database operations implemented through DDL statements, a unified locking mechanism is provided. When a target DDL statement for operating on a target data table is received, the target locking indication information matching the first pending operation corresponding to the target DDL statement is obtained from the locking indication information. The target data table is then locked according to the target locking indication information, allowing the target DDL statement to be executed while the target data table is locked. This ensures that the target DDL statement can be executed correctly without affecting other currently executing DDL statements, thereby improving concurrency issues between multiple DDL statements. Attached Figure Description

[0017] Figure 1 This is an exemplary embodiment of a method for executing DDL statements.

[0018] Figure 2 This is a schematic structural diagram of a computing device provided in an exemplary embodiment.

[0019] Figure 3 This is a block diagram of an execution apparatus for DDL statements provided in an exemplary embodiment. Detailed Implementation

[0020] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with one or more embodiments of this specification. Rather, they are merely examples of apparatuses and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0021] It should be noted that the steps of the corresponding methods are not necessarily performed in the order shown and described in this specification in other embodiments. In some other embodiments, the methods may include more or fewer steps than described in this specification. Furthermore, a single step described in this specification may be broken down into multiple steps in other embodiments; and multiple steps described in this specification may be combined into a single step in other embodiments.

[0022] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties. The collection, use and processing of related data shall comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals shall be provided for users to choose to authorize or refuse.

[0023] To aid in understanding this manual, the technical terms related to this manual will be introduced first.

[0024] Tables and partitions: A table in a database consists of one or more partitions.

[0025] Tablespace: The storage location for database objects. Each partition belongs to a tablespace configured by a specific user.

[0026] Partition move: The process of moving a partition from one tablespace to another.

[0027] Main table: If an index I is created on table A, then the main table of index I is table A.

[0028] Local indexes and global indexes: Both are commonly used indexes in data tables. The difference is that global indexes can have partitioning rules different from the main table, while local indexes can only be partitioned according to the partitioning rules of the main table. In addition, the partitions in the local index and the partitions in the main table are in one-to-one correspondence, with each partition being indexed into the corresponding partition in the main table.

[0029] Online DDL and Offline DDL: Based on whether the execution of DDL statements blocks the execution of the related Data Manipulation Language (DML), DDL statements can be divided into Online DDL and Offline DDL. DML cannot be executed in parallel during Offline DDL execution, while DML can be executed in parallel during Online DDL execution.

[0030] Table locks: Locks used to protect data on a table or partition. Table-level locks can be used to protect data on a table, while partition-level locks can be used to protect data on a partition.

[0031] DDL locks are locks used to protect the structure of database objects (tables, indexes, etc.). This manual mainly focuses on Online DDL locks, which are used to protect the structure of database objects during the execution of Online DDL statements.

[0032] Locking Modes: This specification mainly covers four locking modes: Row Share (RS) lock, Row Exclusive (RX) lock, Share (S) lock, and Exclusive (X) lock. The mutual exclusion matrices for these four lock modes are shown in Table 1 below:

[0033] Table 1

[0034] RS RX S X RS × RX × × S × × X × × × ×

[0035] Taking S locks and RX locks in traditional database tables as an example, an S lock can ensure that the data table with the lock can only be read but not modified. However, every time a row is modified, deleted, or added, an RX lock needs to be added to the table to be operated on. This ensures that the data on the data table will not change while the S lock is held.

[0036] It is important to note that when adding partition-level RS / RX / S / X locks to a data table, table-level RS / RX / S / X locks will be automatically added to the data table.

[0037] The following section provides a detailed description of how to execute the DDL statements provided in this manual.

[0038] This specification provides a method for executing DDL statements, which provides a unified locking mechanism to achieve mutual exclusion between Online DDL and Offline DDL, so as to correctly and concurrently execute DDL statements in the database.

[0039] Generally speaking, online DDL statements and offline DDL statements can be mutually exclusive using the locking methods shown in Table 2 below:

[0040] Table 2

[0041] Offline DDL Online DDL Offline DDL Table lock Table lock Online DDL Table lock Online DDL lock + table lock

[0042] As shown in Table 2 above, when only Offline DDL statements need to be executed, only a table lock is required; when only Online DDL statements need to be executed, both a table lock and an Online DDL lock are required. The mutual exclusion mechanism between Offline DDL statements is a table lock; the mutual exclusion mechanism between two Offline DDL statements is a table lock; and the mutual exclusion mechanism between two Online DDL statements is a combination of Online DDL locks and table locks.

[0043] This manual elucidates the mutual exclusion relationships between various Online DDL and Offline DDL statements, and then strictly defines the lock types of table locks and Online DDL locks corresponding to different types of database operations based on the elucidated mutual exclusion relationships, so as to achieve the purpose of controlling the concurrency between DDL statements by combining table locks and Online DDL locks.

[0044] Optionally, the DDL statements (including Online DDL statements and Offline DDL statements) involved in this specification can be the DDL statements corresponding to the create, alter, and drop commands, which are used to add, modify, or delete table structures based on the current data table structure. This specification does not limit the specific functions implemented by the DDL statements.

[0045] Optionally, the above DDL statement execution method can be executed by a computing device. The computing device can be a terminal device, such as a desktop computer, portable computer, laptop computer, tablet computer, smartphone, smartwatch, etc. Alternatively, the computing device can be a server, such as a single server, multiple servers, server cluster, cloud computing platform, etc. This specification does not limit the type of computing device.

[0046] The above is merely an illustrative description of the application scenarios of this specification and does not constitute a limitation on the application scenarios of this specification. In many possible implementation methods, the solutions provided in this specification can be applied to many other scenarios. This specification does not limit the specific application scenarios.

[0047] After introducing the application scenarios of this manual, the specific implementation process of this manual will be described next.

[0048] See Figure 1 , Figure 1 This is an exemplary embodiment providing a method for executing DDL statements, such as... Figure 1 As shown, the method may include:

[0049] Step 101: In response to receiving a target DDL statement for operating on the target data table, based on the first operation to be executed corresponding to the target DDL statement, determine the target locking indication information that matches the first operation to be executed from the pre-set locking indication information. The locking indication information is used to indicate the locking method when implementing different types of database operations through DDL statements.

[0050] It should be noted that the target DDL statement can be used to indicate the target data table to be operated on and the first operation to be performed on the target data table.

[0051] Taking the target DDL statement as an example, which is the DDL statement corresponding to the create instruction used to create a local index on a column of a data table, the target DDL statement can be "create index i1 on t1(c1)local". This target DDL statement is used to create a local index i1 on column c1 of table t1. Here, the table name t1 indicates the target data table to be operated on, and the action instruction "create...index on..." indicates that the first operation to be performed is to create a local index.

[0052] Taking the target DDL statement as the DDL statement corresponding to the alter instruction used to modify the table name, the target DDL statement can be "alter table t1 rename to t2". This target DDL statement is used to change the name of the data table whose current table name is t1 to t2. Here, the table name t1 indicates the target data table to be operated on, and the action instruction "alter table…rename to…" indicates that the first operation to be performed is to modify the table name.

[0053] Taking the target DDL statement as the DDL statement corresponding to the drop instruction used to delete a data table as an example, the target DDL statement can be "drop table t1". This target DDL statement is used to delete the data table t1. Here, the table name t1 indicates the target data table to be operated on, and the action instruction "drop table..." indicates that the first operation to be performed is to delete the table.

[0054] By pre-setting locking indication information to indicate the locking method when implementing different types of database operations through DDL statements, when a target DDL statement is received, the target locking indication information matching the first operation to be executed indicated by the target DDL statement can be directly obtained from the locking indication information. This allows the target data table to be locked based on the target indication information, and the function to be completed by the target DDL statement can be achieved through various mutually exclusive DDL statements.

[0055] Step 102: Lock the target data table according to the target locking instruction information, so as to execute the target DDL statement while the target data table is in a locked state.

[0056] The above embodiments provide a unified locking mechanism by pre-setting locking indication information to indicate the locking method when implementing different types of database operations through DDL statements. Upon receiving a target DDL statement for operating on a target data table, the system retrieves the target locking indication information matching the first pending operation from the locking indication information based on the target DDL statement. The target data table is then locked according to the target locking indication information, allowing the target DDL statement to be executed while the target data table is locked. This ensures the target DDL statement can be executed correctly without affecting other currently executing DDL statements, thus improving concurrency issues between multiple DDL statements.

[0057] The above is merely an introduction to the basic implementation process of this specification. The following describes in detail the execution method of the DDL statements provided in this specification, in conjunction with optional embodiments.

[0058] It should be noted that locking methods corresponding to various types of database operations can be predefined, and the lock types corresponding to different types of database operations can be stored in the locking indication information, so that the locking indication information can include locking methods corresponding to various types of database operations.

[0059] The following section introduces the principles and methods of pre-setting lock indication information.

[0060] First, we can agree that adding or deleting partitions on the main table or global index table can be implemented in the following way:

[0061] 1. There are only two ways to add a partition to the main table or global index table:

[0062] (1) Add partitions;

[0063] (2) Split and merge new partitions from the original partitions;

[0064] (3) Swapped from other partitions;

[0065] 2. There are only two ways to delete a partition on the main table or global index table:

[0066] (1) Delete / truncate the partition;

[0067] (2) After partition splitting and merging are completed, clean up the original partitions;

[0068] (3) The partition is swapped out.

[0069] Based on the above conventions, online DDL table-level locks can allow four modes (i.e., RS mode, RX mode, S mode, and X mode) to protect the behavior of adding new partitions. Protecting the behavior of adding new partitions can be achieved by adding locks to the data table as shown in Table 3 below:

[0070] Table 3

[0071] Online DDL table level lock Can the holder add partition Can the holder guarantee no one else add partition during the lock holding period RS No No RX Yes No S No Yes X Yes Yes

[0072] As shown in Table 3 above, when a data table is locked with an Online DDL table-level RS lock, partitions cannot be added to the data table, and there is no guarantee that no one else will add partitions while the data table is locked. When a data table is locked with an Online DDL table-level RX lock, partitions can be added to the data table, but there is no guarantee that no one else will add partitions while the data table is locked. When a data table is locked with an Online DDL table-level S lock, partitions cannot be added to the data table, but there is a guarantee that no one else will add partitions while the data table is locked. When a data table is locked with an Online DDL table-level X lock, partitions can be added to the data table, and there is a guarantee that no one else will add partitions while the data table is locked.

[0073] For Online DDL partition-level locks, only RX and X modes are allowed. These can be used to protect actions such as "partition splitting / merging" and "global index partition deletion," and can also protect the state of the tablespace to which the partition belongs. The protection of these actions can be achieved by adding locks to the data table as shown in Table 4 below:

[0074] Table 4

[0075]

[0076] As shown in Table 4 above, when a data table is locked under an Online DDL partition-level RX lock, partitions cannot be split / merged, global index partitions cannot be deleted, and the tablespace to which the partition belongs holds a read lock, meaning the tablespace to which the partition belongs is not accessible. When a data table is locked under an Online DDL partition-level X lock, partitions can be split / merged, global index partitions can be deleted, and the tablespace to which the partition belongs holds a write lock, meaning modification of the tablespace to which the partition belongs is allowed.

[0077] Based on the above conventions and analysis of related behaviors, it can be seen that Online DDL locks can protect the addition of partitions to the main table and global index table, as well as the deletion of partitions in the global index table. However, the protection against deleting partitions in the main table can be handled by table locks. Furthermore, for partition-level table locks, only partition-level X locks allow the deletion / truncation of partitions in the main table.

[0078] It's important to note that during the execution of a particular operation, simply executing that operation alone is insufficient to achieve the intended purpose. Instead, it requires parallel execution of operations that ensure other parts of the data table are not modified (i.e., mutual exclusion operations) to guarantee database consistency. By defining the behaviors described above, we can identify the mutual exclusion operations that need to be executed in parallel for each type of operation. This allows us to subsequently lock the data table based on the original operation (the first operation to be executed) and its corresponding mutual exclusion operation (the second operation to be executed). This ensures that the locked data table allows the DDL statements corresponding to the original operation and its respective mutual exclusion operation to be executed in parallel.

[0079] Taking the database operation of adding / deleting a local index as an example, in order to ensure that the partitions of the main table remain unchanged, the following mutual exclusion operations need to be performed, and the locks corresponding to various mutual exclusion operations can be:

[0080] 1. Partitioning is prohibited; this is achieved by adding an Online DDL table-level S lock / RX lock to the main table.

[0081] 2. Prevent the original partition from splitting or merging by adding Online DDL partition-level RX locks to all partitions of the main table;

[0082] 3. To prevent the deletion and swapping of partitions in the main table, partition-level RS table locks are applied to all partitions of the main table.

[0083] In other words, the Online DDL locks corresponding to adding / deleting local indexes are table-level S locks / RX locks used to lock the main table and partition-level RX locks used to lock all partitions of the main table, while the table locks corresponding to adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table.

[0084] The above is just an example of the locking method corresponding to the database operation of adding / deleting a local index. For other database operations, their mutual exclusion operations can be determined in the same way as the above example, and then the lock corresponding to the database operation can be set based on the determined mutual exclusion operations.

[0085] It should be noted that for Online DDL statements, mutual exclusion between DDL statements may need to be achieved by simultaneously adding Online DDL locks and table locks to the corresponding data table; while for Offline DDL statements, mutual exclusion may only require adding a table lock to the corresponding data table. Therefore, the locking indication information can include first locking indication information and second locking indication information. The first locking indication information stores the lock types of Online DDL locks and table locks corresponding to different types of database operations, and the second locking indication information stores the lock types of table locks corresponding to different types of database operations.

[0086] The first locking indication information may include at least one of the following:

[0087] The Online DDL lock corresponding to the column addition operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the column addition operation is a partition-level RX lock used to lock all partitions of the main table.

[0088] Change the Online DDL lock corresponding to the NOT NULL constraint operation to a table-level RS lock used to lock each data table, and change the table lock corresponding to the NOT NULL constraint operation to a partition-level RX lock used to lock all partitions of the main table.

[0089] The Online DDL lock corresponding to the "Set Column Default Value" operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the "Set Column Default Value" operation is a partition-level RX lock used to lock all partitions of the main table.

[0090] The Online DDL lock corresponding to the rename column operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename column operation is a partition-level RX lock used to lock all partitions of the main table.

[0091] The Online DDL lock corresponding to the rename table operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename table operation is a partition-level RX lock used to lock all partitions of the main table.

[0092] The Online DDL lock corresponding to the row format / compression algorithm modification operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the row format / compression algorithm modification operation is a partition-level RX lock used to lock all partitions of the main table.

[0093] The Online DDL locks corresponding to optimized tablespace operations are table-level RS locks used to lock each data table, and the table locks corresponding to optimized tablespace operations are partition-level RX locks used to lock all partitions of the main table.

[0094] The Online DDL locks corresponding to the add / delete constraint operations are table-level RS locks used to lock each data table, and the table locks corresponding to the add / delete constraint operations are partition-level RX locks used to lock all partitions of the main table.

[0095] The Online DDL lock corresponding to the rename index operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename index operation is a partition-level RX lock used to lock all partitions of the main table.

[0096] The Online DDL locks corresponding to the operations of adding / deleting / rebuilding local indexes are table-level S locks used to lock the main table and partition-level RX locks used to lock all partitions of the main table; or, the Online DDL locks corresponding to the operations of adding / deleting / rebuilding local indexes are table-level RX locks used to lock the main table and partition-level S locks used to lock all partitions of the main table, and the table locks corresponding to the operations of adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table.

[0097] The Online DDL locks corresponding to adding / deleting / rebuilding global indexes are table-level S locks / RX locks used to lock the main table and table-level X locks used to lock the index table. The table locks corresponding to adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table.

[0098] The Online DDL lock corresponding to the main table partitioning operation is a table-level RX lock / S lock used to lock each data table, and the table lock corresponding to the main table partitioning operation is a table-level RX lock used to lock each data table.

[0099] The Online DDL locks corresponding to the main table move partition operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the destination partition in each data table. The table locks corresponding to the main table move partition operation are partition-level RS locks used to lock the destination partition in each data table.

[0100] The Online DDL locks corresponding to the global index partition move operation are table-level RS locks used to lock the global index table and partition-level X locks used to lock the destination partition in the global index table. The table locks corresponding to the global index partition move operation are table-level RS locks used to lock the main table.

[0101] The Online DDL locks corresponding to the main table partition split / merge operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the source partition and the destination partition. The table locks corresponding to the main table partition split / merge operation are table-level RX locks used to lock each data table and partition-level RX locks used to lock the source partition and the destination partition.

[0102] The Online DDL locks corresponding to the global index partition split / merge operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the source partition and the destination partition. The table locks corresponding to the global index partition split / merge operation are table-level RS locks used to lock the main table.

[0103] The Online DDL locks corresponding to the global index partition deletion operation are table-level RS locks used to lock the index table and partition-level X locks used to lock the target partition of the index table. The table locks corresponding to the global index partition deletion operation are table-level S locks used to lock the main table.

[0104] The Online DDL lock corresponding to the column type modification operation is a table-level X lock used to lock the main table, and the table lock corresponding to the column type modification operation is a partition-level RX lock used to lock all partitions of the main table.

[0105] The Online DDL lock corresponding to the column deletion operation is a table-level X lock used to lock the main table, and the table lock corresponding to the column deletion operation is a partition-level RX lock used to lock all partitions of the main table.

[0106] The Online DDL locks corresponding to adding / deleting / modifying primary keys are table-level X locks used to lock the main table, while the table locks corresponding to adding / deleting / modifying primary keys are partition-level RX locks used to lock all partitions of the main table.

[0107] In addition, the second locking instruction information may include at least one of the following:

[0108] The table lock corresponding to the delete / truncate table operation is a table-level X lock used to lock each data table;

[0109] The table lock corresponding to the operation of deleting / truncating the main table partition is a partition-level X lock used to lock all partitions in each data table;

[0110] The table lock corresponding to the primary table partition swap operation is a partition-level X lock used to lock both the source and destination partitions.

[0111] For ease of viewing, the first and second locking information mentioned above are presented in table form below. The contents of the first locking information can be found in Table 5 below:

[0112] Table 5

[0113]

[0114] It should be noted that the S locks and RX locks of the Online DDL locks in Table 5 above are interchangeable, and can still achieve mutual exclusion of the corresponding DDL statements. Furthermore, operations categorized as "Other" in Table 5 are mutually exclusive with all DDL statements. Therefore, for some DDL statements whose concurrency with other DDL statements is difficult to determine, they can be classified into the "Other" category, and the corresponding data tables can be locked in the manner corresponding to operations in this category.

[0115] Additionally, the contents of the second locking information can be found in Table 6 below:

[0116] Table 6

[0117]

[0118] By defining the locking indication information as shown in Tables 5 and 6 above, the lock types corresponding to different types of database operations can be pre-set, so that when the target DDL statement is received, the corresponding target locking indication information can be directly obtained.

[0119] In some embodiments, step 101, when responding to receiving a target DDL statement for operating on a target data table, and determining target lock indication information matching the first operation to be executed from pre-set lock indication information based on the first operation to be executed corresponding to the target DDL statement, may include the following steps:

[0120] Step 1011: In response to receiving the target DDL statement for operating on the target data table, determine the first operation to be executed corresponding to the target DDL statement.

[0121] Optionally, the first operation to be executed corresponding to the target DDL statement can be determined based on the action instructions in the target DDL statement.

[0122] Step 1012: Based on the first operation to be executed, obtain the lock type corresponding to the first operation to be executed from the locking indication information, and use it as the target locking indication information.

[0123] In one possible implementation, when the target DDL statement is an Online DDL statement, the lock type corresponding to the first operation to be executed is obtained from the first locking indication information based on the first operation to be executed, and used as the target locking indication information.

[0124] In another possible implementation, when the target DDL statement is an Offline DDL statement, the lock type corresponding to the first operation to be executed is obtained from the second locking indication information based on the first operation to be executed, and used as the target locking indication information.

[0125] Optionally, the lock type corresponding to the first operation to be executed of different types can be obtained based on the correspondence stored in the locking instruction information.

[0126] Once the target locking instruction information is obtained through the above process, step 102 can be used to lock the target data table according to the target locking instruction information, so that the target DDL statement can be executed while the target data table is in a locked state.

[0127] In some embodiments, when locking the target data table according to the target locking instruction information, so as to execute the target DDL statement while the target data table is in a locked state, step 102 can be implemented in the following way:

[0128] According to the target locking instruction, add a lock to the target data table that matches the lock type corresponding to the first operation to be executed, so as to execute the target DDL statement while the target data table is in a locked state.

[0129] As described in the above embodiments, during the execution of the first operation to be executed, it may also be necessary to execute a second operation to be executed that is mutually exclusive with it, so as to achieve the original operation intention and ensure the consistency of the database through the joint execution of the first and second operations to be executed.

[0130] Therefore, based on the first operation to be executed corresponding to the target DDL statement, a second operation to be executed that is mutually exclusive with the first operation to be executed can be determined. This allows the target DDL statement and the DDL statement corresponding to the second operation to be executed to be executed when the target data table is in a locked state.

[0131] This manual provides locking methods that support more relaxed DDL concurrency control by strictly defining the semantics of different locking modes. Using the solutions provided in this manual, DDL and DML statements can achieve mutual exclusion through table locks, and online DDL statements (except for the online DDL statement of deleting a partition from a global index) and DML statements can be concurrent. Furthermore, main table partition move operations and global index addition / deletion operations can be concurrent, global index partition move operations and local index addition / deletion operations can be concurrent, and online partition operations on the main table and online partition operations on the global index table (except for the global index partition deletion operation) can be concurrent.

[0132] Corresponding to the embodiments of the methods described above, this specification also provides corresponding device embodiments.

[0133] Figure 2 This is a schematic structural diagram of a computing device provided in an exemplary embodiment. Please refer to... Figure 2 At the hardware level, the computing device includes a processor 202, an internal bus 204, a network interface 206, memory 208, and non-volatile memory 210, and may also include other hardware required for tasks. One or more embodiments of this specification can be implemented in software, such as the processor 202 reading the corresponding computer program from the non-volatile memory 210 into memory 208 and then running it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of hardware and software, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0134] This manual also provides a device for executing DDL statements; please refer to [reference needed]. Figure 3 , Figure 3 This is a block diagram of an execution device for DDL statements provided in an exemplary embodiment. This execution device for DDL statements can be applied to, for example... Figure 2 The computing device shown is used to implement the technical solution of this specification. The execution device for the DDL statement may include:

[0135] The determination module 301 is used to respond to receiving a target DDL statement for operating on a target data table, and based on the first operation to be executed corresponding to the target DDL statement, determine the target locking indication information that matches the first operation to be executed from the pre-set locking indication information. The locking indication information is used to indicate the locking method when different types of database operations are implemented through DDL statements.

[0136] The locking module 302 is used to lock the target data table according to the target locking instruction information, so as to execute the target DDL statement while the target data table is in a locked state.

[0137] In some embodiments, the locking indication information stores the lock types corresponding to different types of database operations;

[0138] The determining module 301, in response to receiving a target DDL statement for operating on a target data table, determines, based on the first operation to be executed corresponding to the target DDL statement, target locking indication information matching the first operation to be executed from pre-set locking indication information, is configured to:

[0139] In response to receiving a target DDL statement for operating on a target data table, determine the first operation to be executed corresponding to the target DDL statement;

[0140] Based on the first operation to be executed, the lock type corresponding to the first operation to be executed is obtained from the locking indication information and used as the target locking indication information.

[0141] In some embodiments, the locking indication information includes first locking indication information and second locking indication information. The first locking indication information stores the lock types of Online DDL locks and table locks corresponding to different types of database operations, and the second locking indication information stores the lock types of table locks corresponding to different types of database operations.

[0142] The determining module 301, when used to obtain the lock type corresponding to the first operation to be executed from the locking indication information based on the first operation to be executed, and use it as the target locking indication information, is used for any of the following:

[0143] When the target DDL statement is an Online DDL statement, the lock type corresponding to the first operation to be executed is obtained from the first locking indication information based on the first operation to be executed, and used as the target locking indication information;

[0144] When the target DDL statement is an Offline DDL statement, the lock type corresponding to the first operation to be executed is obtained from the second locking indication information based on the first operation to be executed, and used as the target locking indication information.

[0145] In some embodiments, the first locking indication information includes at least one of the following:

[0146] The Online DDL lock corresponding to the column addition operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the column addition operation is a partition-level RX lock used to lock all partitions of the main table.

[0147] Change the Online DDL lock corresponding to the NOT NULL constraint operation to a table-level RS lock used to lock each data table, and change the table lock corresponding to the NOT NULL constraint operation to a partition-level RX lock used to lock all partitions of the main table.

[0148] The Online DDL lock corresponding to the "Set Column Default Value" operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the "Set Column Default Value" operation is a partition-level RX lock used to lock all partitions of the main table.

[0149] The Online DDL lock corresponding to the rename column operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename column operation is a partition-level RX lock used to lock all partitions of the main table.

[0150] The Online DDL lock corresponding to the rename table operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename table operation is a partition-level RX lock used to lock all partitions of the main table.

[0151] The Online DDL lock corresponding to the row format / compression algorithm modification operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the row format / compression algorithm modification operation is a partition-level RX lock used to lock all partitions of the main table.

[0152] The Online DDL locks corresponding to optimized tablespace operations are table-level RS locks used to lock each data table, and the table locks corresponding to optimized tablespace operations are partition-level RX locks used to lock all partitions of the main table.

[0153] The Online DDL locks corresponding to the add / delete constraint operations are table-level RS locks used to lock each data table, and the table locks corresponding to the add / delete constraint operations are partition-level RX locks used to lock all partitions of the main table.

[0154] The Online DDL lock corresponding to the rename index operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename index operation is a partition-level RX lock used to lock all partitions of the main table.

[0155] The Online DDL locks corresponding to the operations of adding / deleting / rebuilding local indexes are table-level S locks used to lock the main table and partition-level RX locks used to lock all partitions of the main table; or, the Online DDL locks corresponding to the operations of adding / deleting / rebuilding local indexes are table-level RX locks used to lock the main table and partition-level S locks used to lock all partitions of the main table, and the table locks corresponding to the operations of adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table.

[0156] The Online DDL locks corresponding to adding / deleting / rebuilding global indexes are table-level S locks / RX locks used to lock the main table and table-level X locks used to lock the index table. The table locks corresponding to adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table.

[0157] The Online DDL lock corresponding to the main table partitioning operation is a table-level RX lock / S lock used to lock each data table, and the table lock corresponding to the main table partitioning operation is a table-level RX lock used to lock each data table.

[0158] The Online DDL locks corresponding to the main table move partition operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the destination partition in each data table. The table locks corresponding to the main table move partition operation are partition-level RS locks used to lock the destination partition in each data table.

[0159] The Online DDL locks corresponding to the global index partition move operation are table-level RS locks used to lock the global index table and partition-level X locks used to lock the destination partition in the global index table. The table locks corresponding to the global index partition move operation are table-level RS locks used to lock the main table.

[0160] The Online DDL locks corresponding to the main table partition split / merge operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the source partition and the destination partition. The table locks corresponding to the main table partition split / merge operation are partition-level RX locks used to lock the source partition and the destination partition.

[0161] The Online DDL locks corresponding to the global index partition split / merge operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the source partition and the destination partition. The table locks corresponding to the global index partition split / merge operation are table-level RS locks used to lock the main table.

[0162] The Online DDL locks corresponding to the global index partition deletion operation are table-level RS locks used to lock the index table and partition-level X locks used to lock the target partition of the index table. The table locks corresponding to the global index partition deletion operation are table-level S locks used to lock the main table.

[0163] The Online DDL lock corresponding to the column type modification operation is a table-level X lock used to lock the main table, and the table lock corresponding to the column type modification operation is a partition-level RX lock used to lock all partitions of the main table.

[0164] The Online DDL lock corresponding to the column deletion operation is a table-level X lock used to lock the main table, and the table lock corresponding to the column deletion operation is a partition-level RX lock used to lock all partitions of the main table.

[0165] The Online DDL locks corresponding to adding / deleting / modifying primary keys are table-level X locks used to lock the main table, while the table locks corresponding to adding / deleting / modifying primary keys are partition-level RX locks used to lock all partitions of the main table.

[0166] In some embodiments, the second locking indication information includes at least one of the following:

[0167] The table lock corresponding to the delete / truncate table operation is a table-level X lock used to lock each data table;

[0168] The table lock corresponding to the operation of deleting / truncating the main table partition is a partition-level X lock used to lock all partitions in each data table;

[0169] The table lock corresponding to the primary table partition swap operation is a partition-level X lock used to lock both the source and destination partitions.

[0170] In some embodiments, the locking module 302, when locking the target data table according to the target locking instruction information so as to execute the target DDL statement while the target data table is in a locked state, is configured to:

[0171] According to the target locking instruction, add a lock to the target data table that matches the lock type corresponding to the first operation to be executed, so as to execute the target DDL statement while the target data table is in a locked state.

[0172] In some embodiments, the determining module 301 is further configured to determine a second operation to be executed that is mutually exclusive with the first operation to be executed based on the first operation to be executed corresponding to the target DDL statement;

[0173] The device also includes:

[0174] The execution module is used to execute target DDL statements when the target data table is locked.

[0175] The execution module, when used to execute target DDL statements while the target data table is locked, is used for:

[0176] While the target data table is locked, execute the target DDL statement and the DDL statement corresponding to the second operation to be executed.

[0177] The apparatus, module, or unit described in the above embodiments can be implemented by a computer chip or entity, or by a product with a certain function. A typical implementation device is a computer, which can be a personal computer, laptop computer, cellular phone, camera phone, smartphone, personal digital assistant, media player, navigation device, email sending and receiving device, game console, tablet computer, wearable device, or any combination of these devices.

[0178] In a typical configuration, a computer includes one or more processors (Central Processing Unit, CPU), input / output interfaces, network interfaces, and memory.

[0179] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0180] Computer-readable media, including both permanent and non-permanent, removable and non-removable media, can store information using any method or technology. Information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase-change random access memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital video disc (DVD) or other optical storage, magnetic tape, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transfer medium that can be used to store information accessible by a computing device. As defined in this article, computer-readable media do not include transient computer-readable media, such as modulated data signals and carrier waves.

[0181] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0182] The foregoing has described specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than that shown in the embodiments and may still achieve the desired result. Furthermore, the processes depicted in the drawings do not necessarily require the specific or sequential order shown to achieve the desired result. In some embodiments, multitasking and parallel processing are possible or may be advantageous.

[0183] The terminology used in one or more embodiments of this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of one or more embodiments of this specification. The singular forms “a,” “described,” and “the” used in one or more embodiments of this specification and in the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term “and / or” as used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0184] It should be understood that although the terms first, second, third, etc., may be used to describe various information in one or more embodiments of this specification, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from one another. For example, first information may also be referred to as second information without departing from the scope of one or more embodiments of this specification, and similarly, second information may also be referred to as first information. Depending on the context, the word "if" as used herein may be interpreted as "when," "in response to a determination," or "when," or "in the event of a determination."

[0185] The above description is merely a preferred embodiment of one or more embodiments of this specification and is not intended to limit the scope of one or more embodiments of this specification. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of one or more embodiments of this specification should be included within the protection scope of one or more embodiments of this specification.

Claims

1. A method for executing DDL statements, comprising: In response to receiving a target DDL statement for operating on a target data table, if the target DDL statement is an Online DDL statement, based on the first operation to be executed corresponding to the target DDL statement, a target locking indication information matching the first operation to be executed is determined from a pre-set first locking indication information. The first locking indication information is used to indicate the locking method when implementing different types of database operations through Online DDL statements; wherein: the first locking indication information stores the lock types of Online DDL locks and table locks corresponding to different types of database operations; the Online DDL locks include: Online DDL table-level locks and Online DDL partition-level locks. The Online DDL table-level locks are used to control whether a new partition can be added to the target data table, and the Online DDL partition-level locks are used to control whether any partition in the target data table can be split, merged, have its global index partition deleted, or have its tablespace status modified. According to the target locking instruction, the target data table is locked so that the target DDL statement can be executed while the target data table is in a locked state.

2. The method according to claim 1, wherein the first locking indication information stores lock types corresponding to different types of database operations; In response to receiving a target DDL statement for operating on a target data table, if the target DDL statement is an Online DDL statement, based on the first operation to be executed corresponding to the target DDL statement, determining target locking indication information matching the first operation to be executed from pre-set first locking indication information, including: In response to receiving a target DDL statement for operating on a target data table, a first operation to be executed corresponding to the target DDL statement is determined; When the target DDL statement is an Online DDL statement, the lock type corresponding to the first operation to be executed is obtained from the first locking indication information based on the first operation to be executed, and is used as the target locking indication information.

3. The method according to claim 2, wherein the locking indication information further includes second locking indication information, the second locking indication information storing the lock types of table locks corresponding to different types of database operations; the method further includes: When the target DDL statement is an Offline DDL statement, based on the first operation to be executed, the lock type corresponding to the first operation to be executed is obtained from the second locking indication information and used as the target locking indication information; The second locking instruction information stores the lock types of table locks corresponding to different types of database operations.

4. The method according to claim 1, wherein the first locking indication information includes at least one of the following: The Online DDL lock corresponding to the column addition operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the column addition operation is a partition-level RX lock used to lock all partitions of the main table. Change the Online DDL lock corresponding to the NOT NULL constraint operation to a table-level RS lock used to lock each data table, and change the table lock corresponding to the NOT NULL constraint operation to a partition-level RX lock used to lock all partitions of the main table. The Online DDL lock corresponding to the "Set Column Default Value" operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the "Set Column Default Value" operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL lock corresponding to the rename column operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename column operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL lock corresponding to the rename table operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename table operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL lock corresponding to the row format / compression algorithm modification operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the row format / compression algorithm modification operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL locks corresponding to optimized tablespace operations are table-level RS locks used to lock each data table, and the table locks corresponding to optimized tablespace operations are partition-level RX locks used to lock all partitions of the main table. The Online DDL locks corresponding to the add / delete constraint operations are table-level RS locks used to lock each data table, and the table locks corresponding to the add / delete constraint operations are partition-level RX locks used to lock all partitions of the main table. The Online DDL lock corresponding to the rename index operation is a table-level RS lock used to lock each data table, and the table lock corresponding to the rename index operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL locks corresponding to the operations of adding / deleting / rebuilding local indexes are table-level S locks used to lock the main table and partition-level RX locks used to lock all partitions of the main table; or, the Online DDL locks corresponding to the operations of adding / deleting / rebuilding local indexes are table-level RX locks used to lock the main table and partition-level S locks used to lock all partitions of the main table, and the table locks corresponding to the operations of adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table. The Online DDL locks corresponding to adding / deleting / rebuilding global indexes are table-level S locks / RX locks used to lock the main table and table-level X locks used to lock the index table. The table locks corresponding to adding / deleting / rebuilding local indexes are partition-level RS locks used to lock all partitions of the main table. The Online DDL lock corresponding to the main table partitioning operation is a table-level RX lock / S lock used to lock each data table, and the table lock corresponding to the main table partitioning operation is a table-level RX lock used to lock each data table. The Online DDL locks corresponding to the main table move partition operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the destination partition in each data table. The table locks corresponding to the main table move partition operation are partition-level RS locks used to lock the destination partition in each data table. The Online DDL locks corresponding to the global index partition move operation are table-level RS locks used to lock the global index table and partition-level X locks used to lock the destination partition in the global index table. The table locks corresponding to the global index partition move operation are table-level RS locks used to lock the main table. The Online DDL locks corresponding to the main table partition split / merge operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the source partition and the destination partition. The table locks corresponding to the main table partition split / merge operation are partition-level RX locks used to lock the source partition and the destination partition. The Online DDL locks corresponding to the global index partition split / merge operation are table-level RS locks used to lock each data table and partition-level X locks used to lock the source partition and the destination partition. The table locks corresponding to the global index partition split / merge operation are table-level RS locks used to lock the main table. The Online DDL locks corresponding to the global index partition deletion operation are table-level RS locks used to lock the index table and partition-level X locks used to lock the target partition of the index table. The table locks corresponding to the global index partition deletion operation are table-level S locks used to lock the main table. The Online DDL lock corresponding to the column type modification operation is a table-level X lock used to lock the main table, and the table lock corresponding to the column type modification operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL lock corresponding to the column deletion operation is a table-level X lock used to lock the main table, and the table lock corresponding to the column deletion operation is a partition-level RX lock used to lock all partitions of the main table. The Online DDL locks corresponding to adding / deleting / modifying primary keys are table-level X locks used to lock the main table, while the table locks corresponding to adding / deleting / modifying primary keys are partition-level RX locks used to lock all partitions of the main table.

5. The method according to claim 3, wherein the second locking indication information includes at least one of the following: The table lock corresponding to the delete / truncate table operation is a table-level X lock used to lock each data table; The table lock corresponding to the operation of deleting / truncating the main table partition is a partition-level X lock used to lock all partitions in each data table; The table lock corresponding to the primary table partition swap operation is a partition-level X lock used to lock both the source and destination partitions.

6. The method according to claim 2 or 3, wherein the target data table is locked according to the target locking instruction information, so as to execute the target DDL statement while the target data table is in a locked state, comprising: According to the target locking instruction information, a lock matching the lock type corresponding to the first operation to be executed is added to the target data table so that the target DDL statement can be executed when the target data table is in a locked state.

7. The method according to claim 6, further comprising: Based on the first operation to be executed corresponding to the target DDL statement, a second operation to be executed that is mutually exclusive with the first operation to be executed is determined; Executing the target DDL statement while the target data table is locked includes: When the target data table is locked, execute the target DDL statement and the DDL statement corresponding to the second operation to be executed.

8. A device for executing DDL statements, comprising: A determination module is configured to respond to receiving a target DDL statement for operating on a target data table. If the target DDL statement is an Online DDL statement, based on a first pending operation corresponding to the target DDL statement, determine target locking indication information matching the first pending operation from pre-set first locking indication information. The first locking indication information indicates the locking method when implementing different types of database operations through Online DDL statements. The first locking indication information stores the lock types of Online DDL locks and table locks corresponding to different types of database operations. The Online DDL locks include: Online DDL table-level locks and Online DDL partition-level locks. The Online DDL table-level lock controls whether a new partition can be added to the target data table, and the Online DDL partition-level lock controls whether any partition in the target data table can be split, merged, have its global index partition deleted, or have its tablespace status modified. The locking module is used to lock the target data table according to the target locking instruction information, so as to execute the target DDL statement while the target data table is in a locked state.

9. A computing device, comprising: processor; Memory used to store processor-executable instructions; The processor implements the method as described in any one of claims 1-7 by executing the executable instructions.

10. A computer-readable storage medium having stored thereon computer instructions that, when executed by a processor, implement the steps of the method as claimed in any one of claims 1-7.

Images