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.
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.
Aneesh
You actually dont have to explicitly mention a TRANSACTION if you have only one statement as the one you shown
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
ASKER
:) 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.
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.
"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?
I am curious as to why you are anxious about having the transaction complete automatically? Why do you have the commit commented out?
ASKER
"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. :)
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. :)
I always start with a select first to ensure that the number of effected records meets what I am expecting.
ASKER
:) 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.