Positioned updates using updatable cursors can present semantic integrity problems unless strict locking protocols are observed. Cursor updates (as used here, the word update also includes deletes) must be executed within the same transaction as the SELECT statement that defined the cursor.
Because Preprocessor2 does not support large objects, you cannot use cursors of any kind on columns defined with the XML, BLOB, or CLOB data types.
Between-Transaction Integrity Issues
By default, updatable cursors use READ locks, which are implemented as row hash locks internally. READ locks are adequate for preserving database integrity because they prevent access to the database by users who attempt to either change the definitions of database structures or to update table data. As a result, stored data cannot be changed while an open cursor is also manipulating the same data in such a way that it updates the same fields of the same rows. This is sometimes referred to as repeatable read mode. Programmers can also upgrade the locks used by updatable cursors to WRITE or even EXCLUSIVE locks if they so desire.
The potentially most severe problems for database integrity regarding updatable cursors are presented by the use of ACCESS locks, because these locks permit rows to be updated by other users while an open cursor is manipulating them, thus providing greater concurrency. The most important fact to understand about the various locking options for positioned updates via updatable cursors is that there is no integrity risk with READ and more restrictive locks, but there is an almost certain integrity risk with ACCESS locks.
Within-Transaction Integrity Issues
Between-Transaction Integrity Issues above describes the integrity risks presented by concurrent updating of a table by different transactions. This topic describes the risks associated with cursor conflicts that occur within an individual transaction.
- Two cursors are open on the same table at the same time. One attempts a positioned update, but the other has already made a positioned update on the same row, thereby modifying the table.
- While a cursor is open, a searched update is made on a row, and then the cursor attempts a positioned update on that row.
- While a cursor is open, a positioned update is made on a row through that cursor and then a searched update is attempted on the same row.
All three of these updates are dirty because the same row was updated twice without the transaction having been committed.
Note that the system permits all three of the dirty updates to occur and then returns a cursor conflict warning to the requesting program. Resolution of the integrity breach is left to the user.
Because of the multiple possible integrity risks associated with declaring and opening multiple cursors within the same transaction, you should code your applications so those risks cannot occur.