Dead Lock waiting for resource

ak77 · Post by **ak77** » Tue Dec 13, 2005 1:24 am

Hi

I am updating an oracle table
Sequential File looked up against a hash file, the matched records written to a Sequential file, the rejects update the oracle table
As I am dealing with millions of records, i am using link partioner and the transformer where the lookup takes place
I am updating the same table from the 3 output that I am getting
If i use Link collector, the job hangs at a particular record and no transaction from that point
So I avoided the Collector and directly update the table from 3 different links and all update the same table and writing the matched records in three different seq files

The job ran fine and i made some changes in the constraints and when i write the reject records to a seq file, everything is fine
when i write the record to update the table, i got dead lock error

Is this dead lock error something with the database as somebody else is accessing the table or is it because I am updating the same table from three different links?

Help me with this

Thanks
Kishan

ArndW · Post by **ArndW** » Tue Dec 13, 2005 1:30 am

Kishan,

try lowering your commit frequency to 1 or changing your locking scheme and see if it makes a difference. If the deadlock goes away then your question is answered.

ak77 · Post by **ak77** » Tue Dec 13, 2005 1:34 am

Thanks for the immediate response,

By lowering the commit frequency, you mean increase the rows per transaction in transaction handling

Kishan

ArndW · Post by **ArndW** » Tue Dec 13, 2005 1:39 am

set the rows per transaction to 1

ArndW · Post by **ArndW** » Tue Dec 13, 2005 1:53 am

AK77,

I should expand on what you might be doing instead of just stating what to do. I'll just illustrate with a simplified example -

If you have 3 processes all updating the same keys in a table and they have a transaction size of 10, meaning they will do 10 changes and then commit their work. If process #1 updates records 1,2, and 3 and then starts working on record 4 while process #2 updates 4,5 and then starts working on record 1 you will get contention - Process #1 will wait for the lock that process #2 holds on record 4; and Process #2 will wait for the lock that process #1 holds on record 1. Neither one is going to do anything so you have a deadlock condition detected by the database.

So by changing your transaction size to 1 you will remove this deadlock, since a process will only hold it's current record locked and no others. You can also change your read strategy - I'm not on DS machine at present to give you the correct terminology of the 4 options. This type of database process interaction is quite important to understanding how systems work, so it is worth your time to read up on it somewhere in your literature.

ak77 · Post by **ak77** » Tue Dec 13, 2005 2:01 am

Thanks a lot,

the job ran fine after i set the Rows per transaction to 1
and completed faster
Your explanation made it clear why this was happening

Regards,
Kishan

koolnitz · Post by **koolnitz** » Wed Dec 14, 2005 3:25 am

Hi Arnd,

I am little confused with your explaination

If you have 3 processes all updating the same keys in a table and they have a transaction size of 10, meaning they will do 10 changes and then commit their work. If process #1 updates records 1,2, and 3 and then starts working on record 4 while process #2 updates 4,5 and then starts working on record 1 you will get contention - Process #1 will wait for the lock that process #2 holds on record 4; and Process #2 will wait for the lock that process #1 holds on record 1. Neither one is going to do anything so you have a deadlock condition detected by the database.

Are you referring to database level processes?

In my case, I am inserting records into a table (DB2 database). Then, I am updating a column for all those records using SQL>After page.
My database is not partitioned.

Still, sometimes I get the below warning:

db2_PreStg_Consolidated_Reln_ins,1: Warning: djp_Consolidated_Reln_3.db2_PreStg_Consolidated_Reln_ins: [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001

How can it face a deadlock situation? First, all the records will be simply inserted. Once done, then it starts with updation.

Can you please clarify?

Thanks in advance!

ArndW · Post by **ArndW** » Wed Dec 14, 2005 1:26 pm

Koolnitz,

the message doesn't state that you had a deadlock, just that you might have. The locks available in DB/2 are not limited to record-level locking, you can also have block level and table level locks; plus you might also have other processes accessing and locking entries on your tables. The only way to know for sure is to talk with your DBA. It would seem that your post-processing update modifies the whole table, so it would wait on any locks that any process might have on the table. If the lock is not released you would get a timeout - with the same error message as you got.

koolnitz · Post by **koolnitz** » Wed Dec 14, 2005 11:58 pm

Sorry, forgot to mention that the target table is kinda' housekeeping table.
Nobody/ no program/ no batch/ no processes other than our DS job access that table.
What say?

ArndW · Post by **ArndW** » Thu Dec 15, 2005 1:09 am

koolnitz wrote:... What say?

Can't say anything more except what I suggested first - ask your DBA to monitor the table while you run it to find out what is happening. The error itself comes from DB/2 and needs to be analyzed there.

koolnitz · Post by **koolnitz** » Thu Dec 15, 2005 1:32 am

Thanks so much Arnd!
I'll definately trouble my DBA for this issue.