SQL COMMIT Issue

I have been having an intermittent issue with Microsoft SQL 2005 SQL Server Management Studio (SSMS).   For some reason, I have had about a 5% occurrence of the COMMIT command being successful but not committed.

Example:

BEGIN TRAN

UPDATE PrintHistory
SET PrintStatusID = 2
WHERE PrintStatusID = 1

--COMMIT TRAN

I always use BEGIN TRAN on my updates in SSMS.  I tend to verify the results then separately issue the COMMIT TRAN hence it is commented out.  I run the COMMIT separately after the UPDATE/DELETE. So here is the issue on a simple Transaction like the one referenced.  When I issue the COMMIT TRAN, it returns 'Command(s) completed successfully.'.  However, in about 1 in 20 occurrences I get a report shortly after about Timeouts.  Remembering I just did a COMMIT 5 or 10 minutes before, I reissue the COMMIT.  I once again get back 'Command(s) completed successfully.'.

This makes no sense to me.  I've been trying to have a habit now of hitting the COMMIT TRAN until I get the message:

'Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.'

The thing is it usually gives me that on the first COMMIT re-attempt, meaning there was only 1 Transaction.  Any input on why this is happening in SSMS for 2005.  I have never had this in previous Query Analyzer versions.

I'll be the first to admit when in a session I truly forgot to do the COMMIT, but these are not instances of forgetting to issue the COMMIT.  I've been doing DB work for 16 years now.  This has only occurred since I started using the SQL 2005 SSMS.

Thanks for any input you have.
LVL 1
KDSayersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You actually dont have to explicitly mention a TRANSACTION if you have only one statement as the one you shown
0
BrandonGalderisiCommented:
Could it be that you are sometimes double clicking the run button and therefore creating two transactions?
BEGIN TRAN
 
UPDATE PrintHistory
SET PrintStatusID = 2
WHERE PrintStatusID = 1
 
select @@trancount
--COMMIT TRAN

Open in new window

0
KDSayersAuthor Commented:
:) I understand, aneeshattingal.  My issue more resides with why manual statements are periodically doing this.  I use @@Trancount in my stored procs.  I guess that is a further reason I'm seeking an explanation.  I would like to know that it is specific to the SSMS and not SQL 2005 database.  I so far have seen no indication of errant procedures, so I'm inclined to believe it is somehow only in the SSMS.  Of that I'm thankful..

I"m sure I could write an IF statement that checks for number of records updated.  So IF @@ROWCOUNT = 23 then COMMIT TRAN else ROLLBACK TRAN.  I'm just doing quick statements on the fly as they are necessary.  So I usually view the result count and then issue a COMMIT once I'm satisfied, or ROLLBACK as needed.

I've asked others I work with and they can't explain it either.  They have watched me do this and have it occur.

BrandinGalderisi, that too is a plausible explanation.  The only issue there would be, as in this example, the second execution would have 0 rows updated as they were all updated.  That would be visually caught as not the result I'm looking for, and I would ROLLBACK.

I'm glad for the responses.  I've searched Google and here, and when I just couldn't find an answer to this nemesis of mine, I figured it was time to ask.  I continue to hunt for the answer, even though I've had no luck.  If I find one that makes sense before I get an answer I"ll be sure to update here.  I'm hopeful someone will be me to it as I am tired of searching for why this does this only a low percentage of the time.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

BrandonGalderisiCommented:
"BrandinGalderisi, that too is a plausible explanation.  The only issue there would be, as in this example, the second execution would have 0 rows updated as they were all updated.  That would be visually caught as not the result I'm looking for, and I would ROLLBACK."

I am curious as to why you are anxious about having the transaction complete automatically?  Why do you have the commit commented out?
0
KDSayersAuthor Commented:
"BrandonGalderisi: I am curious as to why you are anxious about having the transaction complete automatically?  Why do you have the commit commented out?"

Habit... I've had too many occurrences where an analyst asks me to do something, and then go 'Oops'.  I like them to be sure of what they request.  I've seen others, even when writing a transaction based DELETE, get distracted and forget to write the WHERE clause, or complete it properly  Bye-bye data.  Oh of course there is backup, but that is much more effort and in the meantime business is down.  I leave my COMMIT commented so it is executed with purpose.  Just an old habit I guess for something I've been doing now nearly half my life.

Stored procedures that have been implemented, of course we count on those to do what they were designed to do.  On the fly statements, I like to extra security of COMMITing when I know it is verified result.

Long winded, but I hope that helps you understand my odd reasoning. :)
0
BrandonGalderisiCommented:
I always start with a select first to ensure that the number of effected records meets what I am expecting.
0
KDSayersAuthor Commented:
:) Yes.  I guess I could have added that to my explanation of what I do.  That's how I know how many records I'm looking for from the update statement.
0
KDSayersAuthor Commented:
Well, I've not encountered my problem recently.   I can't seem to find a 'solution' and I started tinkering to see if I was making some addle brained mistake.  I still don't know for sure but I did find one potential answer, that I'm willing to accept.

As part of my method of practice in doing manual updates, as stated previously, I would highlight my COMMIT TRAN or ROLLBACK TRAN depending on my result.  Normally doing so will result in:

Command(s) completed successfully.

Well, if you highlight a comment, ie. -- COMMIT TRAN, including the dashes (--) it returns the same message.

So my only assumption since I've committed that to memory and made sure I wasn't doing that is that for some reason I was highlighting the whole line and not just the words COMMIT TRAN.

I appear to have resolved my own issue since I haven't encountered it in nearly a month since I posted this.  Just wanted to update those that attempted to solve my puzzle.
BEGIN TRAN
 
UPDATE <table>
SET <fieldname> = <value>
where <condition>
 
-- COMMIT TRAN
-- ROLLBACK TRAN

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.