I've ran into a problem that I've solved but I'm still wondering if there's a chance that something weird happens.
Gonna try to be succinct: I made an API to share writings on social media. When a writing has been shared on a medium, I update a db field which stores which medium and when it has been shared in a json format. So basically I read and decode the json, add the current medium, encode and save.
The thing is, when I call APIs asynchronously, they seem to override each other. Each API doesn't catch up with the other ones, and when they decode/encode the json, media are missing in the field.
What I did to solve this issue is, right before reading the field, I refresh my eloquent model ($this->refresh();), make my changes, then save. It seems to work well.
Now I'm still wondering, is there a chance than between the refresh and the saving, some other API actually writes something in the field? I would have to be very unlucky, but in theory, it seems to be that it's possible. In that case, I would lose some data.
So I'm wondering, any way to solve this on the php/eloquent/laravel side? Use DB transaction and pessimistic locking? I'm asking coz I'm not sure I understand those two things correctly.
I know I could queue the API calls and process them synchronously, but that's not the question here. I'm trying to understand and improve my knowledge of this issue with locking, transactions, and having different async calls updating the same row/fields without losing data.
Thanks in advance!