-
Notifications
You must be signed in to change notification settings - Fork 12
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)
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.
[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.
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).
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.
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
.
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
.
- Getting Started
- Basic Principles
- Operative Contexts
- Transactions
- Database Objects
- Data Types
- SQL Execution Plan
- SQL Model
- DeveelDB SQL Dialect
- Data Types
- Routines (Functions and Procedures)
- Collaborate
- Kernel Development
- External Contributions
- Bug Reporting
- Documentation