c2c-oerpscenario team mailing list archive

[Raphaël Valyi - http://www.akretion.com <746620@xxxxxxxxxxxxxxxxxx>]

Olivier,

I agree with what you say and agree that serialized isolation is good,
specially for sequences where we don't want holes (at least for some of
them) but:

currently, if I try to say manually create a procurement while the scheduler
is running and potentially busy at 100% bloating the database of new
procurements, locking the ir_sequence table, then I'll directly be kicked
out with a cannot lock ir_sequence table.

WHY, can we not just wait a few milliseconds for the lock over ir_sequence
to be released and then, during the few millisecs that here is no locking,
then I get my own new sequence number?

I just cannot understand why there is the "NOWAIT" instruction. On Twitter
Tryton lead told me that NOWAIT doesn't make any difference when using the
"serialized isolation level", the one we used. Is that true? Why? In the
strange case where that would be true, can we no fake a few retry at least
in the sequence.py code to avoid systematically falling into that "cannot
lock ir_sequence table" bug ?

Thanks.


On Wed, Jun 1, 2011 at 2:08 PM, Olivier Dony (OpenERP) <
746620@xxxxxxxxxxxxxxxxxx> wrote:

> Let me provide a bit of context.
>
> Using serializable transactions is not an "architecture flaw", but it is in
> fact the only guaranteed way of ensuring the correctness of a concurrent
> system, and thus the proper handling of critical business transactions [1].
> It is critical for an Enterprise class application to support it.
> Without it, phantom reads. dirty reads, etc. are some of the many random
> issues that could arise, and would cause random critical data integrity
> problems: incorrect stock levels, duplicate use of invoice numbers, etc. [2]
>
> Two cases where this matters in OpenERP are the assignation of sequence
> numbers for documents, and the reservation of products in stock
> transactions. In both cases, OpenERP will currently fail the new
> transaction rather than allow any data inconsistency to occur. This is
> therefore a design decision, not a bug, even if it may have some
> undesirable consequences. It is very important to understand that.
>
> If the failed transaction was the one coming for an interactive user
> session, the user can simply press "confirm" again, and the transaction
> should now pass. The likeliness to have this occur is low, unless the system
> is under very heavy load or some batch process is executing during working
> hours, on the same kind of document. If the failed transaction is one of a
> background batch process, that process should have a proper way to handle
> errors, and should be able to cope with it (yes, many other kind of errors
> could occur, so proper error handling is always needed, regardless)
> (Note: I'm rating this a Medium bug for this reason, not higher)
>
> Now, we also agree that this serializable behavior is not required in
> all cases, and may cause annoyances or lower performance in the cases
> where it is not needed: for example for sequence numbers, this only
> matters if you don't want to allow holes in the sequence. Usually, only
> invoice numbers need to follow a gapless, sequence, so more lightweight
> sequences could be provided. These sequences would not guarantee they
> stay gapless, but would not have the possible concurrency issue.
>
> As a consequence, we would like to introduce a new option for
> "lightweight, gap-tolerant" sequences in the next version of OpenERP
> indeed. This is very similar to the sequence_postgres module from
> Openlabs, which seems a very good alternative for those who need this
> features immediately.
>
> I'm confirming this bug in order to address the development of this
> feature.
>
> I hope this explanation makes things more clear.
>
>
> Technical note:
> PostgreSQL does not in fact implement fully serializable transaction,
> because this is considered too slow/expensive. Instead, PostgreSQL (like
> Oracle and many others) [3] uses "Snapshot Isolation" instead, which does
> not protect against all kinds of inconsistencies, namely "write skew".
> Technically, this can be addressed is by materializing the conflicts or
> promoting the transactions when needed. The usual way to accomplish this in
> PostgreSQL is to use SELECT FOR UPDATE, and this is what OpenERP does for
> critical transactions, like sequence numbering and stock reservation.
>
>
> [1] http://en.wikipedia.org/wiki/Serializability
> [2] http://en.wikipedia.org/wiki/Isolation_(database_systems)
> [3] http://en.wikipedia.org/wiki/Snapshot_isolation
>
> ** Changed in: openobject-server
>   Importance: Undecided => Medium
>
> ** Changed in: openobject-server
>       Status: Triaged => Confirmed
>
> ** Changed in: openobject-server
>    Milestone: None => 6.1
>
> ** Changed in: openobject-server
>     Assignee: (unassigned) => OpenERP's Framework R&D
> (openerp-dev-framework)
>
> ** Summary changed:
>
> - serialized transaction architecture flaw PLEASE COLLABORATE
> + Implementation of faster gap-tolerant sequences
>
> --
> You received this bug notification because you are a member of OpenERP
> Drivers, which is subscribed to OpenERP Server.
> 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.
>

-- 
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.