SQL Dialects Reference/Transactions

From Wikibooks, open books for an open world
Jump to: navigation, search

Transactions[edit]

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version Feature Standard
SQL:2011
DB2 Firebird Ingres Linter MSSQL MySQL
Vers. 5.x
MonetDB Oracle
Vers. 11.x
PostgreSQL SQLite Virtuoso
? Start START TRANSACTION
[transaction characteristics]
Implicit SET TRANSACTION ? ? BEGIN TRAN[SACTION] BEGIN [WORK]
START [TRANSACTION]
BEGIN [WORK]
START [TRANSACTION]
Implicit BEGIN [WORK|TRANSACTION]
START TRANSACTION
BEGIN [TRANSACTION] Implicit
? Commit COMMIT [WORK] COMMIT [WORK] COMMIT [WORK] [EXEC SQL] COMMIT [WORK] ? COMMIT [WORK]
COMMIT TRAN[SACTION]
COMMIT [WORK] COMMIT [WORK] COMMIT [WORK] COMMIT [WORK|TRANSACTION] COMMIT [TRANSACTION]
END [TRANSACTION]
COMMIT WORK
? Rollback whole transaction ROLLBACK [WORK] ROLLBACK [WORK] ROLLBACK [WORK] [EXEC SQL] ROLLBACK [WORK] ? ROLLBACK [WORK]
ROLLBACK TRAN[SACTION]
ROLLBACK [WORK] ROLLBACK [WORK] ROLLBACK [WORK] ROLLBACK [WORK|TRANSACTION] ROLLBACK [TRANSACTION] ROLLBACK WORK
? Define a savepoint x, while inside a transaction SAVEPOINT x SAVEPOINT x ON ROLLBACK RETAIN CURSORS SAVEPOINT x SAVEPOINT x ? SAVE TRAN[SACTION] x SAVEPOINT x SAVEPOINT x SAVEPOINT x SAVEPOINT x SAVEPOINT x N/A
? Rollback to given savepoint x ROLLBACK [WORK] TO SAVEPOINT x ROLLBACK [WORK] TO SAVEPOINT x ROLLBACK [WORK] TO [SAVEPOINT] x [EXEC SQL] ROLLBACK [WORK] TO x ? ROLLBACK TRAN[SACTION] x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK] TO [SAVEPOINT] x ROLLBACK [WORK|TRANSACTION] TO [SAVEPOINT] x ROLLBACK [TRANSACTION] TO [SAVEPOINT] x N/A
? Release (forget) savepoint x RELEASE SAVEPOINT x RELEASE [TO] SAVEPOINT x RELEASE SAVEPOINT x N/A ? N/A RELEASE SAVEPOINT x RELEASE SAVEPOINT x N/A RELEASE [SAVEPOINT] x RELEASE [SAVEPOINT] x N/A
? Prepare transaction named id ? ? N/A ? ? ? ? N/A ? PREPARE TRANSACTION id N/A ?
? Commit prepared transaction named id ? ? N/A ? ? ? ? N/A ? COMMIT PREPARED id N/A ?
? Rollback prepared transaction named id ? ? N/A ? ? ? ? N/A ? ROLLBACK PREPARED id N/A ?
Start Commit Rollback Prepare Execute prepared
Linter Implicit
  • a COMMIT statement is executed
  • any DDL statement is executed
  • any statement is executed in AUTOCOMMIT mode
  • a ROLLBACK statement is executed
  • a user process is terminated abnormally or disconnects without COMMIT/ROLLBACK
? ?