Table structure online change method and device, electronic equipment and storage medium

Abstract

The present disclosure provides a table structure online change method and device, an electronic device and a storage medium, which can be applied to the technical field of databases and also can be used in the field of financial technology. The table structure online change method comprises the following steps: in the case that a current database operation statement used for accessing a database is a first type of operation statement, a target table is established according to a target structure; business data of a first type of table is copied to the target table; an exclusive advisory lock associated with a table name of the first type of table is acquired; during a time period in which the first type of table is locked by using the exclusive advisory lock, the table name of the first type of table and the target table is changed, so that after the exclusive advisory lock is released, the target table containing the business data provides services based on the changed table name.

Description

TECHNICAL FIELD

[0001] The present disclosure relates to the technical field of database, and more particularly to a table structure online change method, device, equipment, medium and program product. BACKGROUND

[0002] During the database service, the online table structure change is often needed due to various reasons.

[0003] In the process of implementing the present disclosure, it is found that the existing table structure change method is mostly based on the database system side to change, and the original table is locked during the entire period of executing the change, which causes business blocking due to a large number of lock waiting, and occupies a large amount of CPU and memory resources on the database system side. SUMMARY

[0004] In view of the above problems, the present disclosure provides a table structure online change method, device, equipment, medium and program product.

[0005] In one aspect of the present disclosure, a table structure online change method is provided, which is applied to a processing system independent of a database, and the method comprises:

[0006] In a case where a current database operation statement for accessing the database is a first type of operation statement, a target table is established according to a target structure, wherein the first type of operation statement is used to perform a predetermined form of structure change on a first type of table in the database, the first type of table is a database table to be executed for table structure change, the target structure is the same as the table structure obtained after performing the predetermined form of structure change on the first type of table, and the table names of the first type of table and the target table are different;

[0007] The business data of the first type of table is copied to the target table;

[0008] An exclusive advisory lock associated with the table name of the first type of table is obtained;

[0009] During a time period when the first type of table is locked by using the exclusive advisory lock, the table names of the first type of table and the target table are changed, so that after the exclusive advisory lock is released, the target table containing the business data provides services based on the changed table names.

[0010] According to an embodiment of the present disclosure, the table name of the first type of table is a first name, the table name of the target table is a second name, and the changing of the table names of the first type of table and the target table comprises:

[0011] The table name of the target table is changed from the second name to the first name, and the table name of the first type of table is changed from the first name to a third name, wherein the third name is different from the first name.

[0012] According to an embodiment of the present disclosure, the method further comprises:

[0013] In a case where the current database operation statement accessing the database is a second type of operation statement, it is determined whether the current database operation statement is associated with a second type of table, wherein the second type of table is a database table in a table structure change process, and the second type of operation statement is used to perform a predetermined data access operation on business data of the database according to a predetermined business logic;

[0014] In a case where it is determined that the current database operation statement is associated with the second type of table, a shared advisory lock associated with a table name of the second type of table is acquired;

[0015] The second type of table locked by the shared advisory lock is accessed by using the current database operation statement.

[0016] According to an embodiment of the present disclosure, wherein:

[0017] The exclusive advisory lock associated with the table name of the first type of table is used to lock a first target operation value, wherein the first target operation value is a value obtained by performing a hash operation on the table name of the first type of table;

[0018] The shared advisory lock associated with the table name of the second type of table is used to lock a second target operation value, wherein the second target operation value is a value obtained by performing a hash operation on the table name of the second type of table.

[0019] According to an embodiment of the present disclosure, wherein the determination of whether the current database operation statement is associated with the second type of table comprises:

[0020] A first target table name associated with the current database operation statement is acquired, and a first hash value is obtained by performing a hash operation on the first target table name;

[0021] A second target table name of the second type of table is acquired, and a second hash value is obtained by performing a hash operation on the second target table name;

[0022] In a case where the first hash value is the same as the second hash value, it is determined that the current database operation statement is associated with the second type of table.

[0023] According to an embodiment of the present disclosure, wherein the acquisition of the exclusive advisory lock associated with the table name of the first type of table comprises:

[0024] It is determined whether a current transaction operation associated with the first type of table has been executed;

[0025] In a case where the current transaction operation associated with the first type of table has been executed, the exclusive advisory lock associated with the table name of the first type of table is acquired.

[0026] According to an embodiment of the present disclosure, wherein the determination of whether the current transaction operation associated with the first type of table has been executed comprises:

[0027] record transaction information of the current transaction operation associated with the first type of table to a change list;

[0028] determine whether the current transaction operation associated with the first type of table is completed according to the change list.

[0029] Another aspect of the present disclosure provides a table structure online change device applied to a processing system independent of a database, the device comprising an establishing module, a copying module, a first obtaining module and a changing module.

[0030] The establishing module is configured to, in a case where a current database operation statement for accessing the database is a first type of operation statement, establish a target table according to a target structure, wherein the first type of operation statement is used to perform a predetermined form of structure change on a first type of table in the database, the first type of table is a database table to be subjected to a table structure change, the target structure is the same as a table structure obtained after performing the predetermined form of structure change on the first type of table, and the first type of table and the target table have different table names.

[0031] The copying module is configured to copy business data of the first type of table to the target table.

[0032] The first obtaining module is configured to obtain an exclusive advisory lock associated with a table name of the first type of table.

[0033] The changing module is configured to, during a time period in which the first type of table is locked by the exclusive advisory lock, change the table names of the first type of table and the target table, so that after the exclusive advisory lock is released, the target table containing the business data provides services based on the changed table names.

[0034] According to an embodiment of the present disclosure, the table name of the first type of table is a first name, the table name of the target table is a second name, and the changing module comprises a changing unit configured to change the table name of the target table from the second name to the first name, and change the table name of the first type of table from the first name to a third name, wherein the third name is different from the first name.

[0035] According to an embodiment of the present disclosure, the device further comprises a determining module, a second obtaining module and an accessing module.

[0036] The determining module is configured to, in a case where a current database operation statement for accessing the database is a second type of operation statement, determine whether the current database operation statement is associated with a second type of table, wherein the second type of table is a database table in a table structure change process, and the second type of operation statement is used to perform a predetermined data access operation on business data of the database according to a predetermined business logic.

[0037] The second obtaining module is configured to, in a case where it is determined that the current database operation statement is associated with the second type of table, obtain a shared advisory lock associated with a table name of the second type of table.

[0038] The access module is configured to access the second type of table locked by the shared advisory lock by using the current database operation statement.

[0039] According to an embodiment of the present disclosure, wherein:

[0040] The exclusive advisory lock associated with the table name of the first type of table is used to lock a first target operation value, wherein the first target operation value is a value obtained by performing a hash operation on the table name of the first type of table;

[0041] The shared advisory lock associated with the table name of the second type of table is used to lock a second target operation value, wherein the second target operation value is a value obtained by performing a hash operation on the table name of the second type of table.

[0042] According to an embodiment of the present disclosure, wherein the determination module comprises a first obtaining unit, a second obtaining unit, and a first determination unit.

[0043] The first obtaining unit is configured to obtain a first target table name associated with the current database operation statement, and perform a hash operation on the first target table name to obtain a first hash value;

[0044] The second obtaining unit is configured to obtain a second target table name of the second type of table, and perform a hash operation on the second target table name to obtain a second hash value;

[0045] The first determination unit is configured to determine that the current database operation statement is associated with the second type of table in a case where the first hash value is the same as the second hash value.

[0046] According to an embodiment of the present disclosure, wherein the first obtaining module comprises a second determination unit and a third obtaining unit.

[0047] The second determination unit is configured to determine whether a current transaction operation associated with the first type of table is executed.

[0048] The third obtaining unit is configured to obtain an exclusive advisory lock associated with a table name of the first type of table in a case where the current transaction operation associated with the first type of table is executed.

[0049] According to an embodiment of the present disclosure, wherein the second determination unit comprises a recording subunit and a determination subunit.

[0050] The recording subunit is configured to record transaction information of the current transaction operation associated with the first type of table to a change list.

[0051] The determination subunit is configured to determine whether the current transaction operation associated with the first type of table is executed according to the change list.

[0052] Another aspect of the present disclosure provides an electronic device, comprising: one or more processors; a memory for storing one or more programs, wherein the one or more programs, when executed by the one or more processors, cause the one or more processors to perform the above table structure online change method.

[0053] Another aspect of the present disclosure also provides a computer-readable storage medium having stored thereon executable instructions that, when executed by a processor, cause the processor to perform the above table structure online change method.

[0054] Another aspect of the present disclosure also provides a computer program product comprising a computer program which, when executed by a processor, implements the above table structure online change method. BRIEF DESCRIPTION OF DRAWINGS

[0055] The above and other objects, features and advantages of the present disclosure will become more apparent from the following description when taken in conjunction with the accompanying drawings, in which:

[0056] Figure 1 An application scenario diagram of the table structure online change method, apparatus, device, medium and program product according to an embodiment of the present disclosure is schematically shown;

[0057] Figure 2 A flowchart of the table structure online change method according to an embodiment of the present disclosure is schematically shown;

[0058] Figure 3 A system diagram to which the table structure online change method according to an embodiment of the present disclosure can be applied is schematically shown;

[0059] Figure 4 A flowchart of the table structure online change method according to another embodiment of the present disclosure is schematically shown;

[0060] Figure 5 A structural block diagram of the table structure online change apparatus according to an embodiment of the present disclosure is schematically shown; and

[0061] Figure 6 A block diagram of an electronic device suitable for implementing the table structure online change method according to an embodiment of the present disclosure is schematically shown. DETAILED DESCRIPTION

[0062] Hereinafter, embodiments of the present disclosure will be described with reference to the accompanying drawings. It should be understood, however, that the description is merely exemplary and is not intended to limit the scope of the present disclosure. In the following detailed description of the embodiments, numerous specific details are set forth in order to provide a thorough understanding of the embodiments of the present disclosure. However, it would be apparent to one skilled in the art that the embodiments can be practiced without these specific details. In other instances, well-known structures and functions have been described in detail in order to avoid obscuring the concepts of the present disclosure.

[0063] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the present disclosure. As used herein, the term "includes" and tautological expressions thereof, such as "including," "includes," "include," "contains," "containing," and so on, shall not be taken to exclude the presence of additional items or additional features.

[0064] All terms used herein, including technical and scientific terms, have the same meanings as commonly understood by one of ordinary skill in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having meanings consistent with the context of the present description, and should not be interpreted in an idealized or overly formal manner.

[0065] In the case of using expressions similar to "at least one of A, B, and C, etc.", it should generally be interpreted to include any of one, all, or a combination thereof unless otherwise defined in the context. For example, "a system having at least one of A, B, and C" should be interpreted to include a system having A alone, a system having B alone, a system having C alone, a system having both A and B together, a system having both A and C together, a system having both B and C together, and / or a system having A, B, and C together, etc.

[0066] During the database external service, the situation that the online table structure change is required due to various reasons often occurs, for example, in the case that the production system is busy, some SQL is found to run slowly in an emergency, and the online DDL (Data Definition Language, used to perform table structure change) is required to be performed to increase, change the table, and change the index, etc.

[0067] The existing table structure change method is mostly based on the database system side to change, and the original table is added with an exclusive lock to perform online DDL in the entire period of performing the change. This method will bring the following problems:

[0068] Due to the exclusive lock of the original table, a large number of lock waits occur to cause business blocking, which affects the business service continuity. When there is an uncommitted active transaction on the table in the online transaction, the online DDL will cause a large number of lock waits of the table of the database, seriously affecting the business service continuity and the customer transaction time, and cannot meet the demand;

[0069] It will occupy a large amount of CPU and memory resources of the database system side, and affect the availability of the database. Because the existing online DDL implementation scheme is implemented on the database system side, it occupies CPU and memory resources of the system side. When a large number of lock waits cause transaction blocking, database connection resources cannot be released, which may quickly cause system CPU to rise and the database to be down;

[0070] There is a risk of duplicate key values. When concurrent DML (Data Manupulation Language) statements are executed in the production system, the online table structure change is executed, the threads access the cache log recorded in the online DDL process, and the cache log also records the changes generated by other DML threads, so there may be a duplicate key value error.

[0071] Some online DDLs have poor support capabilities. The existing online DDL based on the database system side cannot support any type of DDL. For example, deleting the primary key will cause the entire table to be locked, affecting business.

[0072] Therefore, an embodiment of the present disclosure provides a table structure online change method, device, equipment, medium and program product.

[0073] In one aspect of the present disclosure, a table structure online change method is provided, which is applied to a processing system independent of a database. The method comprises:

[0074] In a case where a current database operation statement for accessing the database is a first type of operation statement, a target table is established according to a target structure, wherein the first type of operation statement is used to perform a predetermined form of structure change on a first type of table in the database, the first type of table is a database table to be executed for table structure change, the target structure is the same as the table structure obtained after performing the predetermined form of structure change on the first type of table, and the table names of the first type of table and the target table are different;

[0075] The business data of the first type of table is copied to the target table;

[0076] An exclusive advisory lock associated with the table name of the first type of table is obtained;

[0077] During a time period when the first type of table is locked by using the exclusive advisory lock, the table names of the first type of table and the target table are changed, so that after the exclusive advisory lock is released, the target table containing business data provides services based on the changed table name.

[0078] Figure 1 An application scenario diagram of a table structure online change method, device, equipment, medium and program product according to an embodiment of the present disclosure is schematically shown.

[0079] As Figure 1As shown, the application scenario 100 according to the embodiment can include a terminal device 101, a service server 102, a database 103, and a processing system 104, which can communicate with each other through a network. The network can include various connection types, such as wired, wireless communication links, or optical cables, and the like.

[0080] A user can use the terminal device 101 to interact with the server of the service server 102 through the network to receive or send messages, and the like. Various communication client applications can be installed on the terminal device 101, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, and the like (only as examples).

[0081] The terminal device 101 can be various electronic devices with a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop computers, desktop computers, and the like.

[0082] The service server 102 can include one or more servers, which can be servers providing various services, such as a background management server providing support for websites browsed by users using the terminal device 101 (only as an example). The background management server can analyze and process received user requests and the like, and feed back the processing results (such as web pages, information, or data obtained or generated according to user requests) to the terminal device.

[0083] In the application scenario of the embodiments of the present disclosure, during the service server 102 provides services to the outside, online structure changes of part of the tables in the database 103 need to be performed. The processing system 104 is independent of the service server 102 and the database 103, and the processing system 104 can include one or more servers for performing the table structure online change method of the embodiments of the present disclosure. During the database 103 provides services to the outside, a user initiates a request to the service server 102 through the terminal device 101, the service server 102 accesses the corresponding database table in the database 103 through a database operation statement according to the business logic, and the processing system 104 can perform corresponding processing methods according to the type of the current database operation statement, such as the case where the current database operation statement is used to perform structure changes on the tables in the database, the online DDL process is performed to complete the table structure changes; in the case where the current database operation statement is used to access the tables in the database for business, a shared advisory lock needs to be acquired by the access process to access, and the like.

[0084] It should be understood that, Figure 1The number of servers of the terminal device 101, the service server 102, the database 103 and the processing system 104 in the figure is only illustrative. According to the implementation needs, there can be any number of terminal devices 101, service servers 102, databases 103 and processing system 104 servers.

[0085] It should be noted that the table structure online change method and device of the present disclosure can be applied to the database technology field, and can also be applied to the financial technology field, and can also be applied to any field other than the database technology field and the financial technology field. The embodiments of the present disclosure do not limit the application field of the above-mentioned table structure online change method and device.

[0086] The following will be based on Figure 1 The scenario described above, through Figures 2 to 6 The table structure online change method of the disclosed embodiment is described in detail.

[0087] Figure 2 The flowchart of the table structure online change method according to an embodiment of the present disclosure is schematically shown.

[0088] As Figure 2 The table structure online change method of this embodiment is applied to a processing system independent of the database. The method comprises operations S201-S204.

[0089] In operation S201, in the case that the current database operation statement for accessing the database is a first type of operation statement, a target table is established according to a target structure, wherein the first type of operation statement is used to perform a predetermined form of structure change on a first type of table in the database, the first type of table is a database table to be executed for table structure change, the target structure is the same as the table structure obtained after performing the predetermined form of structure change on the first type of table, and the table name of the first type of table and the target table is different.

[0090] In operation S202, the business data of the first type of table is copied to the target table.

[0091] In operation S203, an exclusive advisory lock associated with the table name of the first type of table is obtained.

[0092] In operation S204, within the time period when the first type of table is locked by the exclusive advisory lock, the table name of the first type of table and the target table is changed, so that after the exclusive advisory lock is released, the target table containing business data provides services based on the changed table name.

[0093] According to an embodiment of the present disclosure, during the database service, the online table structure change may be required due to various reasons, and the first type of table is the database table to be executed for the table structure change. Thus, during the database service, the current database operation statement for accessing the database may be a first type of operation statement (i.e., a DDL statement) for performing a predetermined form of structure change on the first type of table in the database, or a second type of operation statement (i.e., a DML statement) for performing a predetermined data access operation on the business data of the database according to a predetermined business logic. The DDL statement (Data Definition Language) refers to a language for defining data structure and database object in the SQL language set, which is composed of three syntaxes of CREATE, ALTER, and DROP; the DML statement (Data Manipulation Language) refers to a language for running access of database objects and data in the SQL language set, which is mainly composed of four syntaxes of INSERT, UPDATE, DELETE, and SELECT.

[0094] Therefore, in operation S201, it is necessary to first determine the type of the current database operation statement. In the case that the current database operation statement is a DDL statement for performing a predetermined form of structure change on the first type of table in the database, the online table structure change process is executed.

[0095] The online table structure change process includes the above-mentioned operations S201-S204, i.e., the operations of table creation, data replication, exclusive locking, and table name table change.

[0096] In the above-mentioned operation S201, the target table to be established is a database table established according to the expected change structure. The target table may be generated by copying the original table structure in the cache space and performing the online table structure change.

[0097] After the target table is established, in the above-mentioned operation S202, the business data of the first type of table is replicated to the target table. It should be noted that the replicated business data includes original data and incremental data. The original data is the business data generated by the application server associated with the first type of table before the table structure change is performed. The incremental data is the business data generated by the application server associated with the first type of table during the table structure change. The business data replication can be completed by using a logical replication method.

[0098] In operation S203 and operation S204, the table is locked by acquiring the exclusive advisory lock associated with the table name of the first type of table, so that the table name of the first type of table and the target table is changed within the time period in which the first type of table is locked by the exclusive advisory lock. For example, the table name of the target table can be changed to the same table name as the first type of table, so that after the exclusive advisory lock is released, the target table containing business data can be provided externally based on the changed table name.

[0099] According to an embodiment of the present disclosure, an advisory lock is a display lock whose meaning is defined by an application, and the system does not force its use, and the application decides how to use it. It includes shared advisory locks and exclusive advisory locks. After setting a shared advisory lock, multiple processes can acquire a shared lock of a resource / object at the same time, and at this time, the resource / object cannot be locked exclusively, unless the shared lock is removed or released. After setting an exclusive advisory lock, the exclusive lock of a resource object can only be owned by one process. When a process cannot acquire a resource, the shared lock or the exclusive lock will make the process in a sleep blocking state.

[0100] According to an embodiment of the present disclosure, in the related art, the table structure change method is mostly based on the change of the database system side. During the entire period of executing the change, the original table is locked exclusively for online table structure change, which will cause business blocking. The table structure online change method of the embodiment of the present disclosure is applied to a processing system independent of the database. Online table structure change is realized by building a table, logically copying data, and renaming a table. Because the long-time occupying operations such as table building and data synchronization are completed outside the database system side, this process does not need to lock the table exclusively. Only during the renaming period of the database, the exclusive lock is added to the table to be changed. Because the renaming time is very short, the exclusive lock has little impact on the business, and does not cause long-time business blocking. The embodiment overcomes the disadvantages of blocking business and affecting service continuity when the existing system executes online DDL, solves the problem of insufficient online DDL capability of the current new database, and does not affect the DML operation, effectively improves the support capability of online DDL, and improves the availability of the database system side.

[0101] According to an embodiment of the present disclosure, because the online table structure change is performed on a newly built table, whether the primary key is deleted or other online structure change can be executed, which effectively improves the support capability of online DDL, and overcomes the problem that the deletion of the primary key in the prior art causes the whole table to be locked and affects the business.

[0102] According to the table structure online change method of the embodiment of the present disclosure, the method is applied to a processing system independent of a database, does not occupy database memory, and compared with the existing scheme of performing the online DDL execution process on the system side, the method of the embodiment of the present disclosure occupies as few database system side CPU and memory resources as possible, and the availability of the database is improved.

[0103] According to the embodiment of the present application, the table names of the first type table and the target table are changed during the period when the first type table is locked by the exclusive advisory lock. The table name of the first type table is a first name, the table name of the target table is a second name, and the table names of the first type table and the target table are changed by changing the table name of the target table from the second name to the first name and changing the table name of the first type table from the first name to a third name, wherein the third name is different from the first name, that is, the table name of the target table is changed to the same table name as the first type table. Thus, after the exclusive advisory lock is released, the target table containing business data can provide services based on the changed table name. At the same time, the table name of the original table needs to be changed to another name to avoid affecting the external service of the database. After changing the table name, the table name of the new table is the same as the original table name, so that the external service can be provided in the state of application measurement unawareness.

[0104] According to the embodiment of the present application, further, the exclusive advisory lock associated with the table name of the first type table specifically includes:

[0105] Determine whether the current transaction operation associated with the first type table is executed; in the case where the current transaction operation associated with the first type table has been executed, acquire the exclusive advisory lock associated with the table name of the first type table.

[0106] According to the embodiment of the present disclosure, in order not to affect the continuity of the business, the exclusive lock needs to be acquired for table renaming in the case where it is determined that the current transaction operation has been executed. Thus, the consistency of the data in the entire process can be maintained, and the error business result can be avoided.

[0107] Specifically, determining whether the current transaction operation associated with the first type table is executed can include: recording the transaction information of the current transaction operation associated with the first type table to the change list during the execution of the table change; and determining whether the current transaction operation associated with the first type table is executed according to the change list.

[0108] According to an embodiment of the present disclosure, the change list records transaction information of a table that is performing a table structure change, for example, table name information, transaction information of a current transaction operation (for example, information whether each current transaction has committed to the database, information whether each current transaction has completed, and the like), and the list is visible to all transactions. Therefore, by checking the change list, information whether the current transaction operation has completed can be obtained. By monitoring the table to be changed and the transaction completion on the table through the online table change list and making the list visible to all transactions, and the transaction process does not access the intermediate log in the cache space, the problem of duplicate key values is avoided in advance.

[0109] According to an embodiment of the present disclosure, during the database service, the current database operation statement for accessing the database can be a first type of operation statement (i.e., a DDL statement) for performing a predetermined form of structure change on a first type of table in the database, or a second type of operation statement (i.e., a DML statement) for performing a predetermined data access operation on the business data of the database according to a predetermined business logic.

[0110] In the case where the current database operation statement is a DDL statement for performing a predetermined form of structure change on a first type of table in the database, the online table structure change process described above is performed.

[0111] In the case where the current database operation statement is a second type of operation statement (i.e., a DML statement) for performing a predetermined data access operation on the business data of the database according to a predetermined business logic, the DML statement currently accessing the database can involve a database table that is performing a table structure change, and in this case, access needs to be limited. Therefore, it is necessary to first determine whether the DML statement currently accessing the database involves a database table that is performing a table structure change, i.e., to determine whether the current database operation statement is associated with a second type of table (i.e., a database table that is in a table structure change process).

[0112] In the case where it is determined that the current database operation statement is not associated with a second type of table, the current DML does not involve a database table that is performing a table structure change, and the database can be directly accessed by the current database operation statement (DML).

[0113] In the case where it is determined that the current database operation statement is associated with a second type of table, the current DML involves a database table that is performing a table structure change, and access is limited, specifically: a shared advisory lock associated with the table name of the second type of table is first acquired, and after the lock is acquired, the second type of table locked by the shared advisory lock is accessed by the current database operation statement (DML).

[0114] According to an embodiment of the present disclosure, by sharing the advisory lock, a threshold of the number of transactions that can access the second type of table at the same time can be set, for example, 20 transactions can be allowed to acquire the shared advisory lock associated with the table name of the second type of table and access the table.

[0115] According to an embodiment of the present disclosure, in the case that the current database operation statement involves a database table that is performing a table structure change, the access restriction is performed by the above method, and only the transaction that acquires the shared advisory lock can access the database table. Since the shared lock can set the number of transaction processes that access the table at the same time, the renaming of the subsequent table change can be provided with an opportunity. In the case that the current transaction is completed, an exclusive lock can be added for renaming. If the shared lock is not set, it is difficult to obtain an exclusive lock due to the continuous entry of online business.

[0116] According to an embodiment of the present disclosure, specifically, whether the current database operation statement is associated with the second type of table is determined, that is, whether the current DML involves a database table that is performing a table structure change is determined, which can be determined by the following method:

[0117] First, a first target table name associated with the current database operation statement is acquired, and a first hash value is obtained by performing a hash operation on the first target table name;

[0118] Then, a second target table name of the second type of table is acquired, and a second hash value is obtained by performing a hash operation on the second target table name;

[0119] After that, in the case that the first hash value is the same as the second hash value, it is determined that the current database operation statement is associated with the second type of table.

[0120] According to an embodiment of the present disclosure, the exclusive advisory lock associated with the table name of the first type of table is used to lock the first target operation value, wherein the first target operation value is a value obtained by performing a hash operation on the table name of the first type of table; and the shared advisory lock associated with the table name of the second type of table is used to lock the second target operation value, wherein the second target operation value is a value obtained by performing a hash operation on the table name of the second type of table.

[0121] By the method of the embodiment of the present disclosure, the object of the locking is the semaphore obtained by performing a hash operation on the table name instead of locking on the original table. By distributing the shared advisory lock to the DML transaction involving the table to be changed, the reading and writing of the original table and the DML transaction are not affected, that is, the normal access of other processes to the original table is not hindered.

[0122] Figure 3 A system diagram to which the table structure online change method according to an embodiment of the present disclosure can be applied is schematically shown.

[0123] As Figure 3As shown, the system to which the table structure online change method according to the embodiments of the present disclosure can be applied includes an application server, a database, and a processing system, during the service provided by the application server, online structure change of part of the tables in the database needs to be performed, wherein the processing system is used to perform the table structure online change method according to the embodiments of the present disclosure, and the processing system is independent of the business server and the database.

[0124] During the service provided by the database, the application server accesses the corresponding database table in the database according to the business logic through a database operation statement, and the processing system detects the operation statement input by the current application into the database to determine the type of the current database operation statement (such as DDL, DML), and submits the next processing according to the result.

[0125] The processing system performs corresponding processing methods according to the type of the current database operation statement, in the case of the current database operation statement being a DDL statement, used to perform structure change of the table in the database, the online DDL process is performed to complete the table structure change, including the operations of table creation, data replication synchronization, exclusive locking, and table name table change. For example, the table creation can include establishing a new table TA NEW in the cache space which is consistent with the table structure of the original table TA, and completing the table structure change (online DDL) of TA NEW; the data replication synchronization can include synchronizing the volume data and incremental data of the TA table through logical replication, and keeping the incremental data up-to-date (the replication delay is less than 5 seconds); the exclusive locking and the table name table change include judging when to acquire the exclusive advisory lock according to the transaction submission condition of the table name to be changed recorded in the online table structure change list, when the committed transactions in the table to be changed are all executed, the exclusive advisory lock is acquired, after the complete synchronization of the small amount of incremental data, the original TA table is renamed as TA BAK, and the new table TA NEW is renamed as TA, after the successful execution of the renaming submission, the exclusive advisory lock is released, and the table list to be changed recorded in the online table structure change list is updated.

[0126] In the case of the current database operation statement being a DML statement, used to perform data access of the table in the database according to the business logic, if the DML does not involve the table to be changed, the transaction is directly submitted to the database without blocking; if the DML involves the table to be changed, the shared advisory lock needs to be acquired by the access process to enable the data access.

[0127] The processing system can establish the online table structure change list during the whole process, the transaction information of the table being executed for the table structure change is recorded in the change list, for example, the table name information and the transaction information of the current transaction operation can be recorded, and the list is visible to all transactions, so as to confirm whether the DML involves the table to be changed according to the table.

[0128] The processing system is also responsible for the distribution and recovery of advisory locks, hashing the table name to be changed to obtain HASH_NUM, which is used as a semaphore for switching locks, distributing and recovering shared advisory locks for DML involving the table to be changed, and distributing and recovering exclusive advisory locks for executing DML for table structure change.

[0129] Figure 4 A flowchart of a table structure online change method according to another embodiment of the present disclosure is schematically shown. Specifically, the method is executed by a processing system Figure 3 The above-described table structure online change method shown can refer to Figure 4 As shown in Figure 4 The method includes operation S401 to operation S407.

[0130] In operation S401, it is detected whether the application request is an online DDL. If it is an online DDL, the DDL statement is executed in an online DDL flow, and table structure change is completed (operation S403 to operation S406). If it is a DML, it is determined whether the DML involves a table to be changed by querying an online table structure change list. If it is returned that the DML does not involve a table to be changed, it is directly submitted to database processing (operation S407). If it is returned that the DML involves a table to be changed, a shared advisory lock needs to be acquired by a DML access process before data access (operation S402).

[0131] Specifically, in operation S402, the table name to be changed is hashed to obtain a series of semaphores HASH_TAB. If the hash result of the table name involved in the DML statement is consistent with HASH_TAB, a shared advisory lock corresponding to the HASH_TAB value of the DML transaction is distributed, and multiple DML transactions can simultaneously access the same table to be changed for reading and writing, and the shared advisory lock is released after the transaction returns.

[0132] Specifically, the method for completing table structure change is completed by operation S403 to operation S406.

[0133] In operation S403, the tables involved in change in the online DDL are extracted by the online table structure change list, and the transaction submission on these tables is recorded.

[0134] In operation S404, a new table TA NEW is generated from the table structure of the original table TA in the cache space, and the online DDL is executed to complete the table structure change.

[0135] In operation S405, the logical replication synchronizes the data of the original table TA to the new table TA NEW (mainly by selecting the data of the original table and replaying in the target table), and uses the Xlog and other log files to copy the incremental data to the new table TA NEW, and keeps the incremental data synchronization delay less than 5 seconds.

[0136] In operation S406, when it is learned from the online table structure change list that there is no uncommitted transaction for the table to be changed, an exclusive advisory lock is acquired. After the acquisition is successful, other transactions cannot write to the original table TA. The remaining small amount of incremental data to be synchronized of the TA original table is copied to the TA NEW new table. The TA table is renamed as TA BAK. The new table TA NEW is renamed as TA. The exclusive advisory lock is released. The online table structure change list is updated. At this point, the online DDL is completed.

[0137] Based on the above-described table structure online change method, the present disclosure further provides a table structure online change device. The following will be described in combination with Figure 5 The device is described in detail.

[0138] Figure 5 The structure block diagram of the table structure online change device according to the embodiment of the present disclosure is schematically shown.

[0139] As Figure 5 shown, the table structure online change device 500 of the embodiment includes an establishing module 501, a copying module 502, a first acquiring module 503, and a changing module 504.

[0140] The establishing module 501 is configured to, in a case where a current database operation statement used for accessing a database is a first type of operation statement, establish a target table according to a target structure, wherein the first type of operation statement is used for performing a predetermined form of structure change on a first type of table in the database, the first type of table is a database table to be subjected to table structure change, the target structure is the same as a table structure obtained after performing the predetermined form of structure change on the first type of table, and the first type of table and the target table have different table names.

[0141] The copying module 502 is configured to copy business data of the first type of table to the target table.

[0142] The first acquiring module 503 is configured to acquire an exclusive advisory lock associated with the table name of the first type of table.

[0143] The changing module 504 is configured to, within a time period during which the first type of table is locked by the exclusive advisory lock, change the table names of the first type of table and the target table, so that after the exclusive advisory lock is released, the target table containing the business data provides services based on the changed table name.

[0144] According to the embodiment of the present disclosure, in the related art, the online table structure change is performed by adding an exclusive lock to the original table in the whole period of performing the table structure change, which causes business blocking. The table structure online change device of the embodiment of the present disclosure is arranged outside the database system, and the online table structure change is completed by establishing the module 501, the replication module 502, the first acquisition module 503, and the change module 504 to establish the table, replicate the data, change the table name, and the like. Because the long-time occupying operations such as table establishment and data synchronization are completed outside the database system, this process does not need to add an exclusive lock to the table, and only an exclusive lock is added to the table to be changed in the table renaming time period. Because the renaming time is very short, the exclusive lock has a small impact on the business and does not cause long-time business blocking. The embodiment overcomes the disadvantages of blocking the business and affecting the service continuity of the existing system when performing the online DDL, solves the problem of insufficient online DDL capability of the current new database, does not affect the DML operation, effectively improves the support capability of the online DDL, and improves the availability of the database system side.

[0145] According to the embodiment of the present disclosure, the table name of the first type of table is a first name, the table name of the target table is a second name, the change module includes a change unit, which is configured to change the table name of the target table from the second name to the first name, and change the table name of the first type of table from the first name to a third name, wherein the third name is different from the first name.

[0146] According to the embodiment of the present disclosure, the device further includes a determination module, a second acquisition module, and an access module.

[0147] The determination module is configured to determine whether the current database operation statement is associated with the second type of table in the case that the current database operation statement is the second type of operation statement, wherein the second type of table is a database table in the table structure change process, and the second type of operation statement is configured to perform a predetermined data access operation on the business data of the database according to a predetermined business logic. The second acquisition module is configured to acquire a shared advisory lock associated with the table name of the second type of table in the case that the current database operation statement is associated with the second type of table. The access module is configured to access the second type of table locked by the shared advisory lock by using the current database operation statement.

[0148] According to the embodiment of the present disclosure, the exclusive advisory lock associated with the table name of the first type of table is configured to lock a first target operation value, wherein the first target operation value is a value obtained by performing a hash operation on the table name of the first type of table. The shared advisory lock associated with the table name of the second type of table is configured to lock a second target operation value, wherein the second target operation value is a value obtained by performing a hash operation on the table name of the second type of table.

[0149] According to the embodiment of the present disclosure, the determination module includes a first acquisition unit, a second acquisition unit, and a first determination unit.

[0150] The first obtaining unit is configured to obtain a first target table name associated with the current database operation statement, and perform a hash operation on the first target table name to obtain a first hash value; the second obtaining unit is configured to obtain a second target table name of the second type of table, and perform a hash operation on the second target table name to obtain a second hash value; and the first determining unit is configured to determine that the current database operation statement is associated with the second type of table in a case where the first hash value is the same as the second hash value.

[0151] According to an embodiment of the present disclosure, the first obtaining module 503 comprises a second determining unit and a third obtaining unit.

[0152] The second determining unit is configured to determine whether the current transaction operation associated with the first type of table is executed; and the third obtaining unit is configured to obtain an exclusive advisory lock associated with the table name of the first type of table in a case where the current transaction operation associated with the first type of table is executed.

[0153] According to an embodiment of the present disclosure, the second determining unit comprises a recording subunit and a determining subunit.

[0154] The recording subunit is configured to record transaction information of the current transaction operation associated with the first type of table to a change list; and the determining subunit is configured to determine whether the current transaction operation associated with the first type of table is executed according to the change list.

[0155] According to an embodiment of the present disclosure, any of the establishing module 501, the replicating module 502, the first obtaining module 503, and the changing module 504 can be combined in one module to be implemented, or any of the modules can be split into multiple modules. Alternatively, at least part of the function of one or more of the modules can be combined with at least part of the function of other modules, and implemented in one module. According to an embodiment of the present disclosure, at least one of the establishing module 501, the replicating module 502, the first obtaining module 503, and the changing module 504 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on chip, a system on substrate, a system on package, an application specific integrated circuit (ASIC), or any other reasonable manner of hardware or firmware by integrating or packaging the circuit, or implemented in any one of software, hardware, and firmware or in a proper combination of any of the above. Alternatively, at least one of the establishing module 501, the replicating module 502, the first obtaining module 503, and the changing module 504 can be at least partially implemented as a computer program module which can perform corresponding functions when the computer program module is run.

[0156] Figure 6A block diagram of an electronic device suitable for implementing the table structure online change method according to an embodiment of the present disclosure is shown schematically.

[0157] As shown in Figure 6 The electronic device 600 according to an embodiment of the present disclosure includes a processor 601 that can perform various appropriate actions and processes according to programs stored in a read-only memory (ROM) 602 or loaded into a random access memory (RAM) 603 from a storage portion 608. The processor 601 can include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor, and / or a related chipset, and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), and / or the like. The processor 601 can also include on-board memory for cache use. The processor 601 can include a single processing unit or multiple processing units for executing different actions of the method processes according to embodiments of the present disclosure.

[0158] In the RAM 603, various programs and data required for the operation of the electronic device 600 are stored. The processor 601, the ROM 602, and the RAM 603 are connected to each other via a bus 604. The processor 601 performs various operations of the method processes according to embodiments of the present disclosure by executing programs in the ROM 602 and / or the RAM 603. Note that the programs can also be stored in one or more memories other than the ROM 602 and the RAM 603. The processor 601 can also perform various operations of the method processes according to embodiments of the present disclosure by executing programs stored in the one or more memories.

[0159] According to an embodiment of the present disclosure, the electronic device 600 can further include an input / output (I / O) interface 605, which is also connected to the bus 604. The electronic device 600 can further include one or more of the following components connected to the I / O interface 605: an input portion 606 including a keyboard, a mouse, and the like; an output portion 607 including a display such as a cathode ray tube (CRT), a liquid crystal display (LCD), and the like, and a speaker, and the like; a storage portion 608 including a hard disk, and the like; and a communication portion 609 including a network interface card such as a LAN card, a modem, and the like. The communication portion 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the I / O interface 605 as necessary. A removable medium 611 such as a magnetic disk, a magneto-optical disk, a semiconductor memory, and the like is attached to the drive 610 as necessary, so that a computer program read out therefrom is installed in the storage portion 608 as necessary.

[0160] The present disclosure also provides a computer readable storage medium, which can be included in the device / apparatus / system described in the above embodiments, or can exist independently without being assembled into the device / apparatus / system. The above computer readable storage medium carries one or more programs, which, when executed, implement the method according to the embodiments of the present disclosure.

[0161] According to an embodiment of the present disclosure, the computer readable storage medium can be a non-volatile computer readable storage medium, which can include, but is not limited to, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any appropriate combination thereof. In the present disclosure, the computer readable storage medium can be any tangible medium that contains or stores a program, which can be used by or in connection with an instruction execution system, apparatus, or device. For example, according to an embodiment of the present disclosure, the computer readable storage medium can include one or more memories, such as the ROM 602 and / or the RAM 603 described above, and / or one or more memory units other than the ROM 602 and the RAM 603.

[0162] The embodiments of the present disclosure also include a computer program product, which includes a computer program containing program codes for executing the methods shown in the flowcharts. When the computer program product is run in a computer system, the program codes are used to make the computer system implement the table structure online changing method provided by the embodiments of the present disclosure.

[0163] The above functions defined in the system / apparatus of the embodiments of the present disclosure are performed when the computer program is executed by the processor 601. According to an embodiment of the present disclosure, the system, apparatus, module, unit, etc. described above can be implemented by computer program modules.

[0164] In one embodiment, the computer program can rely on a tangible storage medium, such as an optical storage medium, a magnetic storage medium, etc. In another embodiment, the computer program can also be transmitted, distributed, and downloaded in the form of a signal via a network medium, and be downloaded and installed via the communication part 609, and / or be installed from the detachable medium 611. The program codes contained in the computer program can be transmitted via any appropriate network medium, including but not limited to wireless, wired, etc., or any appropriate combination thereof.

[0165] In such embodiments, the computer program can be downloaded and installed from the network via the communication section 609, and / or installed from the removable media 611. When the computer program is executed by the processor 601, the above-described functions defined in the system of the embodiments of the present disclosure are executed. According to the embodiments of the present disclosure, the system, device, apparatus, module, unit, and the like described above can be implemented by the computer program modules.

[0166] According to the embodiments of the present disclosure, the program code for executing the computer program provided by the embodiments of the present disclosure can be written in any combination of one or more programming languages, and specifically, these computer programs can be implemented using high-level procedural and / or object-oriented programming language, and / or assembly / machine language. The programming language includes, but is not limited to, such as Java, C++, python, "C" language or similar programming language. The program code can be executed entirely on the user computing device, partially on the user device, partially on a remote computing device, or entirely on a remote computing device or server. In the case involving a remote computing device, the remote computing device can be connected to the user computing device through any kind of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (for example, connected through the Internet by using an Internet service provider).

[0167] The flowcharts and block diagrams in the drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present disclosure. In this regard, each block in the flowcharts or block diagrams can represent a module, a segment, or a portion of code, which comprises one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the drawings. For example, two blocks noted in succession can in fact be executed substantially concurrently or in the reverse order, depending on the functionality involved. It should also be noted that each block in the flowcharts or block diagrams, and combinations of blocks in the flowcharts or block diagrams, can be implemented by dedicated hardware-based systems that perform the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0168] Those skilled in the art can understand that the features described in various embodiments of the present disclosure and / or claims can be combined or / and integrated, even if such combinations or integrations are not explicitly described in the present disclosure. In particular, the features described in various embodiments of the present disclosure and / or claims can be combined and / or integrated in various combinations, without departing from the spirit and teachings of the present disclosure. All these combinations and / or integrations fall within the scope of the present disclosure.

[0169] The above described embodiments of the present disclosure. However, these embodiments are merely for illustrative purposes, and are not intended to limit the scope of the present disclosure. Although each embodiment is described above separately, this does not mean that the measures in each embodiment cannot be advantageously used in combination. The scope of the present disclosure is defined by the appended claims and their equivalents. Those skilled in the art can make various substitutions and modifications without departing from the scope of the present disclosure, and all such substitutions and modifications shall fall within the scope of the present disclosure.

Claims

1. A method for changing table structure on line, applied to a processing system independent of a database, the method comprising: in a case where a current database operation statement for accessing the database is a first type of operation statement, establishing a target table according to a target structure, wherein the first type of operation statement is used to perform a predetermined form of structure change on a first type of table in the database, the first type of table is a database table to be subjected to table structure change, the target structure is the same as a table structure obtained after performing the predetermined form of structure change on the first type of table, and the first type of table and the target table have different table names; copying business data of the first type of table to the target table; obtaining an exclusive advisory lock associated with the table name of the first type of table; changing the table names of the first type of table and the target table within a time period during which the first type of table is locked by the exclusive advisory lock, so that after the exclusive advisory lock is released, the target table containing the business data provides services based on the changed table names; in a case where the current database operation statement for accessing the database is a second type of operation statement, determining whether the current database operation statement is associated with a second type of table, wherein the second type of table is a database table in a table structure change process, and the second type of operation statement is used to perform a predetermined data access operation on business data of the database according to a predetermined business logic; in a case where it is determined that the current database operation statement is associated with the second type of table, obtaining a shared advisory lock associated with the table name of the second type of table; and using the current database operation statement to access the second type of table locked by the shared advisory lock. The table name of the first type of table is a first name, the table name of the target table is a second name, and the changing of the table names of the first type of table and the target table comprises: changing the table name of the target table from the second name to the first name, and changing the table name of the first type of table from the first name to a third name, wherein the third name is different from the first name. 3.The method of claim 1, wherein: the exclusive advisory lock associated with the table name of the first type of table is used to lock a first target calculation value, wherein the first target calculation value is a value obtained by performing a hash operation on the table name of the first type of table; the shared advisory lock associated with the table name of the second type of table is used to lock a second target calculation value, wherein the second target calculation value is a value obtained by performing a hash operation on the table name of the second type of table. The determining whether the current database operation statement is associated with the second type of table comprises: obtaining a first target table name associated with the current database operation statement, and obtaining a first hash value by performing a hash operation on the first target table name; obtaining a second target table name of the second type of table, and obtaining a second hash value by performing a hash operation on the second target table name; and in a case where the first hash value is the same as the second hash value, determining that the current database operation statement is associated with the second type of table. The obtaining the exclusive advisory lock associated with the table name of the first type of table comprises: ​ ​ ​ ​ ​ ​ ​ 2. The method of claim 1, wherein, ​ ​ ​ ​ ​ 4. The method of claim 1, wherein, ​ ​ ​ ​ 5. The method of claim 1, wherein, ​ determine whether a current transaction operation associated with the first type table is completed; obtain an exclusive advisory lock associated with a table name of the first type table, in a case that the current transaction operation associated with the first type table is completed.

6. The method of claim 5, wherein, The determining whether the current transaction operation associated with the first type table is completed comprises: recording transaction information of the current transaction operation associated with the first type table to a change list; determining whether the current transaction operation associated with the first type table is completed according to the change list. 7.An apparatus for online change of table structure, applied to a processing system independent of a database, the apparatus comprising: an establishing module, configured to establish a target table according to a target structure in a case that a current database operation statement for accessing the database is a first type operation statement, wherein the first type operation statement is used to perform a predetermined form of structure change on a first type table in the database, the first type table is a database table to be executed with a table structure change, the target structure is the same as a table structure obtained after performing the predetermined form of structure change on the first type table, and the first type table and a table name of the target table are different; a copying module, configured to copy business data of the first type table to the target table; a first obtaining module, configured to obtain an exclusive advisory lock associated with the table name of the first type table; a changing module, configured to change the table name of the first type table and the target table within a time period during which the first type table is locked by the exclusive advisory lock, so that after the exclusive advisory lock is released, the target table containing the business data provides services based on the changed table name; a determining module, configured to determine whether a current database operation statement is associated with a second type table in a case that the current database operation statement for accessing the database is a second type operation statement, wherein the second type table is a database table in a table structure change process, and the second type operation statement is used to perform a predetermined data access operation on business data of the database according to a predetermined business logic; a second obtaining module, configured to obtain a shared advisory lock associated with a table name of the second type table in a case that it is determined that the current database operation statement is associated with the second type table; an accessing module, configured to access the second type table locked by the shared advisory lock by using the current database operation statement. 8.An electronic device comprising: one or more processors; a storage device for storing one or more programs, wherein the one or more programs, when executed by the one or more processors, enable the one or more processors to perform the method according to any one of claims 1-6. 9.A computer-readable storage medium having stored thereon executable instructions that, when executed by a processor, cause the processor to perform the method according to any one of claims 1-6. 10.A computer program product comprising a computer program that, when executed by a processor, implements the method according to any one of claims 1-6.

Images