openerp-community-leaders team mailing list archive

[Albert Cervera i Areny <albert@xxxxxxxxxxx>]

[Sorry for the cross post, but although not directly related to framework, I 
think it partially is because it's both an important issue and might popup in 
other places too.]

Although OpenERP uses serializable isolation level for all transactions this 
doesn't solve the problem of assigning two or more stock moves when only one 
should be possible.

Let me explain:

Say you have one remaining unit of product X at location Stock. There are also 
two outgoing pickings that require this product. Two users may "Check 
Availability" at the same time and both stock moves would be marked as 
assigned although only one should.

This is true because check_assign() function in stock.picking doesn't try to 
avoid that. What the function does is:

- Search how much stock we have using _product_reserve() from stock.location
- If value is above zero, assign it

So if two transactions do the same at the same time both will assign the move. 

This may seem unprobable but I don't think it is given that:
- Checking available stock requires a sequential scan on stock_move table 
which may be really large.
- It's not necessary that both reads happen at the same time, it's only 
necessary that both *transactions* happen at the same time. In some cases ther 
might be large pickings which would take a long time to process. In others the 
process may be part of another automated process that does many other things 
and  transaction time may be very long.

If my worries are true, I'd go for solving the issue in check_assign() 
function. My proposal would be to create a new table in postgres just to 
handle the lock (ie 'stock_move_lock').

The first thing check_assign() function should do is "LOCK stock_move_lock" 
which would be release at the end of the transaction (this is simply how it 
works in Postgres, there's not UNLOCK mechanism).

The proposal of using a new table for locking is base on a two facts:
- We don't want to lock 'stock_move' because other processes who only want a 
value to be shown to the user shouldn't be locked. There's nothing critical in 
there. Those who reallly want to assign the stock must use check_assign() 
anyway.
- We don't want a semaphore because this would not allow running several 
openobject servers against the same database.

Comments? Do you think I missed something?

-- 
Albert Cervera i Areny
http://www.NaN-tic.com
Mòbil: +34 669 40 40 18