Why Transactional Consistency Matters

Introduction

After college I joined Oracle Corporation. I learned the concepts of transactional consistency at the bootcamp that kicked off my career. The example used in the training to explain a transaction: when I transfer money to you, either my account is deducted and yours increased, or neither one is changed.

Transactions are relevant not only to database writers, but also to readers. Given the example of transferring money: querying the accounts table (as another user/session) I should either see the state before money is transferred, or the after state. The reader side of transactional consistency is often referred to as read consistency, with lack thereof called: dirty reads.

We experience transactions everywhere in daily life. Buy products, in a store or online. Order a ride. Companies manage inventories and their supply chain. They manufacture products. Hospitals treat patients and manage their medication. Etc.

Transactional consistency for databases models how the physical world works. “When I transfer money to you” translates into: I hand you a bank note. Either you have it and I don’t, or I still have it and you don’t. There is no physical representation of both of us having the bank note, or neither one of us having it (it is clear where the money goes if the bank note is blown away, or if it gets stolen).

Intuitively we understand transactional consistency because it follows the physical world. However, for databases/software to implement transactional consistency is complicated. In fact, software developers/vendors will choose not to follow strict transactional consistency if they believe they can get away without it.

Transactions in Analytical Applications

Analytics, as well as agentic operations, often need access to consolidated data. Data is transferred from transaction processing sources or applications into an analytical system such as Snowflake or Databricks. Thanks to the transfer, the analytics and agentic operations can query a scalable environment with no concerns of impact to the operational systems or access challenges such as high latency, rate limits etc.

In the early days of data warehousing most data transfer was no more frequent than daily. Many implementations had the luxury of a batch window with no activity on the source, and nothing else querying the data, to perform data loads and transformations. The intermediate states were irrelevant, as long as the end state was consistent. It was easy to grasp that the entire batch run was a single transaction, and for the duration of the batch window, transactional consistency could take a back seat.

The data warehouse is “eventually consistent” - the industry term for a data store that at some point matches the state of the data store - and that has been good enough.

Nowadays, most data transfer is a lot more frequent than daily, if not close to real-time. We gain better insights with data that is more up-to-date, and agentic operations can be more powerful. We are moving toward operational analytics, using an analytical system to drive operational decisions. Many data sources experience 24x7 activity. And with that, ongoing transactional consistency is increasingly becoming relevant.

In fact, with the rise of agent applications, transactional consistency is more crucial than ever. In Scaling AI agents with trustworthy data, Rajpreet Bajwa, Vice President, Engineering, Data and AI Infrastructure, Shopify says “We must allow agent access to data in safe and trustworthy ways so people can make maximal use of the data, knowing that it’s completely reliable.”

Why Agents Need Transactional Consistency

An agent is given a goal, and access to resources. The agent will do the utmost to achieve its goal. Humans make a lot of assumptions, and we generally share a common point of view (common sense). Agents don't. And agents powered by frontier LLMs are pretty clever. When OpenAI recently tasked an agent to complete a test, the agent decided that rather than solve the test, it would find the answers elsewhere. It decided Hugging Face may have the answers, so it hacked huggingface.co (OpenAI’s report of the incident). Even OpenAI had not expected this.

Agents may perform operations that we did not expect. We cannot assume they ask permission, or apply common sense. For example, an agent told to send invoices will do so at unrivaled speed, without complaints. An invoice should not have a $0 value? Unless explicitly instructed to validate the dollar amount, the agent will just send the invoice. It does not consider financial impact or reputational damage.

How could the agent get to the $0 value? Imagine the invoices table was updated, but not yet the invoice_products table that populates the line items of the invoice, due to a lack of transactional consistency.

Conclusion

Transactional consistency in operational systems allows our experiences to align with our expectations from the physical world. With analytical systems becoming more operational and agentic, transactional consistency becomes a key ingredient for your consolidated environment.

Estuary understands the importance of transactional consistency. In fact, Estuary just rewrote its runtime engine to guarantee transactional consistency whilst improving performance. To learn more, read the blog or download the whitepaper.