Transactions

A database transaction is an isolated operation context towards a database: it is used to encapsulate operations in a given context in a safe and consistent manner, providing a configured isolation level for resources, until the end of the life of the transaction.

DeveelDB requires a transactional layer to operate, and it is mandatory to open a transaction when interacting with a database set. When accessing the database from a remote location, and no transaction is explicitly opened, the engine opens an implicit transaction that is auto-committed when the command issued is executed (eg. an SQL command to create a table, outside the scope of a transaction)

Isolation Levels

The level of isolation of the resources (eg. tables, columns, types, etc.) is defined at the moment of the construction of a transaction and dictates how the resources can be shared with other open transactions towards the same database. A transaction locks and releases resources when accessed by a query, depending on the level of isolation of the transaction: a serializable transaction, for example, acquires a lock on the resource and releases the lock at its end, while a read-committed transaction releases a lock on the committed resources after a write operation is finished, making the resource accessible from other transactions.

Serializable Transactions

[Currently the only supported isolation level]

This is the most restrictive isolation level of all: when a resource is accessed for read or write operation, it is locked by the transaction and the lock is released only at the end of the transaction (with either a commit or rollback command). Other transactions trying to access a resource locked by a serializable transaction will be put on hold on the access to the resource, until the lock is released.

Ending a Transaction

Transactions provide two commands for the termination: COMMIT [TRANSACTION] and ROLLBACK [TRANSACTION], to either persist the changes done within the transaction life-time or discard them.

The COMMIT command performs several integrity checks (to assess constraint violations, data structural alterations, default assignments, etc.), and it is a moment in which error could occur, dependent on the quality of data provided.

When a ROLLBACK command is not explicitly issued, and no COMMIT was done by the user, the operations computed during the transaction are automatically discarded (implicitly rolled-back).

Transaction Scope Variables

User can declare transaction-scope variable that are available for the whole lifetime of a transaction, to the deeper levels (eg. procedures, code blocks, etc.). In fact, a transaction is a transient variable scope, like code blocks, queries and routines: all variables declared and assigned during a transaction are discarded and disposed at the end of its lifetime.

Some of these transactions have a special handling, controlling the behavior of the transaction itself: the command to assign one of these special variables is the SQL SET TRANSACTION command.

Read-Only State

When setting a transaction as read-only, using the SET TRANSACTION READ ONLY, any attempt to modify any resource within the scope of the transaction from that moment on will arise an error. All modifications done in a transaction before making it read-only will be persisted at COMMIT.

Default Schema

This is the name of the schema to be used when an object name is not fully-qualified (it does not include all the components of the name), that will be used to resolve the object name and qualify it. Setting a default schema is done through SET TRANSACTION SCHEMA.