![]() What happened if another transaction also wants to acquire the exclusive lock on that row (say, by trying to subtract $60 from Mary's balance)? The answer is: that transaction will be pending, either until timeout or the exclusive lock is released by our transaction. And the locks are only released when we commit (or rollback) our transaction. ![]() From this, we can have a sense that whenever we make an update (or a write) on the data, our transaction will acquire an exclusive lock on the rows that we updated. Let's continue with the familiar example of Tom's balance in the account table.Įnter fullscreen mode Exit fullscreen mode Meanwhile, if a transaction is holding an exclusive lock on a row, then no other transactions can acquire any kind of lock on that row. Simply put, if a transaction is holding a shared lock on a row, other transactions can only acquire the shared lock on that row, but not the exclusive lock. Those two locks are respectively similar to read lock and write lock in the famous readers-writers problem. ![]() But before going through this isolation level, I'd like to describe the approach that MySQL uses to eliminate the interference of UPDATE statements into our read results.įirst, we might need to go through the basics of shared lock and exclusive lock. The last transaction isolation level is called SERIALIZABLE, and it can be used to protect our transactions from being interfered with by others when running concurrently. Despite having consistent read results, the writing operations can introduce unexpected behaviors or even inconsistent data states after our transaction got committed. In the post about repeatable read isolation level, I've mentioned the serialization anomaly problem.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |