SQLite Transactions (Begin, Commit, Rollback)

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 that will execute together as a unit like a 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 the 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 an auto-commit mode that means SQLite automatically starts a transaction for each command, process, and commit the transaction changes automatically to the 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 the 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 to CREATE and DROP tables operations because those are auto-commit in the 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 the SQLite BEGIN command.

 

We can start the transaction using BEGIN or BEGIN TRANSACTION 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.

 

Here TRANSACTION keyword is optional.

 

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

 

The data in the 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 the ROLLBACK command.

 

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 a table called emp_master and insert some data using the following queries.

 

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

 

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

 

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

 

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

 

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.

 

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

 

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

 

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