Problem
I don't know how to lock rows in my Oracle database using DBTools.h++.

Cause
There are no methods in DBTools.h++ for doing explicit row-level locking.

Action
DBTools.h++ relies on the database server to be responsible for locking, based on whatever isolation level has been set. (With some databases, the RWDBConnection::isolation() method may be used to set the isolation level for the specific connection. See your DBTools.h++ access library guide and your database vendor's manual for exact details on isolation levels.)

It is possible, however, to cause a row-level lock from a DBTools.h++ program by using Oracle's "SELECT?FOR UPDATE" syntax. The "SELECT...FOR UPDATE" query will acquire an exclusive row lock which is released when the transaction is committed or rolled back.

If the NOWAIT clause is also used, the query attempt will return an error code immediately if the lock cannot be acquired. As always, usage of an error handler such as that found in Knowledge Base entry 971103-001 is highly recommended to simplify error checking.

(1) The most straightforward way of using this syntax is by executing an SQL query with RWDBConnection::executeSql(). Here is an example:

(2) Alternately, you may choose to use DBTools.h++ to construct the selector initially, then force the addition of the "FOR UPDATE" clause. This is also executed via RWDBConnection::executeSql(). Here is an example:

(4) Finally, you may use an RWDBCursor with RWDBCursor::Write privilege to produce the "SELECT FOR UPDATE" syntax. Whatever row the cursor is on will be locked, and the lock stays in effect as long as the cursor is in scope. Once the cursor goes out of scope the lock is released.

REMEMBER that the "SELECT...FOR UPDATE" syntax will aquire an exclusive row lock which is released when the transaction is committed or rolled back. If your SELECT? execution is not encapsulated in an explicit transaction, the lock will be released as soon as the select is done executing. Here's an example of putting the select in a transaction: