YouTip

# SQLite Transaction A transaction is a unit of work performed against a database. A transaction is a logical sequence of work either in a unit or a sequence, which can be performed either manually by a user or automatically by some database program. A transaction is an extension that modifies the database. For example, if you are creating a record, updating a record, or deleting a record from a table, you are performing a transaction on that table. It is important to control transactions to ensure data integrity and handle database errors. In practice, you can combine many SQLite queries into a group and execute all of them together as part of a transaction. ## Properties of Transaction Transactions have the following four standard properties, usually referred to by the acronym ACID: * **Atomicity:** Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state. * **Consistency:** Ensures that the database properly changes state upon a successfully committed transaction. * **Isolation:** Enables transactions to operate independently of and transparent to each other. * **Durability:** Ensures that the result or effect of a committed transaction persists in case of a system failure. ## Transaction Control Use the following commands to control transactions: * **BEGIN TRANSACTION:** Starts transaction processing. * **COMMIT:** Saves changes, or you can use the **END TRANSACTION** command. * **ROLLBACK:** Rolls back changes that have been made. Transaction control commands are only used with DML commands INSERT, UPDATE, and DELETE. They cannot be used while creating tables or dropping tables, because these operations are automatically committed in the database. ## BEGIN TRANSACTION Command A transaction can be started using the BEGIN TRANSACTION command or simply the BEGIN command. Such transactions usually continue until the next COMMIT or ROLLBACK command is encountered. However, a transaction is also rolled back if the database is closed or an error occurs. Here is a simple syntax to start a transaction: BEGIN; or BEGIN TRANSACTION; ## COMMIT Command The COMMIT command is the transactional command used to save all the transactions to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command. The syntax for the COMMIT command is as follows: COMMIT; or END TRANSACTION; ## ROLLBACK Command The ROLLBACK command is the transactional command used to undo transactions that have not yet been saved to the database. The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued. The syntax for the ROLLBACK command is as follows: ROLLBACK; ## Example Consider the COMPANY table with the following records: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Now, let us start a transaction and delete records from the table where age = 25, and finally, we use the ROLLBACK command to undo all the changes. sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK; If you check the COMPANY table, still the following records will be present: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Now, let us start another transaction, delete all records having age = 25, and finally use the COMMIT command to commit all the changes. sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> COMMIT; If you check the COMPANY table, the following records will be present: ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 [](#)(#) (#)[](#)