Transaction Management

MonetDB supports transactions and savepoints within a transaction.

Transactions allows you to execute multiple SQL statements as one unit and complete them all together or not, so discard all changes since the start of the transaction.

Within a transaction you can set named savepoints. This allows you to undo statements done since that specific savepoint was set.

transaction_stmt:
  { START | BEGIN } TRANSACTION [ transaction_mode [',' ...] ]
  | SET   [ LOCAL ] TRANSACTION [ transaction_mode [',' ...] ]
  | COMMIT   [ WORK ] [ AND [ NO ] CHAIN ]
  | ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
  | savepoint_stmt

transaction_mode:
    READ WRITE
  | ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }

savepoint_stmt:
    SAVEPOINT savepoint_id_name
  | RELEASE SAVEPOINT savepoint_id_name
  | ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] TO SAVEPOINT savepoint_id_name

A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN TRANSACTION statement (turns autocommit off) with optionally a transaction mode and/or isolation level and ending the transaction with either a COMMIT or ROLLBACK statement.

If autocommit is disabled in your session, the START TRANSACTION is implicit and you should only use COMMIT or ROLLBACK.

Before you can use savepoints you need to start a transaction first.

The transaction management scheme in MonetDB is based on optimistic concurrency control. It provides each transaction with a consistent view on the database, but updates are collected in an addendum processed on transaction commit. If at commit time it can be assured that the data prepared for update affects tables has not changed in the mean time, the results are merged. Otherwise the transaction is aborted.
This optimistic concurrency scheme is particularly useful for query dominant environments. It negatively affects long running transactions which concurrently are affected by updates on their underlying tables. The same holds for applications that tries to perform concurrent updates from multiple threads in a single application. They should be internally serialized by the application to avoid unexpected transaction aborts.
Optimistic concurrency control may be confusing for those who built online-transaction applications, because the granularity of the concurrency control scheme will show higher then expected transaction failures. There isn't a locking scheme to avoid this. Applications may have to resort to serial execution.

Info: Besides the standard data manipulation (DML) commands MonetDB also supports data definition (DDL) commands CREATE, ALTER, DROP and bulk DML commands like COPY INTO table FROM to be part of a transaction! This is quite unique capability.

Note: The tuples being deleted are only marked as such. They do not reduce the table size. You will even experience after many updates that your queries will run slower, because each query first has to establish a consistent private view on the database by inspection of the delete/update lists. It calls for a vacuum cleaning algorithm in the background, which is not yet available.

Examples

START TRANSACTION;
-- auto commit mode: off
CREATE TABLE customer (a INT PRIMARY KEY, b CHAR (20) NOT NULL);
-- operation successful
INSERT INTO customer VALUES (10, 'Anna');
-- 1 affected row
COMMIT;
-- auto commit mode: on

SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Anna   |
+------+--------+

SET TRANSACTION;
-- auto commit mode: off
INSERT INTO customer VALUES (15, 'John');
-- 1 affected row
DELETE FROM customer WHERE b = 'Anna';
-- 1 affected row
ALTER TABLE customer ADD COLUMN c DATE;
-- operation successful
SELECT * FROM customer;
+------+----------------------+------------+
| a    | b                    | c          |
+======+======================+============+
|   15 | John                 | null       |
+------+----------------------+------------+
ROLLBACK;
-- auto commit mode: on

SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Anna   |
+------+--------+


BEGIN TRANSACTION  READ WRITE, ISOLATION LEVEL REPEATABLE READ;
-- auto commit mode: off
-- some other statements
SAVEPOINT sp1;
-- some other statements
RELEASE SAVEPOINT sp1;
-- some other statements
SAVEPOINT sp2;
-- some other statements
ROLLBACK TO SAVEPOINT sp2;
-- some other statements
COMMIT WORK;
-- auto commit mode: on