There is a record that is being written to 20 times through Jobs (mainly to a json column) , concurrently.
I use DB::transaction to ensure that every read and write to that record is atomic, so that I don't get bad data.
However, there are cases where the final json object only has 18, 19, etc... objects inside of it, even though all 20 jobs have ran to completion.
One thought that occurred to me was that multiple Jobs might have read the same record in memory, and when it came to write, they somehow wrote over each other? But that can't be, because both the read and write is wrapped in a transaction.
DB::transaction(function () use ($result) {
$existing = PropertyResult::where('property_id', $propertyId)->first();
if ($existing) {
$record = json_decode($existing->record, true);
$results = $record['results'];
$results[] = $result;
$record['results'] = $results;
$existing->record = json_encode($record);
$existing->save();
}
});
One thing you need to know about transactions, is how they work.
They are session based, and by session means that each time you let a job login to the database, it creates a unique session.
That means that if job #1 starts a transaction, job #2 will be unaware of the things job #1 has done, until job #1 stores the changes in the database with a commit. Only the session making changes to the data will have access to it before it has been properly stored with a commit.
@Tray2 I see, thanks for the reply. So then, do database transactions not guarantee ACID properties? Because this is a case where atomicity is broken (in your example, transaction 2 is reading right AFTER transaction 1 reads, when it should only AFTER transaction 1 writes)
@ad45 As long as it's in the same database session it has access to any and all un-commited changes done in that session, all other sessions will not see it until the first session has commited the changes.
@Tray2 I see. I thought transactions would guarantee me:
READ WRITE, READ WRITE, ...
in any arbitrary job order, but I guess not. Would I be correct in thinking so? Thanks for the commitment to my question BTW
@ad45 Let's say that you log into the database, starts a transaction but don't commit the changes perhaps due to a slow running process. Then I log in and try to query the database, this will work, but I will not be able to see any of the changes you have made, until your process commits the data.
Transactions are usually used to ensure the integrity of the data, take a bank transfer for example,
If one of the steps fail, both of the changes should be rolled back, so that the account owner of account 1 doesn't loose the money, and so that the account owner of account to doesn't get the money of the failed transaction.
@Tray2 I see. I understand it much more clearly now, thanks!
So to ensure that the each transaction/job doesn't read the same object in the DB,
I need to use lockForUpdate() which prevents both reads and writes as opposed to
sharedLock(), which only prevents writes.
Would I be right in thinking this?
The resulting code block would look like this:
DB::transaction(function () use ($result, $propertyId) {
$existing = PropertyResult::where('property_id', $propertyId)->lockForUpdate()->first();
if ($existing) {
$record = json_decode($existing->record, true);
$results = $record['results'];
$results[] = $result;
$record['results'] = $results;
$existing->record = json_encode($record);
$existing->save();
}
});
And this would ensure the
READ WRITE, READ WRITE, ...
chain I was thinking of?
@ad45 yes kinda, you need to filter out the already updated, the ones being updated, and the one about to be updated. Depending on your RDBMS you can use SKIP LOCKED to skip the ones being upåpdated.
https://dev.mysql.com/blog-archive/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/
@Tray2 hmm, ok, I'll read up on that link you just sent, but I'm still not sure what I'm missing
Doesn't the lockForUpdate() make it so the reads from the DB json object aren't stale, and the code block being wrapped in DB::transaction ensure that both the read and write is being done atomically? So there are no concurrency issues?
or am I overlooking something??
@ad45 Lock for update, locks the row(s) for update in that transaction, preventing others from updating it.
This mean that no one else can make updates to the record while it is locked, only the session that has taken out the lock in the first place.
It seems that you are quite new to databases, or at least aren't well versed in how they function. I would suggest that you don't use json in your tables at all if it can be avoided, it will save you a lot of headache later, and improve your performance greatly.
I suggest giving these posts a read through or two.
https://tray2.se/posts/sqlerrm
https://tray2.se/posts/database-design
https://tray2.se/posts/database-design-part-2
https://tray2.se/posts/properly-formed-foreign-keys-are-your-best-friends
There are a few others there as well, but I think they are a bit over your head at the moment.
@Tray2 holy smoke stacks, you got your own website??
Anyways, BIG thanks for sticking with me throughout this thread, I will definitely give what you linked a read!
good example of when not put multiple things in the same json field.
I would probably move these json objects to their own rows, linked to the parent row.
otherwise you are going to have to start using locks to block updates until another process has finished with it.
@Snapey Or even better, don't use json in the database :)
Add lockForUpdate in builder query, it lock row until your transaction execute
$existing = PropertyResult::where('property_id', $propertyId)->lockForUpdate()->first();
Please or to participate in this conversation.