Pessimistic Locking via ODBC

Hello, I am fairly new to ODBC a.k.a. Informix-CLI programming and I would like someone to confirm for me that my assumptions are correct. I have a table which contains one record: a serial number. Many concurrently executing instances of my application need to read the record, bump the value of the serial number (keep and use the new value), and store the updated value back into the database... all within the safety of a transaction. Obviously it is critical that no additional applications even read the serial number in the middle of the read-update transaction. I think that what I need is "pessimistic locking." I.e., I want to place an exclusive lock on the record before the read and not release it until I commit or rollback the transaction. If the record is locked then I want to wait for it to be unlocked before reading and proceeding into the critical region. From my reading of the ODBC docs, I think that what I want to do is something like this: // establish a connection to the database SQLSetConnectOption(..., SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF); SQLSetConnectOption(..., SQL_TXN_ISOLATION, SQL_TXN_SERIALIZABLE); SQLAllocStmt(..., &selectStatement); SQLAllocStmt(..., &updateStatement); SQLSetCursorName(selectStatement, "c1", ...); SQLExecDirect(selectStatement, "select value from Serial_Number where sn_key = 1", ...); SQLBindCol(...); SQLFetch(...); // bump the value of the serial number SQLExecDirect(updateStatement, "update Serial_Number set value=? where current of c1", ...); SQLTransact(..., SQL_COMMIT); Am I right? If not, what is the correct way to do what I want to do? When all is said and done, this should run on SQL Server and Informix (and Oracle). Thanks, -- Art Z. -- ******************************************** Pigasus Software, Inc. http://pigasus1.brick.net/ PGP key at http://pigasus1.brick.net/pgp/art.html