ACID Properties: Are They Absolutely Essential for Every Project?

While recently rewriting the backend for the financial calculators of one of my side projects, I found myself reconsidering how essential ACID properties are in database transactions. Especially when distributed system architectures and high scalability goals come into play, the strict rules of ACID can sometimes feel like an obstacle. So, is full ACID compliance truly a must for every project? Or can we offer more flexible and performant solutions by compromising these properties in certain situations?

Over the years, I’ve experimented with different database approaches, both while developing a large-scale production ERP and in my own smaller projects. What I’ve seen is that there’s no such thing as “the best” solution; there are only trade-offs that best suit the current business needs, budget, and team expertise. In this post, I’ll delve into each ACID component through my own experiences, explaining when to stick to them strictly and when we can be flexible.

Atomicity and Transaction Integrity: The Point of No Return

Atomicity is the principle that all steps within a database transaction must either succeed entirely or fail entirely. In other words, either all of them are committed, or none of them are rolled back. For me, Atomicity is an absolute must-have, especially in critical areas like financial transactions. When transferring a balance in a bank’s internal platform, the money must be debited from the sender’s account and credited to the receiver’s account. In case of any error between these two steps (network interruption, server crash), the entire transaction must be rolled back to ensure no one loses money or engages in double-spending.

In a production ERP, steps like deducting from stock when an order is shipped, generating a shipping invoice, and updating accounting records are typically handled within a single atomic transaction. If the stock deduction occurs but the system crashes before the invoice is generated, a serious inconsistency will arise. In my experience, I’ve frequently used PostgreSQL’s transaction mechanisms to manage such scenarios. A simple SQL transaction like the one below is the most basic example of Atomicity:

BEGIN;

-- Deduct product from stock
UPDATE products SET stock_quantity = stock_quantity - 5
WHERE product_id = 123 AND stock_quantity >= 5;

-- If stock is insufficient, cancel the operation
IF NOT FOUND THEN
    RAISE EXCEPTION 'Insufficient stock';
END IF;

-- Update order status
UPDATE orders SET status = 'Shipped' WHERE order_id = 456;

-- Create shipment record
INSERT INTO shipments (order_id, product_id, quantity, shipment_date)
VALUES (456, 123, 5, NOW());

COMMIT;

In this example, all operations within the block starting with BEGIN and ending with COMMIT will either succeed or be rolled back in case of a RAISE EXCEPTION. If an error occurs in the middle of this transaction, the system automatically rolls back, and the database returns to its consistent state before the transaction began. This has been an indispensable layer of security for me in many critical workflows.

Consistency and Business Data Models: Why Should Data Remain Consistent?

Consistency means that a transaction moves the database from one valid state to another. This implies that the database must adhere to all predefined rules (constraints, triggers, cascading rules). In other words, when the transaction is complete, there should be no remaining situations that violate the database schema and business rules. For example, FOREIGN KEY constraints, UNIQUE constraints, and CHECK constraints are fundamental ways to ensure consistency at the database level.

In a production ERP, there’s a business rule that checks if raw materials are in stock when a production order is created and issues a warning if they are not sufficient. This rule guarantees the system’s consistency. If a production order can be recorded without raw materials, it can disrupt production planning. In such situations, I prefer to use consistency constraints at the database level, in addition to performing validations at the application layer. This is critical, especially in scenarios where multiple applications use the same database.

-- CHECK constraint ensuring stock quantity in the products table does not go below zero
ALTER TABLE products
ADD CONSTRAINT chk_stock_quantity CHECK (stock_quantity >= 0);

-- FOREIGN KEY constraint in the order_items table ensuring the product_id exists in the products table
ALTER TABLE order_items
ADD CONSTRAINT fk_product
FOREIGN KEY (product_id) REFERENCES products (product_id);

These constraints prevent erroneous data from entering the system. However, Consistency is not limited to the database schema; it also encompasses business logic consistency. For example, a new order should not exceed a user’s credit limit. I usually manage these types of checks at the application layer, but in some cases, I also bring them down to the database level using triggers or stored procedures.

Sometimes, in distributed systems or systems with high write loads, “eventual consistency” can be an acceptable trade-off. For instance, in the backend of an analytical dashboard for one of my side projects, it’s not essential for user data to be updated instantaneously. When a new user registers, it might take a few seconds or minutes for this information to reach the analytical system. This improves the overall performance of the system without affecting critical business flows. We followed a similar approach in designing a real-time dashboard for a client project; although the data wasn’t instantaneous, it eventually became consistent with a 1-2 minute delay. The key here is to make the right decision about how “fresh” certain data needs to be.

Isolation and Concurrency Problems: Different Realities at the Same Time

Isolation is the principle that concurrently running transactions should not affect each other. Changes made by one transaction should be invisible to other transactions until that transaction is committed. This is vital for maintaining database consistency when multiple users or processes access the same data simultaneously. Databases offer different mechanisms to manage this isolation level: Read Committed, Repeatable Read, Serializable, and so on.

For me, choosing the isolation level is a matter of balancing performance and data consistency. For example, the default isolation level in PostgreSQL is READ COMMITTED. This level is sufficient for most applications and provides good concurrency. However, when performing a complex reporting task in a production ERP, I used to get different results when running the same report 5 minutes apart. The reason was that even within a transaction at the READ COMMITTED level, changes committed by other transactions were visible (non-repeatable read). To resolve this, I ran the reporting transactions at the REPEATABLE READ level.

-- Setting the isolation level for a specific transaction in PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Reporting queries
SELECT product_name, SUM(quantity)
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY product_name;

COMMIT;

This way, the data I read remained constant throughout the transaction, ensuring the report’s consistency. However, the highest isolation levels, like SERIALIZABLE, can impose a significant performance overhead on concurrency. The SERIALIZABLE level makes all transactions behave as if they were executed serially (one after another), which increases the probability of deadlocks and transaction retries. In a system with very high intensity, using this level can lead to severe performance bottlenecks.

Once, on a client project, we had to use SERIALIZABLE during a heavy write operation. There, the system’s throughput (transaction volume) dropped incredibly due to deadlock issues and transactions constantly encountering serialization failure errors and retrying. In such cases, we had to evaluate alternative approaches like optimistic locking or eventual consistency. With optimistic locking, we read the data along with a version number or timestamp and check if this version number has changed when updating. If it has changed, we retry the operation. This offers a more flexible solution for systems requiring high concurrency.

Durability and Disaster Scenarios: Insurance Against Data Loss

Durability guarantees that once a transaction is successfully committed, those changes will persist even if there is any hardware or software failure in the system. In other words, even if the database system crashes, committed data will not be lost. For me, this feature is one of the most critical elements in database management. Data loss can have consequences that are difficult, if not impossible, to rectify for a business.

Relational databases like PostgreSQL use the Write-Ahead Logging (WAL) mechanism to ensure this durability. Any data change is first recorded in WAL files written to disk and then applied to the main data files. If the system suddenly crashes, the database can be restored to its last consistent state before the crash using the WAL records upon restart.

A few years ago, I experienced a power outage on my own server. The server shut down abruptly. Upon restarting, PostgreSQL automatically recovered the database using the WALs, and all the last committed data was intact. This experience once again demonstrated how important PostgreSQL settings like fsync and full_page_writes are. The fsync = on setting ensures that WAL records are actually written to disk, preventing data loss. If this setting were off, the probability of experiencing data loss in case of a power failure would be much higher.

Relying on a single server is not enough to enhance durability. Replication strategies also come into play here. By using methods like physical replication (Stream Replication) and logical replication, we can keep copies of the database on different servers, enabling rapid recovery in disaster scenarios. In a production ERP, we used synchronous replication to ensure that every commit was persistent on at least two servers. This minimized the risk of data loss if the primary database server failed. However, synchronous replication imposes a slight overhead on write performance, which is a trade-off.

Compromising on ACID: CAP Theorem and Distributed Systems

Ensuring all four ACID properties at all times can be challenging, especially in distributed systems and when aiming for high scalability. This is where the CAP Theorem comes into play. The CAP Theorem states that a distributed system can only provide at most two out of three properties simultaneously: Consistency (C), Availability (A), and Partition Tolerance (P). Since abandoning Partition Tolerance in distributed systems is practically impossible, we often have to compromise on either Consistency or Availability.

In my practice, I have consciously compromised on ACID in some situations. For example, for the backend of a spam-blocking app I developed on the Android side for one of my side projects, I used a NoSQL database with eventual consistency. The data here (spam numbers, user reports) did not need to be consistent across all servers instantaneously. It might take a few seconds for a report to enter the system and propagate to other servers, which did not affect the application’s functionality. This allowed me to achieve much higher write performance and availability.

In the ERP of a manufacturing company, we worked with real-time stock and production status data for AI-driven production planning. However, it wasn’t mandatory for the results of the AI model to be immediately visible in all reports when saved. After the planning results were saved to the system, we triggered an asynchronous process to update the relevant dashboards and reports. This way, users could eventually access consistent data without degrading the planning engine’s performance. This was an example of an Architectural Decision where we gained Availability and performance by slightly compromising on Consistency.

Real-World Applications and My Approach

In my twenty years of field experience, I’ve seen that the approach to ACID properties varies greatly depending on the nature of the project and business requirements. There’s no absolute “essential for every project” situation. For me, the key is to clearly understand the benefit each ACID component provides and the risks of compromising it, and to establish this balance correctly.

When I Use Full ACID:

• Financial Transactions: Bank transfers, invoicing, payment systems. Here, Atomicity, Consistency, and Durability are indispensable.
• Stock and Inventory Management: Product movements, order fulfillment, production input/output. Atomicity and Consistency are critical for inventory accuracy.
• User Authentication and Authorization: User accounts, roles, and permissions. It’s essential for data to be accurate and persistent for security.
• Core ERP Workflows: Fundamental processes from order to shipment, production to accounting. Here, I generally use PostgreSQL’s READ COMMITTED or REPEATABLE READ isolation levels, and I don’t neglect WAL and replication for Durability.

When I Compromise (or Flex) on ACID:

• Logging and Audit Trails: Log systems with heavy write loads. Here, high write speed and availability are more important than the data being 100% consistent instantaneously. Eventual consistency is acceptable for me.
• Analytical and Reporting Data: Real-time dashboards or statistics. It’s not an issue for data to be updated with a few minutes of delay.
• Caching Layers: In systems like Redis, data persistence and full consistency are secondary. Fast access and high availability are important. I also consider this principle when choosing Redis’s eviction policy to manage OOM risks.
• Social Media Feeds or Real-Time Notifications: It’s generally acceptable if a notification doesn’t reach all users instantly or if a post appears with a few seconds of delay.

In my opinion, software architecture is often not just about writing code; it’s about understanding organizational flows and the real needs of the business. It’s impossible to build “the best database architecture” without understanding how the purchasing process works in an ERP and how the supply chain is integrated. I consider ACID properties as part of this bigger picture. I determine where to hold firm and where to be flexible by evaluating the project’s risks and opportunities. When I encountered an OOM-killed error by writing sleep 360 in a systemd unit last month, I found the solution by transitioning to a polling-wait strategy, again through a similar trade-off analysis; I compromised on instant response for less resource consumption and a more flexible workflow.

In conclusion, ACID is not a religious doctrine, but a toolkit. Each tool has a specific area of application and scenarios where it works better. The important thing is to know when and how to use these tools, and to make these decisions based on concrete business requirements and technical trade-offs. My clear stance is: full ACID compliance is essential for critical business processes and financial data. However, when performance or scalability goals come to the forefront, and the nature of the business allows it, controlled compromise on ACID properties can offer more flexible and effective solutions.