Netezza Transaction Management – BEGIN, COMMIT, ROLLBACK

  • Post author:
  • Post last modified:November 18, 2017
  • Post category:Netezza
  • Reading time:9 mins read

A transaction is a series of one or more operations on database-related objects and/or data. The transaction management is process of ensuring the transaction is successfully completed and commited in the Netezza database. In this article, we will discuss about the Netezza transaction management using BEGIN, COMMIT and ROLLBACK. 

Read:

You can execute the DDL statements in transactions. Statements executed outside of a transaction are automatically committed.

Netezza Transaction Management – BEGIN

Use the Netezza BEGIN command to start a transaction block. You need no special privileges to use the BEGIN command in the Netezza.

Netezza BEGIN Syntax

Syntax for starting a transaction block in Netezza:

BEGIN [ WORK | TRANSACTION ];

BEGIN command takes the following optional input parameters:

WORK or TRANSACTION – are optional keywords and have no effect on the transactions.

Netezza BEGIN Examples

TRAINING.ADMIN(ADMIN)=> BEGIN WORK; 
BEGIN 
TRAINING.ADMIN(ADMIN)=> 

Netezza Transaction Management – COMMIT

You can use the Netezza COMMIT command to commit the current transaction. You must be an administrator, or an administrator must have given you the appropriate object privileges, to use this command.

Netezza COMMIT Syntax

Syntax for committing a current transaction:

COMMIT [ WORK | TRANSACTION ];

COMMIT command takes the following optional input parameters:

WORK or TRANSACTION – are optional keywords and have no effect on the transactions.

Netezza COMMIT Examples

TRAINING.ADMIN(ADMIN)=> COMMIT WORK; 
COMMIT 
TRAINING.ADMIN(ADMIN)=> 

Netezza Transaction Management – ROLLBACK

Use the ROLLBACK command to abort the current transaction. You must be an administrator, or an administrator must have given you the appropriate object privileges, to use this command.

Netezza ROLLBACK Syntax

Syntax for rolling back the current transaction:

ROLLBACK [ WORK | TRANSACTION ];

COMMIT command takes the following optional input parameters:

WORK or TRANSACTION – are optional keywords and have no effect on the transactions.

Netezza ROLLBACK Examples

TRAINING.ADMIN(ADMIN)=> ROLLBACK WORK; 
NOTICE: ROLLBACK: no transaction in progress 
ROLLBACK 
TRAINING.ADMIN(ADMIN)=> 

Netezza BEGIN, COMMIT, ROLLBACK Examples

Let us understand the Netezza BEGIN, COMMIT and ROLLBACK with an examples:

--Create sample table for demostration
TRAINING.ADMIN(ADMIN)=> CREATE TABLE tab1 ( col1 INT, col2 CHAR(10) ); 
CREATE TABLE 
TRAINING.ADMIN(ADMIN)=> select * from tab1; 
 COL1 | COL2 
------+------ 
(0 rows) 

-- insert record with transaction blocks
TRAINING.ADMIN(ADMIN)=> BEGIN WORK; INSERT INTO tab1 values ( 1, 'abc' ); COMMIT WORK; 
BEGIN 
INSERT 0 1 
COMMIT 
TRAINING.ADMIN(ADMIN)=> 

-- Check table for records
TRAINING.ADMIN(ADMIN)=> select * from tab1; 
 COL1 | COL2 
------+------------ 
 1 | abc 
(1 row)