SQLite Transactions (Begin, Commit, Rollback)

  Total Views : 1129
  Comments : 0
  Keywords : SQLite begin, commit and rollback transactions with examples, SQLite control transactions using begin, commit and rollback with examples

Here we will learn how to control SQLite Transactions using begin, commit and rollback commands with examples.

SQLite Transactions

Generally in SQLite transaction means it’s a set of T-SQL statements which will execute together as a unit like single T-SQL statement. If all these T-SQL statements executed successfully without having any errors then the transaction will be committed and all the changes made by the transaction will be saved to database permanently. In case if any error occurred while executing these SQLite statements then the complete transaction will be rollbacked.    

 

Generally the SQLite is in auto-commit mode that means SQLite automatically starts a transaction for each command, process and commit the transaction changes automatically to database. 

 

In case if we want to control these transactions to maintain data consistency and to handle database errors then by using following commands we can disable auto-commit mode and explicitly start the transactions based on our requirements.

 

  1. BEGIN – It starts the transaction.
  2. COMMIT – It will commit the transaction that means all the changes saved to database.
  3. ROLLBACK – It will rollback the complete transaction.

 

We can use these commands only when we are performing INSERT, UPDATE and DELETE operations. It’s not possible for us to use these commands for CREATE and DROP tables operations because those are auto-commit in database.

SQLite BEGIN Command

The BEGIN command is used to start or open a transaction. Once an explicit transaction has been opened, it will remain open until it is committed or rolled back.

 

Following is the syntax of SQLite BEGIN command.

 

BEGIN;

 

or

 

BEGIN [TRANSACTION];

We can start the transaction using BEGIN or BEGIN TRANSCATION commands. Here, the keyword TRANSACTION is optional.

SQLite COMMIT Command

The COMMIT command is used to close out the current transaction and commit the changes to the database. We can also use the alias END.

 

Following is the syntax of COMMIT command in SQLite.

 

COMMIT [TRANSACTION]

 

or

 

END [TRANSACTION]

Here TRANSACTION keyword is optional.

 

Once the COMMIT command executed successfully then all the changes are saved to database and become visible to other clients. 

 

The data in database will contain all the changes made during the transaction even if there are power problems or the system failure once COMMIT commands executed successfully.

SQLite ROLLBACK Command

By using ROLLBACK command we can cancel the transaction and roll back all the proposed changes.

 

Following is the syntax of ROLLBACK command.

 

ROLLBACK

 

or

 

ROLLBACK [TRANSACTION]

Here also TRANSACTION keyword is optional.

 

The ROLLBACK command will revert all the proposed changes made by the current transaction and then close the transaction

 

Both COMMIT and ROLLBACK will end the current transaction, putting SQLite back into Autocommit mode.

 

Now we will see how to use these SQLite transaction commands with examples.

SQLite Transaction Examples

To use transaction commands first we need to create table called emp_master and insert some data using following queries.

 

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);

 

INSERT INTO emp_master

values (1,'Honey','Patel', 10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),

(5,'Shweta','Rana',12000,3),

(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 50000,3),

(9,'Shwets','Jariwala',19400,2);

Now run the following query to check records of emp_master table.

 

sqliteSELECT FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

Now write the query like as shown following to start a transaction and delete records from emp_master table where dept_id = 3 and we will use ROLLBACK command to undo all the changes.

 

BEGIN;

DELETE FROM emp_master WHERE dept_id=3;

ROLLBACK;

If you observe above example we are beginning transaction using “BEGIN” command and deleting records from emp_master table where dept_id = 3 and immediately we are rollbacking current transaction using command “ROLLBACK”.

 

Once we execute above statements now we will check the records of emp_master table that will contain all the records like as shown below because we are rolled back current transaction without committing in database table.

 

sqliteSELECT FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

1           Honey       Patel       10100       1

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

Now, let's start another transaction and delete records from the emp_master table where emp_id = 1 and we use COMMIT command to commit all the changes like as shown below.

 

BEGIN;

DELETE FROM emp_master WHERE emp_id=1;

COMMIT;

The above statement will delete records from emp_master table where emp_id = 1 and those changes committed using COMMIT command in database so those changes will reflect in emp_master table. 

 

Now we will check the emp_master table records using following statement that will contain records other than emp_id = 1 records like as shown below.

 

sqlite>SELECT FROM emp_master;

 

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

2           Shweta      Jariwala    19300       2

3           Vinay       Jariwala    35100       3

4           Jagruti     Viras       9500        2

5           Shweta      Rana        12000       3

6           Sonal       Menpara     13000       1

7           Yamini      Patel       10000       2

8           Khyati      Shah        50000       3

9           Shwets      Jariwala    19400       2

This is how we can use transactions in SQLite to commit or rollback transaction details based on our requirements.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User