Ensuring Safe Data Modifications in PostgreSQL - Part 2

Concurrency control in databases ensures that multiple transactions can occur simultaneously without causing data errors. It’s essential because, without it, two people updating the same information at the same time could lead to incorrect or lost data. There are different ways to manage this, including optimistic locking and pessimistic locking. Optimistic locking assumes that conflicts are rare and only checks for them when updating data. In contrast, pessimistic locking assumes conflicts are likely and locks data early to prevent issues. Optimistic locking allows for more concurrent transactions and better performance in systems with fewer conflicts.

If you are interested to read about pessimistic locking here is the link for this: https://stormatics.tech/blogs/ensuring-safe-data-modifications-in-postgresql-with-select-for-update

What is Optimistic Locking?

Optimistic locking is a method used in database management to handle concurrent transactions. It allows multiple users to read and modify data simultaneously without locking the database. The key idea is to assume that conflicts between transactions are rare and to check for conflicts only when the data is about to be written back to the database.

The principles of optimistic locking are built on the assumption that most database transactions do not conflict with each other. Instead of locking data when it is read, optimistic locking tracks changes using a version number or timestamp. When a transaction tries to update data, the system checks if the data has been modified since it was last read. If the data has changed, the update is rejected, and the transaction can be retried.

Advantages of Optimistic Locking

Increased Concurrency: Because optimistic locking doesn’t lock data when it’s read, many users can access the data at the same time without waiting for locks to be released. This leads to higher system performance and better user experience.

Reduced Locking Overhead: Without the need for locking mechanisms during data reads, optimistic locking reduces the complexity and overhead associated with managing locks. This results in more efficient use of system resources and faster transaction processing.

Optimistic locking is particularly beneficial in environments with high read-to-write ratios and low contention for data, making it a powerful tool for modern database management.

Implementing Optimistic Locking

In this example, a products table is created with columns for id, name, price, and version. The version column is used to track changes for optimistic locking. First, the details of a specific product, including its version, are selected from the table. Then, an update is attempted to set the product’s price to 12.00, but this update only succeeds if the version in the database matches the current version. This ensures that no other transaction has modified the product since it was last read, maintaining data integrity.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    version INT NOT NULL
);

SELECT id, name, price, version
FROM products
WHERE id = 1;

UPDATE products
SET price = 12.00, version = version + 1
WHERE id = 1 AND version = 1;

Handling Conflicts

When we attempt to update a record, the system checks whether the version or timestamp of the data has changed since it was last read. If the version has changed, it means another transaction has modified the data, resulting in a conflict. When a conflict is detected, the update fails, and no rows are affected. it’s essential to handle it gracefully.

Re-read the Data: After detecting a conflict, re-read the current state of the data, including the new version or timestamp. This gives you the most up-to-date information.

Apply the Necessary Changes: Apply your intended changes to the newly read data. Ensure you incorporate any updates that may have occurred since your last read.

Attempt the Update Again: Try to update the record using the same optimistic locking mechanism, checking the version or timestamp.

Repeat if Necessary: If another conflict occurs, repeat the process. While this might seem repetitive, it ensures that all changes are applied based on the latest data, preserving data integrity.

Considerations and Best Practices

Optimistic locking is particularly effective in environments with low contention for data, where conflicts between transactions are infrequent. It works best in the following scenarios

Read-Heavy Workloads: Applications that perform a high number of read operations compared to write operations can benefit significantly from optimistic locking, as it allows multiple read transactions to occur simultaneously without locking resources.
Collaborative Applications: Systems where users frequently access and update shared data, such as collaborative document editing tools or social media platforms, can use optimistic locking to maintain high concurrency without significant overhead.
Distributed Systems: In environments where multiple distributed nodes or services interact with a central database, optimistic locking can reduce the complexity and performance bottlenecks associated with managing locks across a distributed network.

Potential Drawbacks and How to Avoid Them

While optimistic locking offers many advantages, it also comes with potential drawbacks

Conflict Handling Overhead: When conflicts do occur, they require additional handling, such as retrying transactions, which can introduce latency and complexity. To avoid this, ensure your application logic is designed to handle retries gracefully and efficiently.
Increased Code Complexity: Implementing optimistic locking often requires more complex application logic to manage version checks and retries. Using libraries or frameworks that support optimistic locking can help simplify this process.
Performance Impact in High-Contention Scenarios: In scenarios with high contention for data, the frequency of conflicts and retries can negatively impact performance. In such cases, consider using a hybrid approach that combines optimistic and pessimistic locking based on the specific needs of different parts of the application.

By understanding when optimistic locking is most effective, being aware of potential drawbacks, and following best practices for implementation, you can leverage this powerful concurrency control mechanism to build highly performant and scalable applications.