Franck Pachot for AWS Heroes

Posted on Oct 11 • Edited on Nov 4

A brief example of an SQL serializable transaction

#yugabytedb #postgres #oracle #database

Here are some additional details about a small live demo I presented while discussing Isolation Levels and MVCC in SQL Databases: A Technical Comparative Study. Serializable transactions must appear to occur one after the other instead of simultaneously. In this demo, I used a transaction that reads a table and writes to it only if it is initially empty.

I am creating the following table to store messages

create table demo (
 id int generated always as identity primary key
 , message varchar(80)
);

I have the following transaction that reads all messages. If the table is empty, it inserts an 'I am the first row' message.

select * from demo;
insert into demo (message) values ('I am the first row');

Oracle Database (Write Skew Anomaly)

Here is what I've run with Oracle Database (using Autonomous 23ai)

Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.07

DEMO@o23_tp>

DEMO@o23_tp> drop table demo;

Table DEMO dropped.

DEMO@o23_tp> create table demo (
  2   id int generated always as identity primary key
  3   , message varchar(80)
  4* );

Table DEMO created.

DEMO@o23_tp> set transaction isolation level serializable;

Transaction ISOLATION succeeded.

DEMO@o23_tp> select * from demo;

no rows selected

DEMO@o23_tp> --> There are no rows. I'm the first. Let's celebrate by inserting one

DEMO@o23_tp> insert into demo (message) values ('I am the first row');

Error starting at line : 1 in command -
insert into demo (message) values ('I am the first row')
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-08177: can't serialize access for this transaction
08177. 00000 -  "can't serialize access for this transaction"
*Cause:    Encountered data changed by an operation that occurred after
           the start of this serializable transaction.
*Action:   In read/write transactions, retry the intended operation or
           transaction.

More Details :
https://docs.oracle.com/error-help/db/ora-08177/

It is known that Oracle may produce false positives with serializable isolation, but getting that without any concurrent transaction is a bit rude.

Serializable errors are re-tryable errors. Let's be lucky on a second try.

DEMO@o23_tp> rollback;

Rollback complete.

DEMO@o23_tp> set transaction isolation level serializable;

Transaction ISOLATION succeeded.

DEMO@o23_tp> select * from demo;

no rows selected

DEMO@o23_tp> --> There are no rows. I'm the first. Let's celebrate by inserting one

DEMO@o23_tp> insert into demo (message) values ('I am the first row');

1 row inserted.

DEMO@o23_tp>

This insertion was successful. I left this session idle without ending the transaction and opened another one to run the same logic.

Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.07

DEMO@o23_tp> set transaction isolation level serializable;

Transaction ISOLATION succeeded.

DEMO@o23_tp> select * from demo;

no rows selected

DEMO@o23_tp> --> There are no rows. I'm the first. Let's celebrate by inserting one

DEMO@o23_tp> insert into demo (message) values ('I am the first row');

1 row inserted.

DEMO@o23_tp>

Considering both sessions, I understand that they cannot be serialized. If one occurs first, the other should have observed a row in the table, and vice versa.

This conflict was not detected with the Oracle isolation level set to serializable.

The first session to commit acts as if it were the first one.

DEMO@o23_tp> commit;

Commit complete.

DEMO@o23_tp> select * from demo;

   ID MESSAGE
_____ _____________________
    3 I am the first row

The second session does not detect the conflict between its read and write states and committed successfully.

DEMO@o23_tp> commit;

Commit complete.

DEMO@o23_tp> select * from demo;

   ID MESSAGE
_____ _____________________
    2 I am the first row
    3 I am the first row

The outcome is an anomaly, known as write skew, in which both sessions appeared to have occurred first. This is not allowed in ANSI/ISO SQL.

Oracle raised an error when there was no concurrent transaction to serialize, and it didn't when there was a serializable conflict.

I run the same in PostgreSQL and YugabyteDB to show two correct behaviors.

PostgreSQL (Serializable Snapshot Isolation Fail-on-Conflict)

I have created the table using standard SQL code to be compatible with PostgreSQL. The SELECT and INSERT statements are the same, but the transaction control is slightly different.

I initiate the first session, which creates the table and starts a transaction.

psql (16.2, server 17.0 (Debian 17.0-1.pgdg120+1))

postgres=# create table demo (
 id int generated always as identity primary key
 , message varchar(80)
);

postgres=# begin transaction isolation level serializable;
BEGIN

postgres=*# select count(*) from demo;
 count
-------
     0
(1 row)

postgres=*# --> There are no rows. I'm the first. Let's celebrate by inserting one

postgres=*# insert into demo (message) values ('I am the first row');
INSERT 0 1

In another session:

psql (16.2, server 17.0 (Debian 17.0-1.pgdg120+1))

postgres=# begin transaction isolation level serializable;
BEGIN

postgres=*# select count(*) from demo;
 count
-------
     0
(1 row)

postgres=*# --> There are no rows. I'm the first. Let's celebrate by inserting one

postgres=*# insert into demo (message) values ('I am the first row');
INSERT 0 1

postgres=*# commit;
COMMIT

The second session was able to commit. This means that PostgreSQL decided it was the first to occur, not seeing any rows and being able to insert 'I am the first row'.

Back to the first session, PostgreSQL detects a conflict between the read and write states when trying to commit.

postgres=*# commit;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

This is the expected behavior of the Serializable isolation level with Fail-On-Conflict, often called optimistic locking.

YugabyteDB (Two-Phase Commit Wait-on-Conflict)

YugabyteDB is compatible with PostgreSQL, allowing the same code to be run without any changes.

I initiate the first session, which creates the table and starts a transaction.

psql (16.2, server 11.2-YB-2024.1.3.0-b0)

postgres=# create table demo (
 id int generated always as identity primary key
 , message varchar(80)
);

postgres=# begin transaction isolation level serializable;
BEGIN

postgres=*# select count(*) from demo;
 count
-------
     0
(1 row)

postgres=*# --> There are no rows. I'm the first. Let's celebrate by inserting one

postgres=*# insert into demo (message) values ('I am the first row');
INSERT 0 1

I am leaving this session without committing yet and will run the transaction in another session.

psql (16.2, server 11.2-YB-2024.1.3.0-b0)

postgres=# begin transaction isolation level serializable;
BEGIN

postgres=*# select count(*) from demo;

YugabyteDB defaults to Wait-on-Conflict, meaning that this session waits for the other transaction to complete because what is read depends on its completion. If the other session rolls back, it can continue and show no rows. However, if the other session commits, it waits for the data to be visible.

yugabyte=*# commit;
COMMIT

The ongoing SELECT query that was waiting can continue. The rows that the other session has committed are visible. The session can continue as if it started after the other one.

yugabyte=*# select * from demo;
 id |      message
----+--------------------
  1 | I am the first row
(1 row)

At this point, my application logic will not insert 'I am the first row' because it has seen rows.

yugabyte=*# insert into demo (message) values ('Bad luck I arrived too late');
INSERT 0 1
yugabyte=*# commit;
COMMIT
yugabyte=# select * from demo;
 id  |           message
-----+-----------------------------
   1 | I am the first row
 101 | Bad luck I arrived too late
(2 rows)

The result is consistent and didn't even receive a serializable error because YugabyteDB could detect the conflict early and serialize the transactions.

Conclusion

Oracle Database doesn't implement Serializable as described by the SQL standard, and you must lock the tables you read in share mode to avoid write skew anomalies. In the past, Oracle had a 'serializable' instance setting (init.ora) set to true to acquire those locks automatically, as expected by the ANSI standard. However, it was not the default and was renamed to an undocumented parameter, "_serializable," and finally removed.
This was documented until Oracle8

SERIALIZABLE and ROW_LOCKING Parameters

Two factors determine how an instance handles locking: the SERIALIZABLE option of the SET TRANSACTION or ALTER SESSION command and the ROW_LOCKING initialization parameter. By default, SERIALIZABLE is set to FALSE and ROW_LOCKING is set to ALWAYS.

In almost every case, these parameters should not be altered. They are provided for sites that must run in ANSI/ISO compatible mode or that want to use applications written to run with earlier versions of Oracle.

Since Oracle 8i, Oracle has removed this parameter, and claims that it provides serializable transaction isolation mode as defined in the SQL standard. However, the simple example above proves this claim wrong. Oracle provides snapshot isolation, but not serializable.
It is not a problem as long as it is known because applications can achieve the same isolation with explicit locking.

Both PostgreSQL and YugabyteDB adhere to the true ANSI/ISO SQL standard for serialization. This means that transactions appear as if they were executed one after the other, and if this is not possible, one fails with a serializable error. PostgreSQL achieves this by tracking reads using range or predicate locks and detecting conflicts at commit. YugabyteDB acquires shared locks when reading (like in a two-phase commit), allowing concurrent transactions to wait rather than fail whenever possible.

Top comments (9)

Connor McDonald 🍸☕️ • Oct 14

_"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 • Oct 14

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

Franck Pachot • Oct 15

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

Ludovico Caldara • Oct 15

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 • Oct 15 • 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 • Oct 18

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.