Menu
What is the difference between concurrency control and transaction isolation levels?
I understand each of them clearly, however, I am having some problems relating them to each other. Specifically, I see some overlap in their functions and I'm not sure when one should use one over the other. Or should both be used together?
Also what does it mean to say pessimistic locking with repeatable read? Doesn't repeatable read already imply that all values to be edited will be locked? So why is there still a need for pessimistic locking?
215
The REPEATABLE READ transaction will still see the same data, while the READ COMMITTED transaction will see the changed row count. REPEATABLE READ is really important for reporting because it is the only way to get a consistent view of the data set even while it is being modified.
There are two models for locking data in a database: Optimistic locking , where a record is locked only when changes are committed to the database. Pessimistic locking , where a record is locked while it is edited.
In most scenarios, optimistic concurrency control is more efficient and offers higher performance. When choosing between pessimistic and optimistic locking, consider the following: Pessimistic locking is useful if there are a lot of updates and relatively high chances of users trying to update data at the same time.
In a Repeatable Read isolation level, new rows can be inserted into the dataset. In a Serializable isolation level, all the rows are locked for the duration of the transaction, no insert, update or delete is allowed.
The issue arises because there are two models for concurrency control, which are sometimes mixed by SQL implementations.
Pessimistic means rows that are read are locked. Optimistic means rows that are read are not locked.
The classic 2PL implementation of Repeatable Read is always pessimistic. The multiversion implementation of Repeatable Read is optimistic. It does not lock the rows that are read for a SELECT statement and allows other transactions to modify the rows that have been read in a SELECT. Such changes are not visible to the transaction that performed the SELECT, until it is committed.
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
