SQL
Transaction
Transactions are units of work accomplished in a logical
order, whether in a manually by a user or automatically by some sort of a
database program. A transaction is the propagation of one or more changes to
the database.
Practically, you will club many SQL queries into a
group and you will execute all of them together as a part of a transaction.
Properties
of Transactions:
Transactions have four standard properties, usually
referred to by the acronym ACID:
Atomicity: certifies
that all operations within the work unit are completed successfully; otherwise,
the transaction is terminated at the point of failure, and previous operations
are rolled back to their former state.
Consistency:
ensures that the database properly changes states upon a successfully committed
transaction.
Isolation:
enables transactions to operate independently of and transparent to each other.
Durability:
ensures that the result or effect of a committed transaction persists in case
of a system failure.
Transaction
Control
There are 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 cannot be used while creating
tables or dropping them because these operations are automatically committed in
the database.
The
COMMIT Command:
The COMMIT command is the transactional command used to
save changes invoked by a transaction to the database. The COMMIT command saves
all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax:
COMMIT;
The
ROLLBACK Command:
The ROLLBACK command is the transactional command used
to undo transactions that have not already been saved to the database. The
ROLLBACK command can only be used to undo transactions since the last COMMIT or
ROLLBACK command was issued.
Syntax:
ROLLBACK;
The
SAVEPOINT Command:
A SAVEPOINT is a point in a transaction when you can
roll the transaction back to a certain point without rolling back the entire
transaction.
Syntax:
SAVEPOINT
SAVEPOINT_NAME;
This command serves only in the creation of a SAVEPOINT
among transactional statements. The ROLLBACK command is used to undo a group of
transactions.
Syntax:
ROLLBACK TO SAVEPOINT_NAME;
The
RELEASE SAVEPOINT Command:
The RELEASE SAVEPOINT command is used to remove a
SAVEPOINT that you have created.
Syntax:
RELEASE
SAVEPOINT SAVEPOINT_NAME;
Once a SAVEPOINT has been released, you can no longer
use the ROLLBACK command to undo transactions performed since the SAVEPOINT.
The
SET TRANSACTION Command:
The SET TRANSACTION command can be used to initiate a
database transaction. This command is used to specify characteristics for the
transaction that follows.
For example, you can specify a transaction to be read
only, or read write.
Syntax:
SET TRANSACTION [ READ WRITE |
READ ONLY ];
