Search

CN-121986328-A - Architecture snapshot isolated access in relational databases

CN121986328ACN 121986328 ACN121986328 ACN 121986328ACN-121986328-A

Abstract

A relational database system is disclosed that supports operation with versioned metadata. The relational database system includes a lock manager, a transaction manager, and version-aware metadata storage and caches configured to store and manage versions of metadata to determine which versions of such versions should be visible at any given point in time and to support creation of correct versions of metadata. In one aspect, a transaction manager manages transaction identifiers and their associated start times, end times, and/or commit times. Such data supports determining transaction visibility at any point in time, and thus supports determining metadata version visibility. In one aspect, such metadata versioning supports snapshot isolation of metadata-enabled transactions.

Inventors

  • N. A. El Shihabi
  • P. Antoniopolos
  • KODAVALLA HANUMANTHA R.
  • N. C. Ogg
  • D. R. Tikmani
  • Jin Yuanxi
  • A. W-m. Swanson
  • R. JAIN

Assignees

  • 微软技术许可有限责任公司

Dates

Publication Date
20260505
Application Date
20241021
Priority Date
20240131

Claims (20)

  1. 1. A relational database system (100), comprising: a metadata version aware execution engine (108) configured to: -receiving (502) a data read command (126, 308, 408), the data read command (126, 308, 408) comprising a first query and a second query, each query referencing a metadata object in a metadata store (114) of the relational database system (100), and each query being configured to read a user data object from a user data store of the relational database system (100); -receiving (504) a metadata modification command (128, 302, 402) to be executed concurrently with the data read command (126, 308, 408), and wherein execution of the metadata modification command (128, 302, 402) causes the metadata object stored in a metadata store (114) of the relational database system (100) to be modified; in response to receiving (506) the data read command (126, 308, 408): At time T1, a first transaction is initiated (512); Obtaining (514) a metadata access lock from a lock manager (124) of the relational database system (100); Creating (516) a first query plan for the first query, wherein the first query plan references the metadata object; Using the metadata access lock, beginning (518) execution of the first query plan within the first transaction at time T2; At time T3, completing (520) execution of the first query plan, and At time T4, committing (522) the first transaction, and -In response to receiving (508) the metadata modification command (128, 302, 402): at time T5, initiating (532) a second transaction concurrently with the first transaction; obtaining (534) a metadata change lock from the lock manager (124); Modifying (536) the metadata object within the second transaction using the metadata change lock, wherein the modification is not blocked by the metadata access lock, and At time T6, the second transaction is committed (538).
  2. 2. The relational database system of claim 1, wherein time T2 is after time T1, time T5 is after time T2, time T3 is after time T5, and time T6 is after time T3.
  3. 3. The relational database system of claim 2, wherein the metadata object comprises a first version of the metadata object prior to execution of the metadata modification command.
  4. 4. The relational database system of claim 3, wherein execution of the metadata modification command causes the first version of the metadata object to be modified to a second version of the metadata object.
  5. 5. The relational database system of claim 4, wherein the execution engine is configured to operate in a committed read snapshot isolation mode to: Executing the second query within the first transaction at a time prior to T6, thereby causing a second query plan to be compiled, wherein the second query plan references the first version of the metadata object, and At a time subsequent to time T6, the second query is executed again within the first transaction, causing the second query plan to be recompiled to generate a third query plan, wherein the third query plan references the second version of the metadata object.
  6. 6. The relational database system of claim 4, wherein the execution engine is configured to operate in a regular snapshot isolation mode to: Executing the second query within the first transaction at a time prior to time T6 or subsequent to time T6, causing a second query plan to be compiled, wherein the second query plan references the first version of the metadata object, and At a time subsequent to time T6, the second query is executed again within the first transaction, thereby reusing the second query plan, wherein the query plan references the first version of the metadata object.
  7. 7. The relational database system of claim 5, further comprising a transaction manager configured to determine which of the first version or the second version of the metadata object is visible to a transaction at a predetermined time.
  8. 8. The relational database system of claim 7, wherein the transaction manager is further configured to determine which of the first version or the second version of the metadata object is visible to a transaction at a predetermined time based at least in part on whether the execution engine is configured to operate in a first snapshot isolation mode, a second snapshot isolation mode, or a non-snapshot isolation mode.
  9. 9. A method (500) in a relational database system (100), the method comprising: -receiving (502) a data read command (126, 308, 408), the data read command (126, 308, 408) comprising a first query and a second query, each query referencing a metadata object in a metadata store (114) of the relational database system (100), and each query being configured to read a user data object from a user data store of the relational database system (100); -receiving (504) a metadata modification command (128, 302, 402) to be executed concurrently with the data read command (126, 308, 408), and wherein execution of the metadata modification command (128, 302, 402) causes the metadata object to be modified; in response to receiving (506) the data read command (126, 308, 408): At time T1, a first transaction is initiated (512); Obtaining (514) a metadata access lock from a lock manager (124) of the relational database system (100); Creating (516) a first query plan for the first query, wherein the first query plan references the metadata object; Using the metadata access lock, beginning (518) execution of the first query plan within the first transaction at time T2; At time T3, completing (520) execution of the first query plan, and At time T4, committing (522) the first transaction, and -In response to receiving (508) the metadata modification command (128, 302, 402): at time T5, initiating (532) a second transaction concurrently with the first transaction; obtaining (534) a metadata change lock from the lock manager (124); Modifying (536) the metadata object within the second transaction using the metadata change lock, wherein the modification is not blocked by the metadata access lock, and At time T6, the second transaction is committed (538).
  10. 10. The method of claim 9, wherein time T2 is after time T1, time T5 is after time T2, time T3 is after time T5, and time T6 is after time T3.
  11. 11. The method of claim 10, wherein the metadata object comprises a first version of the metadata object prior to executing the metadata modification command.
  12. 12. The method of claim 11, wherein executing the metadata modification command causes the first version of the metadata object to be modified to a second version of the metadata object.
  13. 13. The method of claim 12, further comprising running the relational database system in a first snapshot isolation mode and: Executing the second query within the first transaction at a time prior to T6, thereby causing a second query plan to be compiled, wherein the second query plan references the first version of the metadata object, and At a time subsequent to time T6, the second query is executed again within the first transaction, causing the second query plan to be recompiled to generate a third query plan, wherein the third query plan references the second version of the metadata object.
  14. 14. The method of claim 12, further comprising running the relational database system in a second snapshot isolation mode and executing the second query within the first transaction at a time prior to time T6 or after time T6, thereby causing a second query plan to be compiled, wherein the second query plan references the first version of the metadata object.
  15. 15. A computer program product comprising a computer readable memory device (720, 794), the computer readable memory device (720, 794) having computer program logic (714, 776, 796) recorded thereon, the computer program logic (714, 776, 796) when executed by at least one processor (710) of a computing device (702, 774, 792) causing the at least one processor (710) to perform operations comprising: -receiving (502) a data read command (126, 308, 408), the data read command (126, 308, 408) comprising a first query and a second query, each query referencing a metadata object of metadata objects in a metadata store (114) of the relational database system (100), and each query being configured to read a user data object from a user data store of the relational database system (100); -receiving (504) a metadata modification command (128, 302, 402) to be executed concurrently with the data read command (126, 308, 408), and wherein execution of the metadata modification command (128, 302, 402) causes the metadata object to be modified; in response to receiving (506) the data read command (126, 308, 408): At time T1, a first transaction is initiated (512); Obtaining (514) a metadata access lock from a lock manager (124) of the relational database system; Creating (516) a first query plan for the first query, wherein the first query plan references the metadata object; using the metadata access lock, beginning execution (518) of the first query plan within the first transaction at time T2; At time T3, completing (520) execution of the first query plan, and At time T4, committing (522) the first transaction, and -In response to receiving (508) the metadata modification command (128, 302, 402): at time T5, initiating (532) a second transaction concurrently with the first transaction; obtaining (534) a metadata change lock; Modifying (536) the metadata object within the second transaction using the metadata change lock, wherein the modification is not blocked by the metadata access lock, and At time T6, the second transaction is committed (538).
  16. 16. The computer program product of claim 15, wherein time T2 is after time T1, time T5 is after time T2, time T3 is after time T5, and time T6 is after time T3.
  17. 17. The computer program product of claim 16, wherein the metadata object comprises a first version of the metadata object prior to execution of the metadata modification command.
  18. 18. The computer program product of claim 17, wherein execution of the metadata modification command causes the first version of the metadata object to be modified to a second version of the metadata object.
  19. 19. The computer program product of claim 18, the operations further comprising running the relational database system in a first snapshot isolation mode and: Executing the second query within the first transaction at a time prior to T6, thereby causing a second query plan to be compiled, wherein the second query plan references the first version of the metadata object, and At a time subsequent to time T6, the second query is executed again within the first transaction, causing the second query plan to be recompiled to generate a third query plan, wherein the third query plan references the second version of the metadata object.
  20. 20. The computer program product of claim 18, the operations further comprising running the relational database system in the second snapshot isolation mode and executing the second query within the first transaction at a time prior to time T6 or after time T6, such that a second query plan is compiled, wherein the second query plan references the first version of the metadata object.

Description

Architecture snapshot isolated access in relational databases Background Relational database systems (e.g., microsoft SQL SERVER) typically require complete synchronization between read queries and data definition language operations ("DDLs") of a schema, because metadata is not versioned. Thus, all DDLs require a fully exclusive lock on all architecture objects being modified (i.e., an architecture modification lock denoted SCH-M) to keep pace with concurrent read queries that require access to any of these objects. This requirement imposes certain operational limitations. For example, the request SCH-M lock required for DDL operation cannot be granted until all queries depending on the architecture being modified have been completed, thus preventing DDL operation and thus causing an unavailable state for a period of time that greatly affects the execution performance of the workload. Also, while DDL operations wait for ongoing queries to complete, new queries submitted after requesting SCH-M locks are blocked, further extending the window of unavailable status. Further, the DDL operation invalidates any snapshot transactions that attempt to access the fabric object after the DDL operation commits, because the fabric associated with the transaction snapshot is no longer present after the DDL operation commits and therefore cannot be retrieved. This can cause unexpected failures in workload queries, and such queries must be re-tried, further affecting the performance of the workload. Also, the fact that DDL operations require waiting for all ongoing queries to complete before acquiring an architecture modification lock and continuing execution greatly affects the health of the auxiliary copy. The auxiliary copy is dependent on the log replication and such log replication must wait for a long time because it may be blocked by long running analytical queries (such queries are more common in the auxiliary copy). This may accumulate large logs on the primary copy, eventually exhausting log space. In addition, the replication of such large logs lengthens recovery time and when the secondary replica is restarted or when the database fails over to the secondary replica, the secondary replica is not available because all logs accumulated by the replica need to be reprocessed. Disclosure of Invention This summary is provided to introduce a selection of concepts in a simplified form that are further described below in the detailed description. This summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter. A relational database system is described herein that is capable of operating with versioned metadata. The relational database system includes a lock manager, a transaction manager, and version-aware metadata storage and caches configured to store and manage versions of metadata to determine which versions of such versions should be visible at any given point in time, and to support creation of correct versions of metadata. In an aspect, a transaction manager manages transaction identifiers and their associated start times, end times, and/or commit times. Such data supports determining transaction visibility at any point in time, and thus supports determining metadata version visibility. In an aspect, metadata versioning supports snapshot isolation of metadata transactions. Additional features and advantages, and structures and operation of various examples are described in detail below with reference to the accompanying drawings. Notably, the concepts and technologies are not limited to the specific examples described herein. Such examples are presented herein for illustrative purposes only. Additional examples will be apparent to those skilled in the relevant art(s) based on the teachings contained herein. Drawings The accompanying drawings, which are incorporated herein and form a part of the specification, illustrate embodiments of the present application and, together with the description, further serve to explain the principles of the embodiments and to enable a person skilled in the pertinent art to make and use the embodiments. FIG. 1 depicts an example relational database system providing versioned metadata, according to an example embodiment. FIG. 2 depicts an example logical employee table stored in an example SQL database according to an example embodiment. FIG. 3 depicts an example logical timeline of concurrent data read and metadata modification transactions using snapshot isolation in accordance with an example embodiment. FIG. 4 depicts an example logical timeline of concurrent data reads and metadata modification transactions using committed read snapshot isolation in accordance with an example embodiment. FIG. 5A depicts a flowchart of an example method of operating a relational database system to modify a metadata object without concurrent read access blocking by the metadat