Oracle deadlock error

[Former Member]

I have a PB 10.5 app that runs on ASE, SQL Anywhere, SQL Server and Oracle. I only have one customer on Oracle and I don't normally use it so I'm not sure what can be done.

The app ran a single DELETE statement that was deleting multiple rows from a table and got the follow error:

ORA-00060: deadlock detected while waiting for resource

They have less than 5 people using the app. I don't know how many rows are being deleted exactly but it could have been 2-3 thousand from a table with 10-20 thousand rows. The table has one blob column on it.

I am using the O10 interface and the database is Oracle 11g 11.2.0.1.0. The sqlca.DBParm is blank.

[Former Member · Accepted solution]

Are multiple users issuing that same statement so they might be attempting to delete some of the same rows?

Oracle is row locking, so it's actually rather hard to get into such a deadlock.  About the only time I see it is when I'm doing DDL against a table that has a DML operation pending against it.

[Former Member · Accepted solution]

They only gave me the abort info, nothing about what was going on.

This is my WizSource Source Control and they were deleting a Project which most customers would never do. Why they wanted to delete the Project I don't know. I told them that they should have everyone exit PowerBuilder before deleting a Project.

[Former Member · Accepted solution]

force a lock on the entire table first then delete the rows.   that is sometimes more efficient and might prevent that.

are you sure you are only deleting from only the one table?  do you have declaritive referential integrity on it, thus possibly forcing related deletes?

[Former Member · Accepted solution]

It was the WizSource Admin program doing a Project Delete. This is the statement:

DELETE FROM WS_FILECONTENT

WHERE PROJECT_ID = :ll_project;

Prior to this it nulls out the FILECONTENT_ID on WS_FILE so their should be a reference issue. This is the only customer that ever has any problems. I don't know if it is Oracle or 'User Error'. Their English is poor so it is hard to find out what is going on.

[Former Member · Accepted solution]

How do you force a table lock?

[Former Member · Accepted solution]

Look here:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9015.htm

Also, look for v$lock for ways to reference the "lock table" so you can see who has locked.

Have you also checked every transaction to make sure you commit/rollback when finished? (I know it's obvious, but we've all been bit by it in the past)

[Former Member · Accepted solution]

Hi Roland,

i have used the following script:

execute immediate "LOCK TABLE tablename IN EXCLUSIVE MODE" using sqlca;

                    if sqlca.sqldbcode <> 0 then

     lb_ok = false

     il_errcode = sqlca.sqldbcode

     is_errtext = sqlca.sqlerrtext

                    end if

Oracle releases automatically the lock table on the subsequent Commit or Rollback.

Marco