A Simple and Fast Way to Handle Semantic Errors in Transactions

For decades, system design has been centered around humans as the primary users and operators. In database management systems (DBMS), transactions are usually initiated and committed by human users. While syntactic errors can be easily detected and corrected through standard error handling, users may also introduce semantic errors or need additional time to decide whether to commit a transaction. To manage these challenges, researchers and engineers have developed ”undo” mechanisms, ensuring both data integrity and system recoverability.

The ARIES protocol (Algorithm for Recovery and Isolation Exploiting Semantics) has long been the gold standard for recovery, combining write-ahead logging, undo, and redo operations to efficiently manage fine-grained rollbacks (Mohan et al., 1992). However, managing semantic errors requires transactions to remain long-lived for user review, making ARIES potentially too storage-intensive for such use cases. Beyond ARIES, alternative approaches such as sagas have been developed for long-running transactions in distributed systems, enabling partial rollbacks through compensating transactions while maintaining system consistency (Garcia-Molina and Salem, 1987). ACTA is derivative work for sagas, and it extends sagas to include extended transactions while still providing compensating transactions and maintaining consistency(Chrysanthis and Ramamritham, 1992). Similarly, advancements in multi-version concurrency control (MVCC) allow systems to maintain historical states, enabling rollbacks to previous valid database states (Bernstein et al., 1987). Escrow methods, often utilized in high-concurrency environments, only allowed conditional updates and guarantees to facilitate safe rollbacks without locking resources (O’Neil, 1986). These mechanisms either allow ”undo” or give users a chance to store long-lived transactions and review the transactions to fix semantic errors.

Large Language Models (LLMs) are increasingly being seen as active operators within systems, either by translating human commands for system interaction or functioning autonomously to maintain the system based on predefined prompts. LLMs have demonstrated their ability to understand system structures and effectively query data (Zhang et al., 2024; Madden et al., 2024; Fernandez et al., 2023; Chen et al., 2024). While these actions typically do not modify the original database state and instead provide more accessible methods for data extraction, more complex interactions involve ”write” actions that alter the database state (Patil et al., 2024; Subramaniam and Krishnan, 2024; Patil et al., 2023; Wornow et al., 2024; Zhang et al., 2024). These write actions can introduce errors, which are often manageable through validation, as well as semantic errors, which may require user review for correction (Zhang et al., 2024; Xu et al., 2024; Patil et al., 2023).

An intuitive solution is to improve the prediction accuracy and reduce semantic errors of LLMs in adhering to user instructions and expectations. However, due to inherent limitations in LLMs, achieving perfect performance is unrealistic. Thus, we must treat incorrect transactions as errors and remove them when necessary. Existing approaches like ARIES, sagas, escrow, and MVCC provide partial solutions but fall short of addressing the challenges posed by long-lived transactions, especially those generated by LLMs. ARIES, for instance, does not support long-lived transactions. Sagas undo transactions by predefined independent compensating transactions, which cannot handle inter-dependent transactions that violate system constraints. Sagas also requires system implementation case by case. ACTA briefly explains how to handle transactions that cannot be undone. However, it does not discuss inter-dependent transactions, which may violate database constraints based on the other transaction’s state, not just an independent transaction itself. Escrow focuses on counter-type data, limiting its applicability. MVCC does not support selective removal of specific transactions. Approaches like rewriting history rely on commutativity checks to remove incorrect transactions and transactions affected by incorrect transactions, but commutativity imposes overly strict constraints (Liu et al., 2000). A more practical approach would relax this standard by enforcing database constraints rather than insisting on identical database states. Thus, we adapted and extended Invariant Confluence to check whether LLM-generated transactions require review and may need coordination with newer transactions. (Bailis et al., 2014). Coordinations stall certain newer transactions to guarantee that the undoing of long-lived transactions always leads to a consistent database state. We also reviewed additional papers on long-lived transactions; see the details in the discussion section.

In this paper, Section 2 outlines the system settings and requirements. Sections 3 and 4 summarize the processes for identifying transactions that may require coordination for the consistency of long-lived transactions waiting for reviewing. Sections 5 and 6 explain the roles of transaction managers and middleware in supporting coordination mechanisms. Section 7 explores the interplay between system availability, human review, LLM-generated transactions, and the completeness of query and invariant information. Section 8 compares and discusses various strategies, highlighting their strengths and limitations.

At a high level, developers using this framework must first manually analyze the dependencies between each pair of SQL query templates used to interact with their database models. The dependency check details for each pair can be referenced in Table 4. After identifying the dependencies, developers need to register them in the dependency-checking function within the middleware (Section 6). Once a transaction request is received from the user, the middleware passes the dependency-checking function along with the new request to the transaction manager (Section 5). The transaction manager then asynchronously coordinates the transactions. Users can either check the status of their submitted transactions or review LLM-generated transactions (Section 6).

For researchers working on LLM-data system integration, they can focus on each component separately for optimization.

We consider a system that operates in an environment that accepts transactions from human users and LLMs (Large Language Models). Occasionally, transactions may later be identified as incorrect by users, such as when users decide they no longer want to proceed with a transaction or when a semantic error in LLM-generated transactions leads to an imperfect execution of users’ instructions. For example, in Figure 1, after transaction 2 (T2), an account balance is 50 USD, and transaction 3 (T3) increments 10 USD while all new transactions T4 - T8 have a net effect that decreases by 60 USD. One database constraint requires that the account balance must be positive. T3 is an LLM-generated transaction; the user reviewed it and decided to remove it. In this case, T3 cannot be removed; otherwise, it will lead to a violation of the database constraint. This setting raises two key questions:

1. (1)

   How can we isolate or remove these questionable transactions from LLMs, especially when they may no longer be needed, from database history?

2. (2)

   In certain situations, removing a suspicious transaction might not be feasible while maintaining the consistency of the data system due to subsequent new transactions’ modification to the database.

Solution for (1): In this paper, we mainly suggest two approaches for suspicious transaction removal/separation, and we discussed the problem of other approaches:

Buffering Suspicious Transactions: Rather than committing suspicious transactions directly to the database, the web developer can store these transactions in a buffer. Once an administrative user reviews and either accepts or removes the transaction, the transaction manager within the system can then commit or discard the transaction accordingly.

Buffering Compensating Transactions: A compensating transaction is a specialized transaction used to ”undo” the effects of a previously committed transaction by applying an opposite action to restore the system to a consistent state (Korth et al., 1990). Unlike a rollback, which cancels a transaction before it is committed, a compensating transaction is applied after commitment to address cases where reversal is necessary without altering the original transaction history. For instance, in a banking system, if 100 USD is mistakenly transferred from Account A to Account B, the compensating transaction would transfer 100 USD back to Account A to correct the error. Compensating transactions is essential in complex systems where direct rollbacks may be impractical because the transaction has already been committed.

In our context, the system commits the transaction upon receipt and buffers its corresponding compensating transaction. If an administrative user reviews and decides to remove the transaction, the transaction manager within the system can then commit a compensating transaction to negate the effect of the suspicious transaction on the database. If the transaction is approved, no further action is needed for the database.

To ensure the database maintains consistency while also allowing for the removal or separation of suspicious transactions, we consider several approaches:

1) Full Database Locking: The simplest approach is locking the entire database whenever a suspicious transaction request is received. The system continues running but restricts other transactions until users review the suspicious transaction. This enforces strict serializability and ensures ACID properties, particularly consistency. 2) Sandbox Simulation: Another option is to create a sandbox to simulate transactions. As long as a compensating transaction can remove the effects of suspicious transactions while maintaining consistency, even after subsequent transactions, the suspicious transaction is safe to undo. 3) Granular Locking: Locking specific database parts (e.g., rows or tables) can also support removability. However, determining the appropriate granularity to maintain removability without compromising consistency is challenging. 4) Naive Buffering of Suspicious/Compensating Transactions: Suspicious transactions can be temporarily buffered without immediate execution. However, without coordination, a buffered transaction that is initially valid may become uncommittable due to subsequent modifications. Each of these approaches has limitations, which we will further discuss in Section 8.

5) Buffering with Logical Dependency Checks: A logical dependency check can identify new transactions that would compromise the validity of undoing a buffered transaction without committing the buffered transaction. By holding these identified transactions, the system can ensure that all buffered transactions remain valid. Specifically, if the system has knowledge of database invariants and transaction conditions, an \textit{Invariant Satisfaction} (or \textit{Invariant Confluence}) analysis can assess dependencies between buffered transactions and new transactions. Whether buffering suspicious transactions or compensating transactions, invariant analysis determines if new transactions should proceed or be held, maintaining consistency.

Solution for (2): We identify new transactions that will lead to the inconsistency; we will provide inconsistency detection in section 3:

Consistency: Consistency in this paper refers to the ”C” in the ACID property of database transactions (Gray et al., 1981; Haerder and Reuter, 1983). From the perspective of the database, the developers must guarantee its consistency; specifically, they need to ensure the predefined constraints from the table creators are not violated.

Dependency: We define dependency as follows: when two transactions arrive in sequence, the system may be unable to accept both due to consistency constraints. In this case, the second transaction is said to depend on the first. For example, consider a database constraint requiring an account balance to remain above 0 and assume an account currently holds 50 USD. If the first transaction deducts 40 USD and is marked as suspicious, it may be buffered for possible removal. However, if a second transaction attempts to deduct 20 USD, it will be removed or held, as committing both would violate the balance constraint. Thus, the second transaction depends on the first and requires coordination, such as buffering, to maintain consistency.

Recoverability: In the buffering compensating transaction approach, when a transaction commits to the database, it transforms the database from state 1 to state 2. If we later need to undo this transaction’s changes, we must apply a compensating transaction defined by the system designer. However, there is a risk that a compensating transaction could result in an inconsistent state. We define the ability to remove the committed Transaction 2 with consistency satisfaction as recoverability. In a system that buffers suspicious transactions, the buffered suspicious transaction can always be safely removed without affecting a consistent system state. From the perspective of buffering compensating transactions, the recoverability of a suspicious transaction is decided by its corresponding compensating transaction’s dependency on a new transaction. If a committed new transaction does not depend on its compensating transaction, the suspicious transaction can be recovered.

Availability: Availability is impacted by dependencies between transactions. When a suspicious or compensating transaction is buffered, new transactions that depend on it may also be held. The system will delay any dependent transactions until the buffered transaction receives final approval from administrators or users. This holding process reduces transaction throughput and affects overall system availability.

CAD theorem: There are trade-offs between consistency, availability, and dependency. In our system setting, we cannot achieve the three of them at the same time. This is similar to the CAP theorem in distributed systems (Gilbert and Lynch, 2002).

• •

  Consistency and Availability: To maintain both, we need to minimize the duration of dependencies by reducing the time that suspicious transactions are held.

• •

  Consistency and Dependency: Achieving both often requires sacrificing some availability, which may involve reducing the number of accepted transactions and delaying transaction completion within a defined time frame.

• •

  Availability and Dependency: To maintain availability and dependency, we must relax transaction consistency requirements. Specifically, fewer database or application constraints should be enforced at the system design stage.

Scenario 1: Assume that transaction type A is deducting $x_{1}$ USD from account balance of row $y_{1}$, and transaction type B is deducting $x_{2}$ USD from account balance of row $y_{2}$. Invariant: account balance $>0$.

In a web development environment with a relational database, queries within a transaction are represented as SQL query templates, which may require users to input specific parameters. This means that transactions can have different levels of completeness depending on whether all required parameters are specified. For example, in Scenario 1, parameters like $x_{1}$, $x_{2}$, $y_{1}$, and $y_{2}$ may or may not need to be defined by the user, depending on application logic. In more complex transactions, values for $x_{1}$, $x_{2}$, $y_{1}$, and $y_{2}$ might be dynamically retrieved from a SELECT query.

On the other hand, we cannot assume that every developer or user has prior knowledge of all constraints. We will explore how varying levels of completeness in query information affect dependency checks for ensuring consistency. For Scenario 1, we explained the granularity of dependency checks, and in Table 2, we summarized dependency checks for other constraints based on actions and completeness of information.

Complete Query (No User Input), with Invariant Information: When no user input is required, the exact parameters for each transaction are known in advance. For example, transaction type A always deducts 2 USD from account row 1, and transaction type B also deducts 2 USD from account row 1. In this case, without knowing the balance, these two transactions require coordination. We can simply hard-code transaction type B to wait for type A to finish whenever they interact. Here, the second transaction depends on the first only if they both deduct from the same field in the same row, which has an invariant that ensures the value remains positive.

Complete Query (With User Input), with Invariant Information: When user input is required, dependencies between templates cannot be hard-coded. Instead, we can only identify the row affected once the user submits the necessary parameters. For example, if transaction A deducts 2 USD from account row $y_{1}$ and transaction B deducts 2 USD from account row $y_{2}$, we need to dynamically check if $y_{1}$ and $y_{2}$ refer to the same row based on user inputs. If they do, coordination is required.

Partial Query with Invariants Info: If the row number from transaction 1 is read from the system, then transaction 1 is an incomplete partial query until the read is finished. To check the invariant between two transactions requires “locking” the entire column. In this case, if they deduct a field in the same column with an invariant requiring a positive value, the second transaction depends on the first transaction.

If the row number for transaction 1 is retrieved from the database system, then transaction 1 is considered an incomplete or partial query until this read operation is completed. To check the invariance between two transactions, it may be necessary to “lock” the entire column. In this case, any new deduction transaction depends on the first deduction transaction if they both deduct a field in the same column, with an invariant requiring that the value remain positive.

Query with No Invariants Info: In Scenario 1, if we do not know where the invariant has been applied to the table, the only solution is to ”lock” the table. If the second transaction modifies the same table, it should wait and be held. In cases with foreign key constraints, where there is no referencing/referenced table information, strict serializability should be enforced.

In Scenario 1, if we do not know where the invariant has been applied to the table, the only solution is to “lock” the entire table. If the second transaction modifies the same table, it must wait and be held. In cases involving foreign key constraints, where no information about referencing or referenced tables is available, strict serializability should be enforced.

Row/Column/Table/DB?: Is there a universal rule for determining whether a dependency check depends on operations to the same row, column, table, or entire database? The answer is no; it depends on the specific operations and invariant pairs involved. Strictly speaking, an Invariant Satisfaction Check (IC) acts as a form of locking, but it leverages knowledge of invariants to narrow the scope of what needs to be locked. When query and constraint information is incomplete, a broader scope of “locking” is required.

Consistency Validation: It is important to note that, in invariant confluence, all transactions have been committed within a branch. All intermediate and final states are valid under database constraints. In our setting, however, transactions are buffered and thus not immediately committed for constraints validation. The logical dependency check only identifies dependencies between transactions; it does not determine whether a transaction can ultimately commit while preserving consistency.

A buffered suspicious transaction only holds dependent transactions to prevent them from reducing the buffered suspicious transaction’s likelihood of committing successfully. For instance, in Scenario 1, a second transaction might consume a positive balance, which could make the first (deducting) transaction inconsistent. However, whether the first transaction can be committed while maintaining system consistency depends on the current balance. The consistency of an individual transaction is ultimately verified and enforced by the database’s validation logic (not needed for application-level constraints).

In the case of the dependent-upon (buffered) transaction is found inconsistent after physical constraints validation, it will be discarded, and the dependent transaction will not be affected, since the dependent-upon transaction does not change any database state.

Physical Dependency Check & Logical Dependency-Check: We knew that two concurrent decrement transactions may lead to a database state that violates database consistency. Rather than committing transactions to the database and relying on built-in constraints validation, we use logical reasoning to identify dependencies. There are several reasons we prefer a Logical Check over a physical check. 1) A logical check allows us to extend and support dependency checks that are not natively built into the system. 2) Dependency checking through physical transaction simulation is less efficient and will be explained in the discussion section.

In this section, we consider Scenario 2: transaction type A adds $x_{1}$ USD to the account balance of the row $y_{1}$, and transaction type B deducts $x_{2}$ USD from the account balance of the row $y_{2}$. Invariant: The account balance must remain $>0$.

Isolation Coordination: Isolation in this paper refers to the ”I” in the ACID properties of a database system, ensuring that transactions are processed independently to avoid conflicts (Gray et al., 1981). Isolation coordination involves concurrency control to guarantee the serializability of transactions. When two transactions have write-write, read-write, or write-read conflicts and are running concurrently, isolation coordination ensures that the outcome is equivalent to executing these operations in a specific sequential order. In our framework, the database manages isolation, while the middleware we proposed is responsible solely for maintaining consistency.

Consistency Coordination: We have previously discussed consistency and dependency. To ensure system consistency while allowing for the potential removal of suspicious transactions, coordination between transactions is necessary. For instance, if we have two transactions that are both type A (increment operations) in a given scenario, there is no dependency, and no coordination is required. Similarly, if one transaction is an increment and the other a decrement, no coordination is needed. However, if both transactions are decrement operations, a dependency exists, and the second transaction should wait until the first transaction is complete.

Since we cannot assume a specific isolation level in the database system, we consider that transactions without coordination constraints may be committed concurrently.

In our system setting, we characterize the availability of the middleware and web service pipeline by the buffered rate. While end-to-end performance—from the user’s request to transaction completion—may serve as a more comprehensive availability metric, it is influenced by the specific implementations of the transaction manager and middleware, which are not the primary focus of this paper. Also, database performance can impact end-to-end performance. Thus we assume that the database completes all transactions immediately once they exit the middleware. We only simulate transaction management and do not commit it to the database.

TPC-C (Transaction Processing Performance Council Benchmark C) (Transaction Processing Performance Council, 2010) is a standardized benchmark for evaluating the performance of transaction processing systems. It simulates an order-entry environment, which includes operations such as processing new orders, payment transactions, order status checks, deliveries, and stock level monitoring. TPC-C is widely used to measure and compare the efficiency and scalability of database and transaction management systems under a complex, real-world workload.

We have a naive intuition that with more dependency between transactions, the buffered rate will increase. In a web service system, if the proportion of inter-dependent transactions is higher, the buffer rate will accordingly increase. Thus, a fixed distribution of transactions could give us a better experiment parameter control to explore other parameters that affect the buffered rate. This is also the reason we use TPC-C as the test framework because it assumes a roughly fixed distribution between 5 types of transactions.

A natural intuition suggests that as dependencies between transactions increase, the buffered rate will also rise. In a web service system, a higher proportion of interdependent transactions leads to a corresponding increase in the buffered rate. Therefore, using a fixed distribution of transactions provides better control over experimental parameters, allowing us to explore other factors affecting the buffered rate. This rationale also underlies our choice of the TPC-C framework for testing, as it maintains a roughly fixed distribution across five transaction types.

We implement our test frame by adapting and rewriting a TPC-C benchmark tool (py-tpcc) (Pavlo, 2011). It is originally used to generate TPC-C transactions, dock transactions to a database, and measure the throughput performance of the target database. We rewrite this package, dock the transactions to our transaction manager, and control the flow of transaction generation. In our experiments, we mainly control 2 factors, the interval between two LLM-generated suspicious transactions (SI), and the interval between two user reviews to accept or remove the suspicious transaction, (RI). For simplicity, we assume equal intervals for both RI and SI.

Question 1: Benchmark Comparison: how does the completeness of the query and invariant affect the buffered rate

From our previous discussion, we understand that with higher information completeness to queries and invariants, we can assess transaction dependency with finer granularity. For example, in Scenario 1, with complete query and invariant information, we identify a dependency if the second transaction modifies the same field in the same row as the first transaction, assuming a ”¿ 0” constraint on that field. However, without prior knowledge of invariants, we may need to block the entire system to ensure consistency for the first transaction.

We assume that a system without using invariant information can coordinate transactions in the TPC-C benchmark only through table-level locking. This is our baseline.

Therefore, we tested how dependency check granularity, under different levels of query and invariant completeness, affects the buffered rate. Our previous experiments used dependency checks based on Invariant Satisfaction. In TPC-C’s design, all invariant-related transaction queries are complete at the time of coordination checks, meaning a new transaction only depends on a buffered transaction if they modify the same field. To compare, we implemented a transaction manager without prior invariant knowledge using table-level dependency checks. This means that a second transaction is held or buffered as long as it modifies the same table as the first transaction.

Conclusion 1.1: When RI is infinite, and SI is set to 5, there is no user review to reduce buffered transactions. With 20 trials, the average buffered rate with field-level granularity dependency checks (complete invariants and query information) is lower than with table-level granularity checks (incomplete invariants and query information). This suggests that with complete information about invariants and queries, a system can achieve lower buffered rates through finer-grained coordination compared to cases with no invariant information (see Figure 11).

Conclusion 1.2: If user reviews occur at a rate of 80%, which removes 80% buffered transactions, (RI = 50, SI = 5, with 20 trials for each transaction length), the average buffered transaction rate in the case with complete information is higher than in the case without complete information (see Figure 11).

Benchmark Conclusion 1.3: Regardless of user reviews, the buffered rate in the baseline benchmark (without invariant information) is double that of the benchmark utilizing complete query and invariant information.

Question 2: Which type of transaction is most likely to cause congestion or hold new transactions, leading to an increase in the buffered rate?

From the manual analysis and invariant confluence, in TPC-C, the dependency exists between New-order and New-order Transactions; and between Delivery and Delivery Transactions.

Question 3: What factors related to suspicious transactions affect the transaction acceptance rate?

Conclusion 3.1 If the RI is set to infinity—meaning there is no review to remove buffered transactions that may congest new transactions—the buffered rate does not change significantly as the number of transactions increases.

In this scenario, we assume an SI of 5, where one out of every five transactions is labeled as an LLM-generated or suspicious transaction (e.g., True, False, False, False, False, True, …). We calculate all buffered rates 5 times and average the results. As shown in Figure 11, the average buffered rate remains steady at around 0.6 if the number of transactions is larger than 400, regardless of the increasing number of transactions.

Conclusion 3.2 If the RI is set to infinity, a longer SI leads to a decrease in the average buffered rate.

We varied the interval between two LLM-generated transactions, testing SIs of 2, 5, 10, and 50 (e.g., one LLM-generated transaction for every 2 transactions, with 20 trials per SI). As shown in Figure 11, the test case with higher SIs results in a lower average buffered rate.

Fact 3.3: If the user reviews and either approves or rejects all transactions at any point, all buffered transactions will be processed, resulting in a buffered transaction rate of 0.

Conclusion 3.4 A shorter RI results in a lower number of average buffered transactions.

Since reviewing all transactions would result in a buffered rate of 0, we tested the effect of partially reviewing transactions by randomly removing 80% of the transactions in the buffer. Assuming that human reviews occur less frequently than LLM-generated transactions, we set the RI (user review interval) to be a multiple of the SI (suspicious transaction interval), with values of (1, 2, 5, 10, 100) times the SI. For this test, we fixed the SI at 5, giving RI values of (5, 10, 25, 50, 500), with 20 trials per RI.

We focused on cases where the number of transactions exceeded the RI to ensure human processing within each test case, omitting scenarios without human processing. Consequently, a few lines in Figure 11 are truncated. Also, as shown in Figure 11, a shorter RI results in fewer buffered transactions.

Conclusion 3.5: When RI is fixed, the number of buffered transactions remains relatively stable, even as the total number of transactions increases.

Conclusion 3.6: Because the number of buffered transactions remains stable, the buffered transaction rate decreases as the total number of transactions increases (see Figure 11).

In this work, we characterized the workflow of interactions between LLMs and databases in the presence of semantic errors. We introduced the concept of Invariant Satisfaction, which identifies the necessary coordination between long-lived, buffered transactions and new transactions. This ensures that new transactions do not lead to states where buffered transactions become irrecoverable. Invariant Satisfaction extends beyond database constraints to include application logic constraints, dynamically reducing coordination requirements based on the completeness of constraint and query information. We proposed a middleware framework for coordinating undo-able LLM-generated transactions that can be integrated into existing enterprise systems with minimal modifications.

This paper provides a robust solution for managing undo-able long-lived transactions and guarantees consistency. For system researchers, this study introduces an interactive paradigm between LLMs and databases, featuring an ”undoing” mechanism to handle incorrect operations while maintaining consistency. For system engineers, it offers a middleware design that efficiently incorporates undo-able LLM transactions into current workflows, ensuring reliability with minimal disruption.