SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-390
Considerations for UPDATE
Transaction Initiation and Termination
The UPDATE statement will automatically initiate a transaction only if TRANSACTION
AUTOBEGIN is set to ON. If a separate BEGIN WORK was issued, the UPDATE
statement operates under that transaction.
The UPDATE statement will commit the transaction if TRANSACTION AUTOCOMMIT
is set to ON. If AUTOCOMMIT is OFF, you must explicitly commit the transaction.
If a table is not audited, transactions do not apply.
Positioned UPDATE With AUTOCOMMIT
If you are using the positioned form of UPDATE, check that AUTOCOMMIT is set to
OFF before you open a cursor. Otherwise, NonStop SQL/MX commits the transaction
after each UPDATE statement and closes the cursor. Consequently, you might get
rows fetched by your cursor that are part of different transactions.
Isolation Levels of Transactions and Access Options of
Statements
The isolation level of an SQL/MX transaction defines the degree to which the
operations on data within that transaction are affected by operations of concurrent
transactions. When you specify access options for the DML statements within a
transaction, you override the isolation level of the containing transaction. Each
statement then executes with its individual access option.
You can explicitly set the isolation level of a transaction with the SET TRANSACTION
statement. See SET TRANSACTION Statement on page 2-375. The default isolation
level of a transaction is determined according to the rules specified in Isolation Level
on page 10-56.
It is important to note that the SET TRANSACTION statement might cause a dynamic
recompilation of the DML statements within the next transaction. Dynamic
recompilation occurs if NonStop SQL/MX detects a change in the transaction mode at
run time compared with the transaction mode at the time of static SQL compilation. To
avoid dynamic recompilation because of a change in the transaction mode, consider
specifying access options for individual DML statements instead of using SET
TRANSACTION.
Conflicting Updates in Concurrent Applications
If you are using the READ COMMITTED isolation level within a transaction, your
application can read different committed values for the same data at different times.
Further, two concurrent applications can update (possibly in error) the same column in
the same row.
Note. NonStop SQL/MX accepts SQL/MP keywords as synonyms for READ UNCOMMITTED,
STABLE, and SERIALIZABLE.
Embed
Embed