To retain the flexibility of the Adaptive Server transaction model, cursors do not automatically issue begin transaction or commit transaction. The duration of locks acquired during cursor operations is entirely under the control of the application. In other words, an application that uses CUR_LOCKCC on either the dbcursoropen or dbcursor routine must also issue begin transaction for the locking to have any effect.
To hold the lock on the currently buffered rows when CUR_LOCKCC is used on dbcursoropen, the application must issue commit transaction and begin transaction before each dbcursorfetch that scrolls the local buffer (except for the very first dbcursorfetch, which should be preceded only by begin transaction).
To use the short-duration locking feature, issue begin transaction before locking the row to be updated with the CUR_LOCKCC option of dbcursor. If each update is independent, issue commit transaction after each update. If multiple updates to the same screen of data depend on each other, issue commit transaction when the screen is scrolled.
For repeatable-read consistency, specify holdlock in the select statement in dbcursoropen, and issue begin transaction before the first dbcursorfetch. Locks are obtained as the data is fetched and are retained until the application issues commit transaction or rollback transaction.
Although you can close and reopen a repeatable-read cursor, you can get the same effect with FETCH_FIRST.
Other combinations are possible as well. The important thing to remember is that locks are not held unless begin transaction is in effect. Locks acquired while begin transaction is in effect are held until a commit transaction or rollback transaction is issued.
