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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-232
Considerations for UPDATE
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-218. The default isolation
level of a transaction is determined according to the rules specified in Isolation Level
on page 10-45.
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.
In general, to avoid conflicting updates on a row, use the SERIALIZABLE isolation
level. However, note that when you use SERIALIZABLE, you are limiting concurrent
data access.
Requirements for Data in Row
Each row to be updated must satisfy the constraints of the table or underlying base
table of the view. No column updates can occur unless all of these constraints are
satisfied. (A table constraint is satisfied if the check condition is not false—that is, it is
either true or has an unknown value.)
Note. NonStop SQL/MX accepts SQL/MP keywords as synonyms for READ UNCOMMITTED,
STABLE, and SERIALIZABLE.
Embed
Embed