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’.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Atomicity :

Transactions are aborted at point of failure, by this way it ensure the operations of work units were completed successfully.

Consistency :

Upon a successsful commited transcation, it will ensure the database state is changed.

Isolation :

Transactions will operate independently and transparently.

Durability :

In case of system failure, it ensures the result of commited Transaction is persist.

Transaction Commands

Following are the commands used to control the transactions.

COMMIT :

This command will save the changes.

Syntax

COMMIT;


ROLLBACK :

This command will roll back(undo ro revert) the changes.

Syntax

ROLLBACK;


SAVEPOINT :

This command creates save points for the groups of transactions that has to be ROLLBACK.

Syntax

SAVEPOINT SAVEPOINT_NAME;


SET TRANSACTION :

Sets a name on a transaction.

Syntax

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

Pin It on Pinterest

Share This