Overview
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. Any changes made by the statements are permanently saved to the database if they succeed.
BEGIN TRANSACTION
SQLite transactions provide the BEGIN TRANSACTION to create a transaction and COMMIT, or ROLLBACK statements to end it.
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 table data before the transaction:
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:
https://www.testingdocs.com/sqlite-database-tutorials/
For more information on SQLite, visit the official website: