Wednesday, January 9, 2013

Transactions

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.

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.

Set Transaction

There are three transaction control functions. These are:

SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
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.