Portable SQL/Statements/Transactions
From Linuxnetworks
Starting transactions
The ANSI SQL standard defines a statement for starting new transactions:
START TRANSACTION
Unfortunately, database vendors use various different statements for starting transactions and there is no single string that fits for all.
| Database | Statement |
|---|---|
| Firebird | SET TRANSACTION |
| MySQL | BEGIN [WORK], START TRANSACTION (4.0.11) |
| Oracle | SET TRANSACTION ISOLATION LEVEL READ COMMITTED |
| PostgreSQL | BEGIN, SET TRANSACTION mode |
| SQLite | BEGIN [TRANSACTION] |
| SQL Server | BEGIN TRANSACTION |
| Sybase ASE | BEGIN TRANSACTION |
The statement which fits for the most popular Open Source databases is BEGIN because it is understood by MySQL, PostgreSQL and SQLite.
Saving changes
Making the changes done within a transaction permanent is easily done with a single statement for all databases:
COMMIT
Discarding changes
To undo all changes done within a transaction is as easy as committing them. A single statement for all databases exists:
ROLLBACK
Back to Overview
