October 8, 2012 at 8:41 am
I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something lik:
UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;
The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is re received Does the batch still get updated?
Otherwise can anyone think of a better way to acheive this with stored procs?
October 8, 2012 at 9:10 am
blakmk (10/8/2012)
I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something lik:UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;
The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is re received Does the batch still get updated?
Otherwise can anyone think of a better way to acheive this with stored procs?
I am missing something. There is no select statement here. You have a single statement, the update.
I am not sure what the question is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2012 at 9:24 am
sorry the update does the select implicitly and outputs the data using the output clause. The other way to do this would be to break it down, something like
create procedure testproc
as
select x,y,z from Table
update Table set retreived = 'Y'
go
October 8, 2012 at 9:31 am
Sean - I think the OUTPUT clause will return the rows, there is no SELECT.
blakmk - I think if you wrap up your transaction in a BEGIN and COMMIT, you should receive all the data successfully before the update is implemented.
You could potentially also delay the COMMIT until you have successfully processed the SELECTed records and confirmed that everything is ok, so...
BEGIN TRAN xyz
<UPDATE statement with OUTPUT clause>
<do something with the output data>
COMMIT TRAN xyz
Cheers
/>L
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
October 8, 2012 at 9:40 am
Sean Lange (10/8/2012)
blakmk (10/8/2012)
I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something lik:UPDATE dbo.Table
SET Retrieved = 'Y'
OUTPUT inserted.x, inserted.y, inserted.z;
The question I have, is what happens if the procedure executes, returns the select but the connection breaks before the batch is re received Does the batch still get updated?
Otherwise can anyone think of a better way to acheive this with stored procs?
I am missing something. There is no select statement here. You have a single statement, the update.
I am not sure what the question is.
+1
That's exactly what your statement does, Mark - return a bunch of rows and update the set as complete. There's no select in there, and since it's a single statement it will either complete or do nothing. You could certainly put defensive coding in the calling app to log whether or not the data was received or in the db to register an ACK but I wouldn't go as far as holding a transaction open pending feedback from a client over a broken connection.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 9:52 am
I wouldn't go as far as holding a transaction open pending feedback from a client over a broken connection.
+1
Especially since this an ASP.NET application using transactions like this is incredibly dangerous. You have to move your connection object to the session and you are now counting on the session to maintain your connection between page loads. This is NOT a good practice.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2012 at 10:21 am
Having tested it on a very large table. The transaction rolls back. Although i know there is sql process that kicks in periodically to rollback transactions. However not sure if this would work with small transactions
October 8, 2012 at 10:25 am
blakmk (10/8/2012)
Having tested it on a very large table. The transaction rolls back. Although i know there is sql process that kicks in periodically to rollback transactions. However not sure if this would work with small transactions
HUH? What do you mean by there is a sql process that kicks in to rollback transactions?
Assuming you have your default transaction settings, they will rollback when the connection is broken by default.
I am not at all sure what you mean by small transactions in this case.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2012 at 10:35 am
what i mean is the mechanism that you are talking is handled by a background sql process. In sql many of these processes run asynchronously and not continuously
October 8, 2012 at 10:39 am
blakmk (10/8/2012)
what i mean is the mechanism that you are talking is handled by a background sql process. In sql many of these processes run asynchronously and not continuously
You have me totally lost now. What processes are you referring to?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2012 at 10:55 am
Sql server has a number of background processes that it uses to manage internal activities, like ghost cleanup, resource monitor etc. I cant remember the name of the process that handles connection status but i remember reading it somewhere
you may find this useful:
"SQL Server as an application does not and should not proactively probe the client connection to determine its current status. The lower level Inter-Process Communications (IPCs), such as named pipes, IPX/SPX or TCP/IP sockets, are responsible for managing the client connections."
http://support.microsoft.com/kb/137983
Now for the mechanism you suggested to work robustly it would depend on the sql server client being able to verify back to the server that it has received the data. To the best of my knowledge the mechanism doesnt work like this. If you have references to say that it does I would be very interested in seeing them.
October 8, 2012 at 11:58 am
Now for the mechanism you suggested to work robustly it would depend on the sql server client being able to verify back to the server that it has received the data. To the best of my knowledge the mechanism doesnt work like this. If you have references to say that it does I would be very interested in seeing them.
What "mechanism" are you referring to? I didn't suggest anything. All I said is that your Update is a single statement.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2012 at 12:13 pm
My gut says no, the transaction would not be committed until the OUTPUT clause completed being consumed by the caller. It is easy enough to test...
-- setup the environment
USE AdventureWorks2008R2
CREATE TABLE dbo.test
(
test_id INT IDENTITY(1, 1),
Retrieved CHAR(1) NOT NULL
DEFAULT 'N'
);
-- populate test table with 1MM rows
INSERT INTO dbo.test
(
Retrieved
)
SELECT TOP 1000000
'N'
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
GO
-- now run this update, hop over to another query window, right click, go to connections and click disconnect all queries
-- SSMS prompts us to kill the running query, say yes
UPDATE dbo.test
SET Retrieved = 'Y'
OUTPUT inserted.*;
-- this returns 0 rows, OK
SELECT * FROM dbo.test WHERE Retrieved = 'Y';
Now do the same thing but instead of using the disconnect all queries KILL the SPID running the update. same result, not updates to Y.
Now I ran the update and let it complete. Now of course the flag is set to Y.
So no, the update was not committed until the OUTPUT clause was allowed to complete.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 8, 2012 at 12:21 pm
sean you were referring to the default transaction mechanism unless im mistaken. And if thats what your saying id be very curious to hear how you think that works internally.
thanks opc.three. yes when you kill it definatly invokes the rollback mechanism. What im talking about is does the client receiving ALL of the data, depend on the commit taking place. One would hope but...
Difficulty to test because as per the previous quote the server doesnt proactivley check for session state. Its a background process.
October 8, 2012 at 12:45 pm
blakmk (10/8/2012)
thanks opc.three. yes when you kill it definatly invokes the rollback mechanism. What im talking about is does the client receiving ALL of the data, depend on the commit taking place. One would hope but...
Yes. That is what I just showed. Killing it from both perspectives, client and server. SSMS opens a connection to the database instance using .NET. Your ASP.NET website will appear the exact same way to SQL Server.
Difficulty to test because as per the previous quote the server doesnt proactivley check for session state. Its a background process.
If the connection goes into oblivion somewhere between the .NET connection pool and your ASP.NET web app there is no way for SQL Server to handle that gracefully if the connection pool does not terminate the connection and continues to accept the results, if that is even possible. Either way I think your concern is outside SQL Server. I agree that holding a transaction open while results make their way from the server to the client would be a risky thing to implement.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply