This is why understanding database concurrency control is important

the two articles I found

[guilhermelopes]

Please, never lose your calmness and bullshit-free type of teaching.

No music, no intro, no merchandise, no hype over things, just pure calm content.

[yuris]

i would add that if you're really working with some kind of data that may be changed by 100's of users concurrently, you probably want to serialize all the writes to a queue, and have workers perform updates. that's because with either optimistic writes or transactions users could be always getting errors because the resource is stale or locked which forces them to try and try again and put unnecessary load on the system. so basically for reads you just read the database, but for writes those actions (+1) are added to queue and performed by backend separately, you may use batching and so on. this way your user actions will be preserved and still influence the outcome

[michaelkhalsa]

Use transactional records, and a db trigger to update summary values. This also gives you an audit and point in time queries. Next use claims for sensitive information such as an order or purchase order which only one person should be editing at a time, along with a mechanism for one party to release the claim of another. This has an additional benefit of communicating who is doing what. These two approaches have served me well over the decades. Also only updating fields that have actually changed, of which proper db design helps.

[developingWithPaul]

It's important to note that count-fixed-1 only works like you would expect in postgres and not other RDBMS. This is due to the postgres transaction level setting that doesn't allow for "dirty reads"

[amalsalim]

i just needed a simple explanation... after 30mins on gpt, this helped. thank you!

[randomforest_dev]

great stuff! I always like your bite-sized short-to-the-point real-world-issues tutorials.

[gbroton]

Understanding the isolation levels is a key here. And it is worth to mention that different database engines can implement the same isolation in a very different way.

[ghost]

There are much more concurency issues to handle, other than lost updates. It would be great to either cover them in future or push people to learn about them by themselfs.
Unfortunetely, optimistic locking will not save you from phantom reads. I think if anyone wants to become backend developer, those problems and how to solve them are very important

[ultrahash]

Maybe ur channel is what I ever needed for the questions I get from time to time while working on some projects or maybe while thinking. Thanks for the awesome content/experience sharing.

[tmanley]

Another way to approach this could be to store new records for each count instead of updating a single record with a count value. This has its limitations as well, but it skips the entire race condition scenario because you're always just appending a new record and the sum of those records is the count. It also preserves history. This could get hairy with amount of records you create but there's ways to mitigate that as well (e.g., having a count value on the record so that you could delete past records and add that total to some arbitary record if you didn't care about history).

Another thing to consider. If you're using SELECT FOR UPDATE, be careful how you structure your sql statements because you can run into deadlocks. If you're using SELECT FOR UPDATE like a queue (not related to Cody's counting example), you can use SKIPPED LOCKED and the transaction won't wait for that resource to be freed, it will just move onto the next one.

[maddriven]

yow this reservation series is really awesome, the progression from simple reservation to advance database concept is amazing! great content and very smart of you. thanks.

[Arzen]

We use a similar approach. When somebody request a data eg.: user data in our admin site, we send a checkoutstamp alongside the data.
If we want to update the user data for some reason we have to propvide the checkoutstamp alongside the updated value(s).
If the checkoutstamp is older than the updated_at field on the table itself, we send an error to the user that somebody have already updated this record, please refetch the screen. On the FE we have a toast notification showing the errors but we could potentially retry the update if needed based on the error code sent to the FE.

These other approaches are very interesting, I am not a DB expert neither but I love seeing different solutions for the same problem and their trade-offs. Great video! :)

[oSpam]

Great video, I love this new “series” I suppose you could call it. Really helpful and really important too, glad you opened my eyes about this

[uzair]

8:15 working in dynamodb you still have to bring your own locking mechanism. We have implemented optimistic locks using updatedAt field as you described. (I must say that implementing this for existing system was quite some work i.e you have to make sure that updatedAt read from db is not altered before update statement, bcz we need to compare with value in table)

Second thing, for inc/dec you'll have to use atomic counters not traditional way to increment/decrement (because of multi-node or partitions, not sure if thats correct word to use)

Third thing, I believe transaction writes is also required (which has double cost compared with single read/write)

[uzhnmvk]

I learned a very similar problem in uni with the readers/writers problem in Operating Systems . Glad to see a real world problem with it

[mmmike]

I love the format of this, you're learning and sharing your process. Nice one, hope to see more of these!

[MrSoloDev]

You could simply borrow from event sourcing principles where the database acts as a ledger. With any ledger, you cannot delete or modify any existing records. You can only add. This solves any race condition. For reading the data, you can borrow from CQRS pattern where the sum is read from a different table or database which is asynchronously updated, ideally through event based architecture.

[gadgetboyplaysmc]

I encountered this a crap ton at my job a few weeks ago. Examples are reordering, order number (checking the max and incrementing by 1), reservation, and basically operations where you don't do atomic operations and have to involve the application layer for a write.

Kinda had to figure it all out on my own though. I wish you talked about all this sooner haha. This is really useful.

[josecanciani]

Both option 1 and option 2 will lock the row: option 2 is slower because its two different statements and also there’s the latency between app and db server.

[AlleinArk]

holy, thanks for this video, I really need this logic for future postgress project.