3 ms·
You are confusing two concepts: locking used by the implementation to provide isolation and atomicity, and locking used by the application to provide consistenc
by mjb 13y ago
You are confusing two concepts: locking used by the implementation to provide isolation and atomicity, and locking used by the application to provide consistency.
Consistency is a separate concern from Isolation and Atomicity. With the default isolation level (repeatable read) it is frequently necessary to use row-level locks to ensure consistency with concurrent transactions. This is an application-level concern, and an interactions with the isolation level rather than an interaction with the implementation. Short of SSI (serializability), hazards exist which can have counterintutive impacts on data consistency. The implementation's use of MVCC is irrelevant.
Locking is also frequently used in applications to help ensure external consistency, but that's a pattern with real and significant pitfalls that should be avoided.
- Roboprog 13y agoI'm not going to claim to understand the entire topic of DB locking, but I intended to refer to what happens within the DB on your behalf when the application is running a transaction without any explicit locks. If I am within a transaction, and repeat a read for some reason, I expect the same result unless my process made modifications. I forget the name of that level, but that's what I expect. My gripe with record and related level locking comes from past pain. At one past job, our DBA spent a lot of time in Sybase (a non-MVCC DB) constantly twiddling locking granularity levels to keep processes out of each others' way. (as in deadlock)