A brief example of a serializable transaction with ANSI/ISO SQL

Franck Pachot - Oct 11 - - Dev Community

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)
);
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

Oracle Database

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/
Enter fullscreen mode Exit fullscreen mode

It is known that serializable in Oracle may produce false positives, but I have no idea what is happening here without any concurrent transaction.

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>
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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 has not been 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

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

PostgreSQL

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
Enter fullscreen mode Exit fullscreen mode

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=# 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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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

YugabyteDB

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

The result is consistent and didn't even receive a serializable error because YugabyteDB was able to 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 that you read in share mode to avoid write skew anomalies. In the past, Oracle had a 'serializable' instance setting (init.ora) that was set to true to acquire those locks automatically, as expected by the ASNI standard. However, it was not the default and was removed to an undocumented parameter, "_serializable," and finally removed.
This was documented: https://docs.oracle.com/cd/A58617_01/server.804/a58241/ch3.htm#2477

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.

Image description

YugabyteDB and PostgreSQL implement the correct Serializable isolation levels as defined by ANSI SQL but use different implementations. PostgreSQL uses SSI (Serializable Snapshot Isolation), tracking what was read and detecting the conflict at commit. YugabyteDB uses the 2PC (two-phase commit) algorithm, acquiring read locks to detect conflicts with concurrent writes to detect them early.

Image
New Performance Test, CodeBehind Framework VS ASP.NET Core MVC

webdev, csharp, microsoft, performance
Image
React: State X Derived State

webdev, javascript, react, beginners
Image
Fitur Bonus Yang Menggiurkan Di Slot Gold Bonanza
Image
Make your web page faster

javascript, webdev, performance, staticwebapps
Image
Netflix Clone to Level Up Your Frontend Skills

webdev, vue, frontend, javascript
Image
Online Privacy: Which Messenger Will Protect Your Data?

security, cryptocurrency, messenger
Image
A beautiful mess (v2b)

codepen
Image
Top DevOps Tools for Startups in 2024: From Jenkins to Terraform

devops, jenkins, startup, terraform
Image
Unlock the Secrets of Leading Tech Innovators in Software Development
Image
Many to Many Relationship and Flask meet React

javascript, react, python, flask
Image
React Design Patterns~Container Componets / Uncontrolled Controlled Component~

webdev, javascript, react, frontend
Image
Resizing the disk on a Vagrant virtual machine

linux, vagrant, virtualbox
Image
Latest Updates: RWA Framework
Image
Crossplane + ArgoCD: A Perfect Match for Kubernetes Automation

crossplane, argocd, kubernetes, devops
Image
Rule Engines

businesslogic, ruleengine, softwaredevelopment, techtips
Image
How do you handle lack of self-confidence in the programming field?

career, college, discuss
Image
How to Integrate OpenGraph in React to Boost SEO and Social Sharing
Image
Host a Reverse Proxy in Seconds

api, nginx, development, devops
Image
This should be everyones dream

go, webdev, natsio
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player