Oracle blocking table while updating

Question

I have a database, which is used by some people and a service for updating personal data at the same time.

So there is the possibility, that a person updates a row and forgets the commit. later the update-service always wants to update this row and the service hangs, until the commit will be made or the session will be closed. This may take some time, while the update-service could not do its work.

Is it possible that i can determine this situation? Probably the update-service can send a statement like this:

UPDATE person 
  SET email_address = 'new.email@company.com' WHERE person_id='1234567'
ON LOCKED ERROR;

This statement should get an error, if there is a lock on this row.

Or can I configure the oracle server to send an error code after a defined time, if the lock on a row does not finish?

Answer 1

a person updates a row and forgets the commit. later the update-service always wants to update this row and the service hangs, until the commit will be made or the session will be closed

Ideally, readers do not block writers, and writers do not block readers.

What you are describing is not a DEADLOCK scenario. When a session executes an update, it acquires a row-level exclusive lock and other session trying to update these rows, need to wait until the lock is released by a COMMIT/ROLLBACK.

Deadlock happens when two or more sessions are waiting on each other for the lock.

Or can i configure the oracle server to send an error code after a defined time, if the lock on a row does not finish?

To check the blocking session and wait class, you can query the v$session view:

select sid,
       status,
       program,
       sql_id, 
       state, 
       wait_class, 
       blocking_session_status,
       event 
from v$session;

When it comes to deadlocks, Oracle detects a deadlock automatically, throws ORA-00060: deadlock detected while waiting for resource, and rolls back one of the transactions involved in the deadlock which Oracle decided as the victim. The previous successful transactions are not rolled back. Even after the deadlock error, if a commit is issued, the previous successful transaction will be committed. At this time, the other session's transaction will also succeed and you could issue a commit. There is nothing that you need to explicitly do here. Deadlocks are automatically cleared -- you never need to clear them.

See a similar question which I answered here https://stackoverflow.com/a/28455397/3989608

For a detailed demonstration and examples of deadlocks, see Understanding Oracle Deadlock

IF you use FOR UPDATE NOWAIT, then Oracle would not let you update those rows and throw the following error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

For example,

Session 1:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10
  4  FOR UPDATE NOWAIT;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

SQL>

Session 2:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno in (10, 20)
  4  FOR UPDATE NOWAIT;
  FROM emp  WHERE
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Going a step further, if you want to avoid updating the rows which are already locked, you could use the FOR UPDATE SKIP LOCKED clause to avoid other sessions to fetch the rows for update which are already locked.

For example,

Session 1:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno = 10
  4  FOR UPDATE NOWAIT;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

SQL>

Session 2:

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno in (10, 20)
  4  FOR UPDATE NOWAIT;
  FROM emp  WHERE
       *
ERROR at line 2:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Now let's skip the rows which are locked by session 1.

SQL> SELECT empno, deptno
  2    FROM emp  WHERE
  3   deptno IN (10, 20)
  4  FOR UPDATE SKIP LOCKED;

     EMPNO     DEPTNO
---------- ----------
      7369         20
      7566         20
      7788         20
      7876         20
      7902         20

SQL>

So, department = 10 were locked by session 1 and then department = 20 are locked by session 2.

See this similar question about avoiding the updates on already locked rows Oracle deadlock keeps repeating on the same record

Answer 2

You could do it in two stages; query the table with FOR UPDATE NOWAIT, which will throw an exception if the row is already locked, and then do the update (and commit) if it doesn't error:

SELECT * FROM person WHERE person_id = 1234567 FOR UPDATE NOWAIT;
UPDATE person SET email_address = 'new.email@company.com' WHERE person_id='1234567';

If you do that from two sessions, without committing, then the second one to run the select will see:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

You could also have a timeout with WAIT <time>:

SELECT * FROM person WHERE person_id = 1234567 FOR UPDATE WAIT 3;
UPDATE person SET email_address = 'new.email@company.com' WHERE person_id='1234567';

In that case, the second caller will get an error if the first hasn't committed/rolled back in 3 seconds:

ORA-30006: resource busy; acquire with WAIT timeout expired

If you're calling this through JDBC or OCI etc., you may need to have separate back-to-back calls, on the same session, and with the first call handling the exception if it's thrown.