c2c-oerpscenario team mailing list archive

["Olivier Dony \(OpenERP\)" <746620@xxxxxxxxxxxxxxxxxx>]

On 06/01/2011 07:46 PM, Kyle Waid - http://www.gcotech.com wrote:>
> You said the error is of low happening, but it is a terrible issue for
> us.

Indeed there are usage patterns where this can occur much more
frequently, for example if you are routinely importing batches of
documents from external systems in multiple transactions, etc.
For automated transactions, the issue can be mitigated by implementing a
smart error handler that will replay the transaction if it fails for
this reason, with only a lower throughput as a consequence.
But for human users who need to use the system at the same time this is
definitely gonna be an issue.

As a workaround you could also try to patch ir.sequence.get_id() as
Raphael suggests: retrying a few times to grab a sequence number, to
reduce the probability of failure.

> If you can provide an outline that suits you, I can have my
> resources develop the fix to your specs and you can review/integrate it
> into the system. I am totally for making this a better product for all
> of us, not just us.

We would of course be glad if you can implement a solution for this and
provide feedback and testing of the solution, especially in an
intensively-concurrent system like yours :-)

Our R&D discussions on this subject yielded 2 possible solutions.
* [Option 1]
Implement 2 types of ir.sequences in the core. One is the current
implementation (READ next-number then INCREMENT it in our own
ir.sequence table, within a fail-fast serializable transaction thanks to
SELECT FOR UPDATE NOWAIT). The other would be a postgres-sequence backed
implementation. Using postgres-sequences guarantees fast, non-locking
generation of unique numbers, with the disadvantage that you can have
gaps due to transactions that rollback[1]. Basically the part where we
increment the sequence number would be replaced by 'select nextval()'.
Some work also needs to be done to implement the other features of
ir.sequence, like displaying the next expected number (curval()+1),
being able to reset the number (setval()), etc.
It looks like the module from OpenLabs could be used as a starting
point/inspiration, but some features are missing in it, and this should
be directly integrated into the core (in ir.sequence).

* [Option 2]
The problem with Option 1 is that having 2 kinds of sequences introduces
more complexity in the core, more special cases and exceptions, code
maintenance overhead, etc. So perhaps we could completely replace the
current implementation with the postgres-sequence one described in
Option 1. The consequence would be that we need to move the
responsibility of ensuring gapless sequences to the business logic
layer, e.g. the invoice creation code. This could be achieved for
example by always grabbing the sequence number at the very end of the
transaction, or by forcing a commit right after it. This is probably
simpler, but perhaps more difficult to master/understand for
contributors, or not 100% safe, so it has downsides too.

In any case, Kyle, both options at least require a full implementation
of postgres-backed sequences, so any merge proposal[2] (against
openobject-server/trunk) in that direction would be a very useful and
appreciated contribution. The implementation details could be discussed
on the current bug or via the merge proposal comment system.


[1] See "important" paragraph at bottom of
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html
[2]
http://doc.openerp.com/v6.0/contribute/15_guidelines/contribution_guidelines.html#merge-proposals-patches

-- 
You received this bug notification because you are a member of C2C
OERPScenario, which is subscribed to the OpenERP Project Group.
https://bugs.launchpad.net/bugs/746620

Title:
  Implementation of faster gap-tolerant sequences

Status in OpenERP Server:
  Confirmed

Bug description:
  OpenERP is classified as an Enterprise class software package. Meaning
  that more than one person, at least 2 should be able to use the system
  simultaneously. Like packers shipping products, taking orders,
  reserving products. I find that in fact, it is not possible for
  creation of stock moves simultaneously.

   Say I am importing orders from a shop. It is creating stock moves  because the order is paid. 
  At the same time I am shipping products which is normal for an ERP system.
  I might also import orders from a separate shop say a POS system.
  It is not possible!
  [2011-03-31 13:10:47,657][midwestsupplies] WARNING:stock.location:Failed attempt to reserve 1.0 x product 1669, li
  kely due to another transaction already in progress. Next attempt is likely to work. Detailed error available at D
  EBUG level.  
  OperationalError: could not obtain lock on row in relation "ir_sequence"
  Two different errors.
  I  can only perform one action at a time!
  What happens is that any time the program calls for a stock_move it will lock the stock_move table so no other process can access it which means that you cant do hardly anything unless its done individually. Now say with a MS system, or any Enterprise system would be able to handle many simultaneous actions but this program has a serious architecture flaw to not be able to support this.