Definition – What is SQL Transaction?
- SQL Transaction is a single unit of work applied on a database.(like DB2, Oracle, MySQL and so on)
- Its a sequence of operations perfomed on database in an logical order.
- The SQL statements of an transaction will be either commited or rolled back.
- Transaction comprises with two results, they are either Success or Failure.
SQL Transaction Properties
Typically transactions have four standard properties and they are abreviated as ‘ACID’.
Transactions are aborted at point of failure, by this way it ensure the operations of work units were completed successfully.
Upon a successsful commited transcation, it will ensure the database state is changed.
Transactions will operate independently and transparently.
In case of system failure, it ensures the result of commited Transaction is persist.
Following are the commands used to control the transactions.
This command will save the changes.
This command will roll back(undo ro revert) the changes.
This command creates save points for the groups of transactions that has to be ROLLBACK.
SET TRANSACTION :
Sets a name on a transaction.
SET TRANSACTION [ READ ONLY | READ WRITE];
What is SQL Transaction Log
The SQL transaction log is a file which contains the logs that were generated during the logging process for the respective transaction occured in database.
SQL transaction logs are sequential in nature and can be split into chunks called virtual log files.
The SQL transaction log supports the below;
- Recovering Incomplete Transactions.
- Rollback SQL Transaction.
- High Availibility.
- Rolling a restored DB
Transaction log backup is nothing but backup of all database transactions occured since the last transaction log backup was taken. These backups can be done in Full and Incremental.
Read more for SQL wiki @ SQL Guide