Blog
SQLite BEGIN TRANSACTION Statement
SQLite BEGIN TRANSACTION Statement
We can start SQLite transactions using the BEGIN TRANSACTION statement. The SQL statements are executed as a single unit in an SQLite transaction. If one of the statements fails, the entire transaction should be rolled back. If the statements succeed, any changes made are permanently saved to the database.
BEGIN TRANSACTION
SQLite transactions provide the BEGIN TRANSACTION to create a transaction and COMMIT, or ROLLBACK statements to end it.
SQLite BEGIN TRANSACTION Syntax
The general syntax of the statement is as follows:
BEGIN [DEFERRED|IMMEDIATE|EXCLUSIVE]Â TRANSACTION;
or
BEGIN;
We can optionally specify the transaction modes.
- DEFERRED
- IMMEDIATE
- EXCLUSIVE
These modes determine how transactions interact with each other in a multi-user environment.
Example
Let’s look at a simple example. The following transaction is made of two INSERT statements on the emp table. The BEGIN TRANSACTION statement starts the transaction. We can issue the COMMIT statement to save the changes made by the update statements.
BEGIN TRANSACTION;
UPDATE emp SET salary = 5000 WHERE empno = 1;
UPDATE emp SET salary = 5500 WHERE empno = 6;
The transaction updates John Smith’s and Gopal Kumar’s salaries.
We can issue the COMMIT statement to persist the changes made by the transaction.
BEGIN TRANSACTION;
UPDATE emp SET salary = 5000 WHERE empno = 1;
UPDATE emp SET salary = 5500 WHERE empno = 6;
COMMIT;
Notice that we cannot start a transaction within another transaction.
sqlite> BEGIN TRANSACTION;
sqlite> BEGIN TRANSACTION;
Runtime error: cannot start a transaction within a transaction
SQLite Tutorials
SQLite tutorials on this website: