Transactions and locking with JDBC

[timc]

I'm trying to access a Progress 9.1E database using the Progress-supplied jdbc driver in a Java application. I would like to execute simple queries without starting a transaction or locking any records. Is this possible?

I see the following in PROMON when I run queries: A simple query like 'SELECT * from table_name' starts a transaction and locks records. To avoid locking, I have to BOTH make a call to the JDBC API to set the isolation level to READ UNCOMMITTED for the session AND add the 'NOLOCK' phrase to my 'from' clause ('SELECT * from table_name NOLOCK'). But even this starts a transaction, which I would like to avoid.

Finally, I cannot get the 'NOLOCK' phrase to work with more than one table, using all kinds of explicit and implicit joins in my SQL. All help or suggestions gratefully appreciated.

Tim

 

[Casper]

Should work:
KB 20255: http://tinyurl.com/plumm

Are you sure the locks are created by the query? How do you monitor locks?

Casper.

 

[timc]

Casper,

You're right, it does work. I must have tried so many different things that I crossed myself up. Setting the transaction isolation level to 1 (READ UNCOMMITTED) prevents records from being locked, regardless of the use of the 'NOLOCK' phrase. As far as the transaction goes, according to the Progress whitepaper on locking, "Every operation performed by the SQL server operates inside a transaction." So I guess there's no way around that. Thanks for your help,

Tim