Re: Transactions - Infinite Loops
From: bill_morgan_3333 (billmorgan3333_at_discussions.microsoft.com)
Date: 01/26/05
- Next message: David Gugick: "Re: Help"
- Previous message: Aleks: "SQL Expert"
- Next in thread: Kalen Delaney: "Re: Transactions - Infinite Loops"
- Reply: Kalen Delaney: "Re: Transactions - Infinite Loops"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 Jan 2005 19:13:03 -0800
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
> >
> >
>
>
>
- Next message: David Gugick: "Re: Help"
- Previous message: Aleks: "SQL Expert"
- Next in thread: Kalen Delaney: "Re: Transactions - Infinite Loops"
- Reply: Kalen Delaney: "Re: Transactions - Infinite Loops"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
