May 24, 2012 at 1:33 pm
I'm very new to SQL Server and am programming a front end to a database using c++ and ADO. I've noticed that while submitting a SELECT query to the database while actively Inserting into the data the records returned from the query are inconsistent. Do I need to account for changes being made to the database during queries or do I need to modify the way I make inserts? A search on the web seemed to suggest that inserts may be made to a temp table is. Is that how it's done and if so how do you write your insert procedure? Thanks.
My code to insert into table is...
CREATE PROCEDURE [dbo].[Quote_Pair_Insert]
@PairIDVARCHAR(20),
@ALCDECIMAL(18,4),
@pValueDECIMAL(18,4)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Quote_Pair VALUES( @PairID, @ALC, @pValue);
return @@IDENTITY;
END
GO
May 24, 2012 at 1:44 pm
When you say that when you run your select statement the rows are inconsistant in SQL do you mean after inserting say 100 rows and then running your select statement you do not retrieve 100 rows ?
do you use lock hints in your select statement? are you sure that all your rows where commited to the database?
not sure what version of SQL you are using but i would replace
return @@IDENTITY;
with
return @@SCOPE_IDENTITY()
***The first step is always the hardest *******
May 24, 2012 at 1:49 pm
May 24, 2012 at 1:51 pm
tobinare (5/24/2012)
I'm very new to SQL Server and am programming a front end to a database using c++ and ADO. I've noticed that while submitting a SELECT query to the database while actively Inserting into the data the records returned from the query are inconsistent. Do I need to account for changes being made to the database during queries or do I need to modify the way I make inserts? A search on the web seemed to suggest that inserts may be made to a temp table is. Is that how it's done and if so how do you write your insert procedure? Thanks.My code to insert into table is...
CREATE PROCEDURE [dbo].[Quote_Pair_Insert]
@PairIDVARCHAR(20),
@ALCDECIMAL(18,4),
@pValueDECIMAL(18,4)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Quote_Pair VALUES( @PairID, @ALC, @pValue);
return @@IDENTITY;
END
GO
You have a couple of issues going on here.
First, you should be using an output parameter. Second, and possibly more of an issue is you are using @@IDENTITY. This will return the most recent identity for ANY connection. This can be a real problem if you have high volume. Instead use SCOPE_IDENTITY.
MSDS Links:
The following code will demonstrate your example and return the identity.
create table [dbo].[Quote_Pair]
(
SomeID int identity,
PairIDVARCHAR(20),
ALCDECIMAL(18,4),
pValueDECIMAL(18,4)
)
go
create PROCEDURE [dbo].[Quote_Pair_Insert]
@PairIDVARCHAR(20),
@ALCDECIMAL(18,4),
@pValueDECIMAL(18,4),
@Identity int output
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Quote_Pair VALUES( @PairID, @ALC, @pValue);
select @Identity = SCOPE_IDENTITY();
END
GO
declare @NewIdent int
exec Quote_Pair_Insert @PairID = 'some pair', @ALC = 456, @pValue = 789, @Identity = @NewIdent output
select @NewIdent
Hope that helps explain it.
_______________________________________________________________
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/
May 24, 2012 at 1:58 pm
Sean Lange (5/24/2012)
Second, and possibly more of an issue is you are using @@IDENTITY. This will return the most recent identity for ANY connection.
Yikes, no. You're thinking of IDENT_CURRENT; @@IDENTITY is scoped to the current session. Sure, SCOPE_IDENTITY() or an OUTPUT clause would be better (in case there is a trigger on the target that could change @@IDENTITY), but this is a side issue to the main question (which asks about concurrency).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2012 at 2:02 pm
SQL Kiwi (5/24/2012)
Sean Lange (5/24/2012)
Second, and possibly more of an issue is you are using @@IDENTITY. This will return the most recent identity for ANY connection.Yikes, no. You're thinking of IDENT_CURRENT; @@IDENTITY is scoped to the current session. Sure, SCOPE_IDENTITY() or an OUTPUT clause would be better (in case there is a trigger on the target that could change @@IDENTITY), but this is a side issue to the main question (which asks about concurrency).
Oh good grief. I need some coffee...at least my example was better than my description. :blush:
_______________________________________________________________
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/
May 24, 2012 at 2:09 pm
tobinare (5/24/2012)
I've noticed that while submitting a SELECT query to the database while actively Inserting into the data the records returned from the query are inconsistent.
The behaviour of SQL Server when concurrent INSERT and SELECT activity is occurring is dictated by transactions and the transaction isolation level. The single-row inserts you are performing through your procedure each run in their own implicit transaction, meaning that the data is visible to another SELECT query (running at the default READ COMMITTED isolation level) as soon as the implicit transaction commits (that is, immediately the individual INSERT completes successfully).
There are many ways to write code to for different concurrency behaviours. Can you explain simply what behaviour you would like to see? For example, if one connection is adding 100 rows by calling your procedure 100 times, what would you like SELECT statements that run while that operation is in progress to return?
One popular alternative is to write a procedure that accepts a table-valued parameter. This way, the client can pass all 100 rows in one call, and the procedure could INSERT those rows in a single transaction. The 'best' solution for you will depend on your particular needs though.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2012 at 2:13 pm
SGT_squeequal (5/24/2012)
When you say that when you run your select statement the rows are inconsistant in SQL do you mean after inserting say 100 rows and then running your select statement you do not retrieve 100 rows ?do you use lock hints in your select statement? are you sure that all your rows where commited to the database?
not sure what version of SQL you are using but i would replace
return @@IDENTITY;
with
return @@SCOPE_IDENTITY()
I'm using SQL Server 2008 Express. And @@SCOPE_IDENTITY() is not recognized. The SELECT query returns the correct number of rows, the problem is the rows returned are not the correct ones while Inserts are being made to the table. If inserts are halted, the rows returned from the query are correct. I'm not using lock hints in my select statement. I think I need to research "concurrency"
May 24, 2012 at 2:25 pm
tobinare (5/24/2012)
I'm using SQL Server 2008 Express. And @@SCOPE_IDENTITY() is not recognized.
It was a typo by SGT_squeequal; the function name is SCOPE_IDENTITY().
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2012 at 2:25 pm
It is a function, not a global variable. Try it like this:
return SCOPE_IDENTITY()
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 2:37 pm
tobinare (5/24/2012)
I'm very new to SQL Server and am programming a front end to a database using c++ and ADO.
If this is classic ADO, you won't be able to use table-valued parameters. Erland Sommarskog has written a comprehensive review of ways to pass many rows to a procedure here: http://www.sommarskog.se/arrays-in-sql-2005.html
The broad concept is to pass many rows of data in one call, and then write the procedure so that all new rows are INSERTed in one statement (and therefore, one transaction). In this scenario, with the default settings, concurrent SELECT statements will never show some of the new rows, it will return none of them or all of them (thought the SELECT may block until the concurrent INSERT is complete). Again, there are many options and tweaks we can use here to get the exact behaviour you need.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2012 at 3:02 pm
SQL Kiwi (5/24/2012)
tobinare (5/24/2012)
I'm very new to SQL Server and am programming a front end to a database using c++ and ADO.If this is classic ADO, you won't be able to use table-valued parameters. Erland Sommarskog has written a comprehensive review of ways to pass many rows to a procedure here: http://www.sommarskog.se/arrays-in-sql-2005.html
The broad concept is to pass many rows of data in one call, and then write the procedure so that all new rows are INSERTed in one statement (and therefore, one transaction). In this scenario, with the default settings, concurrent SELECT statements will never show some of the new rows, it will return none of them or all of them (thought the SELECT may block until the concurrent INSERT is complete). Again, there are many options and tweaks we can use here to get the exact behaviour you need.
Thanks, that's a great link. I'm going to review it thorougly and ensure that I don't have an issue with ADO and not SQL.
May 25, 2012 at 2:09 am
It was a typo by SGT_squeequal; the function name is SCOPE_IDENTITY().
Paul White - SQL Server MVP
Yup it was past 6 oclock and the kids where killing me lol i too need coffee my bad spelling 🙂
***The first step is always the hardest *******
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply