Home

Transaction Control Language

Transaction

  • Logical Unit of Work
  • Begins implicitly
  • Ended by commit work or rollback work.
  • Implicit commit can be turned off by a database directive − For example in JDBC, connection.setAutoCommit(false);

ACID

A-Atomicity
C-Consistency
I-Isolation
D-Durability

Transaction Commands in SQL:

COMMIT

To save all the transactions to the database since the last COMMIT or ROLLBACK command.

DELETE FROM instructor WHERE salary = 1000000;
COMMIT;
  • The above command deletes all the tuples from the instructor table where the salary is 1000000.
  • After deletion, the result is saved using commit. It ensures durability in a database.
  • Postgresql autocommits all the transactions and thus there is an implicit commit in every command. Thus, even without writing commit, the transaction will be commmitted.

ROLLBACK

To undo transactions that have not already been saved to the database. * To undo transactions since the last COMMIT or ROLLBACK command was issued.

DELETE FROM instructor WHERE id = '4034';
ROLLBACK;
  • The above command first deletes the tuple corresponding to the id '4034' in instructor. But before commiting, a rollback is encountered due to which the delete is reversed and nothing is deleted from the instructor table.

SAVEPOINT

A point used to roll the transaction back to a certain point without rolling back the entire transaction. Syntax for creating a savepoint:

SAVEPOINT <SAVEPOINT NAME>;

Syntax for rolling the transaction back to a savepoint:

ROLLBACK TO <SAVEPOINT NAME>;

BEGIN;
SAVEPOINT SP1;
DELETE FROM instructor WHERE id = '4034';
SAVEPOINT SP2;
DELETE FROM instructor WHERE id = '50885';
SAVEPOINT SP3;
ROLLBACK TO SP2;
  • BEGIN keyword is required to begin the transaction.
  • 3 different Savepoints have been created - namely SP1, SP2 and SP3.
  • The transaction rolls back to SP2 in the end which means that the tuple corresponding to the id '4034' has been deleted but the delere corresponding to the id '50885' has been rolled back since the savepoint is before that delete.
  • Thus the resulting relation contains the id '50885' and lacks the id '4034'.

Syntax for deleting a savepoint:

RELEASE SAVEPOINT <SAVEPOINT NAME>;
  • Can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT. Example:
Release savepoint SP1;

The savepoint named SP1 created above has been deleted.

SET TRANSACTION

Can be used to initiate a database transaction. * To specify characteristics for the transaction that follows. SYNTAX:

SET TRANSACTION [ READ WRITE | READ ONLY ];
  • The transaction is read only or read write.