An exchange is a unit of work that is performed against a data set. Exchanges are units or successions of work achieved in a legitimate 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 proliferation of at least one changes to the information base. For instance, in the event that you are making a record, refreshing a record, or erasing a record from the table, at that point you are performing exchange on the table. It is critical to control exchanges to guarantee information honesty and to deal with data set blunders.
Essentially, you will club numerous PostgreSQL questions into a gathering and you will execute every one of them all together 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 data set appropriately changes states upon an effectively dedicated exchange.
- Disconnection − Enables exchanges to work autonomously of and straightforward to one another.
- Solidness − Ensures that the outcome or impact of a submitted exchange endures in the event of a framework disappointment.
Exchange Control
The accompanying orders are utilized to control exchanges −
- Start TRANSACTION − To begin an exchange.
- Submit − 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 the DML orders INSERT, UPDATE and DELETE as it were. They can't be utilized while making tables or dropping them in light of the fact that these activities are naturally dedicated in the data set.
The BEGIN TRANSACTION Command
Exchanges can be begun utilizing BEGIN TRANSACTION or essentially BEGIN order. Such exchanges for the most part persevere until the following COMMIT or ROLLBACK order is experienced. However, an exchange will likewise ROLLBACK if the information base is shut or if a mistake happens.
Coming up next is the straightforward punctuation to begin an exchange −
BEGIN;
or
BEGIN TRANSACTION;
The COMMIT Command
The COMMIT order is the value-based order used to save changes conjured by an exchange to the data set.
The COMMIT order saves all exchanges to the information base since the last COMMIT or ROLLBACK order.
The language structure for COMMIT order is as per the following −
COMMIT;
or
END TRANSACTION;
The ROLLBACK Command
The ROLLBACK order is the conditional order used to fix exchanges that have not previously been saved to the information base.
The ROLLBACK order must be utilized to fix exchanges since the last COMMIT or ROLLBACK order was given.
The language structure for ROLLBACK order is as per the following −
ROLLBACK;
Example
Consider the COMPANY table is having the accompanying records −
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
Presently, let us start an exchange and erase records from the table having age = 25 lastly we use ROLLBACK order to fix all the changes.
testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;
In the event that you will check COMPANY table is as yet having the accompanying records −
id | name | age | address | salary
----+-------+-----+-----------+--------
1 | Paul | 32 | California| 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall| 45000
7 | James | 24 | Houston | 10000
Presently, let us start another exchange and erase records from the table having age = 25 lastly we use COMMIT order to submit all the changes.
testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;
On the off chance that you will check the COMPANY table, it actually has the accompanying records −
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
3 | Teddy | 23 | Norway | 20000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(5 rows)