openerp-community team mailing list archive

[Gustavo Adrian Marino <gamarino@xxxxxxxxx>]

Juan, Eric:
According with coments in code, the overall transaction strategy has
changed from a default ISOLATION_LEVEL_REPEATABLE_READ as default plus
special code for some critical situations (login, stock reservation, cron
updates: SELECT ... FOR UPDATE in order to early lock the tables) to a new
strategy (PostgreSQL >= 9.1) where ISOLATION_LEVEL_READ_COMMITTED is used
as default (leading to a higher exception rate), compensated by a standard
retry policy on every transaction with 3 attempts randomly delayed.

Conceptually, I think the new strategy is essentially correct.

Nevertheless, there are some code that was not adjusted to take advantage
of the new strategy. For example, in the case of cron the standard process
reading task to be executed from the list of waiting tasks was rewritten
and several problems were solved with the new strategy (even sometimes some
concurrency conflicts are still visible on the log)

For the case of the procurement scheduler most of the problem is that the
whole loop exploring products with low stocks is run as single long
transaction with the standard setup. There is a parameter on cron task
activation (line 35, addons/procurement/schedulers.py) called new_cursor
that can be pased on the cron record to activate a per product transaction
that reduces dramatically contention on schedulers. Unfortunatly, the code
is not well designed and you must be carefull of using the name of the
database to setup the use_new_cursor flag, instead a simple True or False

In the case of stock, the logic does not reflect the new strategy and still
asumes the old way of doing things. If you check in addons/stock/stock.py
line 426 in V7, in case of errors it simply waits for a retry from the user
side (in order to activate the new retry logic, the postgresql exception
should be thrown all the way out). I guess it was simply overlook on
strategy change. From my point of view, using the retry strategy, it makes
little sense to still use the SELECT for UPDATE, and probably just retrying
is the best tradeoff to get the most transactions completed at the end of
the day

On the login case, contention is just a matter of updating last login date,
and in case of concurrent operations, simply a log message is recorded
without aborting the transaction, what means that there is little damage.

>From Eric's original questions, I believe the change in concurrent
transaction strategy is a step in the right direction, and we should fix
any code that stills asume the old behaviour.

Commiting often is allways something good to achieve (unless the slower
transaction commits set a limit on this). This should be the standard
behaviour on procurement

I believe the only reason those remaining SELECT FOR UPDATEs are just code
not reanalyzed under the new transaction strategy. See the history of cron
modifications (I could not find the exact bug #) where you can see that
after many iterations, the last version is a lot simpler than the original
implementation and more important, free of subtle bugs from unconsidered
situations

I suggest to modify whatever is needed to reduce as much as possible any
user requested retry, trying to deal with concurrency problems from within
the server code.

My 2 cents
Gustavo Adrian Marino

2013/5/16 Juan Cristobal Lopez Arrieta <juancristobal@xxxxxxxxx>

> We deal with incoming interfaces from 2 other systems (java and php) and a
> realtime envirnment, and some days is hard to analize a log or have some
> decent monioring and the likes... under some circunstances the
> concurrency/serialization problem produces roolback of complex transactions
> that involve outgoing interfaces with other systems.... it can be easly a
> nightmare...for nothing :-)
>
>
> On Wed, May 15, 2013 at 10:40 PM, Eric Caudal <eric.caudal@xxxxxxxxxxxxxx>wrote:
>
>>  Let's say that before going to some extreme solutions (we are thinking
>> of strongly modifying as well the 3 mentioned files), I would rather find a
>> standard one.
>>
>> We are not dealing with so many transactions (around 3000 to 5000 Sales
>> order lines per day from the website) that I am surprised this has not been
>> issued/solved/discarded before.
>>
>>
>>
>>  Eric Caudal*CEO*
>> --*Elico Corporation, Shanghai branchOpenERP Premium Certified Training Partner *
>> Cell: + 86 186 2136 1670
>> Office: + 86 21 6211 8017/27/37
>> Skype: elico.corperic.caudal@elico-corp.comhttp://www.elico-corp.com
>>
>> [image: Elico Corp]
>> On 05/16/2013 11:06 AM, Juan Cristobal Lopez Arrieta wrote:
>>
>>  The concurrent updates are giving us so much trouble, that i have been
>> thinking to just comment the update for the login in res.user !!
>>  or  replace it with an insert into another table (ie: res_user_login)
>> ....
>>  Sounds too much?
>>
>>
>> On Wed, May 15, 2013 at 10:00 PM, Eric Caudal <eric.caudal@xxxxxxxxxxxxxx
>> > wrote:
>>
>>>  We are in 7.0: we are checking whether this behavior in 6.1 is already
>>> available in 7.0 but so far not conclusive.
>>> By the way, we are using the latest python-psycopg.
>>>
>>>
>>>  Eric Caudal*CEO*
>>> --*Elico Corporation, Shanghai branchOpenERP Premium Certified Training Partner *
>>> Cell: + 86 186 2136 1670
>>> Office: + 86 21 6211 8017/27/37
>>> Skype: elico.corperic.caudal@elico-corp.comhttp://www.elico-corp.com
>>>
>>> [image: Elico Corp]
>>>   On 05/15/2013 06:01 PM, Leonardo Pistone wrote:
>>>
>>>  Dear Eric,
>>>
>>>  I didn't have time to investigate, so that might not be relevant, but
>>> I've seen this commit on 6.1 recently:
>>>
>>>
>>> http://bazaar.launchpad.net/~openerp/openobject-server/6.1/revision/4337
>>>
>>>  Leonardo
>>>
>>>
>>> On Wed, May 15, 2013 at 11:55 AM, Eric Caudal <
>>> eric.caudal@xxxxxxxxxxxxxx> wrote:
>>>
>>>>  Hi,
>>>> We have a case where our procurement scheduler (with
>>>> procurement_confirm and action_assign) is blocking other transactions (such
>>>> as website synchronization) or vice versa with an error such as "Serialized
>>>> Access due to concurrent update".
>>>> So far we have made some researches and we discover that this is maybe
>>>> due to an error " Repeatable Read Isolation Level", documented here:
>>>> http://www.postgresql.org/docs/9.2/static/transaction-iso.html
>>>>
>>>> A simple case would be like this:
>>>>
>>>> Session1: BEGIN;
>>>> Session2: BEGIN;
>>>> Session1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>>>> Session2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>>>> Session1: SELECT * FROM tunnels WHERE un = 1 FOR UPDATE;
>>>>  un | password
>>>> ----+----------
>>>>   1 | bugsy
>>>> (1 row)
>>>>
>>>> Session1: UPDATE tunnels SET password = 'malone' WHERE un = 1;
>>>> Session2: SELECT * FROM tunnels WHERE un = 1 FOR UPDATE;
>>>> ...waits on Session1's lock...
>>>> Session1: COMMIT;
>>>> Session2: ERROR:  could not serialise access due to concurrent update
>>>>
>>>> In current OpenERP code, we have check and there are 3 main places
>>>> where this kind of transactions are used:
>>>> base/res_users.py for function login, line 334,
>>>> stock/stock.py, for function  _product_reserve line 442
>>>> stock_prodlot_wizard/prodlot_wizard, class
>>>> stock_move_split_lines_exist, line 279
>>>>
>>>> My questions:
>>>> 1. Is it something mandatory in standard code? What if we by
>>>> pass/modify them
>>>> 2. Would it be an option to commit more often?
>>>> 3. Is there any other reason why we could have this concurrent update?
>>>>
>>>> Suggestions are more than welcome!
>>>>
>>>> --
>>>>
>>>> Eric Caudal*CEO*
>>>> --*Elico Corporation, Shanghai branchOpenERP Premium Certified Training Partner *
>>>> Cell: + 86 186 2136 1670
>>>> Office: + 86 21 6211 8017/27/37
>>>> Skype: elico.corperic.caudal@elico-corp.comhttp://www.elico-corp.com
>>>>
>>>> [image: Elico Corp]
>>>>
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~openerp-community
>>>> Post to     : openerp-community@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~openerp-community
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>
>>>
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~openerp-community
>>> Post to     : openerp-community@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~openerp-community
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>> --
>> Juan Cristobal Lopez Arrieta
>> http://www.openerp.com/node/560
>> skype  : jclopezar
>>
>>
>>
>
>
> --
> Juan Cristobal Lopez Arrieta
> http://www.openerp.com/node/560
> skype  : jclopezar
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~openerp-community
> Post to     : openerp-community@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~openerp-community
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 

Gustavo Adrian Marino

Mobile: +54 911 5498 2515

Email: gamarino@xxxxxxxxx

Skype: gustavo.adrian.marino