An exchange is a unit of work that is performed against an information base. Exchanges are units or arrangements of work achieved in a sensible request, regardless of whether in a manual style by a client or naturally by some kind of an information base program.
An exchange is the engendering of at least one changes to the information base. For instance, on the off chance that you are making, refreshing, or erasing a record from the table, at that point you are performing exchange on the table. It is imperative to control exchanges to guarantee information respectability and to deal with data set mistakes.
For all intents and purposes, you will club numerous SQLite questions into a gathering and you will execute every one of them together as a component of an exchange.
Properties of Transactions
Exchanges have the accompanying four standard properties, generally alluded to by the abbreviation ACID.
- Atomicity − Ensures that all tasks inside the work unit are finished effectively; in any case, the exchange is cut short at the purpose of disappointment and past activities are moved back to their previous state.
- Consistency − Ensures that the information base appropriately changes states upon an effectively dedicated exchange.
- Segregation − Enables exchanges to work autonomously of and straightforward to one another.
- Strength − Ensures that the outcome or impact of a submitted exchange continues if there should arise an occurrence of a framework disappointment.
Exchange Control
Following are the accompanying orders used to control exchanges:
- Start TRANSACTION − To begin an exchange.
- Commit− To save the changes, on the other hand you can utilize END TRANSACTION order.
- ROLLBACK − To rollback the changes.
Conditional control orders are just utilized with DML orders INSERT, UPDATE, and DELETE. They can't be utilized while making tables or dropping them on the grounds that these tasks are naturally dedicated in the data set.
Start TRANSACTION Command
Exchanges can be begun utilizing BEGIN TRANSACTION or just BEGIN order. Such exchanges normally persevere until the following COMMIT or ROLLBACK order is experienced. In any case, an exchange will likewise ROLLBACK if the data set is shut or if a blunder happens. Following is the basic linguistic structure to begin an exchange.
BEGIN;
or
BEGIN TRANSACTION;
COMMIT Command
Submit order is the conditional order used to save changes summoned by an exchange to the information base.
Submit order saves all exchanges to the information base since the last COMMIT or ROLLBACK order.
Following is the punctuation for COMMIT order.
COMMIT;
or
END TRANSACTION;
ROLLBACK Command
ROLLBACK order is the value-based order used to fix exchanges that have not previously been saved to the data set.
ROLLBACK order must be utilized to fix exchanges since the last COMMIT or ROLLBACK order was given.
Following is the language structure for ROLLBACK order.
ROLLBACK;
Example
Consider COMPANY table with the accompanying 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
Presently, we should begin an exchange and erase records from the table having age = 25. At that point, use ROLLBACK order to fix all the changes.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;
Presently, on the off chance that you check COMPANY table, it actually has the accompanying 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
We should begin another exchange and erase records from the table having age = 25 lastly we use COMMIT order to submit all the changes.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;
On the off chance that you currently check COMPANY table is still has the accompanying 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