We’ve been using the JDBC Adapter for 6.01 without any problems until now. Our current configuration is two Adapter Configs, one with LOCAL_TRANSACTION and one with NO_TRANSACTION to facilitate start, commit and rollback functionality.
All of our transactions with the database are stored procedures. We have started getting a strange error about database deadlocks (which I take to mean that more than one resource is trying to access the same service at once). The error states that the adapter service that I am trying to invoke has become a victim of a deadlock and I must retry the transaction. I don’t have the exact error in front of me. Has anyone else seen this?
We are using JDBC adapter 6.0.2 with the latest MS SQL Server 2K JDBC Driver SP2. For one of our projects, we are using 2 JDBC NO_TRANSACTION JDBC connections (each with 400 pools). We have faced ocassions where database deadlocks occur for some transactions, and this seems to be causing the JDBC connection pools not being released. Ultimately, this would lead to errors stating that no connection pool is available since all the 400 pools did not get released. Our current workaround is to restart the IS every morning. Have you or anyone faced this before and found a better solution?
I’ve had similar issues with the JDBC adapter on wM 6.1, although in our case the deadlock only seems to occur on polling notifications (we’re using Basic Notification). Other transaction types work fine.
One thing we’ve found is that the JDBC polling notifications have an option called “overlap” that webMethods recommends that you not use. (Why they included an option that you shouldn’t use confuses me…) It didn’t solve our particular problem, but I thought I’d throw it out there in case it helps someone else.
I was wondering if anybody out there have ever used SQL Server 2000 with wM 6.0.1 and JDBC adapter 6.0.2 utilizing the latest Microsoft JDBC drivers for SQL Server 2K?n Production environment where the IS is supposed to be running 24x7, several days’ worth of transactions will normally report no JDBC connection pool available. For this particular project we are already setting the 2 “NO_TRANSACTION” connections to maximum 600 (which is quite a high number according to the wM PS consultant). Unless we restart the connections (or restart IS) the busy connections will never be released. Anybody out there with about the same setup?
We are beginning to suspect the JDBC driver provided by Microsoft since we have other JDBC connections to Oracle (with drivers provided by Oracle) which did not have this problem so far.
The database deadlock in the case of Windows2000, SQL server and webMethods6.0.1 is a known issue. There is fix for this KnowledgeBase of Advantage site. This will fix this issue.
The cause of this deadlock is due to usage of DataDirect. For more info refer
Setting the parameter SendStringParametersAsUnicode=false in the TN JDBC Connection Pool Alias also has another good affect: it prevents significant database server performance problems if
(1) your TN tables are on MS SQL Server
(2) you have a large quantity of data in your TN tables, and
(3) your code uses many calls to the wm.tn.doc:view service.
We have recently had regular periods of database server 99-100% CPU utilisation. After considerable investigation it was discovered that SQL was unable to use the CHAR indexes being supplied by wM, and was running full table scans. After making the parameter change all is now OK.
We have a similar situation, where the JDBC connection are not being released. Our environment is Oracle 8i and webMethods 6.0.1 with Oracle JDBC driver (classes12.zip). Wiered thing is that the same environment on another box runs fine.
Apparently when we call the service (an HTTPS service that posts XML message). The service calls three JDBC adapter services (I am not sure in a single flow or separate flows). We can see in the log that JDBC connection is obtained from the pool and then it executes three SQL statements but then the connection is never released. In the connection statistice we see that connection is in busy state. The connection releases only when we re-enable the JDBC connection or reload JDBCAdapter service.
Regards
Ali
Krishnan,
Thanks for your suggestion. Both fix133 and fix118 are installed on my system. Both of them are related to JDBC connection pooling. But it seems that call to releaseConnection is never made. In the JDBC (level 10) logging we see that a connection is made to the database then three sql statements are executed. However when we call the service again a new connection is again made earlier connection is not used again. Apparently it seems that releaseConnection call is never made.
Thanks
Ali
Hello,
I just tried to add the advantage fix of SendStringParametersAsUnicode=false to the jdbc adapters and the jdbc connection pools. The change for the connection pools was accepted without any problems. However, the adapters give this complaint when I try to enable them:
Error encountered
[ART.118.5042] Adapter Runtime (Connection): Unable to enable connection resource PR_ADAPTER_SQL:Pi01_App.
[ART.118.5036] Adapter Runtime (Connection): Unable to configure connection manager.
unwrapping conversion failed
The only thing I tried was to put the given string in the Other Properties field for the adapter. I tried two seperate adapters that each connect to seperate databases (SQL Server both) with this result. I don’t have a terrible time with deadlocks as I assume the statements are recalled by the adapter service.
I wouldn’t say the TN or the adapters were terribly broken to begin with, as I have never stepped through a service that got a deadlock and I only see one or two per day about two or three days a week. So should I forget it for the adapters and praise that TN will be quicker.
Good day.
Even after implementing the fix mentioned on webMethods advantage( SendStringParametersAsUnicode=false;) , we are getting the following errors frequently -
Your transaction (process ID #22) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction."
System -
JDBC Adapter 6.03 with the latest fix.
Integration Server 6.5
MS SQL Server 7
JTDS driver version 1.1
JDBC Connection Connection Properties
Other Properties text box —
SendStringParametersAsUnicode=false; drivertype = thin