‫Hello and welcome to another Q&A session for the month of March 20, 22.
‫Today, I have a wonderful question from our tour.
‫Arthur, Arthur.
‫Arthur.
‫Yeah, fantastic question.
‫Fantastic question.
‫Absolutely fantastic.
‫The question goes like vol
‫Lockwood's serialize Zabel isolations, generalizable isolation.
‫I say saying at three 3123.
‫You mentioned that the realizable isolation level transaction are run one after another.
‫So this is regarding two one on the isolation lecture.
‫So what's the point of lock with pessimistic implementation with select for updates?
‫So.
‫All right.
‫So fantastic question.
‫So two things here.
‫Let's talk about serialize the ability.
‫This idea of serializing things is you have ten concurrent transactions that came to your database at
‫the same time to make them realizable, which means each transaction should execute one right after
‫the other.
‫That's the feel that needs.
‫That's the final result that you need to get.
‫And you can implement that if you will want to implement that for serialization isolation level for
‫a second.
‫Now, if you want to implement that, you can use pessimistic concurrency control by using this select
‫for update and.
‫That order mentioned here I talked about many times in this course.
‫Literally, each transaction tries to obtain an exclusive lock right on a certain row.
‫And since only one transaction can obtain that lock the rest of the transaction, the nine transactions
‫will be blocked and waiting for that lock to be freed.
‫So the first transaction will finish, commit, and then that lock will be gone.
‫The second transaction immediately, whoever wins by this fraction of a microsecond will get that lock
‫and that it the rest eight transaction will be blocked, obviously.
‫And then until all of them will finish one right after, although.
‫So what's the problem with this?
‫The problem with this is you lost concurrency.
‫You killed your parallelism and concurrency.
‫You don't have throughput.
‫But the good thing about this is it's so simple to implement it this way and you won't get have any
‫side effect.
‫Right.
‫So there are tradeoffs here.
‫So it's very simple to implement.
‫But you slowed down your transactions.
‫You slowed down your queries.
‫If you have many, many couldn't get its queries at the same time.
‫So so that's that's basically how serialization supposed to work.
‫Serialized XBL isolation level is smarter than that is not really.
‫It gives you the final result as if all of these transactions have executed one after the other without
‫actually blocking them, they are executed concurrently.
‫So my say I was saying how that's the magic.
‫The database will detect if these transactions are in isolation.
‫In their serialized isolation.
‫They will.
‫They will see.
‫Okay.
‫Did I read something that someone just wrote or did something did someone read something that I just
‫wrote?
‫And it detects these by using different kind of lock that are none, none blocking.
‫And if those are detected, if there was a conflict in the sense that there was a serialize able and
‫or the serialization error, then the transaction that is about to commit will actually fail.
‫It says, hey, you know what, you did touch?
‫You touch something that someone read.
‫And as a result, this will.
‫Affect the final result right off of the entire transaction series.
‫And as a result, sorry, you have to fail.
‫And this is called Optimistic Concurrency Control.
‫So optimistic and classic control.
‫And I talked about this, doing that in the course where, hey, let both of them try to change and
‫will be optimistic.
‫We'll try to be optimistic in a sense that, okay, nobody will step on each other, so nobody will
‫actually touch, read something that someone is changing or write something that someone else was read.
‫So that will be fine.
‫Right.
‫So you go with the idea that optimism, a little bit of optimism that nobody will step on each other
‫at all.
‫And as a result, you can you may continue.
‫But if as a realizable error is detected in a sense that someone read someone else's data or someone
‫read someone else's write, that is when an optimistic concurrency control error will be thrown.
‫And you're going to get this error that says, okay, serialization failed and retry you transaction.
‫So the good thing about it is you still got a concurrency.
‫The bad thing about selling the visible isolation level is you transactions might fail.
‫This is not true.
‫What the pessimistic concurs, if at all.
‫They will never fail as long as they don't have time, obviously, that they will not avail.
‫They'll just take a little bit longer time to do it, but they'll never fail.
‫So it's it's at the end of the day, it's it's a trade off.
‫What do you want?
‫Do you want faster, potentially faster rights with multiple transactions if you have so many concurrent
‫transactions?
‫Right.
‫Or do you want a simpler back end design where we don't really have complex cases where, oh, this
‫transaction won't fail, I have to retry it later and do all this silly logic.
‫You know, it's all depends up to you.
‫So these tools exist for you and it really is up to you to use them or not.
‫I personally prefer to use select for update in certain cases.
‫Obviously not all the time for for use case of what I know this this operation will not be executed
‫concurrently a lot.
‫I use select for update if I want to ensure they are realizable, you know, but if there are many possible
‫concurrency and I need to, you know, I need a lot of transactions to be executed at the same time
‫and I know that nobody will like it.
‫If you think about this as someone editing some some some data, you know that nobody will edit the
‫same data if you are sure about that.
‫And you can redesign your client such in a way that nobody will step on each other still.
‫Then you can use optimistic on classical drone and I sorry, I said realizable isolation level.
‫All right, guys.
‫That's it for today.
‫Got to see you in the next one.