May 16, 2021 SQLite
Transaction is a unit of work that executes on a database. Transactions are units or sequences of work that are completed in logical order, 10 by manual operations by the user, or by some kind of database program.
Transaction refers to an extension of one or more change databases. F or example, if you are creating a record or updating a record or deleting a record from a table, you are executing a transaction on that table. It is important to control transactions to ensure the integrity of the data and to handle database errors.
In fact, you can group many SQLite queries together and put all of this together to execute as part of a transaction.
Transactions have four standard properties, typically ACID by initials:
Atomicity: Ensure that all operations within the unit of work complete successfully, otherwise the transaction terminates in the event of a failure and the previous operation is rolled back to the previous state.
Consistency: Ensures that the database correctly changes state on successfully committed transactions.
Isolation: Makes transaction operations independent and transparent to each other.
Durability: Ensure that the results or effects of a committed transaction persist in the event of a system failure.
Use the following commands to control transactions:
BEGIN TRANSACTION: Start transactions.
COMMIT: Save changes, or you can use the END TRANSACTION command.
ROLLBACK: Roll back the changes.
Transaction control commands are used only with DML commands INSERT, UPDATE, and DELETE. They cannot be used when creating or deleting tables because these operations are automatically committed in the database.
Transactions can be started using the BEGIN TRANSACTION command or a simple BEGIN command. S uch transactions typically continue until the next COMMIT or ROLLBACK command is encountered. H owever, when the database shuts down or an error occurs, the transaction is rolled back. Here's a simple syntax for starting a transaction:
BEGIN; or BEGIN TRANSACTION;
The COMMIT command is a transaction command that is used to save changes to a transaction call to the database.
The COMMIT command saves all transactions since the last COMMIT or ROLLBACK command to the database.
The syntax of the COMMIT command is as follows:
COMMIT; or END TRANSACTION;
The ROLLBACK command is a transactional command that is used to undo transactions that have not yet been saved to the database.
The ROLLBACK command can only be used to revoke transactions since the last COMMIT or ROLLBACK command was issued.
The syntax of the ROLLBACK command is as follows:
ROLLBACK;
Suppose the COMPANY table has 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's start a transaction and remove the record of age s 25 from the table, and finally, we use the ROLLBACK command to undo all changes.
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK;
Check the COMPANY table and still have 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's start another transaction, remove the record of age s 25 from the table, and finally we use the COMMIT command to commit all the changes.
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> COMMIT;
Check the COMPANY table for the following records:
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