SQL Reference ยท Transactions and Performance
SQL BEGIN, COMMIT and ROLLBACK
Reference for SQL transaction control: BEGIN or START TRANSACTION, COMMIT and ROLLBACK, autocommit, SQLite's BEGIN modes and dialect differences.
A transaction groups several statements into one unit of work. BEGIN (or START TRANSACTION) opens it, COMMIT makes every change permanent, and ROLLBACK throws them all away. Without an explicit transaction, most databases run in autocommit mode, where each statement is its own tiny transaction.
Use transactions whenever two or more changes must succeed or fail together, such as moving money between accounts or creating an order and its line items. Inside the transaction your own session sees its changes immediately; other sessions see them only after the commit.
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The last example stops with the error cannot start a transaction within a transaction. To nest work inside a transaction use a SAVEPOINT.
Syntax
BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] [TRANSACTION]; -- SQLite START TRANSACTION [ISOLATION LEVEL level] [READ WRITE | READ ONLY]; -- standard COMMIT [TRANSACTION]; ROLLBACK [TRANSACTION];
| Part | Description |
|---|---|
BEGIN / START TRANSACTION |
Opens a transaction. START TRANSACTION is the standard spelling; BEGIN is shorter and widely accepted. |
COMMIT |
Ends the transaction and makes the changes permanent and visible to others. In SQLite END is a synonym. |
ROLLBACK |
Ends the transaction and discards every change made since it began. |
DEFERRED |
SQLite default: locks are taken when the first read or write happens. |
IMMEDIATE |
SQLite: takes the write lock right away, so later writes cannot fail with a busy error. |
EXCLUSIVE |
SQLite: like IMMEDIATE, but also blocks readers in rollback-journal mode. |
Dialect differences
| Database | Open a transaction | Notes |
|---|---|---|
| SQLite | BEGIN [DEFERRED / IMMEDIATE / EXCLUSIVE] |
One writer at a time. DDL is transactional. |
| PostgreSQL | BEGIN or START TRANSACTION |
DDL is transactional, so CREATE TABLE can be rolled back. A failed statement puts the whole transaction in an aborted state until ROLLBACK. |
| MySQL | START TRANSACTION or BEGIN |
Needs InnoDB. DDL statements such as CREATE TABLE commit implicitly. SET autocommit = 0 also starts transactions implicitly. |
| SQL Server | BEGIN TRANSACTION |
COMMIT TRANSACTION / ROLLBACK TRANSACTION. SET IMPLICIT_TRANSACTIONS ON changes the default. @@TRANCOUNT shows nesting depth. |
| Oracle | No BEGIN statement |
A transaction starts with the first change and ends at COMMIT or ROLLBACK. DDL commits implicitly. (BEGIN opens a PL/SQL block there, not a transaction.) |
-- PostgreSQL START TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- SQL Server BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION; -- Oracle: no BEGIN UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;
Usage notes
- Always finish what you start. An open transaction holds locks and prevents cleanup. Keep transactions short and never wait for user input inside one.
- A closed connection rolls back. If your program crashes before
COMMIT, the database discards the changes. - Statement errors differ. In SQLite and MySQL a failed statement undoes only itself and the transaction continues; in PostgreSQL the whole transaction must be rolled back.
- Not every statement can run inside a transaction. SQLite's
VACUUMand somePRAGMAchanges refuse to; MySQL commits implicitly on DDL. - Application code should wrap transactions in a try/except (or
try/finally) block that commits on success and rolls back on any exception. COMMITcan fail (for example a deferred constraint, or a serialization failure underSERIALIZABLE), so retry logic belongs around the whole transaction.- Reading inside a transaction gives results that depend on the isolation level.