Transaction control

Transaction control enforces database integrity by ensuring that batches of SQL operations run completely or not at all. The transaction control commands are BEGIN, COMMIT, and ROLLBACK.

Auto-commit transaction mode is supported. In this mode, all SQL commands commit when you run them. If the system encounters a SQL command before a BEGIN SQL command, it runs the SQL command in auto-commit transaction mode. If the system encounters a BEGIN SQL command, it runs all successive SQL commands within the transaction. To end a transaction, you must issue a COMMIT or ROLLBACK SQL command.

Some SQL commands are prohibited within the BEGIN/COMMIT transaction block. For example:
  • BEGIN
  • [CREATE | DROP] DATABASE
  • ALTER TABLE [ADD | DROP] COLUMN operations
  • SET AUTHENTICATION
  • [SET | DROP] CONNECTION
  • GROOM TABLE
  • GENERATE STATISTICS
  • SET SYSTEM DEFAULT HOSTKEY
  • [CREATE | ALTER|DROP] KEYSTORE
  • [CREATE | DROP] CRYPTO KEY
  • SET CATALOG
  • SET SCHEMA dbname.schemaname, where dbname is not the current database
These SQL commands are also prohibited within the body of a Netezza Performance Server stored procedure. If you use one of these commands within a transaction block or stored procedure, the system displays an error similar to the following message:
ERROR: CREATE DATABASE: may not be called in a transaction block or stored procedure
Note: Simultaneous sessions could throw the error Cannot insert a duplicate key into unique index. This problem occurs when there are multiple sessions connected to the same database and schema, and in those sessions users start a transaction block and issue a CREATE OBJECT object_name command with the same object_name. All of the subsequent CREATE commands for that object_name wait until the first CREATE command is committed, and then the remaining CREATE object_name commands fail with the duplicate key error. If the first CREATE OBJECT object_name command rolls back, the system moves to the next session/transaction block that issued a CREATE command for that object_name, and any remaining CREATE commands for that object_name fail with the duplicate key error. For table objects, the IF NOT EXISTS syntax inside the transaction block does not prevent the subsequent CREATE TABLE commands for object_name from failing with the duplicate key error.