Skip to Content
  2. Mailing Lists
  3. Contributors
  4. Re: ERROR: could not serialize access due to concurrent update (case using Job Queue)

Archives

Contributors

Re: ERROR: could not serialize access due to concurrent update (case using Job Queue)

Re: ERROR: could not serialize access due to concurrent update (case using Job Queue)

Re: ERROR: could not serialize access due to concurrent update (case using Job Queue) 

On 3/4/24 15:07, Kitti Upariphutthiphong wrote:

>

> I was thinking if there are anyway to unlock the table at least 

> temporarily during execution. But as far as I researching, I still 

> can't find the way.

I don't think "temporary unlock" is possible, or advisable, but another 
way is to lock the table as late as possible, so, closest before 
commit() of your transaction. That way, the time that your lock persists 
is smallest and the chance for conflict is lowest (the lower you get it, 
the more viable it will be to just rely on RetryableJobError for the 
small amount of cases where a conflict arises).

A strategy for this can be to do the thing that locks, and right after 
that, fire a new queue job that will do the rest of the stuff.

We've had success with this in cases whereby you have for example:

Process payment transaction job:

1. Start database transaction
2. Create payment transaction
3. Confirm sale.order, which may generate a stock.picking and confirm 
it, thereby locking quant table
4. Generate invoice (during this time some rows in quant table will 
still be locked, conflicts can occur)
5. Send out invoice by mail (during this time some rows in quant table 
will still be locked, conflicts can occur)
6. End of database transaction (commit)

Instead, you will add "with_delay()" around steps 4+5 so that these are 
run in a separate queue job, for which the locking does not apply.

Of course this requires refactoring of core or custom code so it might 
not be a viable solution in your case.
by Tom Blauwendraat - 03:26 - 4 Mar 2024

Reference

  • ERROR: could not serialize access due to concurrent update (case using Job Queue)
    Dear community,

    We have a case that needs to process a lot of transactions (500k arrive on the last day of month). And so we rely on our best friend OCA's Job Queue and have things run in parallel.

    Most process are OK, but the one creates stock picking, jobs can't run in parallel because there is a concurrent issue on the "stock_quant" table, which looks like many separated job is updating the same record.

    bad query:  update stock_quant set reserved_quantity = 10.00 ... where id in (100)
    ERROR: could not serialize access due to concurrent update
    bad query:  update stock_quant set reserved_quantity = 10.00 ... where id in (100)
    ERROR: could not serialize access due to concurrent update
    .....

    Concurrent updates are very common issues we always face. How do you get around with this problem?

    Thank you,
    Kitti U.








    by Kitti Upariphutthiphong - 02:16 - 4 Mar 2024