SQL/MX 2.x Reference Manual (H06.04+)

Introduction

HP NonStop SQL/MX Reference Manual—540440-003

1-15

Rules for DML Statements

•

DML statements executing on audited tables, views of audited tables, and mixed

views must be performed within a transaction, except when reading data with

READ UNCOMMITED ACCESS.

•

If deadlock occurs, the DML statement is canceled, but the transaction continues.

Audited and Nonaudited Tables

The TMF product works only on audited tables, so a transaction does not protect

operations on nonaudited tables. The simplest approach is to make all tables audited.

The AUDIT file attribute is the default when a table is created.

Nonaudited tables are not protected by transactions and follow a different locking and

error handling model than audited tables. Certain situations such as DML error

occurrences can lead to inconsistent data within a nonaudited table or between a

nonaudited table and its indices.

Effect of AUTOCOMMIT Option

AUTOCOMMIT is an option that can be set in a SET TRANSACTION statement. It

specifies whether NonStop SQL/MX will commit automatically, or roll back if an error

occurs, at the end of statement execution. This option applies to any statement for

which the system initiates a transaction. See SET TRANSACTION Statement on

page 2-218.

If this option is set to ON, NonStop SQL/MX automatically commits any changes, or

rolls back any changes, made to the database at the end of statement execution.

AUTOCOMMIT is set ON by default at the start of an MXCI session.

If this option is set to OFF, the current transaction remains active until the end of the

MXCI session unless you explicitly commit or roll back the transaction. The default is

OFF for embedded SQL in a C or COBOL program. The default is ON for embedded

SQL in a Java program.

Concurrency

Concurrency is defined by two or more processes accessing the same data at the

same time. The degree of concurrency available—whether a process that requests

access to data that is already being accessed is given access or placed in a wait

queue—depends on the purpose of the access mode (read or update) and the

isolation level.

NonStop SQL/MX provides concurrent database access for most operations and

controls database access through the mechanism for locking and the mechanism for

opening and closing tables. For DML operations, access and locking options affect the

degree of concurrency. See Data Consistency and Access Options on page 1-7,

Database Integrity and Locking on page 1-10, and SET TRANSACTION Statement on

page 2-218.