On Sun, 20 Mar 2005 07:19:01 -0800, shif wrote:

Hi shif,

I'm sorry, but you've completely lost me by now. I suggest we go back to
step 1. I can only try to understand what's happening if I can recreate
your situation in my test database. For that, I need:

* An SQL script to create the tables (i.e. CREATE TABLE statements). It
is essential that you include all constraints and properties. If you
have extra indexes, include the appropriate CREATE INDEX statements as
well. (Check www.aspfaq.com/5006 as well).

* An SQL script to fill the tables with some rows of sample data (i.e.
INSERT statements). There's no need to post thousands of rows, just
enough to show the structure of your data. You canb of course replace
sensitive data with faked data, as long as the structure remains intact
(e.g. replacing Bush with Name1 and Clinton with Name2 is okay;
replacing both Bush and Cllinton with Name might not be).

* The exact code of all triggers on your tables and of all stored
procedures used in the operation.

* The exact text of the SQL statements executed, and also indicate which
statements are executed serially from one connection, and which are
executed in parallel from another connection. If your SQL statements are
generated by some middle tier, then you can use Profiler to catch the
statements sent to the server.

If you can post all of the above, I can try if I can find the cause of
your problem.

Oh ... another thing you might try is to run the report that causes the
blocking from a Query Analyser window, then see if that causes blocking
as well (keep the QA window woith the generated report open!). If it
does, the reason is somewhere in the code that generates the report; if
it doesn't introduce blocking, the problem is in the way your client
software calls the procedure.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Shif,

On Tue, 22 Mar 2005 21:41:34 +0100, Hugo Kornelis wrote:

(snip)

Sorry for the long delay. I did get your e-mail, but I lacked the time
to properly look into it.

Some observations I made when reviewing your code:

(1)
Many of your stored procedures include a COMMIT instruction, but I could
not find any BEGIN TRAN instruction. This can mean any of the following
things:

* The transaction is started in another procedure, that you did not
include in the post, or
* The transaction is started by a direct BEGIN TRAN call from the
front-end, or
* You are running with the IMPLICIT_TRANSACTIONS setting enabled.

Based on your previous messages, I now think the last option is the most
probable. But regardless, you should consider changing this. I believe
that each stored procedure should end with the same amount of
transactions open as when it's called, unless a critical error has
occured (in which case all open transactions should be rolled back). And
to make sure that this always happens, don't rely on implicit
transactions - explicitly BEGIN TRANSACTION when you need it, and
explicitly COMMIT (or ROLLBACK) TRANSACTION when you're done. That makes
it easy to check if each procedure indeed commits all transactions it
starts.

(2)
In the procedure pd_unpd (if I recall correctly, this is the proc that
generates a report and starts the mysterious hanging applications,
right?), you have two IF statements that execute an INSERT INTO,
followed by a COMMIT TRAN - but the COMMIT TRAN is commented, so it
won't be executed!!
If my theory that you are running with SET IMPLICIT_TRANSACTIONS ON is
correct, then the INSERT INTO will start a transaction, that will never
be committed or rolled back. As long as the connection that start the
report generation is not lost, the transaction will remain open. And all
locks taken by the transaction will be held.

Possible solutions (in random order):

Make sure that all opened transactions are commited as soon as possible.
Don't ever leave a transaction open while waiting for user input.

Change your report generating code to use the transaction isolation
level read commited. It currently is using either repeateble read or
serializable. If that is necessary - all the more reason to commit the
transaction as son as popssible. If that is not necessary, change it
ASAP.
(BTW, I could not find anything in the script you sent me to change the
transaction isolation level, so I guess that this setting is controlled
by the front-end application that makes the connection)

I hope this helps!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)