Re: Transactions - Infinite Loops

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/26/05 

Date: Tue, 25 Jan 2005 21:50:24 -0800

This still doesn't sound like an infinite loop. It sounds like a simple
uncommitted transaction. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in 
message news:1E61C1BD-B565-45A6-BC8F-5A32CCBFB436@microsoft.com...
> Kalen,
>
> Hi ...
>
> It was an unusual SQL Server response that we were unable to duplicate. I
> was doing a simple update to a small table. I use code that rolls back the
> transaction if the wrong number of felds are being updated (i.e., rollback 
> if
> I made a mistake in the update statement). I forgot the Commit key word at
> the end of the transaction, but the query seemed to work fine - records 
> were
> updated correctly and SQL Server told me how many rows were affected. The
> udate ran in a second or less and the red button turned off, indicating 
> that
> the query ran okay.
>
> Shortly thereafter the sa told me he had to kill the query because it was
> causing an infinite loop inside the transaction log due to the absense of 
> the
> Commit statement. However, I received no indication that anything was awry
> until I tried to exit Query Analyzer, and then I got the "uncommitted
> transaction" message. Before exiting I had no problem executing any number 
> of
> SELECT statements subsequent to my update query.
>
> Now I am thinking that perhaps the transaciton log loop was cuased by some
> other transaction - not the update query I was running.
>
> In any case, I'll remember @@Trancount the next time this occurs.
>
> Thanks for your help ...
>
>
> "Kalen Delaney" wrote:
>
>> Hi Bill
>>
>> What indications did you have that there was a loop of some kind, rather
>> than just an uncommitted transaction?
>> If you're really in a loop, you will not be able to enter any more 
>> commands.
>> If you just forgot to commit, SQL Server cannot tell you that, as it has 
>> no
>> way to know that you really didn't mean to send more commands that are 
>> part
>> of the same transaction. There is nothing wrong here as far as SQL Server 
>> is
>> concerned.
>>
>> If you want to see if your session is inside a transaction, you can 
>> SELECT
>> @@trancount. If the value is >0 it means you have a BEGIN TRAN with no
>> COMMIT TRAN.
>>
>> Good programming practice is to make sure you have your BEGIN TRAN, all 
>> your
>> data modifications, and your COMMIT TRAN all in the same batch, but SQL
>> Server doesn't require this.
>>
>> -- 
>> HTH
>> ----------------
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>>
>> "bill_morgan_3333" <billmorgan3333@discussions.microsoft.com> wrote in
>> message news:24AF058D-F5F7-41C9-B059-49B4A3750A4F@microsoft.com...
>> > Friends,
>> >
>> > I am a client to SQL Server who uses Query Analyzer to do all my work.
>> > Recently I apparently caused an infinite loop to ocurr in the 
>> > transaction
>> > log
>> > by inadvertently failing to end my update Transaction with the "Commit
>> > Tran"
>> > statement (this particular transaction starts with  "Begin Tran".)
>> >
>> > The problem is that there was no indication from SQL Server that this 
>> > loop
>> > was ocurring until I tried to exit Query Analyzer - at that point SQL
>> > Server
>> > told me that I had uncommitted transactions, and did I want to commit 
>> > them
>> > (which I did). In other words, the update query executed fine, and I 
>> > got
>> > no
>> > signal, until I tried to exit Query Analyzer, that anything was wrong.
>> >
>> > QUESTION: I am not an SA, but how can I check to see if I have caused 
>> > any
>> > sort of infinite loop to ocur (either through Query Analyzer or EM)? If
>> > there
>> > is a way to check, can I kill the loop if I am the user who inititated 
>> > it?
>> >
>> > Thanks for your help ...
>> >
>> > Bill Morgan
>> >
>> >
>>
>>
>>
Loading