Re: row locks and ODBC

In article <5h9jo7$qee@zax.whoville.leftbank.com>, cos@zax.whoville.leftbank.com says... >write back in. I'm not very familiar with the relvant Informix words >& concepts, but are "row locks" what I want to use? You probably do want row-level locking. Enabling row-level locking is set up at the database level via the ALTER TABLE tablename LOCK MODE (ROW) statement. >The developers of the ODBC application (I am not one of them, I'm just >supporting them and the database) would like to know: > - How can their programs lock the appropriate rows when they read > the data, and unlock them when they write the new data in? Assuming the ODBC driver you're using is capable, look at the API documentation for SQLSetConnectOption with the SQL_TXN_ISOLATION parameter - probably SQL_TXN_REPEATABLE_READ is what you want. You also want to set up manual transaction handling by calling SQLSetConnectOption with SQL_AUTOCOMMIT set to SQL_AUTOCOMMIT_OFF. To commit a transaction and release the locks, call SQLTransact. Most books that cover ODBC should cover these functions in much more detail. > - What happens when something has already locked that row? It depends on the wait setting and the isolation level. If the application is trying to acquire a lock on the row and has executed a SET LOCK MODE TO WAIT "x" (some interval), the program will wait "x" seconds and then return an error. If the application has an isolation level that is not attempting to acquire a lock on the row, it can read the row but won't be able to update it. If you don't know what the ODBC driver's lock mode is (often an ODBC driver will do a lot of invisible behind-the-scenes stuff), you can get this information with the onstat command. Do an "onstat -u" to find out an ODBC connection, then using the session id from that, do an "onstat -g ses <sessid>". > - What's a good way to deal with the fact that occasionally, a CGI > instance will read data and lock it, but die before it completes > its work? Can locks "time out"? It depends a lot on how the scripts are set up and what the ODBC driver does with an "improper" shutdown, but usually when an application dies, the ODBC driver should be able to detect this and shut down in an orderly manner. Usually this would mean that any work in progress would be rolled back and the rows involved unlocked. Again, this may depend a lot on the specific ODBC and CGI involved. -- William Harris william@carsinfo.com