How can I do Oracle row-level locking with DBTools.h++?

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:

        ...
        RWDBConnection myconnection = myDB.connection();
        RWCString mySql;
        mySql = "SELECT COL1, COL2 FROM MYTABLE WHERE ID=123 FOR UPDATE NO WAIT"
        RWDBResult myresult = myconnection.executeSql(mySql);
        RWDBReader myreader = myresult.table().reader(myconnection);
        int  col1, col2;
        While (myreader())
         {
           myreader >> col1 >> col2 ;
           cout << col1 << col2  << endl;
         }
        ...

(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:

	...
	RWDBConnection myconnection = myDB.connection();
	RWDBTable mytable = myDB.table("TEST_TABLE");
	RWDBSelector mysel = myDB.selector();
	mysel << mytable["COL1"] << mytable["COL2"];
	mysel.where(mytable["ID"] == 123);
	RWDBResult myresult = myconnection.executeSql(mysel.asString() + "FOR UPDATE NOWAIT");
	RWDBReader myreader = myresult.table().reader(myconnection);
	...

Make sure that the reader is taken from the result set, not the original RWDBSelector, or the "FOR UPDATE" clause will not apply.

 

(3) You can also generate the necessary syntax with an RWDBCritFormDefinition as follows:

	...
	RWDBConnection myconnection = myDB.connection();
	RWDBTable mytable = myDB.table("TEST_TABLE");
	RWDBSelector mysel = myDB.selector();
	mysel << mytable["COL1"] << mytable["COL2"];
        RWDBCritFormDefinition funw("%0 FOR UPDATE NOWAIT");
	mysel.where(funw(mytable["ID"] == 123));
	RWDBReader myreader = mysel.reader(myconnection);
	While (myreader())
         {
	...

(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.

        ...
	RWDBConnection myconnection = myDB.connection();
	RWDBTable mytable = myDB.table("TEST_TABLE");
	RWDBSelector mysel = myDB.selector();
	mysel << mytable["COL1"] << mytable["COL2"];
	mysel.where(mytable["ID"] == 123);
        RWDBCursor cursor = mysel.cursor(myconnection,RWDBCursor::Sequential,RWDBCursor::Write);
        ...

NOTE:

 

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:

        ...
        myconnection.beginTransaction() ;
        RWDBResult myresult = myconnection.executeSql ("SELECT ...FOR UPDATE");
        ...
        ... (process result set here) ...
        if (all_okay)
           myconnection.commitTransaction();
        else
           myconnection.rollbackTransaction();
        ...