ElevateDB allows for transactions in order to provide the ability to execute single or multi-table updates and have them treated as an atomic unit of work. Transactions are implemented logically in the same fashion as most other database engines, however at the physical level there are some important considerations to take into account and these will be discussed here.

Executing a Transaction

---

A transaction is started and committed or rolled back by using the START TRANSACTION, COMMIT, and ROLLBACK statements within an ElevateDB job, procedure, function, or trigger.

A typical transaction block of code looks like this:

BEGIN
   START TRANSACTION;
   -- Perform some updates to the table(s) in this database
   COMMIT;
EXCEPTION
   ROLLBACK;
END

Restricted Transactions

---

It is also possible with ElevateDB to start a restricted transaction. A restricted transaction is one that specifies only certain tables be part of the transaction. The START TRANSACTION statement accepts an optional list of tables that can be used to specify what tables should be involved in the transaction and, subsequently, locked as part of the transaction (see below regarding locking). If this list of tables is not specified (the default), then the transaction will encompass the entire database.

The following example shows how to use a restricted transaction on two tables, the Customer and Orders table:

BEGIN
   START TRANSACTION ON TABLES 'Customer', 'Orders';
   -- Perform some updates to the tables
   COMMIT;
EXCEPTION
   ROLLBACK;
END

Flushing Data to Disk During a Commit

---

By default, the COMMIT statement will cause a flush of all data to disk within the operating system. The COMMIT statement has the optional keywords NO FLUSH that will prevent the OS flush from occurring. This will improve the performance of a commit operation at the expense of possible data corruption if the application is improperly terminated after the commit takes place. This is due to the fact that the operating system may wait several minutes before it lazily flushes any modified data to disk. Please see the Buffering and Caching topic for more information.

Locking During a Transaction

---

When a transaction on the entire database is started, ElevateDB acquires a table transaction lock on all tables in the database. This prevents any other sessions from inserting, updating, or deleting any rows from the tables in the database while the current transaction is active. When a restricted transaction is started on a specific set of tables, ElevateDB will only acquire this table transaction locks on the tables specified as part of the transaction. It is very important with ElevateDB that all transactions be kept as short as possible.


Opening and Closing Tables

---

If a transaction on the entire database (not a restricted transaction) is active and a new table is opened, that table will automatically become part of the active transaction. Unlike a transaction on the entire database, if a table involved in a restricted transaction is not currently open at the time that the START TRANSACTION statement is executed, then an attempt will be made to open it at that time. Also, any tables that are opened during the restricted transaction and not initially specified as part of the restricted transaction will be excluded from the transaction. If a table involved in a transaction, either restricted or not, is closed while the transaction is still active, the table will be kept open internally by ElevateDB until the transaction is committed or rolled back, at which point the table will then be closed.

SQL and Transactions

---

The INSERT, UPDATE, or DELETE statements implicitly use a restricted transaction on the involved tables if a transaction is not already active. The interval at which the implicit transaction is committed is internally calculated to be optimal for the table being updated. If a transaction was explicitly started by the user or developer, then ElevateDB will not commit any of the effects of the SQL statement automatically, leaving the committing up to the explicit transaction.


Incompatible Operations

---

The following statements are not compatible with transactions and will cause an exception if encountered during a transaction.

BACKUP DATABASE
RESTORE DATABASE

SAVE UPDATES
LOAD UPDATES

CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
REPAIR TABLE
OPTIMIZE TABLE

CREATE VIEW
ALTER VIEW
DROP VIEW
RENAME VIEW

CREATE INDEX
CREATE TEXT INDEX
ALTER INDEX
DROP INDEX
RENAME INDEX

CREATE TRIGGER
ALTER TRIGGER
DROP TRIGGER
RENAME TRIGGER

CREATE FUNCTION
ALTER FUNCTION
DROP FUNCTION
RENAME FUNCTION

CREATE PROCEDURE
ALTER PROCEDURE
DROP PROCEDURE
RENAME PROCEDURE


CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE

CREATE INDEX
CREATE TEXT INDEX
ALTER INDEX
DROP INDEX
RENAME INDEX

These statements can be executed for temporary tables, even inside of a transaction.

Isolation Level

---

The default and only isolation level for transactions in ElevateDB is serializable. This means that only the session in which the transaction is taking place will be able to see any inserts, updates, or deletes made during the transaction. All other sessions will see the data as it existed before the transaction began. Only after the transaction is committed will other sessions see any new row inserts, updates, or deletes.

Data Integrity

---

A transaction in ElevateDB is buffered, which means that all row inserts, updates, or deletes that take place during a transaction are cached in memory for the current session and are not physically applied to the tables involved in the transaction until the transaction is committed. If the transaction is rolled back, then the updates are discarded. With a local session this allows for a fair degree of stability in the case of a power failure on the local workstation, however it will not prevent a problem if a power failure happens to occur while the commit operation is taking place. Under such circumstances it's very likely that physical and/or logical corruption of the tables involved in the transaction could take place. The only way corruption can occur with a remote session is if the ElevateDB Server itself is terminated improperly during the middle of a transaction commit. This type of occurrence is much more rare with a server than with a workstation.