| Author |
Message
|
| lars882 |
 Posted: Fri Nov 06, 2009 1:23 am Post subject: Deadlock problems with Sybase Database |
 |
|
Newbie
Joined: 04 Nov 2009
Posts: 5
|
Hi all,
I am using a flow (WMB 6.0) to write into a Sybase 12.5 database. Within the database a stored procedure is called on a regularly basis. Sometimes my flow and this procedure run into a deadlock situation.
It’s perfectly clear to me that the overall design has to be improved to eliminate the deadlock probability and I am working on this. But besides any design issues, I have got the following problem:
My flow does not handle this deadlock appropriately. Basically my flow works as follows:
[MQInput] -> [Compute] -> [Insert_Sybase] -> [Insert_Oracle] -> [MQOutput]
[Insert_Sybase] inserts the data into the Sybase table. Setting is that DB errors are not handled as exceptions.
[Insert_Oracle] was inserted by me just to collect debug information. It stores the return values of the Sybase insert (sqlcode, sqlstate, sqlnativeerror, sqlerrortext).
There are now three cases:
1.) There are no DB errors: everything is fine, both Sybase on Oracle contain the data I expect.
2.) There’s a DB error (no deadlock): I tried to insert a string value into an integer field to cause an error. As it should, there was no insert in the Sybase DB. And as I expected it, there was an entry in the Oracle DB with the error (sqlcode: -1, sqlstate: 22018, sqlnativeerror: 0, sqlerrortext: [DataDirect][ODBC Sybase Wire Protocol driver]Invalid character value. Error in parameter 8.).
The behaviour of the flow was as I expected it.
3.) There’s a deadlock: So far I saw this behaviour only in production, I was not able to produce a deadlock in test. In this case, I see an exception in my exception log:
BIP2321E: Database error: ODBC return code '-1'
BIP2322E: Database error: SQL State '40001'; Native Error Code '0'; Error Text '[DataDirect][ODBC Sybase Wire Protocol driver]1340'
These return values do not appear in my Oracle DB. What's even more confusing: My OutputMessage is written into MQOutput queue.
I don't understand this behaviour. There should be no exception but the error should be logged in the Oracle DB. Furthermore, after an exception I wouldn’t expect the flow to continue but to abort, so that my message wouldn't appear in my output queue.
Has anyone encountered this before? Is there anything I am missing?
Thanks for your help
Lars |
|
| Back to top |
|
 |
| lars882 |
| Posted: Wed Nov 11, 2009 1:56 pm Post subject: |
|
|
Newbie
Joined: 04 Nov 2009
Posts: 5
|
Hi again!
Has no one an idea?
Thanks
Lars |
|
| Back to top |
|
|
| vairavan |
| Posted: Wed Nov 11, 2009 5:28 pm Post subject: |
|
|
Apprentice
Joined: 22 Apr 2008
Posts: 42
|
Looks like... An Exception handling will solve your problem. You faced a problem while doing DB insertion, So here you dont have any flow which will handle the Database errors.
Since there is no flow to handle it, this will get continued to the next node. Try having a flow in between, If there any errors.
And deadlock is... Are you commiting anything by manual in your flow? |
|
| Back to top |
|
|
| lars882 |
| Posted: Thu Nov 12, 2009 12:07 pm Post subject: |
|
|
Newbie
Joined: 04 Nov 2009
Posts: 5
|
Hi vairavan,
but there should no exception at all as I configured the database node to not handle database errors as exceptions. So, as you write, this will go to the next node (my Insert_Oracle node), where the deadlock should be logged into my log table. As I wrote, this is not working.
To clarify this: my Insert_Oracle node should log all inserts I make into the Sybase database. This node contains also some special treatment if there's something different than a sqlcode=0 from the Sybase insert.
No, there are no manual commits in my flow.
Regards
Lars |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Nov 12, 2009 12:13 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The first thig I'd look at is the code in the stored procedure.
look at how the code in the SP handles :-
- Transactionality within the SP
- Reads from tables, especially the locking
I've experienced deadlocks on Databases where the lock was on a READ. As there was no intention to update the table or the row selected changing the read to read with no locks solved the issue.
You can dismiss the broker ide of things by using ODBC Tracing. I've also sorted out some crap SP code using this feature.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| lars882 |
| Posted: Sat Nov 14, 2009 5:53 am Post subject: |
|
|
Newbie
Joined: 04 Nov 2009
Posts: 5
|
Hi smdavies99,
as I wrote I am working together with the maintainer of the stored procedure on architectural improvemts to avoid deadlocks at all.
But I am still interested in finding out why the database error is not handled as I expect it. I have never heard of ODBC tracing, Can you give me some more information on this?
Regards
Lars |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sat Nov 14, 2009 6:50 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
If you are running on Windows then this
http://support.microsoft.com/kb/274551
may help.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| lars882 |
| Posted: Sun Nov 15, 2009 10:36 am Post subject: |
|
|
Newbie
Joined: 04 Nov 2009
Posts: 5
|
Hi smdavies99,
thanks for the clarification, at first I thought that this is some kind of tracing I could activate within my flow.
Do you know of something I can do from within the flow? As I am not the administrator of the server (Unix btw), this would make it more complicated.
Regards
Lars |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Nov 15, 2009 2:22 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
A quick google for "Unix ODBCTrace" would give you everything you need to enable it.
I've used ODBC trases to really find out what is going on between Broker and the DB(under the hood so to speak).
However, I'd really look at the coding of the SP first. This is most likely the cause of the problem. That or even badly designed tables & indexes are pretty near the top of the list for me when faced with this type of problem. If you are using a SP to do the work then the work done by broker is really at a minimum and the DB does everything.
In one case (this was using SQLServer) the Test & UAT environments were woefully underpowered. The broker was running on a 4CPU 3.6Ghz Intel box whereas the SQLServer DB was on an old PIII that was a single CPU, has inadequate memory and the network was setup to negociate for EVERY packet that passed between the two systems.
Guess where the bottleneck was?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
|
|
