Thread: 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
On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> 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);
> Is this the correct approach, or am I missing something?
What I would do, is to add trigger on inv_alloc, than when you
insert/update/delete row there, it updates appropriate row in inv_rec by
correct number.
Then, I'd add check on inv_rec to make sure qty is never < 0.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> Hi all
> >
> > I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative.
> >
[...]
> Hi all
> >
> > I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative.
> >
[...]
>
> What I would do, is to add trigger on inv_alloc, than when you
> insert/update/delete row there, it updates appropriate row in inv_rec by
> correct number.
>
> Then, I'd add check on inv_rec to make sure qty is never < 0.
>
> What I would do, is to add trigger on inv_alloc, than when you
> insert/update/delete row there, it updates appropriate row in inv_rec by
> correct number.
>
> Then, I'd add check on inv_rec to make sure qty is never < 0.
>
Thanks, depesz
I can see how that would work, but I have two comments.
1. I am writing my application to support 3 databases – PostgreSQL, sqlite3, and MS SQL Server. Because they are all so different when it comes to triggers and procedures, I am trying to avoid using them, and do as much within the application as possible.
2. I think you are suggesting maintaining a ‘balance’ column on inv_rec. This raises the question of whether or when you should create and maintain a column if the same information could be derived from other sources. I realise that this is a judgement call, and sometimes I struggle to get the balance right. Is this a situation where people would agree that it is warranted?
I would still appreciate some feedback as to whether my proposed solution would work.
Thanks
Frank
Hi,
Personally, I like to make the database responsible for the integrity of the data within it as much as possible. And therefore would favour Depsesz's solution to trying to manage it within the application.
Cheers
Gary
On Wed, Oct 26, 2016 at 8:18 PM, Frank Millman <frank@chagford.com> wrote:
On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> Hi all
> >
> > I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative.
> >
[...]>
> What I would do, is to add trigger on inv_alloc, than when you
> insert/update/delete row there, it updates appropriate row in inv_rec by
> correct number.
>
> Then, I'd add check on inv_rec to make sure qty is never < 0.
>Thanks, depeszI can see how that would work, but I have two comments.1. I am writing my application to support 3 databases – PostgreSQL, sqlite3, and MS SQL Server. Because they are all so different when it comes to triggers and procedures, I am trying to avoid using them, and do as much within the application as possible.2. I think you are suggesting maintaining a ‘balance’ column on inv_rec. This raises the question of whether or when you should create and maintain a column if the same information could be derived from other sources. I realise that this is a judgement call, and sometimes I struggle to get the balance right. Is this a situation where people would agree that it is warranted?I would still appreciate some feedback as to whether my proposed solution would work.ThanksFrank
From: "Frank Millman" <frank@chagford.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, October 26, 2016 4:42:29 AM
Subject: [GENERAL] Locking questionHi allI 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 bWHERE b.rec_id = a.row_id), 0))FROM inv_rec aWHERE 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?ThanksFrank Millman
Is it necessary to have the two separate tables for received and allocated? I would record the receipt and allocation transactions in a single table.
Also, and then if there is no need for the high concurrency performance of SERIAL (which there probably is not, I'm guessing, since you are considering locking), I would make a keyed sequence by recording the last-used row_id as a column in the inv_products table.
Then, apply a strategy such as described in
That pattern employs a trigger for convenience, but you could do without. Then, the first step in your BEGIN ... COMMIT block is to update the last-used value in the corresponding inv_products row to compute the next-to-be-used row_id value (i.e., UPDATE first, then SELECT it back out, or use UPDATE ...RETURNING).
That initial UPDATE transaction will block other transactions attempting to initiate inventory updates on that particular inventory item and effectively serialize your concurrent inventory activity, per inventory item.
After you add the inventory transaction, then check the net balance and throw an exception if negative. That rolls back everything back to the initial row_id update in inv_products for the product
Assuming a well-managed inventory organization, the exception throwing should be relatively infrequent.
Me personally, depending on application specifics, might make the deliberate de-normalization decision and layout the transaction table to model an accounting balance sheet, having separate columns for inventory additions, subtractions, and a running total.
--B
On Wed, 2016-10-26 at 12:18 +0200, Frank Millman wrote:
>
> From: hubert depesz lubaczewski
> Sent: Wednesday, October 26, 2016 10:46 AM
> To: Frank Millman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Locking question
>
> On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> > Hi all
> > >
> > > I am designing an inventory application, and I want to ensure
> that the stock level of any item cannot go negative.
> > >
> [...]
>
> 1. I am writing my application to support 3 databases – PostgreSQL,
> sqlite3, and MS SQL Server. Because they are all so different when it
> comes to triggers and procedures, I am trying to avoid using them,
> and do as much within the application as possible.
>
> 2. I think you are suggesting maintaining a ‘balance’ column on
> inv_rec. This raises the question of whether or when you should
> create and maintain a column if the same information could be derived
> from other sources. I realise that this is a judgement call, and
> sometimes I struggle to get the balance right. Is this a situation
> where people would agree that it is warranted?
>
> I would still appreciate some feedback as to whether my proposed
> solution would work.
>
> Thanks
>
> Frank
>
Hello Frank,
Stock on-hand quantity = goods inwards +
goods returned from customers -
goods invoiced -
goods returned to suppliers due to defects.
Available stock quantity = stock on-hand -
goods ordered.
The hassle you have in a multi-user environment is when User "A" takes
an order from Customer "A" for Product "A" and at the same time User
"B" takes an order from Customer "B" also for Product "A".
Both users will quote the same value for quantity available (or
quantity on-hand if you don't keep track of orders).
Most companies rank their customers according to internal policy. For
example, customers who pay their bills late could be given a lower rank
than those that pay on time. Some customers won't accept partial
deliveries. Etc.
So, if you just take orders you can run a batch process to convert
orders into invoices and read your orders according to customer ranking
criteria.
By running a batch process, with a suitable "locking" mechanism to
avoid the process being run twice at the same time, the maintenance of
stock on-hand quantities is run in a single instance, you won't over
ship and it won't become negative.
I think this method will work on all three databases cited.
HTH,
Rob
On Wed, Oct 26, 2016 at 3:42 AM, Frank Millman <frank@chagford.com> wrote: > I am designing an inventory application, and I want to ensure > that the stock level of any item cannot go negative. One way to do this is to use only transactions at the SERIALIZABLE transaction isolation level to maintain and query this data. When you do that, you can write the transactions as though each would only ever be run by itself, and if a concurrent transaction would cause incorrect behavior you will get an error with a SQLSTATE starting with "40", and you can retry the transaction from the start. For applications like you describe, this often performs better than approaches which use blocking locks (assuming proper configuration and reasonable indexes). https://www.postgresql.org/docs/current/static/transaction-iso.html Logically, the problem is similar to the overdraft protection example here: https://wiki.postgresql.org/wiki/SSI#Overdraft_Protection Basically, you need some way to catch serialization failure errors and retry the failed transaction from the start, and that frees you from worrying about where race conditions exist and covering each one individually. If you want to use a less strict isolation level, you need to either promote the conflict from read-write to write-write by using SELECT FOR UPDATE or you need to materialize the conflict. The latter could be accomplished by maintaining a total within any transactions modifying the detail (either from triggers or application code), which will cause a write conflict if two transactions try to update the same total at the same time, or by using explicit locking controlled from the application. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
> I am designing an inventory application, and I want to ensure that the stock level of any item cannot go negative.
Thanks to all for some really great replies. Much food for thought there.
As mentioned previously, I am trying to avoid using PostgreSQL-specific techniques, as I need to support sqlite3 and SQL Server as well.
There is an additional complication that I forgot to mention in my original post.
For costing purposes, I want to run a FIFO system. This means I have to maintain separate entries for each receipt of stock, and allocate any sales of stock against the receipts ‘oldest first’.
Assume the following purchases -
2016-06-01 qty 5 Unit price $5.00
2016-06-02 qty 10 Unit price $5.50
2016-06-03 qty 15 Unit price $6.00
Quantity on hand after the third purchase is 30. Whether this should be maintained as a total somewhere, or derived from totalling the receipts, is a matter for debate, but I think that it is not relevant for this discussion.
Then assume the following sales -
2016-06-11 qty 8
2016-06-12 qty 12
2016-06-13 qty 16
The first sale will succeed, and will record a ‘cost of sale’ of (5 x $5.00) + (3 x $5.50).
The second sale will succeed, and will record a ‘cost of sale’ of (7 x $5.50) + (5 x $6.00).
The third sale must be rejected, as there is insufficient stock.
This is how I propose to achieve this -
CREATE TABLE inv_rec
(row_id SERIAL PRIMARY KEY,
product_id INT REFERENCES inv_products,
rec_date DATE,
qty INT
unit_price DEC(15, 2));
CREATE TABLE inv_alloc
(row_id SERIAL PRIMARY KEY,
rec_id INT REFERENCES inv_rec,
qty INT);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
VALUES (99, ‘2016-06-01’, 5, 5.00);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
VALUES (99, ‘2016-06-02’, 10, 5.50);
INSERT INTO inv_rec (product_id, rec_date, qty, unit_price)
VALUES (99, ‘2016-06-03’, 15, 6.00);
The sales will be handled at application level. Here is some pseudo code -
qty_to_allocate = sale_qty
cost_of_sale = 0
BEGIN TRANSACTION
SELECT a.row_id, a.unit_price,
a.qty + COALESCE((SELECT SUM(b.qty) FROM inv_alloc b
WHERE b.rec_id = a.row_id), 0) AS balance
FROM inv_rec a
WHERE a.product_id = 99
AND
a.qty + COALESCE((SELECT SUM(b.qty) FROM inv_alloc b
WHERE b.rec_id = a.row_id), 0)
> 0
ORDER BY a.rec_date
FOR UPDATE
for row in rows:
if row.balance >= qty_to_allocate:
INSERT INTO inv_alloc (rec_id, qty)
VALUES (row.row_id, –qty_to_allocate)
cost_of_sale += (qty_to_allocate * unit_price)
qty_to_allocate = 0
else:
INSERT INTO inv_alloc (rec_id, qty)
VALUES (row.row_id, –row.balance)
cost_of_sale += (row.balance * unit_price)
qty_to_allocate –= row.balance
if qty_to_allocate: # i.e. insufficient stock
raise exception and ROLLBACK
else:
COMMIT
My main concern is that this should be robust.
A secondary concern is that it should be reasonably efficient, but that is not a priority at this stage. If it became a problem, I would look at maintaining a ‘balance’ column on each ‘inv_rec’.
Comments welcome.
Frank
On Thu, Oct 27, 2016 at 1:37 AM, Frank Millman <frank@chagford.com> wrote: > As mentioned previously, I am trying to avoid using PostgreSQL-specific > techniques, as I need to support sqlite3 and SQL Server as well. The SERIALIZABLE transaction isolation level is portable. It it part of the SQL standard (and has been since the beginning), and is supported by just about every database product, including SQLite and SQL Server. (In fact, you have to go well out of your way for SQLite transactions *not* to be SERIALIZABLE -- https://www.sqlite.org/isolation.html ) > For costing purposes, I want to run a FIFO system. This means I have to > maintain separate entries for each receipt of stock, and allocate any sales > of stock against the receipts ‘oldest first’. The two ways of doing this which spring to mind are window functions (supported by PostgreSQL and SQL Server, but not SQLite) and cursors (supported by most database products, including the three you mention). -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company