Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Transaction Control Language (TCL) – Part XIV

Posted by Ravi Varma Thumati on October 15, 2009

All the changes made to the database can be referred to as a transaction. A transaction begins with DML commands but ends explicitly with transaction control commands.

Following commands categorized as Transaction control commands

  • Commit
  • Savepoint
  • Rollback

Commit

The syntax for the COMMIT statement is:

COMMIT [WORK] [COMMENT text];

The Commit statement commits all changes for the current session. Once a commit is issued, other users will be able to see your changes.

Remember that all the DDL commands are auto committed but you have to commit all the DML commands explicitly.

Savepoint Command

Savepoint command is used to mark very lengthy transaction into smaller parts. They are used to identify a point in a transaction to which we can later rollback.

Syntax:

Savepoint <savepoint_name>;

Example:

SQL> Savepoint s1;

Rollback

The syntax for the ROLLBACK statement is:

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

The Rollback statement undoes all changes for the current session up to the savepoint specified. If no savepoint is specified, then all changes are undone.

SQL> Rollback to Savepoint s1;

It will rollback transaction up to Savepoint s1. It will rollbacked all the transaction from savepoint to last

transaction.

Set Transaction

There are three transaction control functions. These are:

  1. SET TRANSACTION READ ONLY;
  2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  3. SET TRANSACTION USE ROLLBACK SEGMENT name;

Lock Table

The syntax for a Lock table is:

LOCK TABLE tables IN lock_mode MODE [NOWAIT];

Tables is a comma-delimited list of tables.

Lock_mode is one of:

ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE.

NoWait specifies that the database should not wait for a lock to be released.

 

Advertisements

One Response to “Transaction Control Language (TCL) – Part XIV”

  1. My neighbor and I have been simply debating this specific subject, he’s usually looking for to prove me incorrect. Your view on this is great and precisely how I actually feel. I simply now mailed him this web site to indicate him your personal view. After wanting over your website I ebook marked and shall be coming again to learn your new posts!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: