DEV Community

A brief example of an SQL serializable transaction

Franck Pachot on October 11, 2024

Here are some additional details about a small live demo I presented while discussing Isolation Levels and MVCC in SQL Databases: A Technical Compa...

Read full post

Connor McDonald 🍸☕️ • Oct 14 '24

_"it's a bit rude to get that without any concurrent transaction."
_

Its a bit rude not to read the docs which explicitly states under what situation this will occur, and why the method you chose is not supported :-)

Franck Pachot AWS Heroes • Oct 14 '24

I can't find what's precisely not supported. No row is updated, and there is no concurrent session. SCN is higher than transaction read time, but that's still the case for subsequent inserts. My guess: index leaf block splits, including the first block creation

Connor McDonald 🍸☕️ • Oct 15 '24

Franck Pachot AWS Heroes • Oct 15 '24

It's not only the segment creation. It appears with a not empty table. I think an index block split is also a false positive for changed by another session.

SQL> create table demo (
      id int generated always as identity primary key
      , message varchar(80)
     )  segment creation immediate;

Table created.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> insert into demo (message) values ('I am the first row');
insert into demo (message) values ('I am the first row')
            *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


SQL> rollback;

Rollback complete.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> insert into demo (message) values ('I am the first row');

1 row created.

SQL> insert into demo (message) select  'I am the '||rownum||' row' from xmltable('1 to 500');

500 rows created.

SQL> commit;

Commit complete.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> insert into demo (message) select  'I am the '||rownum||' row' from xmltable('1 to 500');
insert into demo (message) select  'I am the '||rownum||' row' from xmltable('1 to 500')
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

Roman Lemeshko • Mar 10

@connor_mc_d , @franckpachot we found out that it depends on several parameters related to storage, in order to minimize false-positive ORA-08177 errors we can set ROWDEPENDENCIES for table so scn is stored not on block level, but for each row. Also higher inittrans and hash partitioning helps and is a "must have" for indexes.

Ludovico Caldara • Oct 15 '24

I would find it unbelievable if, with today's design patterns, one would still rely on serialization. Serialization is the antithesis of scalability, and over the last two decades, I haven't seen a product owner who would rather have a severely underperforming application than have such a write skew.

Franck Pachot AWS Heroes • Oct 15 '24 • Edited

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.

Franck Pachot AWS Heroes • Oct 18 '24

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.

Anirudh Nimmagadda • May 26

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?

Franck Pachot AWS Heroes • May 26

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)