Atomic table cutting method and device for online table change of database and electronic equipment

Abstract

The invention relates to an atomic table cutting method and device for online table change of a database and electronic equipment. The method comprises the steps that under the condition that a first thread of a table changing thread is in a table lock release preparation state of an original table lock corresponding to an original table, state information of a second thread of the table changing thread is obtained through the first thread; if the state information represents that the second thread is in the state of applying for the original table lock in the renaming processing, the first thread is controlled to release the original table lock; calling a second thread to obtain an original table lock, modifying the table name of the original table into the table name of the temporary table based on the original table lock, a temporary table lock corresponding to the temporary table and a shadow table lock corresponding to the shadow table, modifying the table name of the shadow table into the table name of the original table, and releasing the original table lock, the temporary table lock and the shadow table lock after the table names are modified; and calling the first thread to obtain the temporary table lock, and deleting the original table with the table name corresponding to the table name of the temporary table after renaming by using the temporary table lock. According to the method, the data consistency of the atomic cut table can be ensured.

Description

Technical Field

[0001] This application relates to the field of database technologies, and in particular, to an atomic table splitting method, apparatus, and electronic device for online table modification in a database. Background Art

[0002] During the online table modification process of a relational database (such as MySQL), the atomic table splitting method of the gh-ost solution is generally selected, such as creating a shadow table, modifying the table structure on the shadow table, then batch migrating the existing data to the shadow table, synchronizing the incremental data to the shadow table using binlog at the same time, and finally switching the table name to achieve online table modification. However, concurrent business requests, such as concurrent business writes, will occur during the online table modification process. In this case, if one of the threads used for online table modification releases the original table lock, the thread used for renaming in the online table modification may be waiting for the shadow table lock and unable to obtain the table lock of the original table, and the renaming operation is blocked; in this way, the business request can obtain the table lock of the original table first, and the write operation of the business obtains the table lock of the original table first and executes, resulting in writing data to the original table, and the original table will be deleted later, ultimately resulting in the loss of the data written by the business and the inconsistency of the data before and after the table name switch. Summary of the Invention

[0003] This application provides an atomic table splitting method, apparatus, and electronic device for online table modification in a database, which can ensure data consistency of atomic table splitting during the online table modification process in the database. The technical solution of this application is as follows:

[0004] According to the first aspect of the embodiments of this application, an atomic table splitting method for online table modification in a database is provided, including:

[0005] When the first thread of the table modification thread is in the state of preparing to release the table lock of the original table, obtain the status information of the second thread of the table modification thread through the first thread; the first thread is used for processing other than the renaming process of the table during the online table modification process, and the second thread is used for the renaming process of the table during the online table modification process; the original table lock is the table lock corresponding to the original table targeted by the online table modification in the database;

[0006] If the status information indicates that the second thread is in the state of applying for the original table lock during the renaming process, control the first thread to release the original table lock;

[0007] Call the second thread to obtain the original table lock, and based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name modification;

[0008] Invoke the first thread to obtain the temporary table lock, and use the temporary table lock to delete the original table corresponding to the table name of the renamed table with the table name being the temporary table.

[0009] According to the second aspect of the embodiments of the present application, there is provided an atomic table switching device for online database table modification, including:

[0010] A status information acquisition module, configured to, when the first thread of the table modification thread is in a state of preparing to release the table lock of the original table, obtain the status information of the second thread of the table modification thread through the first thread; the first thread is used for processing other than the table renaming process in the online table modification process, and the second thread is used for the table renaming process in the online table modification process; the original table lock is the table lock corresponding to the original table targeted by the online database table modification;

[0011] An original table lock release control module, configured to, if the status information indicates that the second thread is in a state of applying for the original table lock during the renaming process, control the first thread to release the original table lock;

[0012] A renaming module, configured to invoke the second thread to obtain the original table lock, and based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name modification;

[0013] A table deletion module, configured to invoke the first thread to obtain the temporary table lock, and use the temporary table lock to delete the original table corresponding to the table name of the renamed table with the table name being the temporary table.

[0014] In a possible implementation manner, the status information acquisition module includes:

[0015] A status information acquisition unit, configured to query the system view through the first thread to obtain the status information of the second thread of the table modification thread.

[0016] In a possible implementation manner, the device further includes:

[0017] An original table lock holding control module, configured to, when the status information indicates that the second thread is not in a state of applying for the original table lock during the renaming process, control the first thread to continue holding the original table lock.

[0018] In a possible implementation manner, the device further includes:

[0019] A business thread response module, configured to, during the online table modification process, if an operation request of a business thread for the original table is detected, monitor the original table lock release information of the second thread releasing the original table lock during the renaming process;

[0020] An operation request execution module, configured to, when the original table lock release information indicates release, call the business thread to acquire the original table lock and execute the operation request using the original table lock; or, configured to, when the original table lock release information indicates not released, set the business thread to a waiting state for waiting for the original table lock.

[0021] In a possible implementation manner, the apparatus further includes:

[0022] A data migration module, configured to, in response to an online table modification request, call the first thread to create the shadow table, modify the table structure in the shadow table, and synchronize the data in the original table to the shadow table;

[0023] A lock table module, configured to call the first thread to create a temporary table and perform lock table operations on the original table and the temporary table;

[0024] A table lock release module, configured to use the first thread to delete the temporary table and release the temporary table lock and the shadow table lock, so that the first thread is in a table lock release preparation state for the original table lock.

[0025] In a possible implementation manner, the apparatus further includes:

[0026] A temporary table lock application module, configured to, in response to a renaming request of the second thread, call the second thread to apply for the temporary table lock;

[0027] A shadow table lock application module, configured to, when the second thread acquires the temporary table lock, call the second thread to apply for the shadow table lock;

[0028] An original table lock application module, configured to, when the second thread acquires the shadow table lock, call the second thread to apply for the original table lock, so that the second thread enters a state of applying for the original table lock.

[0029] In a possible implementation manner, the apparatus further includes:

[0030] A waiting table lock module, configured to, when the second thread fails to acquire the temporary table lock or the shadow table lock, set the second thread to a waiting table lock state.

[0031] In a possible implementation manner, the apparatus further includes:

[0032] A table lock waiting duration threshold obtaining module, configured to obtain the table lock waiting duration threshold corresponding to the second thread;

[0033] A timeout retry module, configured to control the second thread to retry the rename request when the duration of the second thread in the waiting table lock state or the duration of the second thread in the state of applying for the original table lock during the rename process reaches the table lock waiting duration threshold.

[0034] In a possible implementation manner, the rename module includes:

[0035] An original table lock obtaining unit, configured to, when there is an operation request of a service thread on the original table, set the service thread to a waiting state for waiting for the original table lock, and call the second thread to obtain the original table lock.

[0036] According to a third aspect of the embodiments of the present application, there is provided an electronic device, including: a processor; a memory for storing executable instructions of the processor; wherein, the processor is configured to execute the instructions to implement the method according to any one of the first aspects above.

[0037] According to a fourth aspect of the embodiments of the present application, there is provided a computer-readable storage medium, when instructions in the computer-readable storage medium are executed by a processor of an electronic device, enabling the electronic device to execute the method according to any one of the first aspects of the embodiments of the present application.

[0038] According to a fifth aspect of the embodiments of the present application, there is provided a computer program product, including computer instructions, when the computer instructions are executed by a processor, enabling the computer to execute the method according to any one of the first aspects of the embodiments of the present application.

[0039] The technical solutions provided by the embodiments of the present application at least bring the following beneficial effects:

[0040] In this application, when the first thread of the table modification thread is in the state of preparing to release the table lock of the original table, the state information of the second thread of the table modification thread is obtained through the first thread. That is, by setting the state of the first thread to be preparing to release the table lock, the original table lock will not be immediately released. Instead, the state information of the second thread of the table modification thread is obtained. When the state information indicates that the second thread is in the state of applying for the original table lock during the renaming process, the first thread is controlled to release the original table lock. In this way, even in the case where a concurrent business thread applies for the original table lock, based on the database feature that the priority of the renaming thread is higher than that of the business thread, the second thread can obtain the original table lock first, so that the original table lock can be used for renaming the table. That is, based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, the table name of the original table is modified to the table name of the temporary table, and the table name of the shadow table is modified to the table name of the original table. In addition, the first thread is called to delete the above-mentioned original table whose table name is the table name of the temporary table after renaming, so that the online table modification process of the database can maintain atomicity and data consistency.

[0041] It should be understood that the above general description and the following detailed description are only exemplary and explanatory, and do not limit this application. Brief Description of the Drawings

[0042] The drawings here are incorporated into the specification and constitute a part of this specification, showing embodiments consistent with this application, and are used together with the specification to explain the principles of this application, and do not constitute an improper limitation to this application.

[0043] Figure 1 is a schematic diagram of an application environment shown according to an exemplary embodiment.

[0044] Figure 2 is a flowchart of an atomic table splitting method for online table modification of a database shown according to an exemplary embodiment.

[0045] Figure 3 is a schematic flowchart of an atomic table splitting method for online table modification of a database shown according to an exemplary embodiment.

[0046] Figure 4 is a schematic diagram of a process for handling a situation where a business thread requests to insert a data record during an atomic table splitting process shown according to an exemplary embodiment.

[0047] Figure 5 is a block diagram of an atomic table splitting device for online table modification of a database shown according to an exemplary embodiment.

[0048] Figure 6 is a block diagram of an electronic device for atomic table splitting for online table modification of a database shown according to an exemplary embodiment. Detailed Description of the Embodiments

[0049] Various exemplary embodiments, features, and aspects of the present application will be described in detail below with reference to the accompanying drawings. Like reference numerals in the drawings denote elements having the same or similar functions. Although various aspects of the embodiments are shown in the drawings, the drawings do not have to be drawn to scale unless otherwise specified.

[0050] As used herein, the term "exemplary" means "serving as an example, embodiment, or illustration." Any embodiment described herein as "exemplary" is not necessarily to be construed as superior or better than other embodiments.

[0051] In the embodiments of the present application, the term "module" or "unit" refers to a computer program or a part of a computer program with a predetermined function, which works together with other related parts to achieve a predetermined goal, and can be implemented in whole or in part by using software, hardware (such as a processing circuit or a memory), or a combination thereof. Similarly, one processor (or multiple processors or memories) can be used to implement one or more modules or units. In addition, each module or unit can be a part of an overall module or unit that includes the function of the module or unit.

[0052] In addition, for a better illustration of the present application, numerous specific details are given in the following detailed implementation manners. Those skilled in the art should understand that the present application can also be implemented without some specific details. In some instances, methods, means, elements, and circuits well-known to those skilled in the art are not described in detail so as to highlight the gist of the present application.

[0053] Please refer to Figure 1 , Figure 1 which shows a schematic diagram of an application system provided according to an embodiment of the present application. The application system can be used for the atomic table splitting method of online table modification of the database of the present application. As Figure 1 shown, the application system can at least include a server 01 and a terminal 02.

[0054] In the embodiments of the present application, the server 01 can be used for atomic table splitting processing of online table modification of the database. The server 01 can include an independent physical server, or can also be a server cluster or a distributed system composed of multiple physical servers, or can also be a cloud server providing basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms.

[0055] In the embodiments of the present application, the terminal 02 can be used to trigger operation requests for tables in a database, such as write requests, data deletion requests, etc., which are not limited in the present disclosure. The terminal 02 can include entity devices of various types such as smart phones, desktop computers, tablet computers, laptop computers, smart speakers, digital assistants, augmented reality (AR) / virtual reality (VR) devices, smart wearable devices, etc. The entity device can also include software running on the entity device, such as application programs, etc. The operating system running on the terminal 02 in the embodiments of the present application can include but is not limited to Android system, IOS system, Linux, Windows, etc.

[0056] In addition, it should be noted that Figure 1 What is shown is only an application environment of the atomic table splitting method for online table modification of the database provided by the present application.

[0057] In the embodiments of this specification, the above-mentioned terminal 02 and the server 01 can be directly or indirectly connected through wired or wireless communication methods, which are not limited in the present application.

[0058] In a specific embodiment, when the server 02 is a distributed system, the distributed system can be a blockchain system. Blockchain is a new application mode of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanism, and encryption algorithms. Blockchain, in essence, is a decentralized database, a string of data blocks generated by using cryptographic methods. Each data block contains information about a batch of network transactions, which is used to verify the validity (anti-counterfeiting) of the information and generate the next block. The blockchain can include the blockchain underlying platform, the platform product service layer, and the application service layer.

[0059] The underlying blockchain platform may include processing modules such as user management, basic services, smart contracts, and operation monitoring. Among them, the user management module is responsible for the identity information management of all blockchain participants, including maintaining the generation of public and private keys (account management), key management, and maintaining the correspondence between the real identity of users and blockchain addresses (permission management). And under authorization, it supervises and audits the transaction situations of certain real identities, and provides the rule configuration for risk control (risk control audit); the basic service module is deployed on all blockchain node devices to verify the validity of business requests, and records the valid requests on the storage after consensus. For a new business request, the basic service first performs interface adaptation parsing and authentication processing (interface adaptation), then encrypts the business information through the consensus algorithm (consensus management), transmits it to the shared ledger completely and consistently after encryption (network communication), and performs record storage; the smart contract module is responsible for the registration and issuance of contracts, as well as contract triggering and contract execution. Developers can define contract logic through a certain programming language, publish it to the blockchain (contract registration), trigger the execution by calling keys or other events according to the logic of the contract terms, complete the contract logic, and also provide functions for contract upgrade and cancellation; the operation monitoring module is mainly responsible for the deployment, configuration modification, contract setting, cloud adaptation during the product release process, and the visual output of the real-time state during product operation, such as: alarm, monitoring network conditions, monitoring the health status of node devices, etc.

[0060] The platform product service layer provides the basic capabilities and implementation frameworks of typical applications. Developers can build on these basic capabilities and overlay the characteristics of the business to complete the blockchain implementation of the business logic. The application service layer provides application services based on the blockchain solution for business participants to use.

[0061] It should be noted that in the specific implementation of this application, when it comes to user-related data, when the following embodiments of this application are applied to specific products or technologies, user permission or consent needs to be obtained, and the collection, use, and processing of relevant data need to comply with the relevant laws, regulations, and standards of relevant countries and regions.

[0062] Before introducing the method embodiments provided by this application, the application scenarios, related terms, or nouns that may be involved in the method embodiments of this application are briefly introduced first, so as to facilitate the understanding of those skilled in the art of this application.

[0063] MySQL: A relational database that supports the atomicity of transactions. Atomicity can mean that a transaction is an indivisible unit of work, and the operations in it are either all done or none of them are done; if an SQL statement in a transaction fails to execute, the statements that have been executed must also be rolled back, and the database returns to the state before the transaction.

[0064] gh-ost: An online table modification solution for MySQL. During the table modification process, a shadow table is first created, the table structure is modified on the shadow table, then the existing data is batch migrated to the shadow table, and at the same time, the incremental data is synchronized to the shadow table using the bin log. Finally, the table name is switched to achieve online table modification. This application is specifically proposed for the problem of data inconsistency caused by concurrent business threads applying for the original table lock during the table name switching phase.

[0065] Atomic table switching: Ensure that the process of switching the table name is an atomic operation, that is, ensure the atomicity of the table name switching process.

[0066] Figure 2 It is a flowchart of an atomic table switching method for online database table modification shown according to an exemplary embodiment. As Figure 2 shown, it may include the following steps.

[0067] Step S201, when the first thread of the table modification thread is in the state of preparing to release the table lock of the original table, obtain the status information of the second thread of the table modification thread through this first thread.

[0068] In the embodiments of this specification, the table modification thread may refer to a thread used for online table modification in the database. Exemplarily, the database may be a relational database, such as MySQL, and this application is not limited thereto. As an example, the table modification thread may include a first thread and a second thread. Among them, the first thread may be used for processing other than the renaming of the table during the online table modification process, such as the creation of the table, the deletion of the table, etc. The second thread may be used for the renaming process of the table during the online table modification process; the above-mentioned original table lock may be the table lock corresponding to the original table targeted by the online database table modification.

[0069] In practical applications, as an example, the first thread of the table modification thread being in the state of preparing to release the table lock of the original table can be triggered by the first thread performing a deletion operation on the temporary table created during online table modification. That is, when the first thread deletes the temporary table, the state information of the second thread of the table modification thread can be obtained through the first thread, so as to determine whether the original table lock needs to be released. Because if the second thread has not yet reached the stage of applying for the original table lock, and the first thread releases the original table lock, assuming that there are concurrent business threads applying for the original table lock, the original table lock will be obtained by the business threads to perform business operations such as writing to the original table, which will lead to inconsistent data in the atomic table cut. For example, the data in the shadow table will be missing the data written to the original table. This application makes use of the fact that when the rename operation and the business DML operation (Data Manipulation Language) in online table modification simultaneously request the table metadata lock, the thread priority of the rename operation is higher than that of the DML operation. For example, the thread of the rename operation can include the second thread in the embodiments of this specification, and the thread of the DML operation can include the business thread in the embodiments of this specification. Thus, without changing the characteristics of the database, the original table lock can be released only when the second thread applies for the original table lock. In this way, even if there are business threads applying for the original table lock, the second thread can obtain the original table lock first, thereby ensuring the atomicity and data consistency of the table cut.

[0070] In a possible implementation manner, the above-mentioned obtaining the state information of the second thread of the table modification thread through the first thread may include: querying the system view through the first thread to obtain the state information of the second thread of the table modification thread. Exemplarily, the state information of the second thread may include, but is not limited to, any one of the following: being in the state of applying for the temporary table lock; being in the state of having obtained the temporary table lock and applying for the shadow table lock; being in the state of having obtained the temporary table lock and the shadow table lock and applying for the original table lock. Among them, the temporary table lock may refer to the table lock corresponding to the temporary table, and the shadow table lock may refer to the table lock corresponding to the shadow table. It should be noted that being in the state of applying for the temporary table lock, and being in the state of having obtained the temporary table lock and applying for the shadow table lock, may be referred to as the waiting table lock state. Being in the state of having obtained the temporary table lock and the shadow table lock and applying for the original table lock may be referred to as the state where the second thread is applying for the original table lock during the rename process, or as the state where the rename process of the second thread is applying for the original table lock.

[0071] In the embodiments of this specification, in a possible implementation manner, referring to Figure 3 , before the step of obtaining the state information of the second thread of the table modification thread through the first thread when the first thread of the table modification thread is in the state of preparing to release the table lock of the original table, the atomic table cut method may further include the following steps:

[0072] In response to an online table modification request, a first thread is called to create a shadow table, modify the table structure in the shadow table, and synchronize the data in the original table to the shadow table;

[0073] Call the first thread to create a temporary table, and lock the original table and the temporary table;

[0074] Use the first thread to delete the temporary table, and release the temporary table lock and the shadow table lock, so that the first thread is in a state of preparing to release the table lock of the original table.

[0075] In the embodiments of this specification, in response to an online table modification request, a first thread can be called to create a shadow table, so that the table structure can be modified in the shadow table. For example, the table structure can be modified in the shadow table according to the modification requirements, which is not limited in this application. And the data in the original table can be synchronized to the shadow table. Exemplarily, the online table modification request can be triggered by a data manager at the terminal, which is not limited in this application.

[0076] Further, a first thread can be called to create a temporary table, and lock the original table and the temporary table; and the first thread can be used to delete the temporary table, and release the temporary table lock and the shadow table lock, so that the first thread is in a state of preparing to release the table lock of the original table.

[0077] Step S203, when the status information indicates that the second thread is in a state of applying for the original table lock during the renaming process, control the first thread to release the original table lock.

[0078] In the embodiments of this specification, after obtaining the status information of the second thread of the table modification thread, it can be determined whether the status information indicates that the second thread is in a state of applying for the original table lock during the renaming process, that is, it is determined whether the second thread is in a state of obtaining the temporary table lock and the shadow table lock and applying for the original table lock. If so, the first thread can be controlled to release the original table lock, so as to ensure that the original table lock is preferentially obtained by the second thread.

[0079] Optionally, referring to Figure 3 , the method may further include: when the status information indicates that the second thread is not in a state of applying for the original table lock during the renaming process, the first thread can be controlled to continue to hold the original table lock until the status information indicates that the second thread is in a state of applying for the original table lock during the renaming process, so as to ensure that the original table lock can be preferentially obtained by the second thread.

[0080] Step S205, call the second thread to obtain the original table lock, and based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name is modified.

[0081] In the embodiments of this specification, when the first thread releases the original table lock, the second thread can be called to acquire the original table lock. Thus, the second thread can, based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name modification, completing the renaming process, that is, the renaming operation is successful.

[0082] In practical applications, there may be business threads that concurrently apply for the original table lock. In this case, the step of calling the second thread to acquire the original table lock can include: when there is an operation request from a business thread for the original table, the second thread can be called to acquire the original table lock, and the business thread can be set to a waiting state waiting for the original table lock.

[0083] Step S207: Call the first thread to acquire the temporary table lock, and use the temporary table lock to delete the original table corresponding to the table name of the temporary table after renaming.

[0084] In the embodiments of this specification, when the second thread successfully executes the renaming operation, the first thread can be called to acquire the temporary table lock, enabling the first thread to use the temporary table lock to delete the original table corresponding to the table name of the temporary table after renaming.

[0085] In this application, when the first thread of the table modification thread is in the state of preparing to release the table lock of the original table, the state information of the second thread of the table modification thread is obtained through the first thread, that is, by setting the state of the first thread to be prepared to release the table lock, the original table lock is not immediately released, but the state information of the second thread of the table modification thread is obtained. When the state information indicates that the second thread is in the state of applying for the original table lock during the renaming process, the first thread is controlled to release the original table lock. In this way, even in the case where concurrent business threads apply for the original table lock, based on the database feature that the priority of the renaming thread is higher than that of the business thread, the second thread can obtain the original table lock first, so that the original table lock can be used for table renaming, that is, based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, modify the table name of the shadow table to the table name of the original table, and call the first thread to delete the above-mentioned original table corresponding to the table name of the temporary table after renaming, so that the atomicity and data consistency can be maintained during the online table modification process of the database.

[0086] In an alternative embodiment, when an operation request from a business thread for the original table occurs during the online table modification process, the operation request of the business thread can be processed in a waiting manner. Based on this, the above atomic table splitting method may further include the following steps:

[0087] During the above online table modification process, if an operation request of the business thread on the original table is detected, monitor the original table lock release information of the second thread releasing the original table lock during the renaming process;

[0088] When the original table lock release information is released, call the business thread to obtain the original table lock and use the original table lock to execute the above operation request;

[0089] When the original table lock release information is not released, the business thread can be set to a waiting state waiting for the original table lock.

[0090] In the embodiments of this specification, considering that the priority of the second thread is higher than that of the business thread, in order to ensure that the second thread can obtain the original table lock, when the operation request of the concurrent business thread on the original table applies for the original table lock, the release situation of the original table lock by the second thread can be monitored, so that after the second thread's renaming process uses the original table lock and releases it, it is the turn of the business thread to use the original table lock. Based on this, the above atomic table splitting method may further include, during the above online table modification process, if an operation request of the business thread on the original table is detected, monitoring the original table lock release information of the second thread releasing the original table lock during the renaming process; so that when the original table lock release information is released, the business thread can be called to obtain the original table lock and use the original table lock to execute the above operation request, such as writing data into the original table, etc. Optionally, when the original table lock release information is not released, the business thread can be set to a waiting state waiting for the original table lock, avoiding the problem of inconsistent data in atomic table splitting caused by the business thread obtaining the original table lock during the renaming block of the second thread.

[0091] In an optional implementation manner, the order in which the second thread applies for the table lock can be to apply for the temporary table lock, apply for the shadow table lock, and apply for the original table lock in sequence. Based on this, the above atomic table splitting method may further include the following steps:

[0092] In response to the renaming request of the second thread, the second thread can be called to apply for the temporary table lock;

[0093] When the second thread obtains the temporary table lock, the second thread can be called to apply for the shadow table lock;

[0094] When the second thread obtains the shadow table lock, the second thread can be called to apply for the original table lock, so that the second thread enters the state of applying for the original table lock. That is, when the second thread obtains the temporary table lock and the shadow table lock and applies for the original table lock, it can be regarded that the second thread is in the state of applying for the original table lock. In this case, the second thread is also waiting for the original table lock.

[0095] Optionally, in the case where the second thread fails to obtain the temporary table lock or the shadow table lock, the second thread can be set to the waiting table lock state.

[0096] In an alternative implementation, a timeout retry mechanism can be configured for the second thread. Based on this, the above atomic table switching method may further include the following steps: obtaining a table lock waiting duration threshold corresponding to the second thread, and this application does not limit this table lock waiting duration threshold. Further, when the duration for which the second thread is in the waiting table lock state reaches the table lock waiting duration threshold or the duration for which the second thread is in the state of applying for the original table lock during the renaming process reaches the table lock waiting duration threshold, the second thread can be controlled to retry the renaming request. By configuring a timeout retry mechanism for the second thread, the atomicity of online table modification can be further ensured.

[0097] Optionally, if the duration for which the second thread is in the waiting state does not reach the table lock waiting duration threshold, the second thread can be maintained in the waiting table lock state. If the duration for which the second thread is in the state of applying for the original table lock during the renaming process does not reach the table lock waiting duration threshold, the second thread can be maintained in the state of applying for the original table lock.

[0098] Refer to Figure 4 , as an application example, assume that the table name of the original table T1 is t_example, the table name of the temporary table T2 is _t_example_del, and the table name of the shadow table T3 is _t_example_gho. Then the process of online table modification can be as follows:

[0099] First thread: In response to the online table modification request, the first thread is called to create the shadow table T3, set the table name to _t_example_gho, so that the table structure can be modified in the shadow table T3, and the data in the original table T1 can be synchronized to the shadow table T3, so that the shadow table lock can be released; further, the first thread can be called to create the temporary table T2, set the table name to _t_example_del, and lock the original table T1 and the temporary table T2.

[0100] Second thread: Trigger a renaming request. For example, the table name of the original table T1 can be modified to the table name of the temporary table, that is, _t_example_del, and the table name of the shadow table can be modified to the table name of the original table, that is, t_example. Since the first thread obtains the original table lock and the temporary table lock, the write operations of other threads to the original table and the temporary table will be blocked. For example, the renaming operation of the second thread and the write operation of the business thread to the record with id = 2 to the original table are both blocked.

[0101] Specifically, during the renaming process, the second thread can first apply for the temporary table lock; when the second thread obtains the temporary table lock, it can call the second thread to apply for the shadow table lock; then, when the second thread obtains the shadow table lock, it can call the second thread to apply for the original table lock, so that the second thread enters the state of applying for the original table lock. That is, when the second thread obtains the temporary table lock and the shadow table lock and applies for the original table lock, it can be considered that the second thread is in the state of applying for the original table lock. In this case, the second thread is also waiting for the original table lock, so it can be called the waiting original table lock state. The business thread is in the waiting state of waiting for the original table lock.

[0102] The first thread can delete the temporary table and release the temporary table lock, so that the first thread is in the table lock release preparation state of the original table lock. This table lock release preparation state can mean not immediately releasing the original table lock.

[0103] Correspondingly, the second thread can obtain the temporary table lock and the shadow table lock in sequence, so as to apply for the original table lock. Since the original table lock has not been released yet, it needs to wait for the original table lock and enter the state of applying for the original table lock. Optionally, the table lock waiting duration threshold corresponding to the second thread can be used to control the waiting timeout retry. For example, when the waiting duration reaches the table lock waiting duration threshold, the second thread can be made to retry the renaming request.

[0104] After deleting the temporary table, the first thread does not immediately release the original table lock. Instead, it chooses to query the system view through the first thread to obtain the status information of the second thread, which is the thread modifying the table, to ensure that the second thread is blocked in the stage of applying for the original table lock. Correspondingly, if the second thread is not in the state of applying for the original table lock, the first thread can continue to hold the original table lock; if the status information of the second thread is that it has obtained the temporary table lock and the shadow table lock and is in the state of applying for the original table lock, such as the blocked situation of applying for the original table lock as described above, the original table lock is released.

[0105] Correspondingly, when the first thread holds the original table lock, the second thread can obtain the original table lock, and thus, based on the original table lock, the temporary table lock, and the shadow table lock, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table. The table names of the original table and the shadow table after renaming are as follows:

[0106] Original table T1: The renamed table name is _t_example_del;

[0107] Shadow table T3: The renamed table name is t_example.

[0108] Further, the second thread can release the original table lock, the temporary table lock, and the shadow table lock after the table name is modified, and the renaming operation is successful. After the second thread finishes the renaming operation and the original table lock is released, the business thread can obtain the original table lock when writing the record with id = 2 and successfully execute the write operation.

[0109] Finally, the first thread can delete the table named _t_example_del, that is, delete the original table T1. For example, the original table T1 with the renamed table name _t_example_del can be deleted using the temporary table lock, and the online table modification is completed.

[0110] Compared with the existing atomic switching method (such as gh-ost), the technical solution of this application splits the deletion of the temporary table and the release of the table lock of the original table into two steps. After deleting the temporary table, the status information of the second thread is checked through the system view of MySQL to ensure that the renaming operation has obtained the temporary table lock and the shadow table lock and is applying for the table lock of the original table. After confirmation, the table lock of the original table is released, so as to ensure that the entire switching process is atomic and there will be no situation where the business thread writes wrongly during the switching process, ensuring data consistency in atomic table switching.

[0111] Figure 5 It is a block diagram of an atomic table switching device for online database table modification shown according to an exemplary embodiment. Referring to Figure 5 The device may include:

[0112] A status information acquisition module 501, configured to obtain the status information of the second thread of the table modification thread through the first thread when the first thread of the table modification thread is in a state of preparing to release the table lock of the original table; the first thread is used for processing other than the renaming process of the table during the online table modification process, and the second thread is used for the renaming process of the table during the online table modification process; the original table lock is the table lock corresponding to the original table targeted by the online database table modification;

[0113] An original table lock release control module 503, configured to control the first thread to release the original table lock when the status information indicates that the second thread is in a state of applying for the original table lock during the renaming process;

[0114] A renaming module 505, configured to call the second thread to obtain the original table lock, and based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name is modified;

[0115] A table deletion module 507 is used to call the first thread to obtain the temporary table lock, and use the temporary table lock to delete the original table corresponding to the table name of the renamed table with the table name of the temporary table.

[0116] In this application, when the first thread of the table modification thread is in the state of preparing to release the table lock of the original table, the state information of the second thread of the table modification thread is obtained through the first thread. That is, by setting the state of the first thread to be prepared to release the table lock, the original table lock will not be immediately released, but the state information of the second thread of the table modification thread is obtained. When the state information indicates that the second thread is in the state of applying for the original table lock during the renaming process, the first thread is controlled to release the original table lock. In this way, even in the case where a concurrent business thread applies for the original table lock, based on the database feature that the priority of the renaming thread is higher than that of the business thread, the second thread can obtain the original table lock first, so that the original table lock can be used for renaming the table. That is, based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, the table name of the original table is modified to the table name of the temporary table, and the table name of the shadow table is modified to the table name of the original table, and the first thread is called to delete the above-mentioned original table corresponding to the table name of the renamed table with the table name of the temporary table, so that the atomicity and data consistency can be maintained during the online table modification process of the database.

[0117] In a possible implementation manner, the above state information acquisition module 501 may include:

[0118] A state information acquisition unit is used to query the system view through the first thread to obtain the state information of the second thread of the table modification thread.

[0119] In a possible implementation manner, the above atomic table switching device may further include:

[0120] An original table lock holding control module is used to control the first thread to continue holding the original table lock when the state information indicates that the second thread is not in the state of applying for the original table lock during the renaming process.

[0121] In a possible implementation manner, the above atomic table switching device may further include:

[0122] A business thread response module is used to monitor the original table lock release information of the second thread releasing the original table lock during the renaming process if an operation request of the business thread on the original table is detected during the online table modification process;

[0123] An operation request execution module, which is used to, when the original table lock release information indicates release, call the service thread to obtain the original table lock and use the original table lock to execute the operation request; or, when the original table lock release information indicates non-release, set the service thread to a waiting state waiting for the original table lock.

[0124] In a possible implementation manner, the above atomic table splitting device may further include:

[0125] A data migration module, which is used to, in response to an online table modification request, call the first thread to create the shadow table, modify the table structure in the shadow table, and synchronize the data in the original table to the shadow table;

[0126] A table locking module, which is used to call the first thread to create a temporary table and perform table locking operations on the original table and the temporary table;

[0127] A table lock release module, which is used to use the first thread to delete the temporary table and release the temporary table lock and the shadow table lock, so that the first thread is in a table lock release preparation state for the original table lock.

[0128] In a possible implementation manner, the above atomic table splitting device may further include:

[0129] A temporary table lock application module, which is used to, in response to the renaming request of the second thread, call the second thread to apply for the temporary table lock;

[0130] A shadow table lock application module, which is used to, when the second thread obtains the temporary table lock, apply for the shadow table lock;

[0131] An original table lock application module, which is used to, when the second thread obtains the shadow table lock, call the second thread to apply for the original table lock, so that the second thread enters the state of applying for the original table lock.

[0132] In a possible implementation manner, the above atomic table splitting device may further include:

[0133] A waiting table lock module, which is used to, when the second thread fails to obtain the temporary table lock or the shadow table lock, set the second thread to a waiting table lock state.

[0134] In a possible implementation manner, the above atomic table splitting device may further include:

[0135] A table lock waiting duration threshold acquisition module, which is used to acquire the table lock waiting duration threshold corresponding to the second thread;

[0136] A timeout retry module, configured to control the second thread to retry the rename request when the duration for which the second thread is in the waiting state reaches the table lock waiting duration threshold.

[0137] In a possible implementation manner, the above-mentioned rename module 505 may include:

[0138] An original table lock acquisition unit, configured to, when there is an operation request of a service thread for the original table, set the service thread to a waiting state for waiting for the original table lock, and call the second thread to acquire the original table lock.

[0139] Regarding the device in the above-mentioned embodiments, the specific manners in which each module performs operations have been described in detail in the embodiments related to the method, and will not be elaborated herein.

[0140] Figure 6 is a block diagram of an electronic device for atomic table switching in online database table modification according to an exemplary embodiment. The electronic device may be a server, and its internal structure diagram may be as Figure 6 shown. The electronic device includes a processor, a memory, and a network interface connected through a system bus. Among them, the processor of the electronic device is used to provide computing and control capabilities. The memory of the electronic device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the electronic device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, it implements a method for atomic table switching in online database table modification.

[0141] Those skilled in the art can understand that Figure 6 the structure shown in is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the electronic device to which the solution of the present application is applied. The specific electronic device may include more or fewer components than those shown in the figure, or combine some components, or have different component arrangements.

[0142] In an exemplary embodiment, an electronic device is further provided, including: a processor; a memory for storing executable instructions of the processor; wherein, the processor is configured to execute the instructions to implement the atomic table switching method for online database table modification as in the embodiments of the present application.

[0143] In an exemplary embodiment, a computer-readable storage medium is further provided. When instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is enabled to execute the atomic table splitting method for online table modification in the embodiments of the present application. The computer-readable storage medium may be a ROM, a random access memory (RAM), a CD-ROM, a magnetic tape, a floppy disk, an optical data storage device, etc.

[0144] In an exemplary embodiment, a computer program product containing instructions is further provided. When it runs on a computer, the computer is enabled to execute the method for atomic table splitting of online table modification in the embodiments of the present application.

[0145] Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it may include the processes of the embodiments of the above methods. Among them, any reference to a memory, storage, database, or other medium used in the embodiments provided in the present application may include non-volatile and / or volatile memories. Non-volatile memories may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memories may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.

[0146] After considering the specification and practicing the invention disclosed herein, those skilled in the art will readily conceive of other embodiments of the present application. The present application is intended to cover any variations, uses, or adaptations of the present application, which follow the general principles of the present application and include known common knowledge or conventional technical means in the technical field not disclosed in the present application. The specification and embodiments are only regarded as exemplary, and the true scope and spirit of the present application are pointed out by the following claims.

[0147] It should be understood that the present application is not limited to the exact structures described above and shown in the drawings, and various modifications and changes can be made without departing from its scope. The scope of the present application is only limited by the appended claims.

Claims

1. An atomic table splitting method for online table modification in a database, characterized in that, Including: When the first thread of the table modification thread is in the state of preparing to release the table lock of the original table lock, obtain the status information of the second thread of the table modification thread through the first thread; the first thread is used for processing other than the renaming process of the table during the online table modification process, and the second thread is used for the renaming process of the table during the online table modification process; The original table lock is the table lock corresponding to the original table targeted by the database online table modification; If the status information indicates that the second thread is in the state of applying for the original table lock during the renaming process, control the first thread to release the original table lock; Call the second thread to obtain the original table lock, and based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name is modified; Call the first thread to obtain the temporary table lock, and use the temporary table lock to delete the original table corresponding to the table name of the temporary table after renaming.

2. The method according to claim 1, wherein The obtaining the status information of the second thread of the table modification thread through the first thread includes: Query the system view through the first thread to obtain the status information of the second thread of the table modification thread.

3. The method according to claim 1, characterized in that, The method further includes: When the status information indicates that the second thread is not in the state of applying for the original table lock during the renaming process, control the first thread to continue holding the original table lock.

4. The method according to claim 1, characterized in that, The method further includes: During the online table modification process, if an operation request of a business thread on the original table is detected, monitor the original table lock release information in which the second thread releases the original table lock during the renaming process; When the original table lock release information is released, call the business thread to obtain the original table lock and execute the operation request using the original table lock; When the original table lock release information is not released, set the business thread to the waiting state for waiting for the original table lock.

5. The method according to any one of claims 1 to 4, characterized in that, Before the step of obtaining the status information of the second thread of the table modification thread through the first thread when the first thread of the table modification thread is in the state of preparing to release the table lock of the original table lock, the method further includes: In response to an online table modification request, call the first thread to create the shadow table, modify the table structure in the shadow table, and synchronize the data in the original table to the shadow table; Call the first thread to create a temporary table, and perform a table locking operation on the original table and the temporary table; Use the first thread to delete the temporary table, and release the temporary table lock and the shadow table lock, so that the first thread is in the state of preparing to release the table lock of the original table lock.

6. The method according to claim 5, characterized in that, The method further includes: In response to the renaming request of the second thread, call the second thread to apply for the temporary table lock; When the second thread obtains the temporary table lock, call the second thread to apply for the shadow table lock; When the second thread obtains the shadow table lock, call the second thread to apply for the original table lock so that the second thread enters the state of applying for the original table lock.

7. The method according to claim 6, characterized in that, The method further includes: When the second thread fails to obtain the temporary table lock or the shadow table lock, set the second thread to the waiting table lock state.

8. The method according to claim 7, characterized in that, The method further includes: Obtain the table lock waiting duration threshold corresponding to the second thread; When the duration of the second thread in the waiting table lock state or the duration of the second thread in the state of applying for the original table lock during the renaming process reaches the table lock waiting duration threshold, control the second thread to retry the renaming request.

9. The method according to claim 1, characterized in that, The calling the second thread to obtain the original table lock includes: When there is an operation request from a business thread on the original table, set the business thread to the waiting state for the original table lock and call the second thread to obtain the original table lock.

10. An atomic table splitting device for online table modification in a database, characterized in that, It includes: A status information acquisition module, configured to, when a first thread of the table modification thread is in the table lock release preparation state of the original table lock, obtain the status information of a second thread of the table modification thread through the first thread; the first thread is used for processing other than the renaming process of the table during the online table modification process, and the second thread is used for the renaming process of the table during the online table modification process; the original table lock is the table lock corresponding to the original table targeted by the database online table modification; An original table lock release control module, configured to, if the status information indicates that the second thread is in the state of applying for the original table lock during the renaming process, control the first thread to release the original table lock; A renaming module, configured to call the second thread to obtain the original table lock, and based on the original table lock, the temporary table lock corresponding to the temporary table, and the shadow table lock corresponding to the shadow table, modify the table name of the original table to the table name of the temporary table, and modify the table name of the shadow table to the table name of the original table, and release the original table lock, the temporary table lock, and the shadow table lock after the table name is modified; A table deletion module, configured to call the first thread to obtain the temporary table lock and use the temporary table lock to delete the original table corresponding to the table name of the temporary table after renaming.

11. An electronic device, characterized in that, It includes: A processor; A memory for storing executable instructions of the processor; Wherein, the processor is configured to execute the instructions to implement the atomic table switching method for database online table modification according to any one of claims 1 to 9.

12. A computer-readable storage medium, characterized in that, When the instructions in the computer-readable storage medium are executed by the processor of the electronic device, the electronic device is enabled to execute the atomic table switching method for database online table modification according to any one of claims 1 to 9.

Images