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.