Community mailing list archives

Re: Odoo Performance and concurrency locks

by Olivier Dony <> - 09/15/2015 14:04:35
On 09/14/2015 06:28 PM, OpenERP Master wrote:
> The part you said in 2a, I can see this at any point while a transaction is
> executing, it does not have to be the beginning.

It does not have to be the beginning. But the login transaction can only abort 
another transaction *if and only if* the other transaction:
  - had started (or restarted, for background jobs) before the login
  - had not taken any shared/exclusive lock on the res.users record yet
  - is going to take a shared/exclusive lock on the res.users record later
    (Shared locks on res.users are most commonly taken via the 'create_uid'
     foreign keys of any record being created/updated.)

If those conditions are not met, the login transaction will execute without 
interrupting anyone, or will simply skip updating the user because it won't be 
able to get an exclusive writer lock (FOR UPDATE).
This definitely requires unusual conditions, and only works before PG 9.3.

After 9.3 the login update will only grab a "FOR NO KEY UPDATE" lock on the 
user (because it does not touch the PK), and the FK will only require a "FOR 
KEY SHARE" lock, so there won't be any possible conflict between them.

Don't take my word for it, read the PG manual and convince yourself using two 
`psql` consoles and isolation level REPEATABLE READ.
It's the best way to really understand the mechanics of transaction isolation.

Upgrading to PG 9.3 is trivial, virtually risk-free and will provide better 
performance, so whenever you see problems with TransactionRollbackError, this 
is your first and most important step.