SQL Transaction

SQL Transactions

A Transaction is a sequence of operations performed as a single logical unit of work.

A transaction is the propagation of one or more changes to the database.

For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table

Properties of Transactions:

Atomicity : Means either all commands completed successfully or all of them are rolled  back

Example: In an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency:  While a transaction is in progress no other sessions will interfere with the transaction. i.e. only one user can make changes to single unit of data at any single point of time.

Example : In an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation:  Each transaction is independence of any other transaction.

Example: in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability: After a transaction has completed, its effects are permanently place in the system.

Example: in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

Transaction Control:

The following commands used to control transactions:

• COMMIT: to save the changes.
• ROLLBACK: to rollback the changes.
• SAVEPOINT: creates points within groups of transactions in which to ROLLBACK
• SET TRANSACTION: Places a name on a transaction.

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They can not be used while creating tables or dropping them because these operations are automatically commited in the database.



Thanks for taking your valuable time for reading my blog-- Sadiq