Statements that initiate transactions in CLI calls

Most CLI calls contain SQL statements that initiate transactions. For instance, with the exception of SQL_BLOB_CLS_REF, all CLI calls contain SQL statements for manipulating data (query, update).

An SQL transaction must consist either of SQL statements for manipulating data or SQL statements for defining or managing schemas (see "Statements within a transaction"). For this reason, it is not possible to successfully execute CLI calls in an SQL transaction, even if the transaction also consists of SQL statements for defining or managing schemas.

Isolation level

The isolation level can be used to influence the parallel processing of transactions. The individual levels and the phenomena that can occur with concurrent transactions are described in the "SET TRANSACTION - Define transaction attributes". The following effects may be seen when using BLOBs in CLI functions:

• If a BLOB value is to be read in a transaction with the isolation level SERIALIZABLE or REPEATABLE READ, any attempts on the part of concurrent transactions to update this value will be delayed until the read process is complete. This means that any updates to be carried out by concurrent transactions will either be visible in their entirety or not at all. The “phantom” phenomenon cannot occur here, since REF values are not reused.

• If a BLOB value is to be read in a transaction with the isolation level READ COMMITTED or READ UNCOMMITTED, it may be updated within the transaction by concurrent transactions. As a result, some of values read may be old while others are new. In the course of reading two segments of a BLOB value, it is even possible for the BLOB to be deleted and replaced by another BLOB with the same object number. In such cases, however, you can use the UPDATED attribute to determine when the object was last updated, and thus ensure that you are actually dealing with one and the same object.

Consistency in updates

A BLOB value is stored in several rows of the BLOB table. When replacing a BLOB value, therefore, you generally need more than one DML statement. The updating of a BLOB is not an atomic operation.

For this reason, it may be possible for an update to be only partially successful. For instance, the first BLOB_VAL_STOW call (see "SQL_BLOB_VAL_STOW - SQLbvst") for updating a BLOB value may be successful while the second fails. If this occurs, it is recommended that you reverse all updates using ROLLBACK.

In contrast, the updating of BLOB attributes and the deletion of BLOBs are atomic operations. If they fail, all values will be restored to their original status.