Propel Row Lock Batch Update Problem

Question

Hello, I am using Propel as my ORM.

I need to do a batch update to a table with the following fields:

ID
Company
Assigned

The update will take in an array of Company and set the Assigned field to 1.

The issue is there might be 2 batch updates that occur at the same time. So when that happens, I will have to accept one update, and reject another. Is there anyway to lock the table for one batch update? Or is there a better solution?

Answer 1

Here's the solution I found.

Instead of locking it, a better way is to use the following equivalent query when doing the batch update

update table 
set Assigned=2
where Assigned=1
and Company in {company1, company2}

Now, if the number of rows returned is not the same as the number of Company selected, then the whole operation should be rolled back.