We're using MS SQL server 2000 enterprise and Coldfusion 8 enterprise. Some auditors insisted we encrypt our SQL connection, and the DataDirect drivers in Coldfusion 8 do not support it. We installed jTDS drivers, which we'd successfully been using for the Java portions of our application for years, and immediately began receiving frequent deadlocks during heavy use periods.
I'm fairly sure the problem is in my connection string... I'm probably doing something wrong, as the documentation on doing this is pretty limited. I was hoping someone here could give it a look and tell me if I'm doing something wrong?
Limit Connections -- Unchecked
Restrict connections to Blank
Maintain Connections Checked
Timeout (min) 20
Interval (min) 7
Disable Connections Unchecked
Login Timeout (sec) 30
CLOB -- Enable long text retrieval (CLOB). Unchecked
BLOB -- Enable binary large object retrieval (BLOB). Unchecked
Long Text Buffer (chr) 64000
Blob Buffer(bytes) 64000
The problem seems to manifest itself primarily in a session table where we're constantly inserting/deleting/updating/selecting small records in large numbers.
Thanks so much for any advice you can provide!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have recently switched to jTDS 1.2.4 driver from the MSSQL database driver.
After this switch I see my application bombing out in times of high usage with
the following deadlock exception from SQL server 2005:
"Transaction (Process ID 189) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction."
As is obvious, my application does not have any retry logic implemented for
deadlocks. But before implementing it, I would like to know if thee is any
configuration available on the jTDS driver which can make it retry failed
executions because of a deadlock.
The fact that we never received a deadlock exception with the MS SQL driver
makes me believe that perhaps that driver had some sort of retry functionality
that kept my application abstracted from this error.
Any pointers will be appreciated.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The jTDS driver doesn't have such retry-logic and to be honest, I'd doubt the
MS driver does. I'd suggest adding such logic to your application.
There has been a feature request (1835413) that I closed due to a lack of
feedback. To sum it up, I don't think such feature should be implemented in a
driver. IMHO this is more of an application-level problem, than a driver-level
problem. Shielding an application from deadlocks may just hide serious
problems within your application logic.
Cheers,
momo
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi there :)
We're using MS SQL server 2000 enterprise and Coldfusion 8 enterprise. Some auditors insisted we encrypt our SQL connection, and the DataDirect drivers in Coldfusion 8 do not support it. We installed jTDS drivers, which we'd successfully been using for the Java portions of our application for years, and immediately began receiving frequent deadlocks during heavy use periods.
I'm fairly sure the problem is in my connection string... I'm probably doing something wrong, as the documentation on doing this is pretty limited. I was hoping someone here could give it a look and tell me if I'm doing something wrong?
JDBC URL: jdbc:jtds:sqlserver://MYSERVER:1433/MYDB;ssl=request;cacheMetaData=true;appName=jTDS_CFusion
Driver Class: net.sourceforge.jtds.jdbc.Driver
Limit Connections -- Unchecked
Restrict connections to Blank
Maintain Connections Checked
Timeout (min) 20
Interval (min) 7
Disable Connections Unchecked
Login Timeout (sec) 30
CLOB -- Enable long text retrieval (CLOB). Unchecked
BLOB -- Enable binary large object retrieval (BLOB). Unchecked
Long Text Buffer (chr) 64000
Blob Buffer(bytes) 64000
The problem seems to manifest itself primarily in a session table where we're constantly inserting/deleting/updating/selecting small records in large numbers.
Thanks so much for any advice you can provide!
I am facing a similar problem.
I have recently switched to jTDS 1.2.4 driver from the MSSQL database driver.
After this switch I see my application bombing out in times of high usage with
the following deadlock exception from SQL server 2005:
"Transaction (Process ID 189) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction."
As is obvious, my application does not have any retry logic implemented for
deadlocks. But before implementing it, I would like to know if thee is any
configuration available on the jTDS driver which can make it retry failed
executions because of a deadlock.
The fact that we never received a deadlock exception with the MS SQL driver
makes me believe that perhaps that driver had some sort of retry functionality
that kept my application abstracted from this error.
Any pointers will be appreciated.
The jTDS driver doesn't have such retry-logic and to be honest, I'd doubt the
MS driver does. I'd suggest adding such logic to your application.
There has been a feature request (1835413) that I closed due to a lack of
feedback. To sum it up, I don't think such feature should be implemented in a
driver. IMHO this is more of an application-level problem, than a driver-level
problem. Shielding an application from deadlocks may just hide serious
problems within your application logic.
Cheers,
momo