Community mailing list archives

community@mail.odoo.com

Re: Odoo Performance and concurrency locks

by Olivier Dony <odo@openerp.com> - 10/06/2015 12:58:49
On 10/01/2015 02:06 PM, Nils Hamerlinck wrote:
> Case closed:
> https://github.com/odoo/odoo/commit/94a0bbed0a94e4bd34965c1466615d225ac8e509
>
> With comment from @odony in https://github.com/odoo/odoo/issues/8585:
>
> "The KEY SHARE/NO KEY UPDATE locks in PG 9.3+ do behave as expected in the
> sense that they do not block each other. However it turns out that PostgreSQL's
> heuristics for detection non-serializable patterns can still trigger when a
> long-running transaction repeatedly acquires the same KEY SHARE lock on a
> record that was concurrently updated. This makes the whole thing less useful
> than we hoped. We're still checking whether PG workarounds are possible in
> 7.0/8.0, but for 9.0 the easiest course is to move the field outside |res.users|."


Quick update: after discussing this with the PostgreSQL developers, it appears 
to be a behavior change/regression introduced in 9.3 [1], and fixed in upcoming 
9.5 [2]. So 9.3 brings many performance improvements for concurrent 
transactions, but actually makes this particular case worse.

The problem has to do with one transaction repeatedly acquiring the same 
(share) lock, with another transaction altering the locked record in the mean time.
This particular configuration occurs when a batch-based transaction T1 performs 
a series of inserts/updates (typically a background job or mass import) and a 
small update transaction T2 (typically a login) comes in the middle and updates 
a record used by T1 via a FK.

Prior to version 9.3, T2 would have had to wait (so the FOR UPDATE NOWAIT guard 
would have stopped it and prevented any trouble), and after the bugfix (in 
9.5), neither wait nor error occurs.

There does not seem to be any workaround for this, but there is a chance the 
9.5 fix could be backported to 9.4/9.3, although there was some concern with 
backwards-compatibility. To be continued...


[1] 
http://www.postgresql.org/message-id/flat/20131121200517.GM7240@alap2.anarazel.de#20131121200517.GM7240@alap2.anarazel.de
[2] 
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=05315498012530d44cd89a209242a243374e274d