Right now, will find out about SQL exchanges, its properties and its orders, which are known as Transaction control orders.
An exchange is a unit of work performed against a database. It is a grouping work done in a coherent request. It follows the rule of all or none for example either all the work is effective and exchange submitted or none of it will be finished for example exchange bombed so it will move back to a past spare point.
For example: Suppose Rs 5000 is moved from account A to B. At that point the exchange is finished by utilizing two little activities
- a. Deduct Rs 5000 from account A
- b. Include Rs 5000 in account B
In the event that both of the two stages bombed the exchange will allude as fizzled and the entire exchange must be done once more.
Properties of exchange
The exchange has four properties alluded by the abbreviation ACID
- Atomicity – It guarantees that all the tasks in a unit are finished effectively and the exchange is submitted, if an issue happens in any of the single activity it will stop and rollback to past spare point.
- Consistency – guarantees that the database will change on an effective submitted exchange.
- Confinement – guarantees that the alteration done by every exchange must be autonomous of one another.
- Toughness – Data changes happen because of a fruitful exchange are kept for all time.
Exchange Control Commands – Commands to control an exchange
1) COMMIT – submit order is utilized to spare changes done by an exchange on the database.
Syntax:
commit;2) ROLLBACK – rollback order is utilized to fix an exchange that isn’t submitted/spared in the database.
Syntax:
rollback;3) SAVEPOINT – a savepoint in exchange is utilized to rollback to that point with the goal that we don’t need to fix the entire exchange.
Syntax:
savepoint savepoint_name;To rollback to a savepoint of name savepoint_name: rollback to savepoint_name
4) RELEASE SAVEPOINT – This order is utilized to evacuate a savepoint.
Syntax:
release savepoint savepoint_name;