February 18, 2016 at 6:35 am
I have a stored procedure which applies data from one table to another, then deletes the table (the transactions, not the one just updated)
it goes something like this:
UPDATE table A
SET flag to processed
FROM table A
JOIN table B
PRINT @@ROWCOUNT
then
INSERT INTO A history
SELECT * FROM Table A
WHERE flag = processed
PRINT @@ROWCOUNT
DELETE table A
WHERE flag = processed
PRINT @@ROWCOUNT
DELETE B
PRINT @@ROWCOUNT
the final statement is executed before the first three!
what is going on here?
February 18, 2016 at 6:39 am
Not possible. SQL executes statements strictly sequentially when they're in the same batch (which a procedure is).
Sure another process isn't somehow deleting B? Or is the insert into B failing perhaps?
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
February 18, 2016 at 6:59 pm
Can you see how many rows are actually updated by the first statement?
_____________
Code for TallyGenerator
February 19, 2016 at 3:04 am
Seggerman-675349 (2/18/2016)
it goes something like this:
Please post the *exact* code. With the "something like this" code, I was unable to reproduce the issue.
February 19, 2016 at 4:00 am
can't - it's considered intellectual property and my client company is very sensitive about that, even though we're hardly state of the art in the area I work in
the snippet was from a stored procedure - and the delete had been in another stored procedure which called that one - I just wanted assurances that moving the delete statement inside the stored procedure would fix the problem
something was done to speed this entire process up about a week ago and a procedure running fine for the last 2 years is starting to have issues like this
February 19, 2016 at 4:54 am
Speeding up changing results implies things were running on two sessions. Within a single session, SQL executes statements strictly in sequence. So if you have code of:
SELECT <something>
INSERT <Something else>
EXEC AProcedure
DELETE <something>
the delete will not start until the execution of AProcedure has completed, and AProcedure won't start executing until the insert has completed
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
February 19, 2016 at 5:15 am
I suggest that you set up a trace/extended events session, or add a (temporary) trigger to the table to track deletes (but note that a trigger will not catch TRUNCATE TABLE statements).
Unless your code expliitly forces asynchronous execution (which by the way is quite hard to do in pure T-SQL!) the statements will execute serially, which means that there has to be some other process that removes the data.
February 19, 2016 at 6:09 am
thank you everyone (especially Ms Shaw) for what was a really basic question - and not mocking me for it
the answer was that a change was made to the system about a week ago and the file coming in was the last one created on the old system - which of course would not match because the records get deleted as soon as they are processed
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply