Problem
Engineering context
A balance update without its movement record leaves inventory history inconsistent.
Solution boundary
Execute both changes in one transaction and rollback on failure.
Transactions
SQL Transactional Stock Movement
Updates a balance and records its movement atomically.
SQL
MySQL
Advanced
illustrative-stock-transaction.sqlSQL
START TRANSACTION;
UPDATE inventory_items
SET quantity = quantity - :quantity
WHERE id = :item_id
AND company_id = :company_id
AND quantity >= :quantity;
INSERT INTO stock_movements (item_id, company_id, quantity_delta)
VALUES (:item_id, :company_id, -:quantity);
COMMIT;How it works
How it works
The guarded update prevents a negative quantity before the movement is recorded.
Security considerations
Security considerations
Bind parameters through the database driver; do not concatenate input.
Performance considerations
Performance considerations
Index item and tenant keys and keep the transaction short.
Tradeoffs
Tradeoffs
Contention may require retry logic under high write concurrency.
Testing notes
Testing notes
Test insufficient stock, concurrency and rollback.
Limitations
Limitations
Illustrative public-safe example; adapt the boundary and domain rules to the actual application.
Engineering evidence
Connected design context
Continue exploring
Related examples
Laravel Service-Layer Transaction
Coordinates a stock adjustment inside a database transaction while keeping HTTP concerns outside the domain operation.
Transactions
Service Layer
Policy-Based Authorization
Examine the engineering example →