A naukri.com initiative
Dev
3w
415
Image Credit: Dev
The Doctor's On-Call Shift example: a Normalized Relational Schema to Avoid Write Skew
- Martin Kleppmann explains how write skew can occur in Read Committed isolation level in his book by using the example of multiple doctors being on-call for a shift in the hospital.
- The default isolation level is Read Committed, which allows for write skew.
- One way to address write skew in databases that do not support the ANSI/ISO isolation level is by using explicit locking to serialize transactions that work on the same data.
- The same example is demonstrated on the new schema in Read Committed isolation level with explicit locking.
- YugabyteDB bases this optimization on the SQL schema by locking a subset of a primary key.
- YugabyteDB can display row locks in the pg_locks table.
- YugabyteDB implements all SQL isolation levels according to the ANSI/ISO definition and the PostgreSQL runtime behavior.
- It also supports the PostgreSQL implicit locking SELECT FOR SHARE and SELECT FOR UPDATE.
- If your database does not guarantee serializability with isolation levels, you can still use explicit locking.
- This solution with SELECT FOR UPDATE achieves the same business logic as serializable transactions but requires more code and tests due to data consistency relying on application design.
Read Full Article
25 Likes
For uninterrupted reading, download the app