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
This is one of the finest indepth tutorials out there.. Trust me no-one teaches these things with this much level of depth and practical knowledge. Even I have 5 years of experience working at Flipkart but still this cleared my concepts. I feel the premium is so much useful of your channel Shrayansh.. Keeping doing this.
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
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.
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.🎉🎉🎉
The IT Community is literally blessed by Having People like, who are having quest for knowledge and understanding and more intense quest of Sharing that knowledge. Thanks a ton Shreyans.
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)?
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 :)
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.
Hi, Can you elaborate on Snapshot isolation level, what is the locking strategy used in this isolation level. And some more anamolies like read skew, write skew, lost update as well.
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.
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
There is a big mistake in the video, when you have explained optimistic locking. You have taken two transaction and on each write you are changing the version. Version should only be changed after commit and not write operation. Ideally all these changes are happening in memory and both the transactions are independent before the final commit.
For range lock, can we say that the entire table is getting locked for a single transaction and as a result concurrency is lowest as other transactions have to wait ? Please correct me if I am wrong.
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.
Hey bro, you do a nice job but the thing is at a lot of places you say things which are inconsistent with what you yourself tell earlier. Please try to self review your videos before publishing it. Just a suggestion I understand this might take more effort but when you are taking responsibility for sharing knowledge then misinformation and causing confusion should be avoided.
Hi Shreyansh , I wanted to understand , how a trasaction can read a uncommited change done by another transaction , is this the case of nested transactions? since if these 2 transactions are being performed by different threads , the read should be same until the change is commited by any one transaction. Please clarify
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.
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.?
At 5:30 you mentioned how synchronized will not work in distributed scenario with multiple processes (because no shared memory between processes). And then you mentioned that for Distributed environment we've Distributed Concurrency Control. But you missed telling how exactly this can help in distributed environment as no shared memory will still be a concern here no matter what locks we use. So as per my understanding, in Optimistic Concurrency Control, systems use versioning or timestamps to track changes and ensure consistency *across different nodes* before committing the transaction. In Pessimistic Concurrency Control, a transaction might acquire a lock via *Zookeeper*, ensuring that no other transaction across any node can access the locked data until the lock is released.
In one of the interview when I explained about distributed concurrency using OCC, the interviewer said these all stuffs are theoretical and for interview preparation theories and later got rejected.
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?
Hi @ConceptandCoding I have one question, so for read committed you mentioned that dirty read is prevented as a write lock is acquired by one transaction and no other transaction can read it during then But wanted to understand what happens when 2 transactions at the sane time try to read and write, so which transaction would get the lock first and how is this decided?
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.
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.
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
Hey why is deadlock not possible in OCC, in if it used read committed, it still acquires X lock when write and if another transaction acquires lock for another row deadlock is still possible right? Also can u share the notes for sys des as well?
Read Phase: The application reads the data along with a version number or timestamp. Compute Phase: The application processes the data without holding any locks. Validation Phase: Before committing the transaction, the application checks if the data has been modified by another transaction using the version number or timestamp. If the data has changed, the transaction is aborted and retried. Why Deadlocks are Unlikely in OCC Short-Lived Locks: In OCC, locks are typically held only during the brief period of the commit phase. This minimizes the window in which a deadlock can occur. Conflict Detection: Instead of waiting for locks to be released (which is a primary cause of deadlocks), transactions in OCC check for conflicts and either proceed or abort based on version checks. Scenario Where Deadlocks Can Occur Even in OCC, deadlocks can occur If two transactions try to acquire locks on multiple resources simultaneously during the commit phase, a deadlock can occur. But very unlikely because of extra validation phase.
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
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.
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.
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
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
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 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.
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?
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?
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.
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.
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.
Hi @ConceptandCoding I have one question, so for read committed you mentioned that dirty read is prevented as a write lock is acquired by one transaction and no other transaction can read it during then But wanted to understand what happens when 2 transactions at the sane time try to read and write, so which transaction would get the lock first and how is this decided?
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..?
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.
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
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
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.
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
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
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 playlist, but I am confused that at some places it is written that in OCC, no locks are used to perform Reads, not even shared locks to maximize concurrency in reads. Only (E) locks are used to perform updates on row. Can someone please clarify ?
@@sumurthdixit8482 in OCC when we read the row, we do not put into any transaction, so no locks placed. and after its work done only at the end when it want to update the data in DB, it uses the transaction because we want rollback feature right. And thats where exclusive lock is taken, and before update it also does the version check that no other transaction has updated the db
Hi Shrayansh, In case of optimistic locking, if transaction A gets the read lock and after that transaction B also gets the read lock, no one will be able to get the write lock right? that is also a dead lock state, with just one row 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 ?
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
This is one of the finest indepth tutorials out there.. Trust me no-one teaches these things with this much level of depth and practical knowledge. Even I have 5 years of experience working at Flipkart but still this cleared my concepts. I feel the premium is so much useful of your channel Shrayansh.. Keeping doing this.
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 ?
this is probably one of the most comprehensive tech videos, hands down
This video cleared all my problems on how to handle concurrency in production as well as interviews :)
Superb job done in imparting the concepts. You have an amazing talent in simplifying and detailing things.
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.
i'm really blessed for having u as my teacher and thanks for clearing all my doubts. thank u soo soo much bhayiyaaa
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
Extremely underrated playlist!! So happy to stumble upon it.
Thanks for your feedback
The IT Community is literally blessed by Having People like, who are having quest for knowledge and understanding and more intense quest of Sharing that knowledge.
Thanks a ton Shreyans.
Thanks a lot for sharing your knowledge, you´re the best professor I ever had.
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)?
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 :)
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
Hi, Can you elaborate on Snapshot isolation level, what is the locking strategy used in this isolation level.
And some more anamolies like read skew, write skew, lost update as well.
Amazing explanation,Thanks Shrayansh.
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.
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
There is a big mistake in the video, when you have explained optimistic locking. You have taken two transaction and on each write you are changing the version. Version should only be changed after commit and not write operation. Ideally all these changes are happening in memory and both the transactions are independent before the final commit.
For read uncommitted, why does it have highest consistency?? Shouldn't it be the lowest?
Thank you for this very informative and knowledgeable session.
thanks
Shaandar jabardust jindabaad very clear explanation thank you 🙏🙏
thanks
For range lock, can we say that the entire table is getting locked for a single transaction and as a result concurrency is lowest as other transactions have to wait ? Please correct me if I am wrong.
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.
@Shrayansh Jain, I also have the same doubt. Please help clarify.
Bro can you please add programatic example on Pessimaric and Opstimatic locking.
Hey bro, you do a nice job but the thing is at a lot of places you say things which are inconsistent with what you yourself tell earlier. Please try to self review your videos before publishing it. Just a suggestion I understand this might take more effort but when you are taking responsibility for sharing knowledge then misinformation and causing confusion should be avoided.
Hi Shreyansh , I wanted to understand , how a trasaction can read a uncommited change done by another transaction , is this the case of nested transactions? since if these 2 transactions are being performed by different threads , the read should be same until the change is commited by any one transaction. Please clarify
@ConceptandCoding informative Video, depth explained. Please share the notes. It will be handy during the preparation of interviews. 🙏
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.
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.?
At 5:30 you mentioned how synchronized will not work in distributed scenario with multiple processes (because no shared memory between processes). And then you mentioned that for Distributed environment we've Distributed Concurrency Control. But you missed telling how exactly this can help in distributed environment as no shared memory will still be a concern here no matter what locks we use.
So as per my understanding, in Optimistic Concurrency Control, systems use versioning or timestamps to track changes and ensure consistency *across different nodes* before committing the transaction.
In Pessimistic Concurrency Control, a transaction might acquire a lock via *Zookeeper*, ensuring that no other transaction across any node can access the locked data until the lock is released.
In one of the interview when I explained about distributed concurrency using OCC, the interviewer said these all stuffs are theoretical and for interview preparation theories and later got rejected.
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
Hi @ConceptandCoding
I have one question, so for read committed you mentioned that dirty read is prevented as a write lock is acquired by one transaction and no other transaction can read it during then
But wanted to understand what happens when 2 transactions at the sane time try to read and write, so which transaction would get the lock first and how is this decided?
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.
Hello Shrayansh I think you inverted the consistency in seralisable we get high consistency or you can use availability instead.
Thanks Shrayanah Bhai
Nice Explanation, Keep up the momentum.
Thank you
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.
Very useful content .Waiting for your 2-phase locking session
It's already there
Nice explanation thanks!!
Outstanding video Sir, and the way you explain in depth about the problem and solution is exceptional❤❤
Thanks
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
Hey why is deadlock not possible in OCC, in if it used read committed, it still acquires X lock when write and if another transaction acquires lock for another row deadlock is still possible right?
Also can u share the notes for sys des as well?
Read Phase: The application reads the data along with a version number or timestamp.
Compute Phase: The application processes the data without holding any locks.
Validation Phase: Before committing the transaction, the application checks if the data has been modified by another transaction using the version number or timestamp. If the data has changed, the transaction is aborted and retried.
Why Deadlocks are Unlikely in OCC
Short-Lived Locks: In OCC, locks are typically held only during the brief period of the commit phase. This minimizes the window in which a deadlock can occur.
Conflict Detection: Instead of waiting for locks to be released (which is a primary cause of deadlocks), transactions in OCC check for conflicts and either proceed or abort based on version checks.
Scenario Where Deadlocks Can Occur
Even in OCC, deadlocks can occur If two transactions try to acquire locks on multiple resources simultaneously during the commit phase, a deadlock can occur. But very unlikely because of extra validation phase.
How is phantom read issue occurring in Repeatable isolation when the read lock is for the transaction? can anyone answer this
it do not put range lock. if any new row is inserted in between.
Can anyone tell... Where are the notes
Great explanation👏👏
thanks
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
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.
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
Awesome explanation
Non-repeatable read❌
Non-repetetable read✅
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.
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
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?
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: ua-cam.com/video/ET_DnJgfplY/v-deo.html
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.
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
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.
Great content
Thanks
Very well explained !
thanks
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.
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
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
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.
Very nicely explained....
Thank you
very helpful video
Thanks
Hi @ConceptandCoding
I have one question, so for read committed you mentioned that dirty read is prevented as a write lock is acquired by one transaction and no other transaction can read it during then
But wanted to understand what happens when 2 transactions at the sane time try to read and write, so which transaction would get the lock first and how is this decided?
Concureeency is low for serialiser and high for red uncommited right ?
Yes right
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..?
It will update because lock is happened by same transaction only
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.
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
When is the 2PL session coming up ? :)
It's already live, pls check the HLD playlist. Today I have made it live buddy
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...
Awesome video
Thanks!
WHo writes these transactions ? DB developers ?
No, transactions (begin, rollback, commit) we app developer write it.
Nice video "-)
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 👍
thank you!!
I believe your consistency is off- read uncommitted have the least consistency
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.
Next video 🥺
Next week.
Waste of time. Not recommended video.
RIP English
LOL India would return back to the 5 century without English
hello
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.
Hi Shreyansh,
Can you please make a video on Kubernetes?
Noted
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
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
If the database also duplicates and scales, then will this work ?
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
beautifully explained all the concepts :)
Why optimistic locking can only work with READ_COMITTED isolation level , can’t we have REPEATABLE_READ with optimistic locking…
Very interesting. I've learned a lot. Thank You
Great playlist, but I am confused that at some places it is written that in OCC, no locks are used to perform Reads, not even shared locks to maximize concurrency in reads. Only (E) locks are used to perform updates on row. Can someone please clarify ?
@@sumurthdixit8482 in OCC when we read the row, we do not put into any transaction, so no locks placed.
and after its work done only at the end when it want to update the data in DB, it uses the transaction because we want rollback feature right.
And thats where exclusive lock is taken, and before update it also does the version check that no other transaction has updated the db
Video and notes not present in udemy. No point in purchasing udemy if some are exclusively present and youtube and made members only.
@@hinata4661 just an fyi: on udemy video is present
Hi Shrayansh,
In case of optimistic locking, if transaction A gets the read lock and after that transaction B also gets the read lock, no one will be able to get the write lock right?
that is also a dead lock state, with just one row right?
Transaction A will get because lock is happen by Ta and it will apply exclusive lock but another transaction cant
Good video but too many ads
i love you shrayansh
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.