June 25, 2009 at 12:41 pm
I am relatively new to T-SQL and ran into an interesting problem I was hoping someone could explain.
I won't put the entire code in, but the basic idea was:
BEGIN TRANSACTION
SELECT FROM TABLE1
UPDATE TABLE2
IF ERROR GOTO ERRORHANDLER
SELECT FROM TABLE3
UPDATE TABLE4
IF ERROR GOTO ERRORHANDLER
COMMIT TRANSACTION
ERRORHANDLER:
Print out error message
This entire script was run one time and an error was encountered because the owner of tables 2 and 4 was not specified. I did not get the 'x rows updated message' and when I queried the table, everything looked ok - no rows had been updated.
I reran the code after specifying the table owner name, and I got the message that the correct number of rows had been updated. However, when I tried to query the tables that had just been updated, it would just hang and not return anything. I was also not able to view either table when attempting to open in Enterprise Manager.
All I could think to do was to run a 'COMMIT' by itself. As soon as I did this, the table lock appeared to clear up, and I could query they table.
Could anyone explain why the table was locked, and why the second commit worked? I would have expected that when I got the message that the rows were updated, that the commit within that would have worked, or else I should have gotten an error. Did the error I got on the first attempt somehow cause this problem?
Any ideas? Thanks in advance.
June 25, 2009 at 4:05 pm
Based on the pseudocode, I think it makes sense...
When you ran the code, the transaction started.
When the error was caught, it jumped over the commit and went straight to the error handling
Therefore, the transaction was never finalized (committed or rolled back).
Error handling should always contain logic to clean up anything that could have been left in an "awkward" state.
June 25, 2009 at 4:12 pm
You need a rollback in the error handling, otherwise the transaction remains open and uncommitted if either GOTO runs.. Either like this:
BEGIN TRANSACTION
SELECT FROM TABLE1
UPDATE TABLE2
IF ERROR GOTO ERRORHANDLER
SELECT FROM TABLE3
UPDATE TABLE4
IF ERROR GOTO ERRORHANDLER
COMMIT TRANSACTION
GOTO Done
ERRORHANDLER:
Print out error message
ROLLBACK TRANSACTION
DONE:
Or, preferred from SQL 2005
BEGIN TRY
BEGIN TRANSACTION
SELECT FROM TABLE1
UPDATE TABLE2
SELECT FROM TABLE3
UPDATE TABLE4
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error happened'
ROLLBACK TRANSACTION
END CATCH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply