Locking & Transactions : ODBC to Progress 9.1E w/ DATADIRECT 4.10

R

ryanm

New Member
Hello,

I'm hoping someone can shed some light on the ability to lock rows in Progress via ODBC.

In short I'd like to be able to do some pessimistic locking. This works using sqlexp on our server with a transaction of the form:

SELECT x,y FROM TABLE z WHERE ... FOR UPDATE;
UPDATE z SET (x,y)=('valuex','valuey') WHERE ... ;
COMMIT;

Unfortunately I cannot get this kind of behavior where the lock is held between my SELECT and my COMMIT when using ODBC. COMMIT doesn't even appear to be supported via ODBC...

Now what confuses me more (or gives me hope) is when I read this in the Merant ODBC Guide:

Repeatable read (2) Locks are obtained for reading and modifying the database. Locks on all modified objects are held until EOT. Locks obtained for reading data are held until EOT. Locks on non-modified access structures (such as indexes and hashing structures) are released after reading.

Serializable (3) All data read or modified is locked until EOT. All access structures that are modified are locked until EOT. Access structures used by the query are locked until EOT.

However nothing I appear to do will hold a lock after a select or after an update. Also, how can I have an EOT without committing or rolling back?
 
AFAIK you should have a look a the transaction isolation level of the ODBC data source. You can do this in the ODBC data source manager of Windows (I think it's the last folder tab). IIRC, the default is something like READ-UNCOMMITTED which might or might not be sufficient for your needs.

Regards, RealHeavyDude.
 
I've tried every isolation level. First I tried by modifying the isolation level as defined in the windows odbc dsn setup -- then I tried by modifying my connection strings (I'm connecting using C# .NET) in my code. In every case I could not hold a lock for any period of time beyond the read or write.

I need to be able to lock a row, hold the lock, then commit or rollback. I need pessimistic locking via ODBC to Progress 9.1E and I can't seem to find a way to do this.
 
You must log in or register to reply here.
Back
Top