Method, device and equipment for deleting database partitions on line and storage medium

Abstract

The invention provides a method, a device and equipment for deleting database partitions online and a storage medium, and is applied to the technical field of databases, the method comprises the following steps: executing a first transaction on a target partition which corresponds to a partition deletion instruction and is in a normal use state according to the partition deletion instruction, the first transaction comprises that a partition state corresponding to the target partition is modified from a normal use state to a deletion state, and the deletion state is used for indicating that new DDL operation and DML operation are not executed any more; after it is detected that the first transaction is submitted, a second transaction is executed on the target partition, and the second transaction comprises the steps that if it is detected that other transactions currently accessing the target partition are all ended, metadata deletion operation is executed, and the partition state corresponding to the target partition is changed into the deleted state from the deleting state after the metadata deletion operation succeeds. According to the method and the device, the transaction execution difficulty is reduced, the blockage of high-frequency concurrent DML operation is avoided, and the concurrent performance of the DML operation during partition deletion is further improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, apparatus, device and storage medium for online deletion of a database partition. Background Art

[0002] The database supports Data Definition Language (DDL) operations such as adding and deleting partitions on partitioned tables; it also supports Data Manipulation Language (DML) operations such as querying, adding, updating, and deleting partitioned tables.

[0003] When deleting a partition from a partitioned table, concurrent DML operations such as querying, adding, updating, and deleting the partitioned table may occur. Data snapshots are used to ensure data consistency during DML operations. To ensure data consistency, the database applies a blocking partition object lock to the partitioned table during the commit phase when deleting a partition. This lock blocks DML operations on the partitioned table. Furthermore, a level 8 lock is applied to the partition being deleted to prevent other concurrent transactions from accessing the partition's data during the deletion period.

[0004] Deleting a partition involves updating the partition table metadata, which is more complex than DML operations on partitioned tables. Furthermore, the frequency of partition deletion is relatively low compared to DML operations, which can block high-frequency concurrent DML operations and reduce the concurrent performance of DML operations when deleting partitions. Summary of the Invention

[0005] At least one embodiment of the present application provides a method, apparatus, device, and storage medium for online deletion of a database partition, for resolving the problem in the prior art that partition deletion operations can cause high-frequency blocking of concurrent DML operations, thereby reducing the concurrent performance of DML operations when deleting partitions.

[0006] In order to solve the above technical problems, this application is implemented as follows: In a first aspect, an embodiment of the present application provides a method for online deleting a database partition, comprising: executing, according to a partition deletion instruction, a first transaction on a target partition corresponding to the partition deletion instruction and in a normal use state, wherein the first transaction includes changing a partition state corresponding to the target partition from the normal use state to a deleting state, wherein the deleting state indicates that no new DDL operations and DML operations will be executed; After detecting that the first transaction is committed, executing a second transaction on the target partition, the second transaction includes executing a metadata deletion operation if it is detected that all other transactions currently accessing the target partition have ended, and changing the partition state corresponding to the target partition from the deleting state to the deleted state after the metadata deletion operation is successful.

[0007] Preferably, the method as described above further comprises: When executing the first transaction, a partition state protection lock for isolating concurrent structural changes is also set on the partition table corresponding to the target partition; When executing the second transaction, the partition status protection lock is released after the metadata deletion operation is successful.

[0008] Preferably, the method as described above further comprises: If it is detected that the metadata deletion operation of the target partition fails, the second transaction is executed again on the target partition according to a preset partition clearing syntax.

[0009] Specifically, in the method described above, re-executing the second transaction on the target partition according to the preset partition clearing syntax further includes: Before executing the metadata deletion operation, setting an exclusive deletion lock on the partition table corresponding to the target partition for ensuring the atomicity of the metadata operation; After executing the metadata deletion operation, all locks corresponding to the partition table are released.

[0010] Preferably, the method as described above further comprises: When performing a DDL operation on the partition table corresponding to the target partition, if it is detected that the target partition is in the deleting state, corresponding preset error information is fed back.

[0011] Preferably, the method as described above further comprises: When performing an operation of obtaining a valid partition list in a DML operation on the partition table corresponding to the target partition, if it is detected that the target partition is in the deleting state, the target partition is skipped.

[0012] In a second aspect, an embodiment of the present application provides a control device, including: a first processing module, configured to execute a first transaction on a target partition corresponding to a partition deletion instruction and in a normal use state according to a partition deletion instruction, wherein the first transaction includes modifying a partition state corresponding to the target partition from the normal use state to a deleting state, wherein the deleting state indicates that no new data definition language (DDL) operations and data manipulation language (DML) operations will be executed; The second processing module is configured to execute a second transaction on the target partition after detecting that the first transaction has been committed. The second transaction includes executing a metadata deletion operation if it is detected that all other transactions currently accessing the target partition have ended, and changing the partition state corresponding to the target partition from the deleting state to the deleted state after the metadata deletion operation is successful.

[0013] In a third aspect, an embodiment of the present application provides an electronic device comprising: a processor, a memory, and a program stored in the memory and executable on the processor, wherein the program, when executed by the processor, implements the steps of the method for online deletion of a database partition as described above.

[0014] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the method for online deletion of a database partition as described above are implemented.

[0015] In a fifth aspect, an embodiment of the present application provides a computer program product, comprising computer instructions, which, when executed by a processor, implement the steps of the method for online deletion of a database partition as described above.

[0016] Compared with the prior art, the embodiments of the present application provide a method, apparatus, device, and storage medium for online deletion of database partitions. The present application divides the less frequent partition deletion operation into two independent transactions, and divides the partition status into normal use status, deleting status, and deleted status. This reduces the difficulty of transaction execution when deleting the target partition, and avoids the recurrence of DDL operations on the partition table during the partition deletion process. Concurrent DML can be processed accordingly based on the status of the partition deletion, ensuring data consistency and avoiding the blocking of high-frequency concurrent DML operations, thereby improving the concurrency performance of DML operations when deleting partitions. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Various other advantages and benefits will become apparent to those skilled in the art upon reading the detailed description of the preferred embodiment below. The accompanying drawings are for illustration purposes only and are not to be considered as limiting the present application. The same reference symbols are used throughout the drawings to represent the same components. In the drawings: Figure 1 One of the flowcharts of the method for online deletion of a database partition in an embodiment of the present application; Figure 2 Flowchart 2 of the method for online deletion of a database partition in an embodiment of the present application; Figure 3Flowchart 3 of the method for online deletion of a database partition in an embodiment of the present application; Figure 4 A schematic structural diagram of a control device in an embodiment of the present application. DETAILED DESCRIPTION

[0018] The following describes exemplary embodiments of the present application in more detail with reference to the accompanying drawings. Although exemplary embodiments of the present application are shown in the accompanying drawings, it should be understood that the present application can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present application and to fully convey the scope of the present application to those skilled in the art.

[0019] The terms "first", "second" etc. in the specification and claims of the present application are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequential order. It should be understood that the data used in this way can be interchangeable in appropriate circumstances, so that the embodiments of the present application described herein, for example, can be implemented in an order other than those illustrated or described herein. In addition, the terms "comprise" and "have" and any of their variations are intended to cover non-exclusive inclusions, for example, the process, method, system, product or equipment comprising a series of steps or units need not be limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or that are intrinsic to these processes, methods, products or equipment. "And / or" in the specification and claims represents at least one of the connected objects.

[0020] Please refer to Figure 1 , an embodiment of the present application provides a method for online deletion of a database partition, comprising: Step S101: Executing a first transaction on a target partition corresponding to a partition deletion instruction and in a normal use state, wherein the first transaction includes changing the partition state corresponding to the target partition from the normal use state to the deleting state, wherein the deleting state indicates that no new DDL operations or DML operations will be executed. Step S102: After detecting that the first transaction is committed, executing a second transaction on the target partition. The second transaction includes executing a metadata deletion operation if it is detected that all other transactions currently accessing the target partition have ended, and changing the partition state corresponding to the target partition from the deleting state to the deleted state after the metadata deletion operation is successful.

[0021] In this embodiment, to implement online deletion of a database partition, the partition deletion operation is divided into two independent transactions: a first transaction and a second transaction. The partition status is divided into three states: normal use, deleting, and deleted. By dividing the partition deletion operation into two independent transactions, the database transaction mechanism can be effectively utilized, reducing the difficulty of creating and / or ending transactions. When the partition is in normal use, all DDL and DML operations can be executed. When the partition is in the deleting state, only previously accessed DDL and DML operations are executed, and no new DDL and DML operations are executed. When the partition is in the deleted state, no new DDL and DML operations are executed.

[0022] Based on the above division of transactions and partition states, upon receiving a partition deletion instruction instructing the deletion of a partition, the database will execute a first transaction on the target partition corresponding to the partition deletion instruction and in a normal use state. After the first transaction is committed, a second transaction will be executed. Specifically, the first transaction modifies the partition state of the target partition from a normal use state to a deleting state, where no new DDL and DML operations are executed. This allows other DML operations to access the partition normally before the transaction commits. Concurrent DML operations initiated after the transaction commits will detect the partition's deleting state and skip the partition when retrieving the list of valid partitions.

[0023] The second transaction includes performing a metadata deletion operation after detecting that all other transactions currently accessing the target partition have completed. After the metadata deletion operation is successful, the partition status is changed to a deleted state. While avoiding the impact of executing new DDL operations and DML operations on the deleted partition, the DDL operations and / or other concurrent DML operations that have already been executed are not blocked, ensuring the smooth execution of the accessed operations, thereby facilitating the improvement of the concurrency performance of DML operations when deleting partitions.

[0024] In summary, this application divides the less frequent partition deletion operation into two independent transactions, and divides the partition status into normal use status, deleting status, and deleted status. This reduces the difficulty of transaction execution when deleting the target partition, and avoids the recurrence of DDL operations on the partition table during the partition deletion process. Concurrent DML can be processed accordingly according to the status of the partition deletion, ensuring data consistency and avoiding the blocking of high-frequency concurrent DML operations, thereby improving the concurrency performance of DML operations when deleting partitions.

[0025] Specifically, to implement the above method, the insert operation module of the database can be expanded. When inserting data, if the module detects that the partition status is being deleted, it will be skipped; if the partition status is in normal use, it will allow data to be inserted into the partition.

[0026] Here, the insert operation module is a module that performs DML operations such as add, delete, and update. That is, the expanded insert operation module can ensure that DML operations will not be concurrent when the partition status is deleting; and when the partition status is in normal use, DML operations such as add, delete, and update are allowed to be performed on the partition.

[0027] Preferably, the method as described above further comprises: When executing the first transaction, a partition state protection lock for isolating concurrent structural changes is also set on the partition table corresponding to the target partition; When executing the second transaction, the partition status protection lock is released after the metadata deletion operation is successful.

[0028] In this embodiment, in addition to deleting a partition as described above, a partition state protection lock is set on the partition table in the first transaction to isolate concurrent structural changes. This lock is then released after the metadata deletion operation is successfully executed in the second transaction. This partition state protection lock further ensures that concurrent DML operations on the partitioned table are not blocked during the partition deletion process, and prevents DDL operations on the partitioned table from occurring again during the partition deletion process.

[0029] Specifically, the partition status protection lock includes but is not limited to a metadata lock at the SHARED_NO_READ_WRITE level of a My Structured Query Language (MySQL) database, a table-level lock (TM) of an Oracle database, and the like.

[0030] See also Figure 2 , at this point, the entire process of deleting a partition can be expressed as: Step S201, start the first transaction; Step S202: Setting a partition status protection lock on the partition table corresponding to the target partition; Step S203, changing the partition state corresponding to the target partition from the normal use state to the deleting state; Step S204, submitting the first transaction; Step S205, start the second transaction; Step S206, waiting for all other transactions currently accessing the target partition to complete; Step S207, performing metadata deletion operation; Step S208, after the metadata deletion operation is successful, the partition state corresponding to the target partition is changed from the deleting state to the deleted state; Step S209: commit the second transaction.

[0031] Preferably, the method as described above further comprises: If it is detected that the metadata deletion operation of the target partition fails, the second transaction is executed again on the target partition according to a preset partition clearing syntax.

[0032] In this embodiment, the execution result is detected or received during the execution of the second transaction. If the metadata deletion operation fails due to an exception during the second transaction, to ensure the partition deletion is successful, the second transaction is re-executed on the target partition using the preset clear partition syntax. Specifically, the syntax module is expanded to add a new syntax function, namely the preset clear partition syntax (CLEANUP PARTITION) described in this embodiment. This syntax command is specifically designed to handle partitions that fail to complete the deletion process in the second transaction due to exceptions such as concurrent transaction conflicts, and clean up the partition separately. This syntax function ensures database consistency and integrity, preventing data inconsistencies caused by the failure of the second transaction to delete the partition online.

[0033] In a specific embodiment, the preset partition clearing syntax can be expressed as: CLEANUP PARTITION table_name(partition_name).

[0034] Where table_name is the name of the partition table that contains the target partition, and partition_name is the name of the target partition to be cleaned.

[0035] Specifically, in the method described above, re-executing the second transaction on the target partition according to the preset partition clearing syntax further includes: Before executing the metadata deletion operation, setting an exclusive deletion lock on the partition table corresponding to the target partition for ensuring the atomicity of the metadata operation; After executing the metadata deletion operation, all locks corresponding to the partition table are released.

[0036] In this embodiment, when a second transaction is executed on the target partition again, to avoid failure of the metadata deletion operation during the second transaction due to failure to lock the partition table during the first or second transaction, an exclusive delete lock for ensuring the atomicity of the metadata operation is first set on the partition table corresponding to the target partition before the metadata deletion operation is executed again. Then, the metadata deletion operation is executed. After the metadata deletion operation is completed, all locks corresponding to the partition table are released. At this time, all locks mentioned include the exclusive delete lock applied this time and / or the partition status protection lock applied during the first transaction.

[0037] In a specific embodiment, the exclusive delete lock includes but is not limited to the MDL_EXCLUSIVE exclusive metadata lock of the MySQL database, the DDL lock of the Oracle database, etc.

[0038] Based on the above operations, in a specific embodiment, the preset partition clearing syntax can be expressed as: CLEANUP PARTITION table_name(partition_name); WITH LOCK TABLESPACE.

[0039] Where table_name is the name of the partitioned table containing the target partition, partition_name is the name of the target partition to be cleaned, and WITH LOCK TABLESPACE is used to instruct the system to lock the entire tablespace during the cleanup process, thereby ensuring that no other concurrent operations will affect the consistency of the partition data during the cleanup operation.

[0040] See also Figure 3 At this time, the entire step of re-executing the second transaction on the target partition according to the preset partition clearing syntax can be expressed as: Step S301, waiting for all other transactions currently accessing the target partition to complete; Step S302, locking the table space with an exclusive delete lock; Step S303, performing metadata deletion operation; Step S304: After the metadata deletion operation is successful, the partition state corresponding to the target partition is changed from the deleting state to the deleted state; Step S305: Release all locks corresponding to the partition table; Step S306: commit the transaction.

[0041] Preferably, the method as described above further comprises: When performing a DDL operation on the partition table corresponding to the target partition, if it is detected that the target partition is in the deleting state, corresponding preset error information is fed back.

[0042] In this embodiment, when the database executes a DDL operation on the partition table where the target partition is located, if it is detected that the target partition is in the deleting state, it is determined that it is caused by the deletion of other partitions and the first transaction has been completed. Since it is impossible to ensure that all transactions accessing the deleted partition have been completed, in order to ensure data consistency, the partition DDL operation in this state must be prevented. Therefore, the corresponding preset error message is fed back to the user to feedback this information.

[0043] Preferably, the method as described above further comprises: When performing an operation of obtaining a valid partition list in a DML operation on the partition table corresponding to the target partition, if it is detected that the target partition is in the deleting state, the target partition is skipped.

[0044] In this embodiment, when the database executes a DML operation on the partitioned table where the target partition is located, during the step of obtaining a valid partition list, if it is detected that the target partition is in the deleting state, it is determined that it is not suitable for executing a new DML operation. Therefore, the target partition is skipped and the DML operation is continued on other partitions. This ensures that deleting the target partition will not block DML operations on other partitions, thereby improving the concurrency performance of DML operations when deleting partitions.

[0045] That is, the module for obtaining partitions during query is extended. After the first transaction that sets the partition status to the deleting state is committed, if the concurrent DML transaction started detects that the partition status is the deleting state, it will skip the partition when obtaining the valid partition list.

[0046] See also Figure 4 Another embodiment of the present application provides a control device, comprising: A first processing module 401 is configured to execute a first transaction on a target partition corresponding to a partition deletion instruction and in a normal use state, the first transaction including changing the partition state corresponding to the target partition from the normal use state to a deleting state, the deleting state indicating that no new data definition language (DDL) operations and data manipulation language (DML) operations will be executed; The second processing module 402 is used to execute a second transaction on the target partition after detecting that the first transaction is committed. The second transaction includes executing a metadata deletion operation if it is detected that all other transactions currently accessing the target partition have ended, and changing the partition state corresponding to the target partition from the deleting state to the deleted state after the metadata deletion operation is successful.

[0047] Preferably, the control device as described above further includes: a third processing module, configured to set a partition state protection lock for isolating concurrent structural changes on the partition table corresponding to the target partition when executing the first transaction; The fourth processing module is configured to release the partition status protection lock after the metadata deletion operation succeeds when executing the second transaction.

[0048] Preferably, the control device as described above further includes: The fifth processing module is configured to execute the second transaction again on the target partition according to a preset partition clearing syntax if it is detected that the metadata deletion operation on the target partition fails.

[0049] Specifically, in the above method, the fifth processing module further includes: A first processing unit is configured to set an exclusive delete lock on the partition table corresponding to the target partition before executing the metadata delete operation, for ensuring the atomicity of the metadata operation; The second processing unit is configured to release all locks corresponding to the partition table after executing the metadata deletion operation.

[0050] Preferably, the control device as described above further includes: The sixth processing module is configured to feed back corresponding preset error information if it is detected that the target partition is in the deleting state when performing a DDL operation on the partition table corresponding to the target partition.

[0051] Preferably, the control device as described above further includes: The seventh processing module is configured to skip the target partition if it is detected that the target partition is in the deleting state when executing the operation of obtaining a valid partition list in the DML operation on the partition table corresponding to the target partition.

[0052] It should be noted that the control device in this embodiment is a device corresponding to the above-mentioned method for online deletion of database partitions. The implementation methods in the above-mentioned embodiments are all applicable to the embodiments of this control device and can achieve the same technical effects. The above-mentioned control device provided in the embodiment of the present application can implement all the method steps implemented in the above-mentioned method embodiment and can achieve the same technical effects. The parts and beneficial effects of this embodiment that are the same as those in the method embodiment will not be detailed here.

[0053] Another embodiment of the present application provides an electronic device, comprising: a processor, a memory, and a program stored in the memory and executable on the processor. When the program is executed by the processor, the steps of the method for online deletion of a database partition as described above are implemented, and the same technical effects can be achieved. The parts and beneficial effects of this embodiment that are identical to those of the method embodiment will not be described in detail herein.

[0054] Another embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon. When executed by a processor, the computer program implements the steps of the method for online database partition deletion described above and achieves the same technical effects. The parts and beneficial effects of this embodiment that are identical to those of the method embodiment will not be further detailed herein. The computer-readable storage medium may be, for example, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0055] Another embodiment of the present application provides a computer program product, including computer instructions, which, when executed by a processor, implement the steps of the method for online deletion of database partitions as described above, and can achieve the same technical effects. The parts and beneficial effects of this embodiment that are the same as those of the method embodiment will not be described in detail here.

[0056] Through the description of the above embodiments, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus the necessary general hardware platform. Of course, they can also be implemented by hardware, but in many cases the former is a more preferred embodiment. Based on this understanding, the technical solution of this application, or the part that contributes to the existing technology, can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a terminal (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of this application.

[0057] The embodiments of the present application are described above in conjunction with the accompanying drawings, but the present application is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the guidance of this application, ordinary technicians in this field can also make many forms without departing from the purpose of this application and the scope of protection of the claims, all of which are within the protection of this application.

Claims

1. A method for deleting a database partition online, characterized in that: include: executing, according to a partition deletion instruction, a first transaction on a target partition corresponding to the partition deletion instruction and in a normal use state, wherein the first transaction includes changing a partition state corresponding to the target partition from the normal use state to a deleting state, wherein the deleting state indicates that no new data definition language (DDL) operations and data manipulation language (DML) operations will be executed; After detecting that the first transaction is committed, executing a second transaction on the target partition, the second transaction includes executing a metadata deletion operation if it is detected that all other transactions currently accessing the target partition have ended, and changing the partition state corresponding to the target partition from the deleting state to the deleted state after the metadata deletion operation is successful.

2. The method according to claim 1, characterized in that Also includes: When executing the first transaction, a partition state protection lock for isolating concurrent structural changes is also set on the partition table corresponding to the target partition; When executing the second transaction, the partition status protection lock is released after the metadata deletion operation is successful.

3. The method according to claim 1 or 2, characterized in that Also includes: If it is detected that the metadata deletion operation of the target partition fails, the second transaction is executed again on the target partition according to a preset partition clearing syntax.

4. The method according to claim 3, characterized in that The step of re-executing the second transaction on the target partition according to the preset partition clearing syntax further includes: Before executing the metadata deletion operation, setting an exclusive deletion lock on the partition table corresponding to the target partition for ensuring the atomicity of the metadata operation; After executing the metadata deletion operation, all locks corresponding to the partition table are released.

5. The method according to claim 1, wherein Also includes: When the DDL operation is performed on the partition table corresponding to the target partition according to the partition DDL operation instruction, if it is detected that the target partition is in the deleting state, corresponding preset error information is fed back.

6. The method according to claim 1, characterized in that Also includes: According to the partition DML operation instruction, when executing the operation of obtaining a valid partition list in the DML operation on the partition table corresponding to the target partition, if it is detected that the target partition is in the deleting state, the target partition is skipped.

7. A control device, characterized in that: include: a first processing module, configured to execute a first transaction on a target partition corresponding to a partition deletion instruction and in a normal use state according to a partition deletion instruction, wherein the first transaction includes modifying a partition state corresponding to the target partition from the normal use state to a deleting state, wherein the deleting state indicates that no new data definition language (DDL) operations and data manipulation language (DML) operations will be executed; The second processing module is configured to execute a second transaction on the target partition after detecting that the first transaction has been committed. The second transaction includes executing a metadata deletion operation if it is detected that all other transactions currently accessing the target partition have ended, and changing the partition state corresponding to the target partition from the deleting state to the deleted state after the metadata deletion operation is successful.

8. An electronic device, characterized in that: include: A processor, a memory, and a program stored in the memory and executable on the processor, wherein when the program is executed by the processor, the steps of the method for online deletion of a database partition according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of the method for online deletion of a database partition according to any one of claims 1 to 6.

10. A computer program product, characterized in that The method comprises computer instructions, which, when executed by a processor, implement the steps of the method for online deletion of a database partition according to any one of claims 1 to 6.

Images