SQLite transaction
Transaction (Transaction) is a unit of work on the database. Transaction (Transaction) is a logical sequence of work or complete sequence, which can be done by the user manually, it can be done automatically by some kind of database program.
Transaction (Transaction) refers to one or more changes to expand the database. For example, if you are creating a record or a record update or delete a record from the table, you are performing a transaction on the table. It is important to control the transaction to ensure the integrity of data and handle database errors.
In fact, you can put a lot of SQLite query federation as a group, all of them put together as part of a transaction for execution.
Property transactions
Transaction (Transaction) standard has the following four properties, usually based on ACID Acronym:
Atomicity (Atomicity): to ensure that all work units within the operations are completed successfully, otherwise, the transaction will be terminated in the event of a fault, before the operation will be rolled back to a previous state.
Consistency (Consistency): Ensure that the database change status correctly on the transaction successfully submitted.
Isolation (Isolation): the transaction to operate independently and transparently.
Persistent (Durability): to ensure that the transaction has been submitted results or effects in case of system failure still exists.
Transaction Control
Use the following commands to control the transaction:
BEGIN TRANSACTION: begin transaction.
COMMIT: Save your changes, or you can use the END TRANSACTIONcommand.
ROLLBACK: roll back the changes.
Transaction control commands only with DML commands INSERT, UPDATE, and DELETE together. When they can not create or delete tables using the table, because these operations in the database are automatically committed.
BEGIN TRANSACTION command
Transaction (Transaction) you can use the BEGIN TRANSACTION command or simple BEGIN command to start. Such transactions usually last execution continues, until the next COMMIT or ROLLBACK command. However, when the database is closed or an error occurs, the transaction will be rolled back. The following is the start of a transaction simple syntax:
BEGIN; or BEGIN TRANSACTION;
COMMIT command
COMMIT command is used to save changes to the database transaction call transaction command.
COMMIT command to save all the transactions since the last COMMIT or ROLLBACK command to the database.
COMMIT command syntax is as follows:
COMMIT; or END TRANSACTION;
ROLLBACK command
ROLLBACK command is a command to undo the transaction has not been saved to the database of the transaction.
ROLLBACK command can be used to undo the transaction since the last COMMIT or ROLLBACK command issued since.
Syntax ROLLBACK command is as follows:
ROLLBACK;
Examples
Suppose 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 delete records from the table age = 25, and finally, we use the ROLLBACK command to undo all changes.
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK;
Check COMPANY table, 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, delete records from the table age = 25, and finally we use the COMMIT command to commit all changes.
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> COMMIT;
Check COMPANY table, 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