April 15, 2009 at 5:45 pm
We have a large Java program converted from a Forte application. During the day we are getting Blocking SPID's in the server. We think the application is opening a transaction then never closes the transaction. We set up a a profile template to run to catch the locking/blocking action. When we run this profile the blocking problem goes away. Why?
This application is distributed using RMI and has around 70 users. We are using SQL 2000 and windows 2000 servers to keep compatibility with a bunch of old VB helper applications.
After we started running the profiler, the number of active SPIDS dropped from 70 to about 25 . We traced the blocking down to a specific screen and stored procedure but now we can't reproduce the errors while the profiler is running. We also can't reproduce the error without load of at least 20 active users in the system.
we think it might be a code problem on the
"Set Implicit Transaction On " when the user does not ever update the data so the transaction never commits but locks other users from writing to the table.
Could this be as simple as using a different JDBC driver ? This problem did not occur in the Forte version of the App.
We are on Service Pack 3.
Thanks for any help!
April 21, 2009 at 4:24 pm
This query is executed within a nested transaction,
the transaction isolation level is read commiited
no lock hints are used in the selects of the stored procedure in an attempt by the stored procedure to determine of the data allready exists before it commits
the duration for which the locks are held are infinite when the data exists
This appearst to be an attempt to enforce referential integrity via code instead of relying on database schema
April 21, 2009 at 5:28 pm
Actually the stored procedure is trying to update a record that does not exist.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply