Performing commit operations with recovery on data system
Various example embodiments described herein provide for systems, methods, devices, instructions, and the like for performing commit operations (e.g., write operations) with recovery on a data system, such as a database system, that is interacting with a data source external to the data system, such as an external catalog. For some example embodiments, the database system generates an operation identifier in association with each command to commit a change to an entity of the catalog linked database, where the operation identifier serves as a unique tracking mechanism for commit operations across the distributed systems.
1 . A database system comprising:
at least one processor; and
at least one memory storing instructions that cause the at least one processor to perform operations comprising:
receiving a command to commit a change to a first entity of a catalog linked database that links the database system to an external catalog that is external to the database system; and
based on receiving the command:
generating a unique operation identifier in association with the command;
sending, from the database system to the external catalog, a commit request to commit the change to a second entity of the external catalog, the second entity of the external catalog corresponding to the first entity of the catalog linked database, the commit request comprising the unique operation identifier;
determining an outcome of the commit request by checking whether the unique operation identifier is found in metadata of the second entity of the external catalog, the metadata of the second entity of the external catalog being received from the external catalog; and
performing a synchronization action on the database system based on the outcome, the synchronization action comprising synchronizing a commit state of the change on the catalog linked database with a commit state of the change on the external catalog.
2 . The database system of claim 1 , wherein the operations comprise:
after the generating of the unique operation identifier, storing the unique operation identifier on the database system in association with the first entity of the catalog linked database.
3 . The database system of claim 2 , wherein the storing is performed before the sending of the commit request to the external catalog is performed.
4 . The database system of claim 1 , wherein the command to commit the change to the entity comprises either a command to write target data to the entity, or a command to create, drop, or modify the entity.
5 . The database system of claim 1 , wherein the commit request comprises a request to add the unique operation identifier to the metadata of the second entity of the external catalog.
6 . The database system of claim 1 , wherein the checking of whether the unique operation identifier is found in metadata of the second entity of the external catalog comprises:
requesting and receiving, from the external catalog, the metadata of the second entity of the external catalog.
7 . The database system of claim 1 , wherein the checking of whether the unique operation identifier is found in metadata of the second entity of the external catalog comprises:
sending, from the database system to the external catalog, a metadata request for the metadata of the second entity of the external catalog;
detecting whether the metadata request fails; and
retrying the sending of the metadata request based on determining that the metadata request fails.
8 . The database system of claim 7 , wherein the retrying of the sending of the request comprises:
after the retrying of the sending of the metadata request a predetermined number of times, determining that the outcome of the commit request is unsuccessful.
9 . The database system of claim 7 , wherein the retrying of the sending of the request comprises:
after the retrying of the sending of the metadata request a predetermined number of times:
transitioning the commit request to an unknown status; and
scheduling the synchronization action to resolve the unknown status at a later time.
10 . The database system of claim 1 , wherein the determining of the outcome of the commit request comprises:
determining that the outcome of the commit request is successful based on the unique operation identifier being found in the metadata of the second entity of the external catalog.
11 . The database system of claim 1 , wherein the determining of the outcome of the commit request comprises:
determining that the outcome of the commit request is unsuccessful based on the unique operation identifier not being found in the metadata of the second entity of the external catalog.
12 . The database system of claim 1 , wherein the performing of the synchronization action on the database system based on the outcome comprises:
determining whether the outcome indicates that the commit request is unsuccessful; and
based on determining that the outcome indicates that the commit request is unsuccessful, causing removal of the unique operation identifier stored in the metadata of the first entity of the catalog linked database.
13 . The database system of claim 1 , wherein the performing of the synchronization action on the database system based on the outcome comprises:
determining whether the outcome indicates that the commit request is successful; and
based on determining that the outcome indicates that the commit request is successful, causing one or more changes to the second entity of the external catalog to be applied to the first entity of the catalog linked database based on the metadata of the second entity of the external catalog, the one or more changes comprising the change to the second entity of the external catalog caused by the commit request.
14 . The database system of claim 13 , wherein the performing of the synchronization action on the database system based on the outcome comprises:
based on determining that the outcome indicates that the commit request is successful and after the causing of the one or more changes to the second entity of the external catalog to be applied to the first entity of the catalog linked database, causing removal of the unique operation identifier stored in the metadata of the first entity of the catalog linked database.
15 . The database system of claim 1 , wherein the commit request comprises either a data manipulation language (DML) operation or a data definition language (DDL) operation.
16 . The database system of claim 1 , wherein the unique operation identifier comprises an identifier that is unique to the commit request from the database system.
17 . The database system of claim 1 , wherein the unique operation identifier comprises at least one of:
a common prefix identifying the database system as a source of the commit request;
a hash value corresponding to at least one of a deployment identifier, an account identifier, or an entity identifier of the first entity of the catalog linked database; or
a timestamp component indicating when the commit request was generated by the database system.
18 . The database system of claim 1 , wherein the commit request is a first commit request, and wherein the operations comprise:
after a second commit request is sent from the database system to the external catalog:
identifying one or more unique operation identifiers that are currently stored in metadata provided by the external catalog and that are corresponding to commit transactions determined to be in terminal state on the external catalog based on timestamp and entity information; and
causing removal of one or more unique operation identifiers from the metadata of the external catalog.
19 . A method comprising:
receiving, by at least one processor of a database system, a command to commit a change to a first entity of a catalog linked database that links the database system to an external catalog that is external to the database system; and
based on receiving the command:
generating, by the at least one processor, a unique operation identifier in association with the command;
sending, from the database system to the external catalog, by the at least one processor, a commit request to commit the change to a second entity of the external catalog, the second entity of the external catalog corresponding to the first entity of the catalog linked database, the commit request comprising the unique operation identifier;
determining, by the at least one processor, an outcome of the commit request by checking whether the unique operation identifier is found in metadata of the second entity of the external catalog, the metadata of the second entity of the external catalog being received from the external catalog; and
performing, by the at least one processor, a synchronization action on the database system based on the outcome, the synchronization action comprising synchronizing a commit state of the change on the catalog linked database with a commit state of the change on the external catalog.
20 . A machine-storage medium storing instructions that when executed by a machine operating a database system, cause the machine to perform operations comprising:
receiving a command to commit a change to a first entity of a catalog linked database that links the database system to an external catalog that is external to the database system; and
based on receiving the command:
generating a unique operation identifier in association with the command;
sending, from the database system to the external catalog, a commit request to commit the change to a second entity of the external catalog, the second entity of the external catalog corresponding to the first entity of the catalog linked database, the commit request comprising the unique operation identifier;
determining an outcome of the commit request by checking whether the unique operation identifier is found in metadata of the second entity of the external catalog, the metadata of the second entity of the external catalog being received from the external catalog; and
performing a synchronization action on the database system based on the outcome, the synchronization action comprising synchronizing a commit state of the change on the catalog linked database with a commit state of the change on the external catalog.