catch locked oracle sql record c#

Question

I try to update a record via c#. It works fine, but if someone else edits the same record and doesnt commit or rollback it (so the transaction is still open) the program freezes until it gets commited or rollbacked. its not a problem, but i dont want the programm to freeze. it should print an error or something.

any clues to catch it?

Answer 1

In your case you have to

1. Try locking the records for update (exclusive lock)
2. If succeeded, update them in the same transaction

So instead of just update you should execute something like that:

  select 1
    from MyTable
   where id = :prm_Id   -- the same condition as in the update part
     for update nowait; -- throw exception if the record is locked; 
                        -- "skip locked" is an alternative Oracle 11g behaviour

  update MyTable
     set myField = :prm_myField
   where id = :prm_Id;

  commit; -- or continue the transaction