Sorry to leave this hanging for so long. We solved the problem, and
had to move into 'delivery mode' - but I promised myself I would get
back here with the SOLN.
Description of our app - per Werner's request:
Our application is performing IP network scans, and the end user can
control the number of thread to invoke (default of 40 threads). So -
these threads go out hit an IP address and preform all sorts of work
to determine things about that remote host. When it has the results ,
each thread will save them to the database. However, each thread
needs to perform a lookup such that if it is going to add some object
associated with that remote server (into the database) , it will find
the already created (in some circumstances) server and preform the
proper associations.
Description of the problem:
In the scenario above, when we queried the database to see if the
server exists - it was performed through an OQL query. Then we would
create OR load that server, create some other associated objects
(that the server will hold reference to) and commit the whole lot.
Description of the solution:
We changed the server query to db.ReadOnly. When we did this the
occasional lock between the two relationship tables was no longer a
problem.
Thanks,
George
On Oct 31, 2006, at 2:37 AM, Werner Guttmann wrote:
Before trying to come up with a typical yes/no general type of
statement, I'd like to see a short description of what you are
actually
trying to do, and why a (dead)lock is being produced ? How many
threads
are being involved ? WHat are these threads doing ? ETc.
Werner
George Rebens wrote:
OK - I did some more testing this afternoon. (reading some SQL
Server/Driver manuals etc).
I have switched to the latest MSFT driver, and I still see a sporadic
hang. However, I have also reduced the lock timeout from Indefinitely
(default value apparently) to 20 seconds. The app no longer hangs,
and
rolls back just fine.
However, my big question now is this: Is it correct to assume that
a DB
will reach deadlock at some point?
Maybe I was spoiled with MySQL - but we never had deadlock there, and
still don't even in all our production deployments. Any insight from
this group?
Thanks,
George-
On Oct 30, 2006, at 4:00 PM, Ralf Joachim wrote:
George,
take a look at
http://castor.codehaus.org/how-to-connection-proxies.html on how to
enable logging of SQL statements. Especially take a look at
locking as
Werner suggested.
Regards
Ralf
George Rebens schrieb:
Ralf,
I am using just a simple driver at this point.
In regards to logging, hey why not, I will try anything at this
point;) Let me know how to turn it on, and also what you suggest
that I look for.
Thanks,
George-
On Oct 30, 2006, at 4:00 AM, Ralf Joachim wrote:
Hi George,
are you using simple driver or datasource driver with connection
pooling? May the number of max. concurrent connections be
exceeded or
are your connections timed out according to some settings on
driver or
database engine.
Having said that I have no experience with SQL server, but I such
things
do not happen to me at oracle. Internally castor handles all
database
engines quite similar except that the SQL statements executed
vary a
bit. If you think, logging of SQL statements may help you, I could
instruct you how to enable this in castor.
Regards
Ralf
George Rebens schrieb:
I have a highly threaded application - each thread does
several read/
writes within their lifetime. The application works fine with
MySQL
(InnoDb tables), but when we ported the schema to SQL Server
we have
been running into sporadic problems.
It seems like SQLServer is not honoring the transactions
properly, as
when i change my MySQL tables to ISAM , I get nearly the same
behavior
from MySQL.
A bit further of investigation in my app, and it seems that my
attempts
to load object A in one thread (for writing). Thread 2 does a
query to
load (read only) and the two seem to lock. In the SQL Server
enterprise
manager I can see the lock hanging there.
This does not happen all the time, and is rather sporadic.
Since I
don't have these issues at ALL in MySQL, am I wrong to believe
that SQL
Server should do the same?
Is there a better driver choice, or do i need to somehow enable
transactions in SQL Server?
Any help is appreciated.
-- JTDS driver, SQL Server 2000.
Example Trace below:
ERROR 2006-10-19 17:17:26.593 .LocalDatabaseImpl } }
[Thread-574] This transaction has been aborted and rolled back:
Nested
error: org.exolab.castor.jdo.TransactionAbortedException: Nested
error: java.sql.SQLException: Invalid state, the Connection
object is
closed.: Invalid state, the Connection object is closed.: Nested
error:
java.sql.SQLException: Invalid state, the Connection object is
closed.:
Invalid state, the Connection object is closed.
org.exolab.castor.jdo.TransactionAbortedException: Nested error:
org.exolab.castor.jdo.TransactionAbortedException: Nested error:
java.sql.SQLException: Invalid state, the Connection object is
closed.:
Invalid state, the Connection object is closed.: Nested error:
java.sql.SQLException: Invalid state, the Connection object is
closed.:
Invalid state, the Connection object is closed. at
org.castor.persist.AbstractTransactionContext.commit
(AbstractTransactionContext.java:1322) at
org.exolab.castor.jdo.engine.LocalDatabaseImpl.commit
(LocalDatabaseImpl.java:170)
Thanks,
George-
-----------------------------------------------------------------
----
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
------------------------------------------------------------------
---
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
-------------------------------------------------------------------
--
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
--------------------------------------------------------------------
-
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
---------------------------------------------------------------------
To unsubscribe from this list please visit:
http://xircles.codehaus.org/manage_email
