In Isolation Level Table: I meant Concurrency but instead i wrote Consistency. Read Uncommitted has high Concurrency and Serializable has Least Concurrency.
@@ConceptandCoding Hi sir because of you I was able to crack lld ,hld rounds in companies like amex,paytm payments bank, cars24,clearTax and all and joiner paytm payments bank.Thank you so much sir for all your videos and content
Hi sir because of you I was able to crack lld ,hld rounds in companies like amex,paytm payments bank, cars24,clearTax and all and joiner paytm payments bank.Thank you so much sir for all your videos and content
This video is a gem, got to know lot of concept in this 1 hr video. Thank you @Shreyans for making such videos. These contents need to be reached to larger groups surprised to see only 13k views on it
I have watched your concurrancy control mechanisms , Book my Show design and Cap theorem . Due to lack of time before my interview , i just sticked to watch limited videos . As expected they asked me about concurrancy control , and book my show follow up question and syncronized block . Interviewer is very happy about my answers . Finally i have cleared my dream designation . Thank you soo much . Felt like its worth to buy memebership.🎉🎉🎉
Nice stuff! To add to it, the `Serializable` isolation level has the lowest efficiency/concurrency because it processes the tasks submitted to it in a sequential or serial fashion. It picks the tasks in the `ORDER OF SUBMISION` from the queue, and that's why it guarantees correctness.
Hey Shreyansh, Thanks for discussing the important topic it'll definitely help a lot of backend engineers. I just wanted to point something on consistency, I think consistency is low for "Read Uncommitted" isolation level and High for "Serializable"
@@ConceptandCoding Yes, in notes it is mentioned as "consistency" next to the table but it should have been "concurrency". Anyways, mentioning it here so that people can get clarity on that.
In pcc, If some transaction has started (say T1) and then we are reading a row (say row A) then T1 itself won’t be able to update row A, as there is shared lock already on it.. Am I correct..?
really you way of explanation awesome 👌now i got very much clarity how transactions happening because i saw so many videos this much explanation i never seen , really thanks so much once again , one request pls provide English version of lld
can we control the updation or versioning? since there's a possibility that the column which were update by txn A could be irrelevant for txn-B txn-B was trying to update some other column against the same row, but if txn - A changes the version, txn-B rolled back which is incorrect right.
Hey @shrayansh, I have a question please clarify. If only read is required, then I think no isolation required, then when this Read Committed isolation will be used?
Hey Shreyansh, Can you please explain the Range lock? Is this a lock on the whole table or what? In a transaction, I can have a query `select * from table1 where name='abc'` which can be executed more than one time. And I don't want a phantom read issue. Then I am left with 2 kinds of isolation levels - snapshot isolation and serializable isolation. In snapshot isolation, the DB can create a snapshot for my transaction and I will only see that snapshot in my whole transaction. In serializable isolation There should be a lock on the whole table, then only phantom read can be avoided.
Thanks bro for this video. 1 Question, isolation level is working fine multiple application instances. But what if DB is also distributed, with active-active connection (2 primary DB)?
Also at the end, when you show optimistic concurrency control can prevent deadlocks - If the isolation level was SSI instead of read committed: In transaction 2, The read of B will be outdated because of the commit by T1 as there is a new version of B. If a stale value of B was used to make some decision and that decision involves writing to A, then the decision becomes invalid. I think, the algorithm of optimistic concurrency control atleast in SSI will detect that the read of B has become stale and T2 will be aborted and T2 would have to be retried.
You have such an in-depth knowledge, great video. I have this doubt when you said no chance of deadlock in OCC when TA has to write on row 1,2 and TB has to write on 2,1 after first step if TA takes exclusive lock on 1 and TB takes exclusive lock on 2 and for second step both cant proceed as the shared resources needed are locked then in that case we get a deadlock even in OCC.
41:14 why lock 4 ? when the query clearly makes the range and inclusive both the limits .. locking the range 1-3 makes sense though.. someone please clarify this
Great content Shreyansh, I think after deadlock detection not every DB aborts all the transactions. For instance Postgresql aborts the transaction based on the victim selection and executes the other transactions post lock release
how can you say that consistency is high on read uncommitted isolation ?? let say transaction t1 and t2 is there, and t2 made some change and didn't commit and rolled that back so dirty read would be there by t1 which an inconsistency case because latest changes are not there in DB.
Hi sir, A quick question on RANGE LOCKING. "In case of Serializable Isolation Level - What is the purpose of locking neighboring rows via Range Locking?" Scenario: SERIALIZABLE ISOLATION LEVEL - In a banking setup, customer IDs with 1 - 10 have been queried/ Read in a transaction say "Txn A". ----- SHARED LOCK (S) IS APPLIED. - As per RANGE LOCKING, even the neighboring rows (customer's record with ID = 11) also gets locked. [LOCKED 1 - 11 RECORDS] - In a different transaction "Txn B", customer ID = 11 wants to transfer some money to customer X. - But Customer 11 is unable to send money as his record is in SHARED LOCKING & Once there is a SHARED LOCK, we cannot have an EXCLUSIVE LOCK on top of it. (As transferring money requires UPDATING Customer 11's record) To summarize, a query operation which does not involve Customer 11 is affecting his ability to transfer money. Could you please help me understand this (or) correct me if I am wrong? Thank you.
Hi Shrayansh, the default isolation level in MySQL is REPEATABLE_READ. So, if we have to use optimistic locking in MySQL, is it required to change the isolation level to READ_COMMITTED first?
hi shreyanh ,wanted to ask a question, the following video could be answer to a scenario where multiple resources are trying to access a shared resource to compute on it right?
if i have multiple read transaction and also a write transaction present at same time, will DB going to priortize write transaction over read transactions ?
Kudos @ConceptandCoding Good Explanation and great effort. Watching from Tamilnadu without knowing Hindi. Please give english subtitles when you explain in your native language. In other videos, few places you explained in your native language. So I need to deeply concentrate to understand.
In optimistic locking, it is handled by application. For example Oracle DB, they do not support row version concept, so it's on application to manage the version column by itself
Great video as always Shrayansh!! Thanks a lot! I had one doubt though. How do we go about acquiring locks in case of replicated dbs ? For example, lets say I acquired an exclusive lock on row1 in db1, but some other server gets redirected to db2, and the user is able to update row1 in that replica. Can you please explain in brief about this ? Thanks!
There are distributed lock mechanism like usage of Zookeeper, which will send the msg to all the active dbs that put a lock on this data. There are other mechanism too , will cover that may be in separate video buddy
@@ConceptandCodingIsn't Zookeper to hold locks for multiple nodes of the microservice? I don't think Zookeeper has any relation with database or its replicas.
I appreciate you sharing knowledge. I think optimistic locking is essentially lock free, decision to commit or rollback depends on version values In your video you have mentioned that optimistic locking uses SELECT FOR UPDATE, this is inaccurate, taking a lock means pessimistic locking
I am not inline with it buddy. While comparing the version, that time you have to take lock else how you will handle the scenario when 2 request read and compare the version with DB at same time too? Both will go ahead and update the DB and "Lost Update" issue will arise. But glad you raised the point.
@@ConceptandCoding the way it is done is let say we have 2 sessions trying to update a row with primary key R1 and with Version V10 Both sessions will read V10 version and will try to update the row using following UPDATE SQL pattern UPDATE table SET someCol=someVal WHERE primaryKey=R1 AND version=V10 Since both sessions are firing the SQL, there will be a race condition and one will succeed and update one row while the other will update 0 rows, the session which updated 0 rows now knows that its update failed and it throws an exception(ORM such as Hibernate throw OptimisticLockException) As a programmer we can choose to retry this request being OPTIMISTIC that there will not a race condition again and its request will succeed this time.
@@bangbang86 make sense, as DB for each insert/update puts lock and during race condition one will fail. Generally handling this at application is faster than at DB. As you know that, in real code, application never directly connects with DB, there is one mediator which do lot of stuff like grouping of queries, diverting the query so that one DB server do not get overload etc. I am totally agree with your point, but i still feel handling this at application layer is much better. What do you think?
@@ConceptandCoding in any real world application there will be at least two application servers for fault tolerance and in such a scenario if the two requests working on same entity for e.g. user bank balance row, land on different app servers then it is impossible to do concurrency handling on app side since the conflicting requests lie outside the scope of each app server. This is why concurrency handling on common/converging part such as DB or any datastore is a better option as it has context of every request and is the place which stores the state of the entity. Hope I was able to explain my thought.
Agree, and that's why we are discussing distributed locking mechanism (lock on DB rather than distributed synchronised which app server do). But we both agree here now, optimistic is not actually Lock free. It does put a lock during update/insert :)
Can you provide a brief overview on the transaction isolation levels that is employed in systems like BookMyShow and Tatkal booking on IRCTC to handle concurrency during their respective booking processes? I think strict isolation level like SERIALIZABLE suits best for BookMyShow to avoid phantom reads by applying range lock, because here the requirement is - user should get the ticket either Booked or Not Booked, there is no concept of Waiting ticket. But in terms of IRCTC Tatkal booking, there is short window of time with lot of concurrent requests and Waiting ticket is also a thing, So To trade of System performance, do you think they apply less strict Isolation Level?? ( Not sure on this- but they allow some level of incorrect reads, if we check ticket availability status on 2 laptops, one shows available, but when you book with other at the same time, it can give Waiting ticket),
In my view, BookMyShow might be using optimistic concurrency Control (with Read Committed) isolation level. Couple of reasons: 1. As you and me can select same seat at the same time but at the time of checkout one of us will see the issue. 2. I can select/ unselect multiple seats, i can not put locks on all seats, as you know, in pessimistic if lock is put it will be released only after end of txn. So optimistic is the best option. For IRCTC, i will think and get back to you (but seems very similar to BookMyshow usecase only)
Hi @Shreyansh, First of all a very big Thanks for all your efforts for educating the community. My doubt is platforms like BookmyShow and IRCTC, we can also book multiple seats in single transaction right. Shouldnt we use Serailizable isolation level as it involves range of seats to be booked.
Hey Shreyansh, Great video, just one minor clarification related to optimistic concurrency control both user will read that seat is free with version 1 now in your example you said first user will take the exclusive lock till the end of the transaction and if other user tries to update he will check first the version if version is different rollback and try again. one doubt here like both tries to read the row parallely both got the shared lock now if both tries to take exclusive lock parallely will database will handle this thing to give one transaction exclusive lock and other will be in waiting state am I right here it is database responsibility to give exclusive lock to a particular transaction if many are coming parllely
Yes it is database responsibility to provide only 1 exclusive lock to 1 txn at a time. And just one correction buddy, in optimistic concurrency control there is no lock required, (so that select for update, is generally select only, no lock is required ) In comment section, there is one long discussion happened, see if you can get that, that will clarify you, if not let me know, we will connect and try to clarify buddy.
hi, @conceptandcoding, in OCC you have said that no deadlock is possible, take this case ID:1 and ID:2 ,Trans-A has acquired ex-lock on ID1 and trans-B has acquired ex-lock on ID2 after that Trans-A wants to put a shared lock on the ID2 and Trans-B wants to put a lock on ID1, in this case it is a deadlock right?
hi Pavan, pls check in comment section my discussion with @bangbang86 member. That will clarify your doubts fully. let me know if you able to find and got your answer.
@@ConceptandCoding i gone through all the conversation, it means that OCC never acquires the lock at all and it only checks while writing the data has been modified or not? am i right
@@pavankumar-cy6mg no OCC does help to achieve the isolation level below repeatable read. Only part is, application do not explicitly put lock, it adds the db row version in the query. thats it.
Phantom is seen when you do a query for multiple rows - the number of rows returned are different when you do that query multiple times in the same transaction. "One transaction changing the result of another transaction's search query" Non repeatable read - when you read same object multiple times within a transaction - you see different values . IT happens in READ COMMITTED isolation level because another transaction which started later than the first transaction can update and commit a new value. The first transaction which is long running can see old value as well as new value in different reads.
A little more into repeatable read , phantom read is possible in repeatable read because the shared lock can only be acquired on rows which are already present in table, not on rows which are new to table. Hence on multiple query we may see new rows. My question is will these new rows get shared locked during any read after they are inserted?
@@ConceptandCoding at time t0, transaction with repeatable read isolation level got some N rows on executing a query, it put shared lock on all of them, at time t2 some new M records satisfying the same condition were inserted in table, at time t4 same query was executed again with shared locks resulting in phantom read situation - N+M records will be fetched with shared lock on all N rows and no lock on all M rows, will these new M rows be given a lock after 2nd time read?
Well done, a really well structured and clear explanation! PS: A little bit confused by the "consistency" scale decreasing while the isolation level is increasing. Perhaps you really meant "concurrency" there?
Select for update query, Insert, update query put Exclusive lock. Normal select query internally put shared lock based on Isolation Level set for the txn.
in read committed, lets say tA comes and reads a value and releases the shared lock, but then tB comes acquire exclusive lock and update that value. So tA has done a dirty read here
I am trying to implement this project as a part of my resume. I am a fresher with 0 experience. Which type of locking should i add in my spring boot Application.Optimistic or Pessimistic.?
thanks for the videos but i could not understand how these strategies managed the distributed architecture..if read calls are to be done from different server of db...all the transaction are being on hit on same node even for the read?
There is a concept of distributed locking, means if there are multiple instance of DB having similar record, there there are service like zookeeper which make sure that all instances of db put the lock on that row, there are more apart from zookeeper
@@ConceptandCoding bhaiya thanks a lot apne toh abhi reply kar diya..bhai in technologies ko code level pe kaise seeekhein...scaler jaise course bhut mahnge hain..internet pe proper resouce smj nhi aa rhe bhai....please reply please...
@@car_holic1995 code level Pe bhi Sikh jaoge. 1 way is as you grow in your career, you will automatically get exposed to these things. 2nd way is, take a sample app and start building it (take guidance from your seniors or other people)..
Hi Shreyanh @ConceptandCoding, You skipped one very important scenario i.e how repeatable read does not solves phanton read. Can you give the same example using lock strategy?
Can you please explain by taking scenario of two transactions T1 and T2 that how Repeatable Read is not able to solve phantom problem@@ConceptandCoding
Next to your table for transaction Isolation, it should concurrency going from high to low. Consistency goes from low to high, in your table from top to bottom.
One Doubbt sheryansh , Like in Read Uncommited Level --> AnyOne Transaction is Coming aur koi bhi aakar db ko apne hisab se change and read kr rha h ,, so i think it should have least consistency na ? . or can you explain me this
in OPTIMISTIC CONCURRENCY CONTROL DEAD LOCK POSSIBLE. 1. T-a take Exclusive lock(row-1), 2. T-b take Exclusive lock(row-2), 3. T-a req. Exclusive lock(row-2) it block, 4. T-b req. Exclusive lock(row-1) it block.
Concurrency is distributed system: how we will handle Concurrency between many microservices. How do we rollback complete transaction if one of the service fails.
Hey nice tutorial, but there are some of wrong things taught in this tutorial. Optimistic locking doesn't use any lock. And what you mean db put share lock and after reading it releases the lock? Whats point of putting shared lock, it can just read it
Where are we applying these Locks? On Db-Level or Code-Level( duty of AppDev or DBDev) ? How does the code look like ? 2 more requests: - Can you tell What Common Concurrency-questions, that are asked in interviews ? - Pls explain Class-level & Object-level lock.
Transaction and isolation level we have to define at code level, locks is at DB level. This is the most frequently asked interview question in concurrency buddy.
@@ConceptandCoding for isolation we will be writing code to get the shared / exclusive lock ? How does that look in Java? What are good books to refer concurrency ?
@@UtkarshSingh-cb8fq any SQL book is okay for it. When you do Select query it put shared lock. When you use update, delete, select for update it put exclusive lock
@@ConceptandCoding so when you say put shared lock at start of Read and hold it till transaction completes , then it will be DB property/responsibility to hold shared lock until transaction completed? And how DB knows about the transaction , like how many statements/operations are present in a transaction
Thanks for a great tutorial... I can feel the passion in your teaching....Any one interested in forming a group to learn... as I am just starting out would be a great help
I think in optimistic also deadlock possible? T1 Write A Write B T2 Write B Write A Both will wait for each other at time t2 to release exclusive lock.
I think the DB locks that are used on processing the transaction are not same among all the DB's. In postgres, if you try to execute a deadlock scenario using repeatable read isolation level. It will commit without any issues. Any comments on this @Shreyansh?
In postgres too, definitely One of the txn should get aborted (as postgres automatically detect deadlock and abort only 1 txn). Have you tried testing this usecase?
@@ConceptandCoding Yes, I had a table called test with two rows. Tried executing 2 different transactions according to the deadlock scenario but it is not giving any error. I tried the same with serializable and it gives error
Serializable Snapshot Isolation as explained in DDIA describes optimistic concurrency control technique is an easy way. "when a transaction wants to commit- the DB checks whether anything bad happened" (i.e whether isolation was violated), if so the transaction is aborted and has to be retried." " SSI has an algorithm for detecting serialization conflicts among writes and determining which transactions to abort" The key idea is to detect if any read in a transaction has become outdated because of a write in another transactions. Such stale reads can lead to an invalid decision(writes) and hence the transaction is aborted and has to be retried. Versions are used to detect this.
In Isolation Level Table: I meant Concurrency but instead i wrote Consistency.
Read Uncommitted has high Concurrency and Serializable has Least Concurrency.
I was about to post it 😀
hi Shreyansh. Great video. When is the 2 phase locking video coming? And do you mean 2 phase commit by 2 phase locking?
@@panmenia no two phase commit is different and two phase locking is different
@shreyansh you can consider adding an on-video note.
@@ConceptandCoding Hi sir because of you I was able to crack lld ,hld rounds in companies like amex,paytm payments bank, cars24,clearTax and all and joiner paytm payments bank.Thank you so much sir for all your videos and content
Hi sir because of you I was able to crack lld ,hld rounds in companies like amex,paytm payments bank, cars24,clearTax and all and joiner paytm payments bank.Thank you so much sir for all your videos and content
Congratulations bhai...can I get your mail for some guidance bhai.
how did u get the interview calls ?
Extremely underrated playlist!! So happy to stumble upon it.
Thanks for your feedback
i'm really blessed for having u as my teacher and thanks for clearing all my doubts. thank u soo soo much bhayiyaaa
Truely a gem. Thanks for makling this series, I could learn the most difficult low level system design just because of you. May god bless you
Thank you
This video is a gem, got to know lot of concept in this 1 hr video. Thank you @Shreyans for making such videos. These contents need to be reached to larger groups surprised to see only 13k views on it
thanks for the feedback buddy
I have watched your concurrancy control mechanisms , Book my Show design and Cap theorem . Due to lack of time before my interview , i just sticked to watch limited videos . As expected they asked me about concurrancy control , and book my show follow up question and syncronized block . Interviewer is very happy about my answers . Finally i have cleared my dream designation . Thank you soo much . Felt like its worth to buy memebership.🎉🎉🎉
@@ydayanandareddy7283 congratulations buddy
Very interesting. I've learned a lot. Thank You
Essential topic for understanding what your system might be doing, but rarely used in day to day tasks
This topic is very very frequently used buddy in day to day work.
Optimistic and pessimistic locking yes, but the mvcc is taken care by the database.
Outstanding video Sir, and the way you explain in depth about the problem and solution is exceptional❤❤
Thanks
Thanks Shrayanah Bhai
Thank you for this very informative and knowledgeable session.
thanks
Nice stuff!
To add to it, the `Serializable` isolation level has the lowest efficiency/concurrency because it processes the tasks submitted to it in a sequential or serial fashion. It picks the tasks in the `ORDER OF SUBMISION` from the queue, and that's why it guarantees correctness.
Right
Nice Explanation, Keep up the momentum.
Thank you
Shaandar jabardust jindabaad very clear explanation thank you 🙏🙏
thanks
Very well explained !
thanks
Awesome explanation
nicely explained
@ConceptandCoding informative Video, depth explained. Please share the notes. It will be handy during the preparation of interviews. 🙏
thank you!!
Hey Shreyansh,
Thanks for discussing the important topic it'll definitely help a lot of backend engineers.
I just wanted to point something on consistency, I think consistency is low for "Read Uncommitted" isolation level and High for "Serializable"
Nope it's opposite. Serializable has the least concurrency as it use range lock.
Actually it is written "consistency" in the notes, but what Shrayansh meant is "concurrency"..
@@ConceptandCoding Yes, in notes it is mentioned as "consistency" next to the table but it should have been "concurrency".
Anyways, mentioning it here so that people can get clarity on that.
Oops my bad. Just now saw it again. Sorry for this
No need to be sorry you have done a good job discussing this topic we appreciate your efforts and corrections can be done in the comments section.
In pcc, If some transaction has started (say T1) and then we are reading a row (say row A) then T1 itself won’t be able to update row A, as there is shared lock already on it..
Am I correct..?
really you way of explanation awesome 👌now i got very much clarity how transactions happening because i saw so many videos this much explanation i never seen , really thanks so much once again , one request pls provide English version of lld
Thanks, all latest videos of LLD are in English only and few initial which are in hindi, i have explained in English in LLD live playlist
can we control the updation or versioning?
since there's a possibility that the column which were update by txn A could be irrelevant for txn-B
txn-B was trying to update some other column against the same row, but if txn - A changes the version, txn-B rolled back which is incorrect right.
Hello Shrayansh I think you inverted the consistency in seralisable we get high consistency or you can use availability instead.
Very nicely explained....
Thank you
Hey @shrayansh,
I have a question please clarify. If only read is required, then I think no isolation required, then when this Read Committed isolation will be used?
Hey Shreyansh,
Can you please explain the Range lock? Is this a lock on the whole table or what?
In a transaction, I can have a query `select * from table1 where name='abc'` which can be executed more than one time. And I don't want a phantom read issue. Then I am left with 2 kinds of isolation levels - snapshot isolation and serializable isolation.
In snapshot isolation, the DB can create a snapshot for my transaction and I will only see that snapshot in my whole transaction.
In serializable isolation There should be a lock on the whole table, then only phantom read can be avoided.
very helpful video
Thanks
Great explanation👏👏
thanks
Great content
Thanks
Thanks bro for this video.
1 Question, isolation level is working fine multiple application instances. But what if DB is also distributed, with active-active connection (2 primary DB)?
Awesome video
Thanks!
Also at the end, when you show optimistic concurrency control can prevent deadlocks -
If the isolation level was SSI instead of read committed:
In transaction 2, The read of B will be outdated because of the commit by T1 as there is a new version of B.
If a stale value of B was used to make some decision and that decision involves writing to A, then the decision becomes invalid.
I think, the algorithm of optimistic concurrency control atleast in SSI will detect that the read of B has become stale and T2 will be aborted and T2 would have to be retried.
You have such an in-depth knowledge, great video. I have this doubt when you said no chance of deadlock in OCC when TA has to write on row 1,2 and TB has to write on 2,1 after first step if TA takes exclusive lock on 1 and TB takes exclusive lock on 2 and for second step both cant proceed as the shared resources needed are locked then in that case we get a deadlock even in OCC.
Yes, i think the best way to say is OOC reduce the probability of deadlock, rather than it fully removes it.
Since Transactions are atomic, how come TA reads the data written by TB which is not yet committed, can anyone help me to understand?
Nice video "-)
41:14 why lock 4 ? when the query clearly makes the range and inclusive both the limits ..
locking the range 1-3 makes sense though..
someone please clarify this
For read uncommitted, why does it have highest consistency?? Shouldn't it be the lowest?
Very useful content .Waiting for your 2-phase locking session
It's already there
Great content Shreyansh, I think after deadlock detection not every DB aborts all the transactions. For instance Postgresql aborts the transaction based on the victim selection and executes the other transactions post lock release
True depends upon locking mechanism.
how can you say that consistency is high on read uncommitted isolation ?? let say transaction t1 and t2 is there, and t2 made some change and didn't commit and rolled that back so dirty read would be there by t1 which an inconsistency case because latest changes are not there in DB.
I was waiting for this one..thanks ❣️
Hope you will find it useful.
Hi sir, A quick question on RANGE LOCKING.
"In case of Serializable Isolation Level - What is the purpose of locking neighboring rows via Range Locking?"
Scenario: SERIALIZABLE ISOLATION LEVEL
- In a banking setup, customer IDs with 1 - 10 have been queried/ Read in a transaction say "Txn A". ----- SHARED LOCK (S) IS APPLIED.
- As per RANGE LOCKING, even the neighboring rows (customer's record with ID = 11) also gets locked. [LOCKED 1 - 11 RECORDS]
- In a different transaction "Txn B", customer ID = 11 wants to transfer some money to customer X.
- But Customer 11 is unable to send money as his record is in SHARED LOCKING & Once there is a SHARED LOCK, we cannot have an EXCLUSIVE LOCK on top of it. (As transferring money requires UPDATING Customer 11's record)
To summarize, a query operation which does not involve Customer 11 is affecting his ability to transfer money.
Could you please help me understand this (or) correct me if I am wrong?
Thank you.
Hi Shrayansh, the default isolation level in MySQL is REPEATABLE_READ. So, if we have to use optimistic locking in MySQL, is it required to change the isolation level to READ_COMMITTED first?
Optimistic runs below Repeatable read.
We can define the isolation level while creating the Txn.
hi shreyanh ,wanted to ask a question, the following video could be answer to a scenario where multiple resources are trying to access a shared resource to compute on it right?
Yes
@@ConceptandCodingIsn't Zookeper (ZAB) solving the same problem? I think consensus algorithms like AB, RAFT, PAXOS solve the same
Thanks for the detailed explanation 😊
Thanks
How can one transaction can read uncommited changes ... will they be under same session?
video is gem
thank you
if i have multiple read transaction and also a write transaction present at same time, will DB going to priortize write transaction over read transactions ?
no prioritization as such i am aware of.
Kudos @ConceptandCoding Good Explanation and great effort. Watching from Tamilnadu without knowing Hindi. Please give english subtitles when you explain in your native language. In other videos, few places you explained in your native language. So I need to deeply concentrate to understand.
Sorry for that, i will make sure that I will do in full English
This version checking and updating the version is this done by db or is it to handled by application developers? Can someone help on this
In optimistic locking, it is handled by application.
For example Oracle DB, they do not support row version concept, so it's on application to manage the version column by itself
Great video as always Shrayansh!! Thanks a lot!
I had one doubt though. How do we go about acquiring locks in case of replicated dbs ? For example, lets say I acquired an exclusive lock on row1 in db1, but some other server gets redirected to db2, and the user is able to update row1 in that replica. Can you please explain in brief about this ?
Thanks!
There are distributed lock mechanism like usage of Zookeeper, which will send the msg to all the active dbs that put a lock on this data.
There are other mechanism too , will cover that may be in separate video buddy
@@ConceptandCoding Ohh..ok. Thanks for the response!
@@ConceptandCodingIsn't Zookeper to hold locks for multiple nodes of the microservice? I don't think Zookeeper has any relation with database or its replicas.
I appreciate you sharing knowledge.
I think optimistic locking is essentially lock free, decision to commit or rollback depends on version values
In your video you have mentioned that optimistic locking uses SELECT FOR UPDATE, this is inaccurate, taking a lock means pessimistic locking
I am not inline with it buddy.
While comparing the version, that time you have to take lock else how you will handle the scenario when 2 request read and compare the version with DB at same time too?
Both will go ahead and update the DB and "Lost Update" issue will arise.
But glad you raised the point.
@@ConceptandCoding the way it is done is let say we have 2 sessions trying to update a row with primary key R1 and with Version V10
Both sessions will read V10 version and will try to update the row using following UPDATE SQL pattern
UPDATE table SET someCol=someVal
WHERE primaryKey=R1 AND version=V10
Since both sessions are firing the SQL, there will be a race condition and one will succeed and update one row while the other will update 0 rows, the session which updated 0 rows now knows that its update failed and it throws an exception(ORM such as Hibernate throw OptimisticLockException)
As a programmer we can choose to retry this request being OPTIMISTIC that there will not a race condition again and its request will succeed this time.
@@bangbang86 make sense, as DB for each insert/update puts lock and during race condition one will fail.
Generally handling this at application is faster than at DB.
As you know that, in real code, application never directly connects with DB, there is one mediator which do lot of stuff like grouping of queries, diverting the query so that one DB server do not get overload etc.
I am totally agree with your point, but i still feel handling this at application layer is much better.
What do you think?
@@ConceptandCoding in any real world application there will be at least two application servers for fault tolerance and in such a scenario if the two requests working on same entity for e.g. user bank balance row, land on different app servers then it is impossible to do concurrency handling on app side since the conflicting requests lie outside the scope of each app server. This is why concurrency handling on common/converging part such as DB or any datastore is a better option as it has context of every request and is the place which stores the state of the entity. Hope I was able to explain my thought.
Agree, and that's why we are discussing distributed locking mechanism (lock on DB rather than distributed synchronised which app server do).
But we both agree here now, optimistic is not actually Lock free. It does put a lock during update/insert :)
Can you provide a brief overview on the transaction isolation levels that is employed in systems like BookMyShow and Tatkal booking on IRCTC to handle concurrency during their respective booking processes?
I think strict isolation level like SERIALIZABLE suits best for BookMyShow to avoid phantom reads by applying range lock, because here the requirement is - user should get the ticket either Booked or Not Booked, there is no concept of Waiting ticket.
But in terms of IRCTC Tatkal booking, there is short window of time with lot of concurrent requests and Waiting ticket is also a thing, So To trade of System performance, do you think they apply less strict Isolation Level??
( Not sure on this- but they allow some level of incorrect reads, if we check ticket availability status on 2 laptops, one shows available, but when you book with other at the same time, it can give Waiting ticket),
In my view, BookMyShow might be using optimistic concurrency Control (with Read Committed) isolation level.
Couple of reasons:
1. As you and me can select same seat at the same time but at the time of checkout one of us will see the issue.
2. I can select/ unselect multiple seats, i can not put locks on all seats, as you know, in pessimistic if lock is put it will be released only after end of txn. So optimistic is the best option.
For IRCTC, i will think and get back to you (but seems very similar to BookMyshow usecase only)
Hi @Shreyansh,
First of all a very big Thanks for all your efforts for educating the community.
My doubt is platforms like BookmyShow and IRCTC, we can also book multiple seats in single transaction right. Shouldnt we use Serailizable isolation level as it involves range of seats to be booked.
Hey Shreyansh,
Great video, just one minor clarification related to optimistic concurrency control
both user will read that seat is free with version 1
now in your example you said first user will take the exclusive lock till the end of the transaction and if other user tries to update he will check first the version if version is different rollback and try again.
one doubt here like both tries to read the row parallely both got the shared lock now if both tries to take exclusive lock parallely will database will handle this thing to give one transaction exclusive lock and other will be in waiting state am I right here it is database responsibility to give exclusive lock to a particular transaction if many are coming parllely
Yes it is database responsibility to provide only 1 exclusive lock to 1 txn at a time.
And just one correction buddy, in optimistic concurrency control there is no lock required, (so that select for update, is generally select only, no lock is required )
In comment section, there is one long discussion happened, see if you can get that, that will clarify you, if not let me know, we will connect and try to clarify buddy.
hi, @conceptandcoding, in OCC you have said that no deadlock is possible, take this case ID:1 and ID:2 ,Trans-A has acquired ex-lock on ID1 and trans-B has acquired ex-lock on ID2 after that Trans-A wants to put a shared lock on the ID2 and Trans-B wants to put a lock on ID1, in this case it is a deadlock right?
hi Pavan, pls check in comment section my discussion with @bangbang86 member. That will clarify your doubts fully. let me know if you able to find and got your answer.
@@ConceptandCoding i gone through all the conversation, it means that OCC never acquires the lock at all and it only checks while writing the data has been modified or not? am i right
@@pavankumar-cy6mg yes lock is done at DB level but application do not put the lock, application just add the row version in the query
@@ConceptandCoding and we could say then the Read committed does not comes under OCC, there is mistake in video
@@pavankumar-cy6mg no OCC does help to achieve the isolation level below repeatable read.
Only part is, application do not explicitly put lock, it adds the db row version in the query. thats it.
Seems to be both phantom and non repeatable are the same in both the cases we are reading the value multiple times, can anyone tell me the difference
Phantom is seen when you do a query for multiple rows - the number of rows returned are different when you do that query multiple times in the same transaction.
"One transaction changing the result of another transaction's search query"
Non repeatable read - when you read same object multiple times within a transaction - you see different values . IT happens in READ COMMITTED isolation level because another transaction which started later than the first transaction can update and commit a new value. The first transaction which is long running can see old value as well as new value in different reads.
i love you shrayansh
Hi can you suggest me some Db books where i can get lot of practice question on isolation, transaction and concurrency.
Design data intensive application book is one of the best book
@@ConceptandCoding thanks...
A little more into repeatable read , phantom read is possible in repeatable read because the shared lock can only be acquired on rows which are already present in table, not on rows which are new to table. Hence on multiple query we may see new rows. My question is will these new rows get shared locked during any read after they are inserted?
Could you please elaborate, i did not understand the question well.
Repeatable read do not put range lock.
@@ConceptandCoding at time t0, transaction with repeatable read isolation level got some N rows on executing a query, it put shared lock on all of them, at time t2 some new M records satisfying the same condition were inserted in table, at time t4 same query was executed again with shared locks resulting in phantom read situation - N+M records will be fetched with shared lock on all N rows and no lock on all M rows, will these new M rows be given a lock after 2nd time read?
@@bhaskarsharan4280thanks for explaining.
Yes, shared lock will be put on these new rows fetched
Well done, a really well structured and clear explanation!
PS: A little bit confused by the "consistency" scale decreasing while the isolation level is increasing. Perhaps you really meant "concurrency" there?
yes
Very well explained.. Thanks Brother..
But can you how to take the shared and exclusive lock on table/resource in distributed env
Select for update query, Insert, update query put Exclusive lock.
Normal select query internally put shared lock based on Isolation Level set for the txn.
If one transaction has obtained shared lock on a db row, then how many more transactions can get the lock for reading the row?
Share lock can be taken by many txns at same time buddy.
I don't think there is as such limit
Bro can you please add programatic example on Pessimaric and Opstimatic locking.
👍👍
Non-repeatable read❌
Non-repetetable read✅
in read committed, lets say tA comes and reads a value and releases the shared lock, but then tB comes acquire exclusive lock and update that value. So tA has done a dirty read here
I am trying to implement this project as a part of my resume. I am a fresher with 0 experience. Which type of locking should i add in my spring boot Application.Optimistic or Pessimistic.?
thanks for the videos but i could not understand how these strategies managed the distributed architecture..if read calls are to be done from different server of db...all the transaction are being on hit on same node even for the read?
There is a concept of distributed locking, means if there are multiple instance of DB having similar record, there there are service like zookeeper which make sure that all instances of db put the lock on that row, there are more apart from zookeeper
@@ConceptandCoding bhaiya thanks a lot apne toh abhi reply kar diya..bhai in technologies ko code level pe kaise seeekhein...scaler jaise course bhut mahnge hain..internet pe proper resouce smj nhi aa rhe bhai....please reply please...
bhai ek baar apse baat ho jaaye toh jeevan sudhar jaaye😀😀 ...itni confusion h bhai..kya karein
@@car_holic1995 code level Pe bhi Sikh jaoge.
1 way is as you grow in your career, you will automatically get exposed to these things.
2nd way is, take a sample app and start building it (take guidance from your seniors or other people)..
@@car_holic1995 :) i am also learning buddy
Hi Shreyansh,
Can you please make a video on Kubernetes?
Noted
Hi Shreyanh @ConceptandCoding, You skipped one very important scenario i.e how repeatable read does not solves phanton read. Can you give the same example using lock strategy?
let me check
Can you please explain by taking scenario of two transactions T1 and T2 that how Repeatable Read is not able to solve phantom problem@@ConceptandCoding
Next to your table for transaction Isolation, it should concurrency going from high to low. Consistency goes from low to high, in your table from top to bottom.
Your are right, i have pinned that in comment too.
@@ConceptandCoding If possible, could you please add asterisks (*) to the videos? The absence of asterisks is causing confusion.
@@harshagarwal_net * sorry did not bot get it, for what reason could you pls elaborate
One Doubbt sheryansh , Like in Read Uncommited Level --> AnyOne Transaction is Coming aur koi bhi aakar db ko apne hisab se change and read kr rha h ,, so i think it should have least consistency na ? . or can you explain me this
yes, i have mentioned in comment and pinged that too, you are right
in OPTIMISTIC CONCURRENCY CONTROL DEAD LOCK POSSIBLE. 1. T-a take Exclusive lock(row-1), 2. T-b take Exclusive lock(row-2), 3. T-a req. Exclusive lock(row-2) it block, 4. T-b req. Exclusive lock(row-1) it block.
How do you decide when to use which isolation level?
based on the trade off between consistency, concurrency and performance your application requires
@@ConceptandCoding Thanks for your reply. To help me understand better, can you give me some examples?
Concureeency is low for serialiser and high for red uncommited right ?
Yes right
when the next video on 2-phase-locking will come? are you preparing for it?
Yes working on it
Concurrency is distributed system: how we will handle Concurrency between many microservices. How do we rollback complete transaction if one of the service fails.
Already explained this in: czcams.com/video/ET_DnJgfplY/video.html
Hey nice tutorial, but there are some of wrong things taught in this tutorial. Optimistic locking doesn't use any lock. And what you mean db put share lock and after reading it releases the lock? Whats point of putting shared lock, it can just read it
right, and same has been discussed in the comment section. Do check it out, hope that will clarify your doubt.
Can you provide notes of the same by uploading it somewhere?
pls check the description of the video buddy.
Why optimistic locking can only work with READ_COMITTED isolation level , can’t we have REPEATABLE_READ with optimistic locking…
Good video but too many ads
Where are we applying these Locks? On Db-Level or Code-Level( duty of AppDev or DBDev) ? How does the code look like ?
2 more requests:
- Can you tell What Common Concurrency-questions, that are asked in interviews ?
- Pls explain Class-level & Object-level lock.
Transaction and isolation level we have to define at code level, locks is at DB level.
This is the most frequently asked interview question in concurrency buddy.
@@ConceptandCoding for isolation we will be writing code to get the shared / exclusive lock ? How does that look in Java?
What are good books to refer concurrency ?
@@UtkarshSingh-cb8fq any SQL book is okay for it.
When you do Select query it put shared lock.
When you use update, delete, select for update it put exclusive lock
@@ConceptandCoding so when you say put shared lock at start of Read and hold it till transaction completes , then it will be DB property/responsibility to hold shared lock until transaction completed? And how DB knows about the transaction , like how many statements/operations are present in a transaction
@@UtkarshSingh-cb8fq DB does not know about how Many statements, DB know about Transaction start or aborted or committed
Thanks for a great tutorial... I can feel the passion in your teaching....Any one interested in forming a group to learn... as I am just starting out would be a great help
When is the 2PL session coming up ? :)
It's already live, pls check the HLD playlist. Today I have made it live buddy
WHo writes these transactions ? DB developers ?
No, transactions (begin, rollback, commit) we app developer write it.
Hi.... Interesting topic.... But can i get one POC.. Meaning full low level code ????
I will write it and upload on gitlab and update
@@ConceptandCoding thank you sooo much. 🥰
most of the imp low level design video are available only to join member. can you please make it public
Only interview questions i have kept for the members buddy.
But all the fundamentals and concepts are available public
Yes but those videos were initially free and now become paid which is not a good thing in my opinion . Although the content is great 👍
Next video 🥺
Next week.
I think in optimistic also deadlock possible?
T1
Write A
Write B
T2
Write B
Write A
Both will wait for each other at time t2 to release exclusive lock.
Okay I think in OCC we dont do locks on db rows
Instead we go with versioning
Yes OCC use version
I think the DB locks that are used on processing the transaction are not same among all the DB's.
In postgres, if you try to execute a deadlock scenario using repeatable read isolation level. It will commit without any issues.
Any comments on this @Shreyansh?
In postgres too, definitely One of the txn should get aborted (as postgres automatically detect deadlock and abort only 1 txn).
Have you tried testing this usecase?
@@ConceptandCoding Yes, I had a table called test with two rows.
Tried executing 2 different transactions according to the deadlock scenario but it is not giving any error.
I tried the same with serializable and it gives error
@@tejass5060 can you notice and tell one thing buddy:
In Postgres for repeatable read deadlock scenario, does one txn getting rolled back or not?
@@ConceptandCoding Not rolling back.
Both the rows are getting updated.
Serializable Snapshot Isolation as explained in DDIA describes optimistic concurrency control technique is an easy way.
"when a transaction wants to commit- the DB checks whether anything bad happened" (i.e whether isolation was violated), if so the transaction is aborted and has to be retried."
" SSI has an algorithm for detecting serialization conflicts among writes and determining which transactions to abort"
The key idea is to detect if any read in a transaction has become outdated because of a write in another transactions. Such stale reads can lead to an invalid decision(writes) and hence the transaction is aborted and has to be retried. Versions are used to detect this.
i think consistency is reversed here
yes, in table, i have mentioned in the comment section
@@ConceptandCoding yeah saw it thank you.
Hello sir,
Just visited your channel after long time again, but videos are now paid , feeling bad .😢
Only interview questions are Locked buddy, rest all available for free.
Waste of time. Not recommended video.
RIP English
LOL India would return back to the 5 century without English
hello