Re: cannot insert(urgent)
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 14 Apr 2005 23:19:26 +0200
Hi Shif,
On Tue, 22 Mar 2005 21:41:34 +0100, Hugo Kornelis wrote:
(snip)
>I can only try to understand what's happening if I can recreate
>your situation in my test database. For that, I need:
(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)
.
- Prev by Date: Re: SQL 2000 licensing enquiry
- Next by Date: Format Date in GroupBy query
- Previous by thread: search in arabic
- Next by thread: Format Date in GroupBy query
- Index(es):
Relevant Pages
|
|
