In PostgreSQL, using the Serializable isolation level may be necessary because the Read Committed isolation level can lead to inconsistent results in case of conflict without read restart. Serializable isolation level also has a scalable implementation, a form of optimistic concurrency control.
In Oracle, the Read Committed isolation level is consistent with transparent restarts, and applications can function without a serializable isolation level with the proper locking.
There's no better or worse implementation, but it's essential to understand how these isolation levels work in different databases because the ANSI/ISO definitions are obsolete.
Here is an example where serializability can be acheived in Read Committed with explicit locking: The Doctor's On-Call Shift example and this works in all databases.
Hi Franck, thank you for the post. Could you please elaborate on "... Read Committed isolation level can lead to inconsistent results in case of conflict without read restart."?
Are you talking here about two SELECTs in a transaction seeing different results if another transaction committed in-between them? How would read-restart help in this case?
Or did you have an entirely different set of events in mind?
Hi, yes. I was talking about single statement consistency (reading as-of a single point in time) that is broken in PostgreSQL Read Committed because it can read from two different states. Other MVCC databases restart the statement to a newer snapshot to get it right (because in Read Committed, this is allowed to get a new read time for each statement - higher levels would have to raise a serialization error).
Here is an example: dev.to/franckpachot/comment/2bp8n
(some people will argue that it is still consistent with the SQL ANSI definition of Read Committed, but this definition ignored MVCC databases and this anomaly doesn't happen with blocking reads)
In PostgreSQL, using the Serializable isolation level may be necessary because the Read Committed isolation level can lead to inconsistent results in case of conflict without read restart. Serializable isolation level also has a scalable implementation, a form of optimistic concurrency control.
In Oracle, the Read Committed isolation level is consistent with transparent restarts, and applications can function without a serializable isolation level with the proper locking.
There's no better or worse implementation, but it's essential to understand how these isolation levels work in different databases because the ANSI/ISO definitions are obsolete.
Here is an example where serializability can be acheived in Read Committed with explicit locking: The Doctor's On-Call Shift example and this works in all databases.
Hi Franck, thank you for the post. Could you please elaborate on "... Read Committed isolation level can lead to inconsistent results in case of conflict without read restart."?
Are you talking here about two SELECTs in a transaction seeing different results if another transaction committed in-between them? How would read-restart help in this case?
Or did you have an entirely different set of events in mind?
Hi, yes. I was talking about single statement consistency (reading as-of a single point in time) that is broken in PostgreSQL Read Committed because it can read from two different states. Other MVCC databases restart the statement to a newer snapshot to get it right (because in Read Committed, this is allowed to get a new read time for each statement - higher levels would have to raise a serialization error).
Here is an example:
dev.to/franckpachot/comment/2bp8n
(some people will argue that it is still consistent with the SQL ANSI definition of Read Committed, but this definition ignored MVCC databases and this anomaly doesn't happen with blocking reads)
Thank you Franck!