Pessimistic concurrency control vs Optimistic concurrency control in Database Systems Explained

[@shantanutripathi8407]

"i am taking my umbrella babe, i am taking it..." and thats how you explained pcc so simply and lightly like you always do. Would love to watch some videos around database engineering especially concurrency and locks. Cheeers!

[@isagarsehwag]

Please make more content on CONCURRENCY, I have been trying study different ways different languages are tackling this problem but being a fresh graduate it's to pretty hard to understand this complex topic & you're phenomenal at explaining complex stuff easily with nearly all deep stuff, usually everyone else makes things easy by removing the complex details and that's no fun at all. I have been trying to compare Go's CSP approach, javascript's event loop, swift's GCD approach & the all time popular, conventional threads and locks of java.

[@saadsaleem2467]

Thanks Dear! Now I got the basic technique about pessimistic and optimistic concurrency control. And really loved your style and real life example. Thanks again.

[@raceystacey8902]

Pessimists and optimists both have their place in the world . It was the pessimist that thought of insurance policies and the parachute . It's not good to be so optimistic that you're unrealistic about certain things that could go wrong and be prepared .

[@mmbznt]

Thank you for the explanation. I was reading about serilizabel snapshot isolation in book DDIA but was so consfused. Your explanation made it very clear.

[@maamounhajnajeeb209]

I really like the power you have Hussein keep going man برافو

[@vinny142]

Pessimistic locking wherever possible. I've had cases where I ultimately removed the CRUD rights from a table and replaced it with functions so I could be sure that a lock would be placed and that sufficient data was locked to prevent race conditions (locking a parent row does not stop you updating it's children) Another interesting problem with locks is that for example in HTTP there is no persistent connection between the client and the server so transactions cannot span HTTP requests and neither can locks, so you'r forced to do lokcing with timestamp-flags. Such fun!. So in the interwebs database locking is only useful to stop race conditions within a single transaction and a simple SELECT FOR UPDATE can probablt take care of most of that.

[@hnasr]

You bring an interesting case with HTTP, since HTTP is stateless can’t really acquire lock and perform a long transaction and go on ..

[@lordandee]

​@@hnasr​Agreed with pessimistic locking where possible! However you can certainly obtain commit control over distributed systems connected by HTTP if you use distributed transactions. This would be a great topic to cover actually maybe an idea for another video ;)

[@tyomidi]

This channel really is gold. Love the style!

[@birds352]

Resourceful and amazing video, thanks for time stamping it as well.

[@hnasr]

Brennan J your welcome 🙏 glad you enjoyed the content

IMHO Pessimist: good for database-driven apps Optimistic: good for event-driven apps

[@pauloiscoding]

Deixei o like logo no início do vídeo, mas no final tirei o like pra poder dar like de novo.

[@lonez5228]

AMAZING VIDEO!!! I loved the entire explanation and example! Finally I got how it works! Thanks Hussein Nasser!!!

[@lordandee]

Optimistic control can be very useful when exposing load/save APIs. There can be significant amounts of time between you serving data to a third party application and it attempting to commit changes.

[@hnasr]

This is an interesting use case. let us think about it You want to build a Todo App with multi-user support, you either can design it so that you support conflict or last-in-win kind of a scenario. A) Last-in-win problem 1) User 1 loads the todo list and its - Do laundry - Go Shopping 2) User 2 loads the todo list and the same - Do laundry - Go Shopping 3) User 2 changes the local "Do laundry" to "Do laundry at 3PM" and applies to the server. 4) User 1 changes the local "Do laundry" to "Do laundry at 4PM" and applies to the server, overwriting the "Do laundry at 3PM" from User 2. We might be happy with this result no locking required. B) Conflict detection problem 1) User 1 loads the todo list and a hash of each string - Do laundry (hash A) - Go Shopping (has B) 2) User 2 loads the todo list and the same - Do laundry (hash A) - Go Shopping (hash B) 3) User 2 changes the local "Do laundry" to "Do laundry at 3PM" and applies to the server and sends the original hash (A), Server applies the result to the database and adds a condition only if the hash is (A). the row is updated successfully since the hash matching the original content, success is returned to the user. Generating a new hash (A2) 4) User 1 changes the local "Do laundry" to "Do laundry at 4PM" and applies to the server and sends the original hash (A), Server applies the result to the database and adds a condition only if the hash is (A). no rows are updated because no match for hash(A), a failure is returned to the user with the new content. I think we can include a third case where you actually resolve the conflict somehow if possible.. but that is way out of my league in terms of complexity.. Let me know if there are other use cases that optimistic concurrency control is preferable I'm interested and thanks!

[@vinny142]

@@hnasr Or skip the concurrency problem and go for revision history. A simple trigger can keep track of revisions and tell the user that there have been X revisions since you last saved, and show the revisions and let the user decide if their save is still useful.

[@hnasr]

I like your pragmatic approaches vinny

[@lordandee]

@@hnasr Thanks for the effort you put into this channel by the way! I come from a non computer science background but am now involved with developing large enterprise back end applications. Your videos help fill in a lot of the gaps I never got to study. Your scenario B is very common in large applications, particularly when you have have a mix of web services and lots of concurrent users altering the same data set. For a real example imagine a back office banking platform: 1) A credit decision engine loads a bunch of a customers data using an API that would result in an approval. 2) A user then changes some of the customer data through the back-end UI - maybe reduces the recorded salary of the customer - now he has bad credit! 3) Credit decision engine then makes a decision on the now stale data and attempts to commit an 'Approve' response. - If this happened this would be bad! Whats also interesting about this scenario is the return from the credit decision engine may not alter any of the original data it loaded - however it must still not be comiited. I have seen this solved with a 'persistence ticket' which relates to the data being loaded and saved. This must be provided on the load and save requests - from this the application can tell if any relaveant data has been changed since the load.

[@johnathoncrawford7610]

really good analogies, attitude and explanation. Liked and subscribed. Awesome!

[@raghugrinus4779]

Clean understanding

[@hhellohhello]

Hi Hussein, I recently received an interview question for a fresh grad backend engineer role which goes like this: A user can only log in on a single device at any time. The user lost his previous device where he was logged in. The user now wants to log in from a new device, how do you determine if the login can succeed? My answer was to use a heartbeat. Is there a better or more detailed solution? The interviewer also asked how long/how many times to try to connect to the previous device. Anyways, thanks for your videos. They really help. Because Im a engineer with a game development background. I really need to make sure I got my Backend knowlege covered.

[@hnasr]

Interesting question 😊 I would store the device Id on the backend. When the user logs-in successfully from a new device I will for invalidate the old session and send an email to the user Notifying the new location/device with a report option in case the login was not him or her This way when the old device wants to access system their session is no longer valid.. This obviously does not work with JWT without serious hacks

[@ekaterinagalkina7303]

In the booking example (for Postgres) one should set Repeatable Read for update transaction. Optimistic c c is used by default, and the concurrent transaction will roll back, if the value has changed since the beginning of transaction. Tha's for database level. On application level there is a @Version annotation in Hibernate.

[@constantineJohn]

Very nice topic Hussien keep up the perfect backend content ,pls make more content on python on backend tornado, etc make a look at fastapi a new python framework it changes everything ♥️♥️♥️

[@hnasr]

appreciate you !! thank you so much... I need to get back to python didn't wrote python code for over a year now

[@kornelijussliubauskas8299]

6:30 What about combining pessimistic and optimistic together so it results in having locks only for certains fields within a row/document so it doesn't lock out the sequential writes/reads to the row/document if they're not leaping on one another. Probably its already in the implementation details.. 😀

[@diegosalasnoain1149]

How about using event emitters in nodejs, for example, to queue requests for a certain transaction at an application level at a certain endpoint. We wouldn't have to think about hitting the database in the first place for a transaction. These events would process sequentially holding a lock until the process is done and emit a "done" event. Though, In terms of performance, I am not sure how this would be better than the common database level approach.

[@johnnyniu8503]

Thanks great explanation n examples

[@RockTheCage55]

at least for Entity Framework the way it works is it compares the timestamp from the time you read it & the time you updated it (not that the value your updating has changed) & if it has it will abort. We also add RCSI (Read-commited snapshot isolation) which is sql servers version of MVCC. With sql servers default read-commited & MVCC turned off (RCSI which it is by default) our database would come to a screeching halt. We have to many long running transactions that are unavoidable. We can't get our usings to deal with eventual consistency.

[@saggitt]

I’m all about that bass, no trEble =)

[@dl2651]

thanks a lot! great speaker!

[@rubyk1932]

Hi @Hussein , I am able to pay to "Join" your channel, as clicking on "Join" button is not leading me anywhere, Can I please some help here?

[@saggitt]

Maybe you could talk more about isolation levels and their application. I’ve never seen anybody use anything except “read committed”. Why would you want to see dirty state of other transactions? Select statements are executed with DB state fixed at the time when statement execution starts, so that’s not exactly “read committed” at the level of one select. But when do you need anything else on transaction level?

[@hnasr]

I discussed Isolation in depth on my ACID video, the repeatable read is very important to provide consistency .. you can jump to that topic using youtube chapters kzbin.info/www/bejne/pqDQqX2Fe7uWh9U

[@saggitt]

@@hnasr Cool, thank you!

[@soulofjack7294]

Locks in redis please?

[@МаксимКузичев-к8ч]

I think you are wrong about optimistic lock. You don't need to select before update. You just -> update .... where version = 0. And if updete did not change anything this means than there is a new version and exception should be thrown

[@hnasr]

And update where version = 0 does a select

[@МаксимКузичев-к8ч]

@@hnasr Yes but maybe db does it in consistant way? I mean 2 commands will be atomic and consistant and other thread will see updated version(no update) or just will be blocked before selecting version?

[@МаксимКузичев-к8ч]

@@hnasr I mean second thread blocks on row before it selects version. And this rdbms feature gives us save optimistic locking way.

[@aatifnazar8203]

If both can solve the problem at the end of the day, I will always go for optimistic concurrency control. Pessimistic concurrency control is a blocking operation. Rollback is better than busy waiting. Or is it the case that there is a scenario which pessimistic approach can solve but optimistic cannot ? What could go wrong ?

[@aatifnazar8203]

Assuming that we are not doing optimistic locking at application level (so no problems of time to check and time to use) but at db level using multi version concurrency control (mvcc)

[@ejpeninger]

Thank you :-)

[@AbleToLiveHere]

Like for this. Thank you

[@lotierm]

Atomic operations.

[@KunwarPratapSingh41951]

Is withforupdate pessimistic

[@hnasr]

correct

[@lotierm]

CQRS

[@jerkmeo]

very nice explaination

[@curiossoul]

Your content is wonderful but sorry to say this, I can't stand that over movement of hands and expressions. As a true follower of your content on youtube and Linkedin, I would suggest to try reducing that.

[@amanshivhare5592]

So @2:50 You will roam, unitl Covid Hits you :P Lol No precautions.

[@MarincaGheorghe]

There are low level operations (offered by HW support and exposed by OSes or languages) that allow you to read/modify/update in an attomic way so that you can update a "pointer" if it was not changed in a concurrent environment: en.m.wikipedia.org/wiki/Read%E2%80%93modify%E2%80%93write