|
Hi all
I am designing an inventory application, and I want to ensure that the
stock level of any item cannot go negative.
Instead of maintaining a running balance per item, I store the original
quantities received in one table (call it ‘inv_rec’), and any amounts removed in
another table (call it ‘inv_alloc’).
CREATE TABLE inv_rec
(row_id SERIAL PRIMARY KEY,
product_id INT REFERENCES inv_products,
qty INT);
CREATE TABLE inv_alloc
(row_id SERIAL PRIMARY KEY,
rec_id INT REFERENCES inv_rec,
qty INT);
To get the balance of a particular item -
SELECT SUM(
a.qty + COALESCE(
(SELECT SUM(b.qty) FROM inv_alloc b
WHERE b.rec_id = a.row_id), 0))
FROM inv_rec a
WHERE a.product_id = 99;
To remove a quantity from a particular item -
INSERT INTO inv_alloc (rec_id, qty)
VALUES (23, -1);
I want the application to check that there is sufficient quantity before
attempting to execute the INSERT command.
If ok, it will look for a suitable row in ‘inv_rec’ to allocate
against.
The danger of course is that, in a multi-user system, another user might
have removed an additional quantity from the same item in between the SELECT and
the INSERT.
I *think* that the solution is to BEGIN the transaction, then perform
SELECT ... WITH UPDATE, then proceed with INSERT and COMMIT if ok, else
ROLLBACK.
Is this the correct approach, or am I missing something?
Thanks
Frank Millman
|
