Question Re: Oracle deadlock error in Technology Q&A

Oracle deadlock error

Former Member — Fri, 04 Oct 2013 17:16:13 GMT

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.

Re: Oracle deadlock error

Former Member — Fri, 04 Oct 2013 17:22:08 GMT

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.

Re: Oracle deadlock error

Former Member — Fri, 04 Oct 2013 17:42:00 GMT

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.

Re: Oracle deadlock error

Former Member — Sat, 05 Oct 2013 03:03:42 GMT

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?

Re: Oracle deadlock error

Former Member — Sat, 05 Oct 2013 03:27:41 GMT

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.

Re: Oracle deadlock error

Former Member — Sat, 05 Oct 2013 03:28:15 GMT

How do you force a table lock?

Re: Oracle deadlock error

Former Member — Sat, 05 Oct 2013 12:15:22 GMT

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)

Re: Oracle deadlock error

Former Member — Mon, 07 Oct 2013 16:29:29 GMT

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