Isolation Levels - part X: Non-Transactional Writes

Previous isolation levels described in this series were focused on ensuring consistency of read operations and maintaining the read state from the time of reading to the commit time. Although modern databases with MVCC (Multi-Version Concurrency Control) allow for some level of consistency in read operations, the modified rows must still be locked until the end of the transaction. Successful transactions must appear as if all reads and writes happened instantaneously and atomically at the commit time.

In YugabyteDB, locks for modified rows are stored in the IntentsDB with the new version. This atomicity is achieved through a single status change in the distributed transaction table. All sessions filter the committed changes when reading the IntentsDB by checking the committed status of their transaction. The committed changes are applied later, asynchronously, to the RegularDB in the background and then deleted from the IntentsDB. This process eliminates the need to further read the Intents and transaction status in addition to the versions stored in RegularDB.

In some scenarios, such as when you are uploading large amounts of data into a table that isn't accessed by the application yet, you might not need this visibility atomicity. In this case, you can choose to consider each row visible as soon as it's written, even before the commit. Those writes escape to the current transaction visibility and are non-transactional. By doing this, bulk loading becomes faster as it can write directly to the RegularDB. This behavior is activated at the session level using yb_disable_transactional_writes and effectively modifies the write time. With this optimization, the SQL database can be as fast as a NoSQL database for fast data ingest, with all ACID guarantees once the load is completed.

This optimization does not define an isolation level but affects write visibility. It is important to mention this when discussing transaction isolation and race conditions and, like with isolation levels, the performance can be higher when the application is aware of possible concurrent transactions anomalies.

I explained how to use non-transactional write in YugabyteDB but did you know all databases can employ that non-transactional writes? For example, Oracle and PostgreSQL use them to update the sequences. A sequence stores the last value in a table. When you read the next value, it updates it to a higher value. If this was transactional, a rollback should also rollback this update. However, this is not how it works:

postgres=# create sequence my_sequence;
begin transaction;
CREATE SEQUENCE

postgres=# begin transaction;
BEGIN

postgres=*# select nextval('my_sequence');
 nextval
---------
       1
(1 row)

postgres=*# select nextval('my_sequence');
 nextval
---------
       2
(1 row)

postgres=*# rollback;
ROLLBACK

postgres=# select nextval('my_sequence');
 nextval
---------
       3
(1 row)

For higher concurrency, the update of the sequence is non-transactional. The update is immediately visible by another session, and no lock is held, even when the transaction continues.

YugabyteDB extends this possibility and allows users to disable transactional writes to speed up the operations that can bypass ACID isolation.