db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Monroe <mon...@dukece.com>
Subject RE: Torque and SQL 2005
Date Mon, 02 Feb 2009 23:17:40 GMT
I haven't really used "Implicit On" with SQL 2005, but it seems like
it may be an issue with Torque's built in connection pooling.  Torque
uses the Apache commons DBCP (DB common Pooling) code to manage it's 
connections with the DB.  This means that it can be configured to open
a set of connections with the DB and share them with different 
application threads.  

It sounds like with Implicit On, a transaction gets opened when the
pool opens a connection and this creates a transaction lock that is
being "held" until the connection is reused (or closed).

The pool size, timing, etc, are all set in the Torque properties in
the format of torque.dsfactory.<db conn name>.pool.<dbcp setting>.
Where:

   <db conn name> is a name used by Torque programmer to select
the pool to connect from.  Generally this is either "default" or 
the name set in the torque.database.default property.

   <dbcp setting> is a config property defined by the dbcp
package. See http://commons.apache.org/dbcp/configuration.html

That said the only things I can think of are:

Set the pool size to 1 connection using:

torque.database.<db conn name>.pool.maxActive = 1
torque.database.<db conn name>.pool.maxIdle = 1

but this may slow down the application using torque since it has to 
be shared among all the people.

You could write an implementation of the AbstractDataSourceFactory
class (Torque's "wrapper" for pooling) that returns a new connection
each time the getDataSource() method is called. Slower than pooling
since you're doing TCP socket stuff each time a connection is needed
and may use more total connections.

There is also a setUseTransaction method on the Criteria objects used
to create Torque queries. If this is set when you code the query 
criteria objects, Torque will wrap each call in a separate transaction.
This may or may not override the connection level transaction.

It might also be possible to disable the implicit transaction modes
with the following properties:

torque.database.<db conn name>.pool.validationQuery = 
	SET IMPLICIT_TRANSACTIONS OFF
torque.database.<db conn name>.pool.testOnBorrow = true

This will cause the pool to "test" if a connection is still valid 
(e.g., not closed by the server) with the SET command each time
a connection is requested.

Hope this makes sense.

Let us know if one of these works for you.

> -----Original Message-----
> From: Greg Monroe [mailto:monroe@dukece.com]
> Sent: Monday, February 02, 2009 4:33 PM
> To: 'Apache Torque Users List'
> Cc: 'mjdevlugt@firstcommand.com'
> Subject: FW: Torque and SQL 2005
> 
> I'm forwarding this to the torque-user@db.apache.org<mailto:torque-
> user@db.apache.org> mailing list where problem discussion should be held.
> 
> From: mjdevlugt@firstcommand.com [mailto:mjdevlugt@firstcommand.com]
> Sent: Friday, January 30, 2009 12:48 PM
> To: Greg Monroe
> Subject: RE: Torque and SQL 2005
> 
> I need to find out how to fix the optimistic blocking issues with Torque
> and SQL2005 - are their any configuration settings that can be fixed
> whether its SQL 2005 or Torque that we can change those settings for
> selects to run without escalated blocking (and the issue with "Implicit
> On" for selects?
> 
> I'm a .NET/SQL DBA so it's hard for me to see how to fix "Implicit On"
> when the transaction stays open for 30-40 minutes per connection and they
> say that Torque is where that setting is maintained within code and we
> are using app pools so we have to restart app/sql daily. It looks like
> it's a SQL2005 issue because of all the blocking, but I think it's a
> Torque/code/config/driver issue and the "it's your fault not mine" is
> happening instead of "lets get this working".  Microsoft just states the
> vendor has to fix their code and use the latest Microsoft JDBC driver -
> they are using the 1.2.4 version = maybe that is the issue too.  Using
> torque, maybe I should use the JTDS driver?  Any suggestions would be
> helpful.  The app is setup to scan a lot of records with dynamic SQL so
> SQL is escalating locks to an exclusive table lock with each process so
> it limits the number of people that can use the app.  I max about 15
> people and I need to get it usable for 400!  They have about 800 tables
> in their database.  The inserts/Updates are so quick that most of those
> transactions don't even show up in the SQL traces, so it's just the
> Selects that I am having issues with.
> 
> Well, Thanks again for responding and sending any information that will
> help me if you are able to.
> 
> Have a great weekend.
> 
> 
> Mary Myers-DeVlugt, MCDBA2008, MCITP2008, MCTS
> 
> SQL Database Analyst/DBA
> First Command Financial Services, Inc., parent of
> First Command Financial Planning, Inc.
> 1 FirstComm Plaza, Fort Worth, TX 76109-4999
> (817) 569-2043 (office)   (817) 569-2690 (fax)
> (817)  903-5198 (cell)     (817) 577-4694 (home)
> mjdevlugt@firstcommand.com<mailto:mjdevlugt@firstcommand.com>
> www.firstcommand.com<http://www.firstcommand.com>
> 
> 
> 
> ________________________________
> From: Greg Monroe [mailto:monroe@dukece.com]
> Sent: Friday, January 30, 2009 8:23 AM
> To: Myers-DeVlugt, Mary
> Subject: RE: Torque and SQL 2005
> 
> Yes, in fact, we're using 2005 for our main Torque based apps now.
> 
> From: mjdevlugt@firstcommand.com [mailto:mjdevlugt@firstcommand.com]
> Sent: Thursday, January 29, 2009 4:11 PM
> To: Greg Monroe
> Subject: Torque and SQL 2005
> 
> Is Torque supported for SQL2005?
> 
> http://db.apache.org/torque/version-specific/supported-databases.html
> 
> Thanks,
> 
> Mary Myers-DeVlugt
> mjdevlugt@firstcommand.com<mailto:mjdevlugt@firstcommand.com>
> SQL Database Analyst/DBA
> FirstCommand Financial Planning
> 817 569 2043 office
> 817 903 5198 cell or 817 577 4694 home
> 817-213-9954 or 8172139954@page.metrocall.com
> 1 FirstComm Plaza, Fort Worth, TX 76109
> "Opportunity knocks at your door every day - answer it".
> 
> 
> ________________________________
> First Command Financial Services, Inc., parent of First Command Financial
> Planning, Inc. (Member SIPC), a registered broker-dealer and registered
> investment adviser, and First Command Bank.
> 
> Financial planning services and investment products, including
> securities, are offered by First Command Financial Planning, Inc.
> Insurance products and services are offered by First Command Financial
> Services, Inc. In certain states, as required by law, First Command
> Financial Services, Inc. does business as a separate, domestic
> corporation, and does business in California as "First Command Insurance
> Services." Banking products and services are offered by First Command
> Bank. Securities products are not FDIC insured, not bank guaranteed and
> may lose value.
> 
> STATEMENT OF CONFIDENTIALITY: The information contained in this message
> or any attachments to this message are intended only for the person(s) or
> entity to which it is addressed and may contain confidential and/or
> privileged material as well as being protected from disclosure. The
> information contained herein is based on sources we believe reliable but
> is not considered all-inclusive. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is strictly prohibited. If you received this in error, please contact the
> sender immediately and delete the material from any computer.
> www.firstcommand.com
> ________________________________
> Duke CE Privacy Statement
> Please be advised that this e-mail and any files transmitted with it are
> confidential communication or may otherwise be privileged or confidential
> and are intended solely for the individual or entity to whom they are
> addressed.  If you are not the intended recipient you may not rely on the
> contents of this email or any attachments, and we ask that you please not
> read, copy or retransmit this communication, but reply to the sender and
> destroy the email, its contents, and all copies thereof immediately.  Any
> unauthorized dissemination, distribution or copying of this communication
> is strictly prohibited.
> 
> 
> 
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted with
> it are confidential communication or may otherwise be privileged or
> confidential and are intended solely for the individual or entity
> to whom they are addressed. If you are not the intended recipient
> you may not rely on the contents of this email or any attachments,
> and we ask that you please not read, copy or retransmit this
> communication, but reply to the sender and destroy the email, its
> contents, and all copies thereof immediately. Any unauthorized
> dissemination, distribution or copying of this communication is
> strictly prohibited.
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message