CODEWITHSUNDEEP
oracle-databaseoracle11gora-00054
AppleGrew
AppleGrew

Reputation: 9578

What is the reason for ORA-00054 error?

From Oracle's documentation:-

ORA-00054 resource busy and acquire with NOWAIT specified   

  Cause: Resource interested is busy.
  Action: Retry if necessary.

In our code we issue a SELECT FOR UPDATE NOWAIT command to lock the row we are about to update.

Right now the logic is if it returns SQL error 54 then it is assumed that another user is trying to update that same record. Is this logic valid?

From Oracle's documentation it looks more like if the DB is overwhelmed then this might also cause this error to be thrown.

What are the possible reasons for this error, when we are only using the above SQL command?

Upvotes: 0

Views: 12873

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

The SELECT ... FOR UPDATE attempts to acquire an RS (Row Share) lock on the table and an X (eXclusive) lock on the row. If another session has an exclusive lock on the table (eg creating an index) or an exclusive lock on the row (update, delete, or select for update) then the query will wait for the other transaction to release the lock (commit or rollback generally) unless you have specified NOWAIT.

So one possibility is to not specify NOWAIT.

I don't recognise the situation where the database might throw this error due to being "overwhelmed".

Upvotes: 2

Related Questions