Transaction-specific selective uncommitted read for database transactions

Techniques are described for use in database and data management systems to enable a database transaction to read uncommitted data from another database transaction on a selective (e.g., transaction-specific) basis, without requiring a change in the isolation level of either transaction (or related transactions). Accordingly, transaction speeds may be increased, and operations to audit or debug ongoing database transactions are also facilitated.

TECHNICAL FIELD

This description relates to database processing.

BACKGROUND

Databases are generally used to store large quantities of information, in an organized manner that enables fast and convenient searching and other database operations. Often, such large databases are accessed by multiple entities (e.g., users, or clients) during overlapping time frames. In order to maintain data consistency, lock management techniques are used to ensure that a given data entry may only be changed by one such entity at a time. Thus, such lock management techniques ensure correct, expected results from database changes (e.g., writes, deletions, modifications).

Meanwhile, the same database system may also be accessed for query or read operations. That is, for example, even while some users or operations are making database changes, other users may simply need to access the same database, in order to obtain desired information. Such read operations do not generally present a danger of causing data inconsistency within the database tables being read, since the read operations are not changing those database tables. However, other difficulties may be encountered, such as the danger that the read operations will receive incorrect or incomplete results. Such difficulties may be mitigated or controlled to a desired extent through the use of isolation levels, which generally define an extent to which a database transaction is isolated from reading changes made by other transactions.

Lock management techniques and isolation levels help to provide consistent, complete, accurate database operations, but consume computing resources, while also contributing to processing delays (including causing bottlenecks in the context of otherwise high-speed database transactions). Consequently, it is desirable to retain and/or optimize the associated benefits, while also conserving computing resources and increasing a speed with which correct database operations are conducted.

DETAILED DESCRIPTION

FIG. 1is a block diagram of a system for transaction-specific selective dirty read for database transactions. InFIG. 1, a database system102includes at least one processor104, as well as non-transitory computer-readable storage medium106. That is, the database system102should be understood to be implemented using suitable hardware processing resources, including two or more processors acting in parallel, as well as any suitable computer memory, or memories. For example, the database system102may be implemented as an in-memory or main memory database system, in which a main memory is used for database calculations and other operations, while a secondary memory (e.g., disk storage) is used for bulk, long-term, or backup data storage. One example of such an in-memory or main memory database system is the HANA database system of SAP SE of Walldorf Germany.

The database system102is illustrated as including a database108, which includes a database table110, which itself includes a record112. Of course,FIG. 1is a highly simplified example that is provided for the sake of illustration and explanation, and, in practice, the database system102may include extremely large numbers of databases, tables, and records. Moreover, the database system102may be implemented in many different ways, such as row store or column store implementations, or combinations thereof, which are not described here in detail, but would be apparent to one of skill in the art.

Further inFIG. 1, the record112is illustrated as including a record value114, which is stored within the record112in association with transaction information116. In this regard, a database transaction, or just transaction, refers generally to a logical unit of work, e.g., sequence or flow of operations, applied against a database, such as the database110. Transactions may be implemented to cause desired changes to the database108, such as writing or deleting the record value114, or may be implemented to query or read existing data (such as the record value114).

Transactions may be executed independently of one another, or may be dependent, such as when a transaction requires the input of an earlier transaction in order to proceed. Each transaction is ended by a transaction commit (also referred to as commitment), which finalizes or completes the transaction, and saves the resulting data changes indefinitely within the database (e.g., until changed by another operation).

In other words, during an ongoing execution of a transaction, related data and data changes may be considered tentative or potential. For example, even if multiple operations of a transaction have already been completed, a current operation that reaches a nonviable or error state may cause the entire transaction to experience rollback, in which all of the previously-executed operations are undone and the database is returned to a previous state that existed prior to commencement of the transaction.

Put another way, a single transaction may include multiple internal operations, executed over a period of time. For example, a transaction may include multiple operations, O1, O2, O3, and O4executed at times t1, t2, t3, and t4. Following the time t1at which the transaction begins with the operation O1, until the time t4after which the operation O4is completed, the transaction is considered to be open or uncommitted.

Thus, one way to help ensure data consistency and accuracy is to wait for a transaction to commit before reading a transaction result. There are existing isolation levels that take this or a similar type of approach (e.g., the serializable or snapshot isolation levels). However, this approach requires waiting until a time following time t4in the above example. In the aggregate, e.g., across many different operations and transactions, the delays (and associated resource usage) resulting from this approach may be undesirable or unacceptable.

Another option is to read the data prior to commitment occurring, which may be referred to as an uncommitted read, or dirty read. For example, data could be read following completion of the operation O2at time t2. This approach is potentially problematic because, for example, results of the operation O2may change during the remainder of the transaction. For example, the operation O2may be rolled back (undone), or the operation O3or O4may further change the results of O2. More generally, results of a query may be inconsistent with other parts of relevant table(s), or inconsistent with other parts of the query itself, or inconsistent with other queries.

Some scenarios exist in which uncommitted (dirty) reads are permitted. In particular, as referenced above, it is possible to assign various different isolation levels to database transactions. In some existing database systems, at least one such isolation level permits uncommitted reads, and may be selected or permitted with respect to one or more transactions. For example, such an isolation level may be appropriate when it is acceptable to treat query results as estimates, or any time that a defined threshold of error is acceptable in order to obtain results faster.

Such existing uses and implementations are limited to particular scenarios, while, even there, being potentially problematic and prone to unintended or unpredictable consequences. For example, a transaction or group of transactions granted an uncommitted read isolation level may attempt to read results from a large number of transactions or transaction operations. Therefore, it may be difficult to account for potential errors that may occur, particularly in the aggregate.

The system100ofFIG. 1provides for transaction-specific, selective dirty read operations, in which individual transactions are specified for uncommitted reads with respect to one another. In other words, for example, a conventional transaction that is designated with a conventional uncommitted read isolation level typically is permitted to perform uncommitted reads with respect to any available, target transaction, and/or is open to having its uncommitted data read by any requesting transaction.

In contrast, as described in detail below, a transaction manager118ofFIG. 1is configured to permit and enable a single transaction, referred to as a snooping transaction, to perform uncommitted reads with respect to a single target transaction (or specified group of individual, target transactions). This approach may be enabled independently or separately from an existing isolation level of either the snooping or target transaction. In other words, for example, either the snooping and/or target transaction may have a restrictive isolation level(s), but the approaches described herein provide a selective, transaction-specific relaxation of those restrictions or constraints, without changing the isolation level of the transaction(s) as a whole.

Further, the system100ofFIG. 1thus enables obtaining many of the benefits of performing uncommitted reads (e.g., faster processing), while limiting or eliminating potential risks. For example, by permitting uncommitted reads selectively, it becomes possible to examine a specific target transaction(s) (e.g., internal operations thereof), and take corresponding action to reduce or eliminate risks associated with performing uncommitted reads (examples are provided below, e.g., with respect toFIGS. 16-18).

InFIG. 1, a transaction manager118may interact with a session manager120to provide the above-referenced features and benefits to a client122executing a software application124designed to access the database108. Of course, the database system102should be understood to include many other components besides the transaction manager118and the session manager120(e.g., a component for performing details of query executions), which are not described here for the sake of clarity and conciseness. Similarly, the transaction manager118and the session manager120themselves may include various features that are not described here in detail, except as may be necessary or helpful in understanding the operations of the system100ofFIG. 1.

In general, any query received from the client122may be received by the session layer120with respect to a particular instance of the database108. The session layer120may be configured, for example, to compile the received query, formulate an execution plan for the query within the database instance, and ultimately provide a query response, if any, to the client122.

Meanwhile, the transaction manager118is generally configured to implement logistical aspects of interactions with the database108, and, in particular, with individual database table110. In more detail, the transaction manager118may be configured to track running and closed transactions, including specific transaction events, such as transaction commits or rollback operations. The transaction manager118may also be configured to inform the database110with respect to such transaction events, so that the database110may execute any associated actions.

Thus, the system100generally represents and illustrates simplified scenarios in which various clients, represented by the client122, are configured to issue a plurality of queries and associated database commands to a single/same instance of the database system102. The various queries and commands are received at the session layer120, which, as referenced, may compile or otherwise prepare the various received queries and commands for use by the transaction manager118.

Following successful completion of a requested database operation, a control flow to execute a transaction commit may be executed. For example, the client122may send a commit command to the database instance, and the session layer120may again interpret or compile the received command. In this case, the received command is a transaction control command, and not, e.g., an SQL statement. Accordingly, the transaction manager118may proceed to commit the previously-started transaction.

As referenced above, inFIG. 1, the record value114of the record112is stored in conjunction with transaction information116. Detailed examples and explanations of the transaction information116are provided below, e.g., with respect toFIG. 3. In general, the transaction information116is defined, stored, and accessed by the transaction manager118and the session manager120in a manner that enables the types of transaction-specific, selective uncommitted reads described herein.

In more detail, as shown, the session manager120may define a session126with respect to the application124, and at least two transactions thereof. As already referenced above, the at least two transactions may be referred to as a snooping transaction and a target transaction, where the snooping transaction is configured to perform uncommitted reads with respect to the target transaction. InFIG. 1, it should be appreciated that the session126represents at least one session, but possible two or more sessions. For example, the target transaction object128and the snooping transaction object130may belong to different sessions.

In the system100ofFIG. 1, each database transaction is associated with, e.g., defined with respect to, a corresponding transaction object (detailed examples and explanations of which are also provided below, e.g., with respect toFIG. 3). Thus, the session126includes a target transaction object128and a snooping transaction object132.

Further, the application124is illustrated as including a snoop request handler132, which receives a request in the context of the application124for a specified uncommitted read operation(s). In particular, the transaction manager118includes a snoop manager134, which may represent or include an application program interface (API) that is callable by the snoop request handler132.

The snoop manager134leverages and controls operations of other components of the transaction manager118to provide the various features and benefits described herein. In particular, a transaction object manager136is configured to create, update, maintain, and generally allocate/deallocate transaction objects, such as the target transaction object128and the snooping transaction object130. In so doing, the transaction object manager136may utilize a transaction identifier (ID) generator138, as well as a timestamp generator140, as described below.

In particular,FIG. 2is a flowchart200illustrating example operations of the system100ofFIG. 1. In the example ofFIG. 2, operations202-210are illustrated as separate, sequential operations. In various implementations, additional or alternative operations or sub-operations may be included, and/or one or more operation or sub-operation may be omitted. In these and other implementations, it may occur that any two or more operations or sub-operations occur in a partially or completely overlapping or parallel manner, or in a nested, iterative, branched, or looped fashion.

In the example ofFIG. 2, a snoop request is received from a software application executing transactions against a database, the snoop request specifying a target transaction and a snooping transaction of the transactions, the snooping transaction being identified for read access for at least one uncommitted record from the target transaction (202). For example, as just described, such a snoop request may be received from the software application124, via the snoop request handler132. As referenced, the snoop request may specify a target transaction corresponding to the target transaction object128, as well as a snooping transaction corresponding to the snooping transaction object130. The snooping transaction of the snooping transaction object130is thus identified for read access for at least one uncommitted record from the target transaction.

As also referenced, the snoop manager134of the transaction manager118may be configured to receive the snoop request from the snoop request handler132. Further, in many of the following examples, the record112represents the type of uncommitted record associated with the target transaction, and with the target transaction object128. Consequently, the record value114represents an example of data that may be uncommitted within the database table110, but that may nonetheless be read by the snooping transaction, as described herein.

Specifically, a target transaction identifier for the target transaction may be stored within a snooping transaction object of the snooping transaction (204). For example, the snoop manager134may cause the transaction object manager136to store such a target transaction identifier within the snooping transaction object130. In other words, as described below, the target transaction identifier may be included in a listing or set of visible target transaction(s) that are readable by that snooping transaction, even when the target transaction(s) is uncommitted.

Transaction information may be stored in conjunction with the at least one uncommitted record and specifying the target transaction identifier (206). For example, the snoop manager134may cause the transaction information116to be updated with a pointer to the target transaction object128.

A read request may be received from the snooping transaction that specifies the at least one uncommitted record (208). For example, the snooping transaction requested by the application124may request to read the record value114, representing the uncommitted record value associated with the transaction information116within the record112.

The read access to the at least one uncommitted record may be granted, based on the target transaction identifier being specified in both the snooping transaction object and in the transaction information (210). For example, the snoop manager134may grant the read request of the snooping transaction of the snooping transaction object130for the record value114, based on the fact that the record value114is stored in association with the transaction information116, and where the transaction information116specifies (e.g., points to, or otherwise identifies) the target transaction object128.

FIG. 3is a block diagram illustrating example formats for a transaction object302and transaction information304that may be used in the system100ofFIG. 1. In other words, for example, the transaction object302corresponds to either of the target transaction object128or the snooping transaction object130, while the transaction information304represents the transaction information116ofFIG. 1.

As shown, in the transaction object302, a field306includes an identifier of a corresponding transaction. In the simplified example ofFIG. 3, the identifier (ID) is given a value of 1. In general, a transaction identifier (transaction ID, or TID) generally is selected to uniquely identify a specific transaction within the appropriate, corresponding context of the database system102.

A field308includes a timestamp value, illustrated in the example ofFIG. 3as having a value of 11. That is, the timestamp field is populated with a value provided by the timestamp generator140of the transaction manager118, and identifies a time at which the corresponding transaction is permitted to see a corresponding database state. That is, in the example, the transaction object302would be permitted to see a state of another transaction object and associated transaction, as long as that transaction has a timestamp of 11 or less.

The transaction object302also includes a field310specifying a target transaction identifier set. In other words, the transaction object302utilizes the field310to identify one or more target transactions that will be visible to the transaction object for purposes of the type of uncommitted reads described herein. In the example ofFIG. 3, the field310includes a transaction ID having the value 2, meaning that the transaction corresponding to the transaction object302can perform uncommitted reads with respect to the data associated with transaction ID2.

With respect to the field310, it will be appreciated that the transaction object302may represent either or both of the target transaction object128and the snooping transaction object130, in various scenarios. That is, for example, the transaction object302may represent a target transaction object that is being snooped by the first snooping transaction object, while itself serving as a snooping transaction object with respect to a separate (target) transaction object. In other words, for example, the transaction object302may be configured to snoop transaction data of a transaction specified within the field310, while itself being snooped by a separate transaction, as well.

A field312illustrates a write set that identifies records within a specific table that have been updated by the transaction of the transaction object302. In general, the write set of the field312and specified data records will be associated with appropriate, corresponding database locks designed to ensure consistency with respect to write operations of other transactions.

A field314represents an object reference count that defines a number of entities referencing the transaction object302. In the example ofFIG. 3, the illustrated object reference count is set to a value of 1, meaning that, for example, only a single transaction has a pointer to the transaction object302. As referenced below, the object reference count of the field314may represent an explicit, stored value for a number of referencing entities, and/or may be stored implicitly by storing a separate identifier for each referencing entity.

Finally with respect to the transaction object302, a field316specifies a timestamp (TS) at which a transaction commit of the transaction corresponding to the transaction object302occurs. In the example ofFIG. 3, the transaction commit has not yet occurred, so that the value of the field316is set to max.

Further inFIG. 3, the transaction information304includes a field318and a field320. As shown, the field318may represent metadata that characterizes a nature of content of the field320.

Specifically, in the example ofFIG. 3, the field318is implemented using a single bit, which may therefore be set either to a value of 0 or 1. In the example, if set to 1, the field318identifies that the content of the field320is a 63-bit transaction pointer, which would thus point to the transaction object302and cause the object reference count of the field314to be incremented. On the other hand, if the field318is not set, e.g., has a value of 0, then the field320is indicated to include a timestamp. As described below, for example, the timestamp of the field320may represent a commit timestamp of a corresponding transaction.

FIG. 4is a flowchart400illustrating an example database usage scenario utilizing the system100ofFIG. 1. In the example ofFIG. 4, a transaction TX1represents a target transaction, such as may be associated with the target transaction object128, while a transaction TX2represents a snooping transaction designated for snooping the target transaction TX1, and corresponding to the snooping transaction object130.

FIG. 4provides a high-level overview of the described scenario, in order to provide a start-to-finish description thereof. SubsequentFIGS. 5-14each provide more detail with respect to at least one of operations402-424ofFIG. 4.

In the example ofFIG. 4, the transaction TX1starts (402). In the example, the transaction TX1is illustrated as having a repeatable read isolation level, which is an isolation level that provides, in addition to read guarantees provided by the read-committed isolation level, that any data read is guaranteed to be the same if/when read again by the same transaction(s). In other words, as referenced above,FIG. 4illustrates that the transaction TX1may be associated with a particular isolation level, and that the uncommitted read operations described herein may be executed in the context of, and without changing, that isolation level.

In the simplified example, the transaction TX1proceeds to insert values into a specified database table (404). In the example, the table is represented as t (which may represent, e.g., the table110ofFIG. 1), and the inserted values are represented as (0).

The transaction TX2then starts (406). Again, the transaction TX2is illustrated as having a repeatable read isolation level, thereby illustrating that the following operations ofFIG. 4may be executed without regard for, and without changing, the isolation level of the transaction TX2.

TX2may then begin to snoop TX1(408). Specifically, in the example, even though the transaction TX1is uncommitted, and even though the transaction TX1has the repeatable read isolation level, the transaction TX2may perform a select or other read operation from relevant records of the table t (410). As a result, the transaction TX2may receive a result corresponding to the previous insert operation of the transaction TX1, therefore represented inFIG. 4as a result {0}.

FIG. 4illustrates that a database state for the table t indicates that no transaction commit for the transaction TX1has occurred (412). The transaction TX1may then insert additional values into the table t, illustrated inFIG. 4as inserting values (1) (414). Following this insertion, the transaction TX1may proceed to commit (416). Consequently, the database state of the table t is indicated to be {(0), (1)}, indicating that previously-inserted records (0) and (1) have now been committed (418).

Further inFIG. 4, it may occur that the transaction TX2executes a select or other read command with respect to the table t (420). The corresponding result set is illustrated as {0, 1} because the transaction TX2is permitted to read the committed changes of the transaction TX1. As described in detail below with respect toFIGS. 11 and 12, this result would not generally be feasible in conventional systems, because the transaction TX2began after commencement of the transaction TX1, and both transactions TX1, TX2are illustrated as having the repeatable read isolation level. In such scenarios, the transaction TX2would normally only be able to read a state of the table t corresponding to a time (and associated timestamp) prior to the beginning of the transaction TX1, in order to ensure repeatability.

Continuing the example ofFIG. 4, the transaction TX2commits, resulting in a deallocation of a corresponding transaction object (422). The transaction object for the transaction TX1may then be deallocated, as well (424). That is, as may be observed from the process flow ofFIG. 4, and as described in detail below with respect toFIGS. 13 and 14, the transaction object for the transaction TX1is kept alive (e.g., not deallocated), until following a commit and deallocation performed with respect to the (snooping) transaction TX2and its corresponding transaction object. Accordingly, the transaction TX2may continue to have the type of transaction-specific, selective uncommitted read permissions described herein with respect to the transaction TX1, until the transaction TX2itself has fully completed.

As referenced above,FIGS. 5-14are block diagrams illustrating operations of corresponding portions of the system100ofFIG. 1, as those portions execute various, corresponding operations402-424ofFIG. 4.

FIG. 5is a block diagram illustrating example operations of the transaction manager118in implementing the first operation402ofFIG. 4. That is, as illustrated, as the transaction TX1begins, the transaction ID generator138generates a new transaction ID, illustrated as transaction ID1506. As the transaction TX1is in the repeatable read isolation mode, the timestamp generator140generates a snapshot timestamp for the transaction, illustrated inFIG. 5as having a timestamp value of 11508.

A corresponding transaction object502is generated by the transaction object manager136. Thus, the transaction object502ofFIG. 5represents, and corresponds to, the target transaction object128ofFIG. 1. In conjunction therewith, the transaction object manager136may generate a pointer504that points to the transaction TX1. As described above and illustrated in detail below with respect toFIG. 6, the pointer504may thus be included within the transaction information of each record that is inserted by virtue of operations of the transaction TX1. Further, specific content of the transaction object502may be understood with reference to the transaction object302ofFIG. 3.

As shown, the transaction object502has a transaction identifier with the value 1, the generated timestamp value 11, and a commit timestamp value of max. The target transaction ID set is empty, because the transaction TX1is not snooping any other transactions. The write set field is also empty, because the transaction TX1has not yet executed any write operations. Further, the object reference count field is set to 1, because the corresponding session of the transaction TX1owns the transaction object502, and therefore itself references the transaction object502.

FIG. 6is a block diagram illustrating implementation of the second operation404ofFIG. 4. Specifically,FIG. 6illustrates implementation of insertion of values (0) into the tablet as part of operation of the transaction TX1.

Thus, inFIG. 6, a transaction object602corresponds to the transaction object502ofFIG. 5, but with updates corresponding to the described insertion operation. Specifically, as shown, the write set field is now set to a value of {T:0}.

Further inFIG. 6, a new record604with the example value (0)606is inserted into the tablet, illustrated in the example as the table110ofFIG. 1. Further, transaction information608for the new record604is set and updated to include a pointer to the transaction object602. It will be appreciated that, as of the time of implementation of the operation404as illustrated inFIG. 6, the record604and the record value606are invisible to all other transactions, because of the repeatable read isolation level of the transaction TX1.

FIG. 7is a block diagram illustrating an implementation of the third operation406ofFIG. 4, in which the transaction TX2begins. As shown, the transaction object manager136generates an additional pointer704that points to a transaction object702that corresponds to the transaction TX2.

Thus, the transaction object702ofFIG. 7corresponds to, and represents, the snooping transaction object130ofFIG. 1, and the transaction TX2represents an example of a corresponding snooping transaction with respect to the target transaction TX1. Consequently, the transaction object702includes the transaction ID2, generated by the transaction ID generator138as transaction ID706.

The timestamp generator140generates a snapshot timestamp for the transaction TX2, where the timestamp708is illustrated as having the value 11. Because snooping has not yet begun, the target transaction ID set of the transaction object702is empty. Similarly, the write set field is empty, because the transaction TX2is not requesting write operations with respect to any other transaction. The object reference count field of the transaction object702is set to 1, reflecting the existence of the corresponding transaction TX2and its pointer704. Finally inFIG. 7, the commit timestamp is set to max, because the transaction TX2is currently uncommitted.

FIG. 8is a block diagram illustrating the fourth operation408ofFIG. 4, in which the transaction TX2initiates snooping of the transaction TX1. Specifically, as referenced with respect toFIG. 1, the application124may be configured to call an API of the snoop manager134, which may be represented as: snoopTransaction(TX, target TX).

In other words, for example, the user of the application124may designate both the snooping transaction and the target transaction, using the API of the snoop manager134. For example, the user of the application124may be a developer of the database system102. Additionally, or alternatively, developers of the database system102may hard-code appropriate code portions for calling the referenced API, as illustrated and described above with respect to the snoop request handler132ofFIG. 1. Specific example operations and implementations of the application124are provided below, e.g., with respect toFIGS. 15-18.

Thus, inFIG. 8, a transaction object802corresponds to the transaction objects502,602ofFIGS. 5 and 6, and thus represents an updated version of the transaction object for the target transaction TX1. Meanwhile the transaction object804represents an updated version of the transaction object702ofFIG. 7, and represents the transaction object for the snooping transaction TX2.

InFIG. 8, the transaction ID for the transaction TX1is added to the target transaction ID set that indicates visible transactions for the transaction TX2, illustrated as the value {1}. Meanwhile, for the transaction object802, the object reference count is incremented by 1, since the transaction object802is now referenced by the transaction TX2, so that a value of the object reference count of the transaction object802is set to a value of 2.

FIG. 9is a block diagram illustrating implementation of a fifth operation410ofFIG. 4. As described with respect toFIG. 4, the transaction TX2executes a select command to read from the record604within the table110. As described, the table110includes an uncommitted record604that would typically be invisible to other transactions.

Nevertheless, inFIG. 9, the transaction object804for the snooping transaction TX2has a value of {1} within its target transaction ID set, and therefore proceeds to inspect the transaction information608for the requested record. Specifically, the snoop manager134may determine from the transaction information608that the transaction information is set to include a pointer to the corresponding transaction object802for the transaction TX1. Thus, by matching the transaction ID value stored within the target transaction ID set of the snooping transaction object804with the transaction ID stored within the transaction ID field of the target transaction object802, to which the transaction information608points, the snoop manager134may determine that the snooping transaction TX2is permitted to read the record value606, even though the record value606is not associated with (a transaction having) a read uncommitted isolation level.

FIG. 10is a block diagram illustrating implementation of the seventh operation414ofFIG. 4. Specifically, as already described with respect toFIG. 4, the transaction TX1may proceed to insert additional values (1) into the table110. As shown inFIG. 10, a transaction object1002represents a corresponding, updated version of the target transaction object802ofFIGS. 8 and 9, and thus has a value of 1 within the transaction ID field.

InFIG. 10, the transaction TX1creates a new record1004within the table110. As shown, the new record1004includes a record value1006having an example value of (1), along with transaction information1008. The transaction information1008is illustrated as being set to include a pointer to the transaction object1002. Further, the write set field of the transaction object1002is updated to include the value T:1, representing the insert operation described with respect toFIG. 10.

FIG. 11is a block diagram illustrating the example implementation of the eighth operation416ofFIG. 4. Accordingly,FIG. 11illustrates a commit of the transaction TX1.

A transaction object1102corresponds to an updated version of the transaction object1002ofFIG. 10, in which the commit timestamp field has been updated to a value of 12, as generated by the timestamp generator140and assigned by the transaction object manager136. Further, compared to the transaction object1002ofFIG. 10, it may be observed that the object reference count field of the transaction object1102is decremented from a value of 2 to a value of 1, reflecting that the transaction TX1has committed, and therefore no longer references the transaction object1102.

In other words, it may be observed that the transaction object1102for the transaction TX1is maintained, even after the commit of the transaction TX1, so that the snooping transaction TX2may continue to execute read uncommitted access operations, as long as the snooping transaction TX2itself remains alive (i.e., has not yet committed).

Specifically, as shown, the transaction object1102for the target transaction TX1is kept alive in conjunction with maintaining a positive value within the object reference count field, reflecting that the transaction object manager136maintains the previously-defined pointer504identifying the transaction TX1. Further in conjunction with the commit of the transaction TX1, the transaction object manager136may be configured to take ownership of the transaction object1102from the corresponding session previously maintained by the session manager120.

FIG. 12is a block diagram illustrating an example implementation of the tenth operation420ofFIG. 4. In the operation420, the snooping transaction TX2executes another select operation against the table110. As shown and described, the table110, at this time, includes the two committed records604,1004, both of which would typically be invisible to transactions having timestamps lower than the commit timestamp12of the transaction TX1.

As shown inFIG. 12, a transaction object1202illustrates an updated version of the transaction object1102ofFIG. 11, reflecting the just-described commit of the transaction TX1. Specifically, because the snooping transaction object804continues to include the transaction ID with a value of 1 within the target transaction ID set field, the object reference count field of the transaction object1202remains set to 1, notwithstanding the already-executed transaction commit of the transaction TX1. Consequently, the transaction information608of the record604and the transaction information1008of the record1004continue to point to the transaction object1202, and convey that the snooping transaction TX2continues to have uncommitted read permission with respect to the record values606,1006.

FIG. 13is a block diagram illustrating an example implementation of the eleventh operation422ofFIG. 4, in which the transaction TX2commits, and the corresponding transaction object1202is deallocated. As shown inFIG. 13, a transaction object1302corresponds to the transaction object1202ofFIG. 12, but has the object reference count field decremented from a value of 1 to a value of 0, reflecting the deallocation of the transaction object804.

FIG. 14is a block diagram illustrating an example implementation of the twelfth operation424ofFIG. 4, in which a transaction object for the target transaction TX1is deallocated. In other words, as just referenced with respect to the transaction object for the snooping transaction TX2, a decrement of the object reference count field from a value of 1 to a value of 0 causes object deallocation by the transaction object manager136. The transaction object manager136follows the write set element for the transaction TX1, and replaces the pointers within the relevant transaction information fields with corresponding commit timestamps.

That is, as shown inFIG. 14, transaction information1402is updated (using the write set from the transaction object1302ofFIG. 13), to thereby remove the previously-included pointer, to change the value of the first bit from set to unset, and to update the value of the remaining 63 bits to include and reflect a timestamp value of 12. Similar comments apply to transaction information1404, while the transaction object manager136is illustrated as including an empty field1406, reflecting destruction of the previously-included pointers.

FIGS. 15-18illustrate and describe an example scenario in which the application124ofFIG. 1represents an application for workload capture and replay. For example, a particular software application may utilize a first version of a database system, and may be configured accordingly. If a second, newer version of the database system is released, then the software application may not be fully compatible with the second version. In order to test compatibility, it is possible to capture changes that occur during interactions between the software application and the first version to obtain a first result (i.e., capture a workload), and then replay those changes with the second version to obtain a second result. In this way, the first and second results may be compared, in order to assess and correct any potential compatibility issues.

The preceding scenario is merely one example of many different types of situations in which it may be desirable to allow transaction-specific, selective uncommitted read operations (e.g., debugging, or auditing), but is used with respect toFIGS. 15-18for the sake of illustration and example.

FIG. 15is a block diagram of a dependency graph illustrating transactions used in a workload capture and replay scenario. In the example ofFIG. 15, example transaction TX1of node1502inserts values (0) into a table t1, inserts values (1) into table t1, inserts values (0) into table t2, and inserts values (1) into table t2. Then, in a node1504, a transaction TX2selects sum(c1) from table t1, and updates into t1set c1=c1+1.

In short,FIG. 15is intended to provide a simplified example in which TX1updates t1and t2, and TX2updates and reads t1, and TX2begins after TX1commits. In such a scenario, it will be appreciated that if TX2starts prior to a commit of TX1, the select statement of TX2may return an incorrect result set, and the update statement of TX2may not update desired records (i.e., will result in an inconsistent database state).

In the example, it is assumed that the application124(workload capture and reply application) includes a capture data preprocessor that is configured to analyze transactions of a given workload to be captured/replayed, and generate the type of dependency graph illustrated inFIG. 15. Then,FIG. 16may be understood to represent example results of such capture data preprocessing, in which it is determined that TX2may start (in replay mode) as soon as TX1has completed updating t1, including performing uncommitted read operations, because TX2actually requires only partial completion of TX1in order to have all necessary information to begin and obtain correct, consistent results.

Specifically,FIG. 16is a flowchart illustrating example operations for injecting snooping operations into the transactions ofFIG. 15. InFIG. 16, TX1starts (1602), and inserts values (1) into table t1(1604), and inserts values (1) into table t1(1606).

At this point, the transaction TX2begins and snoops transaction TX1(1608), as described above with respect toFIGS. 1-14. Specifically, the transaction TX2performs an uncommitted read operation of the values (0) and (1) from the table t1, even though the transaction TX1has not yet committed.

FIG. 16continues with TX1inserting values (1) into table t2(1610), and values (1) into table t2(1612), followed by TX1committing (1614). At this point, TX2may proceed to select sum(c1) from t1(1616) and to update t1set c=c1+1 (1618), before TX2then commits (1620).

FIG. 17is a block diagram of an optimized dependency graph obtained from the dependency graph ofFIG. 15, using the techniques described with respect toFIG. 16. As shown, a node1702and a node1704represent the original TX1, but split into separate transaction steps for purposes of the optimized dependency graph ofFIG. 17. As shown, the nodes1702,1704illustrate that the transaction TX2, represented by a node1706, has no dependencies on the portion of the transaction TX1of the node1704.

FIG. 18is a timing diagram of a workload capture and replay scenario using the optimized dependency graph ofFIG. 17. As shown, multiple replay threads execute the replay operations of the captured workloads. InFIG. 18, an operation1802begins at time4, which would otherwise have been required to wait until time7to begin.

Thus,FIGS. 15-18illustrate that the system100ofFIG. 1may be configured to analyze a plurality of operations of a target transaction, and then identify a snooping injection point within the plurality of operations. The snooping injection point may be defined as a point within the plurality of operations, prior to which permitting the read access would result in an inconsistent database state, as just described. Then, the snooping transaction may be initiated at the snooping injection point.

FIG. 19illustrates example pseudocode of an additional example implementation of the system100ofFIG. 1. InFIG. 19, the result of the INSERT statement at line2would normally be visible to the SELECT statement at line3, without being visible to the SELECT statement at line5(since the SELECT statement at line5belongs to a different transaction, as described herein, and assuming at least a read-committed isolation level). However, inFIG. 19, lines4and6illustrate that a transaction ID tid is obtained (1902), and used to enable the types of snooping transactions described herein (1904).

FIG. 20is a block diagram illustrating another example implementation of the system100ofFIG. 1. InFIG. 20, each uncommitted record version has its creator transaction ID (TID). To decide the visibility of an uncommitted version, the target transaction ID set may be checked for matching with the target version's creator-TID value. If there is a match, the reading transaction is allowed to read the uncommitted record version.

Thus, by way of notation, V1and V3are uncommitted record versions which are created by transactions TX1(tid1) and TX2(tid2), respectively. V2is an already committed version. For an uncommitted version, TID15associated. For a committed version, commit ID (CID) is assigned.

Then, as shown by the arrows2024-2032, transaction TX12018can read from versions2006,2010(arrows2024,2026), while the transaction TX2can read from version2014(arrow2028). Transaction TX32022has a target transaction ID set of {tid1, tid2}, and can therefore read from versions2006,2014, even though those versions are not committed prior to the commit id (cid=3) of the snooping transaction TX32022.

In other words, V1is visible to TX1because TX1is the creator of V1. But, V1is also visible to TX3, because TX3is snooping tid1. V2is visible only to TX1because V2is a committed version and TX1has higher snapshot timestamp (cid6) than the V2's creation timestamp (cid4). TX2and TX3cannot read V2because cid4is higher than their snapshot timestamps (cid2, cid3). V3is visible to TX2(creator transaction) and TX3(snooping transaction).

Various additional aspects may be required. For example, if the target transaction has an ‘abort’ status, the snooping transaction may be prevented from snooping. If a snooping transaction already began snooping prior to the abort status being initiated, then garbage collection procedures for the aborted transaction may be modified to ensure that garbage collection is implemented as if the aborted transaction had been committed.

To implement the type of interface(s) described herein, it is possible to expose a system function such as SnoopUncommitedChanges( ), as described, e.g., with respect toFIG. 19, which identifies a member of the current transaction object, and obtains arguments of the target transaction ID. In other implementations, a more general SQL command may be exposed, such as SET TRANSACTION SNOOP UNCOMMITTED CHANGES <transaction ID>. These and other interface approaches may be provided for internal developers, and/or to other users.