MonetDB/SQL supports a multi-statement transaction scheme marked by START TRANSACTION and closed with either COMMIT or ROLLBACK. The session variable AUTOCOMMIT can be set to true (default) if each SQL statement should be considered an independent transaction.
In the AUTOCOMMIT mode, you can use START TRANSACTION and COMMIT/ROLLBACK to indicate transactions containing multiple SQL statements. In this case, AUTOCOMMIT is automatically disabled by a START TRANSACTION, and reenabled by a COMMIT or ROLLBACK.
If AUTOCOMMIT mode is OFF, the START TRANSACTION is implicit, and you should only use COMMIT/ROLLBACK.
WARNING: The transaction management scheme 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 is not a locking schema to avoid this. Applications may have to resort to serial execution.
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.
Start a transaction and a local transaction (turns auto-commit off) with optionally a transaction mode or isolation level.
SET TRANSACTION
| SET_LOCAL_TRANSACTION
[ READ ONLY | READ WRITE | ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } |
DIAGNOSTICS _sqlSize_ ]
Start a local transaction (turn auto-commit off) with optionally a transaction mode or isolation level
| { START | BEGIN } TRANSACTION
[ READ ONLY | READ WRITE | ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | DIAGNOSTICS _sqlSize_ ]</pre>
Disable auto-commit and start a user controlled transaction
Note: A transaction can also include data definition (DDL) commands such as CREATE, ALTER, DROP.
| COMMIT [ WORK ] [ AND [ NO ] CHAIN ]
| ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ]
| SAVEPOINT savepoint_id_name
| ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] TO SAVEPOINT savepoint_id_name
| RELEASE SAVEPOINT savepoint_id_name
You need to start a transaction first before you can use savepoints Make all changes done since the start of the transaction persistent
SET TRANSACTION;
ROLLBACK;
SET TRANSACTION READ ONLY;
ROLLBACK;
SET TRANSACTION READ WRITE;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ROLLBACK;