Hi all,
I am getting this error when i run a job.
[IBM][CLI Driver][DB2/6000] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001
This is the only job I am running and it takes 4mins usually to finish this job.I tried running this job again and I am getting the same error.
Please let me how to solve this
Thank You
deadlock error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The error message, as you can see from its tags (in square brackets) is being generated within DB2. Talk with your DBA about possible causes of deadlocks and timeouts, such as other operations happening in the database.
I was once badly bitten because overnight batch update processes were run, about which we were never informed - the job tested OK during business hours, but failed to run overnight.
Does your design select from and update the same table? This can cause deadlocks in some cases. The workaround is to stage the data (for example into a text file) so that the SELECT can complete before the updating begins.
I was once badly bitten because overnight batch update processes were run, about which we were never informed - the job tested OK during business hours, but failed to run overnight.
Does your design select from and update the same table? This can cause deadlocks in some cases. The workaround is to stage the data (for example into a text file) so that the SELECT can complete before the updating begins.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 143
- Joined: Thu Nov 04, 2004 6:53 am
Does your design select from and update the same table? This can cause deadlocks in some cases. The workaround is to stage the data (for example into a text file) so that the SELECT can complete before the updating begins.[/quote]
Ya , The job updates the same table from which it is selecting .I have reduced the transaction size for reading data from this table and it is fine now.Do you think it will be a problem later if dont stage data into a text file?
Thank You for your help
Ya , The job updates the same table from which it is selecting .I have reduced the transaction size for reading data from this table and it is fine now.Do you think it will be a problem later if dont stage data into a text file?
Thank You for your help
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Reducing the transaction size does help, though the only safe size is 1.
Otherwise all you've done is to reduce (not eliminate) the risk that you're trying to update the same page as the one from which you're selecting.
This happens in databases that use page level locking rather than row level locking, striving for efficiency of lock and I/O management.
Otherwise all you've done is to reduce (not eliminate) the risk that you're trying to update the same page as the one from which you're selecting.
This happens in databases that use page level locking rather than row level locking, striving for efficiency of lock and I/O management.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 82
- Joined: Thu Dec 02, 2004 10:27 pm
- Location: INDIA
Hi
Did you stop the job while it was running?? This will leave the database process still running. So when you start the same job aain may result in deadlocks. Ask the DBA to clear all the locks and start afresh.
Also for selects add the phrase "for read only" . That helps a lot.
Thanks
dsxuserrio
Did you stop the job while it was running?? This will leave the database process still running. So when you start the same job aain may result in deadlocks. Ask the DBA to clear all the locks and start afresh.
Also for selects add the phrase "for read only" . That helps a lot.
Thanks
dsxuserrio
dsxuserrio
Kannan.N
Bangalore,INDIA
Kannan.N
Bangalore,INDIA
Deadlock & Timeout error
Hi All,
I am also facing a similar problem.
I am using a DB2/UDB API stage to insert records into a table. In the same stage, after insertion, I am updating a column for all the inserted tuples.
Below are the details that might be helpful to analyze the problem:
DS Version: EE 7.5.1A
Stage used: DB2/UDB API
Array Size: Tried with many values (1,50,100...)
Transaction size: Tried with many values (1,50,100...)
Update Action: Insert rows without clearing
SQL>Generated: "Insert Into..." statement
SQL>After: "Update ..." statement
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"
Would appreciate if anyone can advise me to resolve this.
PN: Earlier, I was able to run the same job w/o any warnings. And there are not enough records to suck up the Database rollback segment.
Thanks in advance!!
- Nitin
I am also facing a similar problem.
I am using a DB2/UDB API stage to insert records into a table. In the same stage, after insertion, I am updating a column for all the inserted tuples.
Below are the details that might be helpful to analyze the problem:
DS Version: EE 7.5.1A
Stage used: DB2/UDB API
Array Size: Tried with many values (1,50,100...)
Transaction size: Tried with many values (1,50,100...)
Update Action: Insert rows without clearing
SQL>Generated: "Insert Into..." statement
SQL>After: "Update ..." statement
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"
Would appreciate if anyone can advise me to resolve this.
PN: Earlier, I was able to run the same job w/o any warnings. And there are not enough records to suck up the Database rollback segment.
Thanks in advance!!
- Nitin