oracle db lock issue, commit

Question

This is oracle 10g. If I issue an update on a table from sqlplus but do not commit, then in another session update the same row and try to commit, it just hangs until I commit the first one. I know some would argue it's the locking mechanism but the application we have does the same thing and users are being blocked.

Whats the best way to avoid this issue?

Answer 1

Instead of just trying the update, you can issue a lock with the NOWAIT option, e.g.

SELECT * FROM mytable
WHERE xxx
FOR UPDATE NOWAIT;

If the row is locked by another user, Oracle will immediately raise ORA-00054: resource busy and acquire with NOWAIT specified.