Dead Lock waiting for resource
Moderators: chulett, rschirm, roy
Dead Lock waiting for resource
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
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
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
set the rows per transaction to 1
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi Arnd,
I am little confused with your explaination
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!
I am little confused with your explaination
Are you referring to database level processes?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.
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!
Nitin Jain | India
If everything seems to be going well, you have obviously overlooked something.
If everything seems to be going well, you have obviously overlooked something.
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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 wrote:... What say?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>