Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases

Preventing Inconsistent Data with Database Locking

When two database requests read and change the same data at the same time, one request can use a value the other request is changing to make its decision. A transaction groups database operations so they succeed or fail as one unit. A lock controls access to data while a transaction uses it. If another transaction tries to make a conflicting change, the lock makes it wait or, in some cases, fail.

To prevent inconsistent results, define the rule the data must obey, then protect the reads and writes that enforce it. One update with a condition, or a database constraint, may be enough. When a request must read a value before deciding what to write, use a short transaction and an appropriate lock or isolation level. An isolation level sets what changes a transaction can see while it runs. The details depend on the database engine.

Use an atomic update when the rule fits

For a simple inventory reservation, the database can check availability and subtract the requested quantity in one statement:

UPDATE inventory
SET available = available - :quantity
WHERE sku = :sku
  AND available >= :quantity;

The application checks how many rows the update affected. One affected row means the reservation succeeded; zero means the item was missing or had insufficient stock. Because the condition and change happen in one statement, a second request cannot reserve based on a separate, earlier read of the same available quantity.

Parameter syntax and affected-row reporting depend on the database driver and product. Confirm how the driver reports an update when the stored value does not change. Transactions coordinate concurrent requests, as explained in how databases manage concurrent transactions.

Lock rows when a decision depends on a read

When a business rule requires an application to read a row and inspect its current values before deciding what to write, put the read and change in the same transaction and lock the row while making that decision:

BEGIN;

SELECT available
FROM inventory
WHERE sku = :sku
FOR UPDATE;

-- Check availability and update the row within this transaction.

COMMIT;

FOR UPDATE is a locking read: it requests a lock on the rows returned by the query so conflicting changes wait until the transaction ends. In PostgreSQL, row locks block conflicting writers and lockers, while ordinary reads can continue. MySQL InnoDB also supports locking reads, but the records and ranges it locks depend on the search and isolation level. Check the behavior for your engine in the PostgreSQL locking documentation or the MySQL locking-read documentation.

Keep the transaction open only as long as the database work requires. Other requests wait longer for locked rows while the transaction handles user input. Network calls and unrelated processing inside the transaction extend that wait too. For MySQL-specific implementation details, see locking reads in InnoDB.

Protect the entire rule

A row lock protects the rows returned by the query. It does not automatically protect a condition that depends on several rows, or stop another transaction from inserting a row that was not there when the query ran.

For example, a rule that allows only one active assignment per person applies to every assignment for that person, including one another request might insert. A unique database constraint is a good option when the rule can be expressed as uniqueness. For rules involving a range of values or more complex conditions, use an isolation level or a locking strategy designed for that database engine to protect the full rule.

Serializable isolation asks the database to prevent results that could not happen if transactions ran one at a time. When concurrent work conflicts, the database can abort a transaction. The application must handle that failure. PostgreSQL documents serialization failures and advises retrying the whole transaction, including its reads and decisions, rather than only the final write. See its transaction isolation documentation and serialization failure guidance. Locking behavior varies by product and isolation level. A query that protects a range in one engine does not necessarily protect the same range in another.

Limit blocking and deadlocks

A deadlock happens when transactions wait for locks held by one another. For example, one transaction locks account A and then requests account B, while another locks B and then requests A. The database can abort a transaction to let the other proceed. MySQL InnoDB recommends keeping transactions small and acquiring locks in a consistent order. It also recommends indexes on columns used by locking reads and updates in its deadlock guidance.

When a transaction must lock several rows, have every code path lock them in the same order. Use indexes so the database can find the intended rows without scanning a wider range than necessary. Log deadlocks and transaction failures so you can trace recurring conflicts to the queries and lock order involved. Retry only errors your database identifies as temporary. Retry the whole transaction when earlier reads may no longer support the decision.

Match the approach to the database

Locking syntax and behavior are not interchangeable across database engines. MySQL InnoDB can lock index ranges for some searches, depending on the query and isolation level. SQL Server also uses row versioning in some configurations, which lets certain readers use row versions rather than wait for writers; write decisions still need protection against conflicting changes. SQLite permits multiple simultaneous readers but only one simultaneous write transaction, so row-locking advice for PostgreSQL or MySQL does not directly apply. Check the relevant MySQL isolation rules, SQL Server locking and row-versioning guide, or SQLite transaction documentation.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *