Locking question

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