Cursor inside a transaction
-
Hello,
I am writing a script to do some heavy data transformation in my ERP. The script begins with starting a transaction which currently is rolled back at the end, until the script is finished. Then I have 4-5 begin/end blocks to do various things inside the transaction. The first block that runs does an update to some data in a table in side a cursor loop, then the last block deletes data that is no longer needed. I am getting an error on the last block because of a foreign key reference to the data I updated in the first block. The issue here is, the first block successfully replaces all references to the data being deleted in the last so there should be no foreign key issues. However, it appears the changes that happen inside the cursor are being reverted after exiting the cursor. I can verify this because I run a select statement inside the cursor and the data is replaced as desired, but the same exact select statement outside after the cursor shows the data in it's original form. I did not think cursors function like that. Anyone have any thoughts?
-
My first thought here is that it is rolling back when the cursor ends. Do you have ANY "ROLLBACK" statements inside your loop or after the loop but before the DELETE?
A cursor and a loop should not be rolling anything back and I've personally never had that experience.
Are a cursor and a loop required OR could you do this without them? If you can remove the cursor and loop, you will get a pretty good performance boost!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
There are no other transaction statements in the script. No rollbacks or commits. I've used find to search for "tran", "commit", and "rollback" just to make sure I am not crazy and it found nothing.
I have debated trying to rewrite the cursor/loop out of the script. I didn't write the original we paid a consultant to do the major work and I am just trying to finish it off so I wasn't looking forward to the idea of trying to reverse engineer everything they did. I don't think it's going to be possible for some of it but might be for other parts.
I'm not too worried about performance boost because this is a 1 time clean up script that we just need to be able to run within an 8 hour window. Since it's getting all the way to the last code block in about 5 minutes right now performance is good enough.
-
I would re-evaluate the code and remove any redundancy where possible. For example, I often see someone perform an insert into a table (often a temp table) followed by multiple updates and deletes. In these examples, it is often much more efficient to insert the data with the final value instead of 'updating' the column with that value. The same with deletes...if the row isn't needed then don't include it in the original insert.
Now - if the 'cursor' is used as a batching mechanism then I don't see any issues, except the requirement of everything being done in a single transaction. The idea of batching is to reduce the impact on the system overall - and wrapping that in a transaction is actually worse than running the code as a set operation.
My final thought - I would build staging tables and run the transformations on those staging tables. Once the data has been staged - then I would start a transaction to update the production tables from the staging tables using direct insert/update statements. This way the transaction itself should be minimal - unless the number of rows is enough to warrant batching the inserts/updates.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Then that is weird. I've never come across that behavior before.
Any chance that the UPDATE is missing updating something that is causing the DELETE to fail?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Interesting behavior. Have not seen that occur "naturally" before.
Edit: Now that I think about it, I thought all changes made w/i a trans were visible to that trans, even if they weren't yet visible normally (excluding UNCOMMITTED) to other trans.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Any triggers that could be rolling back the transaction?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I am 100% sure the update is not missing the records. I put in SELECT statements to show me the data at various points in the script. The select statement inside the cursor shows the altered the way I need it, the same select statement after outside the cursor shows the same records but reverted back. So I know the update statement is removing the references to the data being deleted. I pasted the section of the script that does this below. The one before the CLOSE and DEALLOCATE statements is inside the cursor, it's in a IF statement so it shows me the data after the row I am concerned with is updated.
SET @Sql = 'UPDATE ' + @TableName + ' SET ' + @CustSeqColName + ' = ' + CAST(@CustSeq AS NVARCHAR(10)) + ' WHERE cust_num = ''' + @CustNum +'''' + ' AND ' + @CustSeqColName + ' = ' + CAST(@CustSeqToMatch AS NVARCHAR(10))
PRINT @Sql
EXEC sp_executesql @Sql
IF @TableName = 'inv_hdr_mst' AND @CustNum = ' 10011' AND @CustSeqToMatch = 28
BEGIN
SELECT * FROM inv_hdr_mst WHERE cust_num = ' 10011' ORDER BY cust_seq
END
END
CLOSE custToProcess
DEALLOCATE custToProcess
END
CLOSE updateProcess
DEALLOCATE updateProcess
END -- cust_num
SELECT * FROM inv_hdr_mst WHERE cust_num = ' 10011' ORDER BY cust_seq -
Rollback in trigger is an interesting thought, but no, just checked and confirmed no transactions at all in the 3 triggers on that table. The ERP expects the middleware data service to handle the transaction so I am not surprised it is not in the triggers.
-
I have several BEGIN/END blocks in my code, mostly for organizational purposes. That wouldn't do it, would it?
-
In the attached image I am showing the results of the SELECT statements. The first result set is inside the nested cursor the 2nd result set is inside the top level cursor after the nested cursor closes, and the 3rd result set is outside all the cursors. I am replacing the cusT_seq column. You can see the cust_seq column for the record where inv_num = 22504 is changed to 35 in the first result, then back to the original value of 28 in the next 2. There are no rollbacks between the 3 statements.
Select statement for all 3 result sets: SELECT * FROM inv_hdr_mst WHERE cust_num = ' 10011' ORDER BY cust_seq
-
As a random thought, I was looking at this chunk of code:
SET @Sql = 'UPDATE ' + @TableName + ' SET ' + @CustSeqColName + ' = ' + CAST(@CustSeq AS NVARCHAR(10)) + ' WHERE cust_num = ''' + @CustNum +'''' + ' AND ' + @CustSeqColName + ' = ' + CAST(@CustSeqToMatch AS NVARCHAR(10))
PRINT @Sql
EXEC sp_executesql @Sql
IF @TableName = 'inv_hdr_mst' AND @CustNum = ' 10011' AND @CustSeqToMatch = 28
BEGIN
SELECT * FROM inv_hdr_mst WHERE cust_num = ' 10011' ORDER BY cust_seq
ENDand was thinking, since this is in a cursor, is there a chance that @CustSeqToMatch is an incrementing value? What I mean is what are the chances that you are going to have another update happening on the table inv_hdr_mst, for custnum 10011, but for CustSeqToMatch = 35?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I just notice this: is there supposed to be a space in the @CustNum value? Just curious. Since that appears to be your own custom data type, it might be that an embedded space is correct, I get that, but it's still odd for those us used to standard data types.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
The space is part of the data structure. it's dumb, ERP dev does it this way though.
I think Mr Gale may be on to something though. I tweaked it a bit to show more results sets and it may indeed be getting switched back after. I have to review this a little further but you gave me something to look at. Greatly appreciated!
-
And Mr. Brian Gale is the winner. It was a data issue all along, totally my bad. As Brian questioned, it would remove references via 1 row of the "ship tos to fix" table, then put references back from a later row. Cleaned up the data and the script works as expected. Thank you all for dealing with my stupidity.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply