Online large object data migration method, device, equipment and product in database

Abstract

The disclosed embodiment discloses a method, device, equipment and product for online large object data migration in a database, including: updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address; determining the large object data to be migrated based on the row to be operated and the initial row header information of the table to be operated; the row to be operated needs to perform large object data migration; blocking the large object to be migrated based on the large object unique identifier; the large object to be migrated includes the large object data to be migrated and the initial row header information; based on the initial row header information, copying the large object data to be migrated to the target table space corresponding to the target large object segment address, and updating the initial row header information; submitting the DDL transaction of the large object data migration, and releasing the initial table space. The technical solution provided by this embodiment allows other concurrent transactions to perform concurrent DML operations on the table during the large object migration process, eliminating the performance bottleneck of large object data migration.

Description

Technical Field

[0001] The embodiments of the present disclosure relate to the field of database technology, and in particular to a method, device, equipment and product for online large object data migration in a database. Background Art

[0002] In database systems, the data storage of large objects is generally implemented using large object (LOB) technology. Large object data refers to data types such as binary large objects (BLOB), character large objects (CLOB), text (TEXT), and images (IMAGE). The complete large object data is stored in a dedicated LOB segment, which is called out-of-row data. The table data page only stores some key information of the object, called the in-row header, which usually includes LOB_ID (globally unique large object unique identifier), LOB data length, table space number, file number, page number, and / or page offset, etc. When accessing a large object in a table, the in-row header in the data page can be used to locate the location of the out-of-row data in the LOB segment, thereby obtaining the complete data of the large object from the LOB segment. This technology avoids storing too long data in the table data page, avoids increasing the storage dispersion of the table due to infrequently accessed large object data, and makes the index structure of the table more compact, thereby increasing index efficiency and improving the performance of accessing or modifying the table. At the same time, since the storage size of data pages and records is usually limited, LOB technology also enables the database to store very large object data.

[0003] In some database systems, the LOB segment of a table can be separated from the table space to which the table belongs, that is, the table regular data and the table LOB data can be stored in different table spaces. The benefit of this feature is that the table regular data and the table LOB data can be placed on different storage devices, thereby flexibly configuring storage performance and high availability. For example, the table regular data uses dual-active and high-performance storage, while the LOB object query frequency is low and the storage with average performance can be used. In addition, when scanning a table with LOB fields, parallelization can be performed to fully utilize the IO capabilities of the two storages to improve data query performance.

[0004] In this type of database, it is usually supported to migrate table large object data from the original tablespace to the target tablespace. The general approach is: 1) Perform a specific level of blocking on the tablespace, table, LOB segment, etc. to be operated to prevent concurrent access or modification; 2) Create a new LOB segment in the target tablespace to store the LOB data of the table; 3) Traverse all records in the table, and if there is out-of-row data, copy the out-of-row data to the newly created LOB segment in the target tablespace; 4) If LOB data migration occurs, update the corresponding in-row header in the table to point to the out-of-row data in the new LOB segment; 5) Update the table dictionary, release the block in 1, and release the LOB segment in the original tablespace.

[0005] Since the LOB segment stores large object data, the data volume is usually large. When there is a large amount of LOB data in the table, the above steps 3 and 4 will have a large I / O volume and take a long time, which is the performance bottleneck of migrating large object data operations. At this time, the blockade in step 1 is still retained, that is, the table is considered to be executing the data definition language (Data Definition Language, DDL). Before the execution is completed, the table cannot be manipulated (Data Manipulation Language, DML) operations (insert, update, delete), affecting the concurrency and availability of the database system. Summary of the invention

[0006] The embodiments of the present disclosure provide a method, device, equipment and product for online large object data migration in a database, which eliminates the performance bottleneck of large object data migration and enables large object data migration to fully utilize the isolation level provided by the database transaction mechanism.

[0007] In a first aspect, a method for online large object data migration in a database is provided, the method comprising:

[0008] Update the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address;

[0009] Determine the large object data to be migrated based on the row to be operated and the header information in the initial row of the table to be operated; the row to be operated needs to migrate the large object data; the large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address;

[0010] The large object to be migrated is blocked based on a large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object data to be migrated and the initial row header information;

[0011] Based on the initial in-row header information, copy the large object data to be migrated to the target table space corresponding to the target large object segment address; and update the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space;

[0012] The DDL transaction for large object data migration is submitted, and the initial table space is released to complete the data migration of the large object data to be migrated.

[0013] In a second aspect, a device for online large object data migration in a database is provided, comprising:

[0014] An update module, used for updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address;

[0015] A positioning module is used to determine the large object data to be migrated based on the row to be operated and the header information in the initial row of the table to be operated; the row to be operated is the row that needs to be migrated for the large object data; the large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address;

[0016] A blocking module, used for blocking the large object to be migrated based on a large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object data to be migrated and the initial row header information;

[0017] A data migration module, configured to copy the large object data to be migrated to a target table space corresponding to the target large object segment address based on the initial in-row header information; and update the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space;

[0018] The commit module is used to commit the DDL transaction of the large object data migration and release the initial table space to complete the data migration of the large object data to be migrated.

[0019] In a third aspect, an electronic device is provided, including:

[0020] at least one processor; and

[0021] a memory communicatively connected to the at least one processor; wherein,

[0022] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the method for online large object data migration in a database as described in the first aspect above.

[0023] In a fourth aspect, a computer-readable storage medium is provided, on which a computer program is stored. When the program is executed by a processor, the method for online large object data migration in a database as described in the first aspect is implemented.

[0024] In a fifth aspect, a computer program product is provided, the computer program product comprising a computer program, and when the computer program is executed by a processor, the computer program implements the method for online large object data migration in a database as described in the first aspect above.

[0025] The disclosed embodiment discloses a method, device, equipment and product for online large object data migration in a database, comprising: updating an initial large object segment address in a table dictionary of a table to be operated to a target large object segment address; determining the large object data to be migrated based on the row to be operated and the initial row header information of the table to be operated; the row to be operated is a row that requires large object data migration; the large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address; blocking the large object to be migrated based on a large object unique identifier; the large object to be migrated includes the large object data to be migrated and the initial row header information; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; based on the initial row header information, copying the large object data to be migrated to the target table space corresponding to the target large object segment address; and updating the initial row header information so that the updated row header information points to the migrated large object data stored in the target table space; submitting a DDL transaction for large object data migration, and releasing the initial table space to complete the data migration of the large object data to be migrated. The technical solution provided in this embodiment blocks the large object data to be migrated only through the large object unique identifier during data migration, fully utilizes the isolation level, visibility, concurrency, fault recovery, rollback and other capabilities provided by the transaction mechanism, allows other concurrent transactions to perform concurrent queries and DML operations on the table during the large object migration process, eliminates the performance bottleneck of large object data migration, improves the concurrency and availability of the system, and improves the overall performance of the system.

[0026] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present disclosure, nor is it intended to limit the scope of the embodiments of the present disclosure. Other features of the embodiments of the present disclosure will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] In order to more clearly illustrate the technical solutions in the embodiments of the present disclosure, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0028] Figure 1 It is a flow chart of a method for online large object data migration in a database provided by the first embodiment of the present disclosure;

[0029] Figure 2 This is a schematic diagram of a large object data storage provided by the first embodiment of the present disclosure;

[0030] Figure 3 This is a schematic diagram of a large object data migration process provided by the first embodiment of the present disclosure.

[0031] Figure 4 This is a schematic diagram of the structure of an online large object data migration device in a database provided by the second embodiment of the present disclosure;

[0032] Figure 5 It is a structural diagram of an electronic device provided in Embodiment 3 of the present disclosure. DETAILED DESCRIPTION

[0033] In order to enable those skilled in the art to better understand the solutions of the embodiments of the present disclosure, the technical solutions in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are only embodiments of a part of the embodiments of the present disclosure, rather than all of the embodiments. Based on the embodiments in the embodiments of the present disclosure, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the embodiments of the present disclosure.

[0034] It should be noted that the terms "first", "second", etc. in the specification and claims of the embodiments of the present disclosure and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present disclosure described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products, or devices.

[0035] Embodiment 1

[0036] Figure 1This is a flowchart of a method for online large object data migration in a database provided by the first embodiment of the present disclosure. This embodiment is applicable to the case of migrating online large object data in a database. The method can be executed by an online large object data migration device in a database. The online large object data migration device in the database can be implemented in the form of hardware and / or software. The online large object data migration device in the database can be configured in an electronic device, and the electronic device includes but is not limited to a computer, a terminal, a server, and other devices with data processing capabilities. Figure 1 As shown, the method includes:

[0037] S110: Update the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address.

[0038] In this embodiment, the initial large object segment address in the table dictionary of the table to be operated can be updated to the target large object segment address. The table to be operated can be a table that needs to migrate large object data, the initial large object segment address can be the original segment address where the large object data is located, and the target large object segment address can be the segment address after the large object data is migrated, that is, it can be understood as the target segment address after the migration.

[0039] It should be explained that the initial large object segment address in the table dictionary of the table to be operated is updated to the target large object segment address so that the large object data newly inserted by subsequent concurrent transactions can be stored in the target large object segment address.

[0040] S120, determining the large object data to be migrated based on the rows to be operated and the header information in the initial rows of the table to be operated; the rows to be operated that require large object data migration; and storing the large object data to be migrated in the initial table space corresponding to the initial large object segment address.

[0041] Specifically, for the data in the table, each data header contains an identifier for identifying whether the data exists in the current data page or in the large object segment. The initial in-row header information can be an identifier that marks that the data of the current row is stored in other segments. The rows to be operated in the table to be operated can be screened based on the initial in-row header information, and the large object data corresponding to the address information stored in the rows to be operated containing the initial in-row header information can be determined as the large object data to be migrated. Among them, the rows to be operated require large object data migration; the rows to be operated can be understood as row records that can be seen or accessed by the current transaction under a specific transaction isolation level. This concept is closely related to the transaction isolation level of the database, and different isolation levels determine which data is visible to the current transaction. The large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address.

[0042] S130, blocking the large object to be migrated based on the large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object data to be migrated and the initial row header information.

[0043] In this embodiment, the large object unique identifier may be stored in the initial row header information of the table data page of the table to be operated. The large object unique identifier may be an identifier in the row header for uniquely identifying the LOB object. It is a globally unique value used to accurately locate and access specific LOB data in the database. Exemplarily, the large object unique identifier may be represented as LOB_ID, and the database can find the exact location of the LOB data on the storage medium through the large object unique identifier.

[0044] Specifically, the large object to be migrated can be blocked based on the large object unique identifier. The large object to be migrated includes the large object data to be migrated and the initial in-row header information. The initial in-row header information can be metadata stored in each row of data on the table data page. The metadata describes the structure and location information of the row data. In some database systems, the in-row header may include a pointer or offset to the data stored outside the row.

[0045] S140. Copy the large object data to be migrated to the target table space corresponding to the target large object segment address based on the initial in-row header information; and update the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space.

[0046] In this embodiment, the large object data to be migrated can be accessed according to the initial in-row header information, and the large object data to be migrated can be copied to the target table space corresponding to the target large object segment address; the initial in-row header information includes the large object data information. For example, the large object data information may include other metadata besides LOB_ID, which may include the length of the LOB data, the storage location (such as table space number, file number, page number, etc.), the data version, etc. These initial in-row header information helps the database management system manage and access the LOB data, but they do not directly provide a unique identifier for the LOB data.

[0047] Specifically, after the large object data to be migrated is copied to the target table space corresponding to the target large object segment address, the initial in-row header information can be updated, and the updated in-row header information is executed for the migrated large object data stored in the target table space.

[0048] S150: Submit the DDL transaction for large object data migration and release the initial table space to complete the data migration of the large object data to be migrated.

[0049] In this embodiment, the DDL transaction for large object data migration may be a transaction for executing large object data migration. Submitting the DDL transaction for large object data migration may make all changes permanent. After submitting the DDL transaction for large object data migration, the initial table space may be released, and the data migration of the large object data to be migrated may be completed.

[0050] The present embodiment provides an online large object data migration method in a database, comprising: updating an initial large object segment address in a table dictionary of a table to be operated to a target large object segment address; determining large object data to be migrated based on a row to be operated and initial row header information of the table to be operated; the row to be operated is a row that requires large object data migration; the large object data to be migrated is stored in an initial table space corresponding to the initial large object segment address; blocking the large object to be migrated based on a large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object to be migrated The method comprises the following steps: first, copying the large object data to be migrated and the initial in-row header information; based on the initial in-row header information, copying the large object data to be migrated to the target table space corresponding to the target large object segment address; and updating the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space; submitting the DDL transaction of the large object data migration, and releasing the initial table space to complete the data migration of the large object data to be migrated, thereby eliminating the performance bottleneck of the large object data migration, enabling the large object data migration to make full use of the isolation level provided by the database transaction mechanism, improving the concurrency and availability of the system, and improving the overall performance of the system.

[0051] As an optional implementation of this embodiment, after updating the initial in-line header information, the method further includes:

[0052] Unblock the large object to be migrated.

[0053] Specifically, after the initial in-row header information is updated, the blockade of the large object to be migrated may be released, that is, it can be understood as releasing the blockade of the migrated large object data and the updated in-row header information.

[0054] As an optional implementation of this embodiment, the method for online large object data migration in a database provided by this embodiment, before updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address, the method further includes:

[0055] The database object is blocked based on a first preset transaction isolation level to prevent concurrent modification of the initial database object; the database object includes a table space, a table and / or a large object LOB segment.

[0056] In this embodiment, the first preset transaction isolation level may be a pre-set level, and the database object may be blocked at the first preset transaction isolation level to prevent concurrent modification, wherein the database object includes a table space, a table and / or a large object LOB segment.

[0057] Among them, in the database management system, a specific level of blocking of objects such as tablespaces, tables, LOB segments, etc. to be operated can refer to the implementation of a locking mechanism to control concurrent access to these database objects. This blocking is to ensure the consistency, integrity and isolation of data and prevent conflicts between modification operations in concurrent transactions.

[0058] It should be noted that the first preset transaction isolation level may include multiple different levels, that is, different data objects correspond to different locking levels. Different levels of locks are implemented on data objects (such as rows, tables, table spaces, etc.). The levels of these locks determine how multiple transactions can safely interact and access shared data in a concurrent environment, while preventing data inconsistency and conflicts.

[0059] It should be noted that after completing the data migration of the large object data to be migrated, not only the initial table space needs to be released, but also the blockade of other database objects (tables and / or large object LOB segments) needs to be released.

[0060] As an optional implementation of this embodiment, the method for online large object data migration in a database provided by this embodiment, after updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address, the method further includes:

[0061] Lowering the locking level of the table to be operated so that the concurrent transaction performs query and / or data manipulation DML operation on the table to be operated;

[0062] Specifically, the blocking level of the table to be operated can be lowered so that the concurrent transaction can perform query and / or data manipulation DML operations on the table to be operated. It should be noted that DDL operations on the table are not allowed at this time. In this embodiment, the DDL operation can be a transaction for performing large object data migration. DML can be a part of SQL (Structured Query Language) and is used to perform operations such as query, insert, update and delete on data in the database.

[0063] As an optional implementation of this embodiment, the online large object data migration method in the database provided by this embodiment also includes:

[0064] If a failure occurs before the DDL transaction of the large object data migration is committed, the updated in-row header information is rolled back to the initial in-row header information according to the rollback log during failure recovery, and the rollback log is generated by the migration process of the large object data to be migrated;

[0065] Specifically, if a failure occurs in the large data migration process before the DDL transaction of the large object data migration is committed, the updated in-row header information can be rolled back to the initial in-row header information according to the rollback log after the failure is recovered. The rollback log can be generated through the migration process of the large object data to be migrated.

[0066] As an optional implementation of this embodiment, the DML operation is an insert operation, and the concurrent transaction performs the DML operation on the table to be operated, including:

[0067] In the case where the inserted data includes large object data, the large object data to be inserted is inserted into the target table space corresponding to the target large object segment address based on the table dictionary of the table to be operated.

[0068] It can be known that if a concurrent transaction performs an insert operation on a table, based on the transaction visibility rule, the newly inserted data is invisible to the DDL transaction that performs the large object data migration, and therefore will not be traversed. Therefore, based on the table dictionary of the table to be operated, if a concurrent transaction inserts large object data, the large object data will be directly inserted into the target table space corresponding to the target large object segment address according to the dictionary information.

[0069] Among them, transaction visibility rules refer to the visibility rules of the intermediate states of transactions to other transactions during the transaction processing in the database management system. These rules ensure the isolation of transactions, that is, before a transaction is completed, at a specific transaction isolation level, the modifications it makes to the database are not visible to other transactions.

[0070] Optionally, the method further includes:

[0071] 1) Execute the DDL transaction of the large object data migration; in this embodiment, the DDL transaction execution can be understood as the database system is modifying the structure of the database according to the DDL statement, and the DDL transaction can include creating (CREATE), modifying (ALTER), deleting (DROP) tables or indexes, etc. Exemplarily, the DDL transaction can be a transaction for executing large object data migration.

[0072] 2) If the DDL transaction for the large object data migration fails to execute, the updated in-row header information is rolled back to the initial in-row header information according to the rollback log, and the target table space is released;

[0073] In this embodiment, if the DDL transaction fails to execute, the updated in-row header information can be rolled back to the initial in-row header information according to the rollback log, and after the rollback is successful, the target table space is released.

[0074] 3) Migrate the inserted data back to the initial tablespace.

[0075] Specifically, after the table space is released, the insert data newly inserted by the concurrent transaction may be migrated back to the initial table space, wherein the insert data refers to out-of-row large object data.

[0076] It should be explained that, since concurrent transactions will insert new large object data into the target tablespace, it is necessary to copy it to the initial tablespace to ensure the integrity and correctness of the data. The specific operation is to first block the table, then traverse the table columns and determine the record visibility. If the record is newly inserted by a concurrent DML transaction (including inserts resulting from updates), the LOB data will be in the target tablespace and needs to be copied to the original initial tablespace and the initial row header information updated. The execution steps are the same as the execution steps of the online large object data migration method provided in the first embodiment of the present invention, and will not be repeated here. After the rollback is completed, the target tablespace is released. If there are other uncommitted transaction rollbacks involving the table, they need to be performed first to ensure that the transaction for online large object data migration is the last rollback involving the table.

[0077] As an optional implementation of this embodiment, the data manipulation DML operation is a delete operation, and the concurrent transaction performs the data manipulation DML operation on the table to be operated, including:

[0078] The row to be deleted is marked with a preset deletion identifier to complete the deletion operation of the row to be deleted.

[0079] Specifically, if a concurrent transaction performs a deletion operation on a table, the row to be deleted can be directly marked for deletion (a mark bit (preset deletion identifier) ​​is set on the record header to indicate that the record has been deleted, and the transaction number at the end of the record is modified to the transaction that deleted the record). The record header can be the beginning of a row of records in a table in the database, which contains the metadata of the record, which provides some information about the record itself.

[0080] It should be noted that if the data manipulation DML operation is an update operation, and a concurrent transaction performs an update operation on the table, it can be converted into a delete operation and an insert operation and their respective rules can be followed.

[0081] As an optional implementation of this embodiment, the method provided in this embodiment, after marking the row to be deleted by using a preset deletion identifier, the method further includes:

[0082] According to the transaction visibility rule, the to-be-migrated large object data corresponding to the to-be-deleted operation row is migrated to the target table space corresponding to the target large object segment address.

[0083] It should be explained that based on the transaction visibility rule, the deletion of concurrent transactions is invisible to DDL transactions. Therefore, even if a row that has been marked for deletion by a concurrent transaction is traversed, the out-of-row data must be migrated to the target tablespace corresponding to the target large object segment address.

[0084] For example, for the record (A, B, C, D), columns C and D are large object data columns. Before the migration transaction traverses to this record, a concurrent transaction has deleted the record. If the concurrent transaction (DDL transaction for large object data migration) has not been committed, the DDL transaction cannot see the deletion of the concurrent transaction, and the LOB data of columns C and D still needs to be migrated to the new LOB segment.

[0085] This embodiment also provides an application example of an online large object data migration method in a database: In this embodiment, online LOB migration means that while large object data migration is being performed, other concurrent transactions can still query and perform DML operations on the table. The DDL transaction hereinafter can be understood as a transaction that performs large object data migration.

[0086] 1) Start a DDL transaction, and block the tablespace, table, LOB segment and other objects to be operated at a specific level (the first preset transaction isolation level) to prevent concurrent modification. Then modify the table dictionary, and update the LOB segment information (initial large object segment address) in the table dictionary to the new LOB segment (target large object segment address) of the target tablespace. The table dictionary is modified at this time to allow the newly inserted LOB data of subsequent concurrent transactions to be stored in the new LOB segment.

[0087] 2) Downgrade the block on the table in step 1 so that concurrent transactions can query and perform DML operations on the table, but do not allow DDL operations on the table.

[0088] 3) Traverse each visible row (row to be operated) in the source table to find the large object data that needs to be migrated (large object data to be migrated). Figure 2 A schematic diagram of a large object data storage provided in this embodiment, such as Figure 2 As shown, the in-row header of large field data (i.e., large object data) C and the in-row header information of large field data (i.e., large object data) D are stored in the data page of the table together with regular data A and regular data B, and the out-of-row complete data of large field data (large object data) C is stored in the LOB segment pointed to by the in-row header of C, and the out-of-row complete data of large field data (large object data) D is stored in the LOB segment pointed to by the in-row header of D.

[0089] First, the LOB object is blocked by the LOB_ID (large object unique identifier) ​​in the row header, and then the out-of-row data in the original LOB segment is accessed according to the LOB information in the row header (initial row header information), and the out-of-row data (large object data to be migrated) is copied to the new LOB segment (the target table space corresponding to the target large object segment address). Then the row header (updated row header information) is updated to point to the out-of-row data (large object data to be migrated) in the new LOB segment (in the target table space). Then the LOB_ID block is released.

[0090] Figure 3 A schematic diagram of a large object data migration process provided in this embodiment is as follows: Figure 3 As shown, the in-row header of large field data (large object data) C and the in-row header information of large field data (large object data) D are stored in the data page of the table together with regular data A and regular data B, the out-of-row complete data of large field data (large object data) C is stored in the LOB segment pointed to by the in-row header of C, and the out-of-row complete data of large field data (large object data) D is stored in the LOB segment pointed to by the in-row header of D. If it is necessary to migrate the out-of-row complete data of large field data (large object data) D, the new in-row header information of large field data (large object data) D can be replaced with the in-row header information of large field data (large object data) D and saved in the table data page, and the out-of-row complete data of large field data (large object data) D can be copied to the new LOB segment (target table space) for storage.

[0091] 4) Commit the DDL transaction (DDL transaction for large object data migration) to release the LOB segment in the original tablespace. At this time, the LOB segment migration of the table is completed and stored in the target tablespace.

[0092] At the same time, during the data migration process, a rollback log for updating the row header can be generated. If a failure occurs before the DDL transaction is committed, the row header in the table will be restored to the state before the update (initial row header information) according to the rollback log during failure recovery. At this time, the old LOB segment (initial tablespace) has not been released, and the LOB data (large object data to be migrated) is still in the old LOB segment (initial tablespace).

[0093] After the rollback is complete, the row header points to the data in the old LOB segment, and then the new LOB segment (target tablespace) is released.

[0094] During the data migration process, the table lock is downgraded, and concurrent transactions can query and perform DML operations on the table. However, the LOB_ID of the object currently being migrated will be blocked during the migration process, and concurrent transactions cannot access or modify the blocked LOB object, indirectly achieving a function similar to row lock.

[0095] The present invention proposes a new method for online migration of large object data. When copying out-of-row LOB data to a new LOB segment, the blocking of the table is downgraded, and only a single LOB object is blocked through LOB_ID, thereby eliminating the performance bottleneck of large object data migration, so that the large object data migration can fully utilize the isolation level, visibility, concurrency, fault recovery, rollback and other capabilities provided by the database transaction mechanism, allowing concurrent transactions to perform concurrent queries and DML operations on the table. Compared with the existing methods, it has obvious advantages, improves the high concurrency and high availability capabilities of the database system, and improves the overall system performance.

[0096] Embodiment 2

[0097] Figure 4 is a schematic diagram of the structure of an online large object data migration device in a database provided by the second embodiment of the present disclosure; Figure 4 As shown, the device includes: an update module 210, a positioning module 220, a blocking module 230, a data migration module 240, and a submission module 250.

[0098] The updating module 210 is used to update the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address;

[0099] A positioning module 220 is used to determine the large object data to be migrated based on the row to be operated and the header information in the initial row of the table to be operated; the row to be operated is the row that needs to be migrated for large object data; the large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address;

[0100] The blocking module 230 is used to block the large object to be migrated based on the large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object data to be migrated and the initial row header information;

[0101] The data migration module 240 is used to copy the large object data to be migrated to the target table space corresponding to the target large object segment address based on the initial in-row header information; and update the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space;

[0102] The commit module 250 is used to commit the DDL transaction of the large object data migration and release the initial table space to complete the data migration of the large object data to be migrated.

[0103] Embodiment 2 of the present disclosure provides an online large object data migration device in a database, which eliminates the performance bottleneck of large object data migration, enables large object data migration to fully utilize the isolation level provided by the database transaction mechanism, improves the concurrency and availability of the system, and improves the overall performance of the system.

[0104] Furthermore, the device further comprises:

[0105] A first blocking module, configured to block a database object based on a first preset transaction isolation level to prevent concurrent modification of the initial database object; the database object includes a table space, a table and / or a large object LOB segment;

[0106] The device also includes:

[0107] A downgrade blocking module, used to lower the blocking level of the table to be operated, so that concurrent transactions can perform query and / or data manipulation DML operations on the table to be operated;

[0108] The device also includes:

[0109] The unblocking module is used to unblock the large object to be migrated.

[0110] Furthermore, the device also includes:

[0111] A rollback module, used for rolling back the updated in-row header information to the initial in-row header information according to a rollback log during failure recovery if a failure occurs before the DDL transaction of the large object data migration is committed, wherein the rollback log is generated through the migration process of the large object data to be migrated;

[0112] The device also includes:

[0113] An execution module, used for executing the DDL transaction of the large object data migration;

[0114] A release module, used for rolling back the updated in-row header information to the initial in-row header information according to the rollback log and releasing the target table space if the DDL transaction execution of the large object data migration fails;

[0115] The first migration module is used to migrate the large object data to be migrated that has been migrated to the target tablespace back to the initial tablespace.

[0116] Furthermore, the DML operation is an insert operation, and the concurrent transaction performs the DML operation on the table to be operated, including:

[0117] In the case where the inserted data includes large object data, the large object data to be inserted is inserted into the target table space corresponding to the target large object segment address based on the table dictionary of the table to be operated.

[0118] Furthermore, the data manipulation DML operation is a delete operation, and the concurrent transaction performs the data manipulation DML operation on the table to be operated, including:

[0119] The row to be deleted is marked with a preset deletion identifier to complete the deletion operation of the row to be deleted.

[0120] Furthermore, after marking the to-be-deleted operation row with a preset deletion identifier, the method further includes:

[0121] According to the transaction visibility rule, the to-be-migrated large object data corresponding to the to-be-deleted operation row is migrated to the target table space corresponding to the target large object segment address.

[0122] The online large object data migration device in a database provided by the embodiments of the present disclosure can execute the online large object data migration method in a database provided by any embodiment of the present disclosure, and has the corresponding functional modules and beneficial effects of the execution method.

[0123] Embodiment 3

[0124] Figure 5 A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the embodiments of the present disclosure described and / or claimed herein.

[0125] like Figure 5 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0126] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0127] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microprocessor, etc. The processor 11 executes the various methods and processes described above, such as an online large object data migration method in a database.

[0128] In some embodiments, the method for online large object data migration in a database may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the method for online large object data migration in a database described above may be executed. Alternatively, in other embodiments, the processor 11 may be configured to execute the method for online large object data migration in a database in any other appropriate manner (e.g., by means of firmware).

[0129] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0130] The computer programs for implementing the methods of the embodiments of the present disclosure may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.

[0131] In the context of the disclosed embodiments, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, device, or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0132] To provide interaction with a user, the systems and techniques described herein may be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).

[0133] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0134] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.

[0135] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps recorded in the embodiments of the present disclosure can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solutions of the embodiments of the present disclosure can be achieved, and this document does not limit this.

[0136] The above specific implementations do not constitute a limitation on the protection scope of the embodiments of the present disclosure. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the embodiments of the present disclosure shall be included in the protection scope of the embodiments of the present disclosure.

[0137] The embodiments of the present disclosure also provide a computer program product, including a computer program and / or instructions, which, when executed by a processor, implements the method for online large object data migration in a database as provided in any embodiment of the present application.

[0138] In the process of implementation, the computer program product can be written in one or more programming languages ​​or a combination thereof to write computer program codes for performing the operation of the disclosed embodiments, the programming languages ​​including object-oriented programming languages, such as Java, Smalltalk, C++, and also including conventional procedural programming languages, such as "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computer (e.g., using an Internet service provider to connect through the Internet).

[0139] Note that the above are only preferred embodiments of the embodiments of the present disclosure and the technical principles used. Those skilled in the art will understand that the embodiments of the present disclosure are not limited to the specific embodiments herein, and that various obvious changes, readjustments and substitutions can be made by those skilled in the art without departing from the protection scope of the embodiments of the present disclosure. Therefore, although the embodiments of the present disclosure are described in more detail through the above embodiments, the embodiments of the present disclosure are not limited to the above embodiments, and may include more other equivalent embodiments without departing from the concept of the embodiments of the present disclosure, and the scope of the embodiments of the present disclosure is determined by the scope of the attached claims.

Claims

1. A method for online large object data migration in a database, characterized in that: The method comprises: Update the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address; Determine the large object data to be migrated based on the row to be operated and the header information in the initial row of the table to be operated; the row to be operated needs to migrate the large object data; the large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address; The large object to be migrated is blocked based on a large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object data to be migrated and the initial row header information; Based on the initial in-row header information, copy the large object data to be migrated to the target table space corresponding to the target large object segment address; and update the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space; The DDL transaction for large object data migration is submitted, and the initial table space is released to complete the data migration of the large object data to be migrated.

2. The method according to claim 1, characterized in that Before updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address, the method further includes: Blocking a database object based on a first preset transaction isolation level to prevent concurrent modification of the initial database object; the database object includes a table space, a table and / or a large object LOB segment; After updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address, the method further includes: Lowering the locking level of the table to be operated so that the concurrent transaction performs query and / or data manipulation DML operation on the table to be operated; After updating the initial in-line header information, the method further includes: Unblock the large object to be migrated.

3. The method according to claim 2, characterized in that The DML operation is an insert operation, and the concurrent transaction performs the DML operation on the table to be operated, including: In the case where the inserted data includes large object data, the large object data to be inserted is inserted into the target table space corresponding to the target large object segment address based on the table dictionary of the table to be operated.

4. The method according to claim 3, characterized in that The method further comprises: If a failure occurs before the DDL transaction of the large object data migration is committed, the updated in-row header information is rolled back to the initial in-row header information according to the rollback log during failure recovery, and the rollback log is generated by the migration process of the large object data to be migrated; The method further comprises: Execute the DDL transaction of the large object data migration; If the DDL transaction execution of the large object data migration fails, the updated in-row header information is rolled back to the initial in-row header information according to the rollback log, and the target table space is released; Migrate the inserted data back to the original tablespace.

5. The method according to claim 2, characterized in that: When the DML operation is a delete operation, the concurrent transaction performs the DML operation on the table to be operated, including: The row to be deleted is marked with a preset deletion identifier to complete the deletion operation of the row to be deleted.

6. The method according to claim 5, characterized in that After marking the to-be-deleted operation row with a preset deletion identifier, the method further includes: According to the transaction visibility rule, the to-be-migrated large object data corresponding to the to-be-deleted operation row is migrated to the target table space corresponding to the target large object segment address.

7. An online large object data migration device in a database, characterized in that: include: An update module, used for updating the initial large object segment address in the table dictionary of the table to be operated to the target large object segment address; A positioning module is used to determine the large object data to be migrated based on the row to be operated and the header information in the initial row of the table to be operated; the row to be operated is the row that needs to be migrated for the large object data; the large object data to be migrated is stored in the initial table space corresponding to the initial large object segment address; A blocking module, used for blocking the large object to be migrated based on a large object unique identifier; the large object unique identifier is stored in the initial row header information of the table data page of the table to be operated; the large object to be migrated includes the large object data to be migrated and the initial row header information; A data migration module, used for copying the large object data to be migrated to a target table space corresponding to the target large object segment address based on the initial in-row header information; and updating the initial in-row header information so that the updated in-row header information points to the migrated large object data stored in the target table space; The commit module is used to commit the DDL transaction of the large object data migration and release the initial table space to complete the data migration of the large object data to be migrated.

8. An electronic device, characterized in that: include: at least one processor; as well as a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the method for online large object data migration in a database as described in any one of claims 1-6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the method for online large object data migration in a database as described in any one of claims 1 to 6 is implemented.

10. A computer program product, characterized in that The computer program product comprises a computer program, and when the computer program is executed by a processor, the computer program implements the method for online large object data migration in a database as claimed in any one of claims 1 to 6.

Images