Oracle Database Discussions

We are developing an application using JDBC to connect to Oracle 20g R2 on windows xp.

Basically I am trying to find a way of having sql statements return if they encounter locks, either immediately or after 2-3 seconds.

In MS SQL Server, we simply set a 'LockTimeout" on the database connection and any statement whatsoever that was waiting for locks woudl return afetr a given timeout with a specific error code, making it very easy to have error reporting to users

Here’s my example, where, in Oracle I get the second statement simply “waiting for the locks”. I do not want this, I need it to return after a given interval so I can let our users know “Sorry, that data is currently being modified.

Through the bulk of our product we are doing this using SELECT FOR UPDATE. This is fine for screens, but for rapidly accessed tables like user statistics etc, it is unacceptable. We want to simply issue an update statement, and have it either succeed (i.e. lock the row and perform the update) or return with an error if it cannot lock the row to perform the update.

An example.

1) We create a record, primary key being column 1.
INSERT INTO TABLE1
VALUES(1, 1, 1, 'aaaa')
/
COMMIT WORK
/


2) Then, from transaction A, we perform an update

UPDATE TABLE1
SET COL4 = ‘bbbb’
WHERE COL1 = 1
/


3) Then, without committing transaction A, transaction B comes along and tries to do the same thing. But trying to update it to ‘cccc’

UPDATE TABLE1
SET COL4 = ‘cccc’
WHERE COL1 = 1
/

In our tests, without firstly using a SELECT FOR UPDATE NOWAIT, the second update will simply hang.

Please oh Oracle guru’s tell me if there a way to do this.

Thanks in advance.

James