sql server programming: Hi,

I have written a stored procedure in which I am doing
deletions and insertions.

I am also using linked servers.

The problem that I am facing is that the stored procedure
is taking a very long time to execute when I use BEGIN
TRANSACTION and COMMIT TRANSATION statements and I have to
kill the process even before it finishes executing.

But when I remove the BEGIN TRANSACTION and COMMIT
TRANSATION statements the stored procedure finishes
executing in 2-3 seconds.

If anybody has faced the same problem and has found the
reason please let me know.

Thanks
Raja

Hi Raja,

One possibility of the long running stored procedure is one of your sql
statement is waiting for a lock to be released, but the lock is acquired by
other statement inside the same stored procedure. Since these two statements
are running under the same transaction, the lock that is acquired may not be
released until the transaction ends. So the wait for lock action never be
satisfied, the statement will wait forever.

If this is your situation, you need to change your stored procedure design
to avoid this or using other transaction isolation level.

Thanks,
Jian Zeng

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.


[quoted text, click to view]