SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can concurrent INSERTs mess up a UNION?


Can concurrent INSERTs mess up a UNION?

Author
Message
siggemannen
siggemannen
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 380
Can SQL Server guarantee that if I issue a command like:

INSERT INTO T(...)
SELECT t.a, t.b, 1
FROM sometable t
UNION
SELECT -t.a, t.b, 1
FROM sometable t



, both "sides" of union will retrieve same table rows?
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13042 Visits: 4077
first the records from both tables will be clubbed then UNION will distinct the records.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
baabhu
baabhu
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2445 Visits: 1218
siggemannen (1/28/2013)
Can SQL Server guarantee that if I issue a command like:

INSERT INTO T(...)
SELECT t.a, t.b, 1
FROM sometable t
UNION
SELECT -t.a, t.b, 1
FROM sometable t



, both "sides" of union will retrieve same table rows?



SQLserver will never guarantee the order if no specific order by statement is given.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2658 Visits: 2582
siggemannen (1/28/2013)
Can SQL Server guarantee that if I issue a command like:

INSERT INTO T(...)
SELECT t.a, t.b, 1
FROM sometable t
UNION
SELECT -t.a, t.b, 1
FROM sometable t



, both "sides" of union will retrieve same table rows?



Not sure what you mean by "same table rows", but as one other poster pointed out, UNION will return only one instance of a row that is returned by more than one of the UNIONed queries with identical values in each column. That won't ever happen in your example, assuming that "-t.a" actually returns the negative of the value in t.a. If you want all rows returned by the UNIONed queries, even if they return "duplicate" rows, use UNION ALL.

Also, I don't know if this pertains to your question, but each query in a UNION/UNION ALL can have its own WHERE clause to set the conditions for the rows to be returned by that query.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 2417
siggemannen (1/28/2013)
Can SQL Server guarantee that if I issue a command like:

INSERT INTO T(...)
SELECT t.a, t.b, 1
FROM sometable t
UNION
SELECT -t.a, t.b, 1
FROM sometable t



, both "sides" of union will retrieve same table rows?



This looks like an isolation question to me.
If I understand correctly, you are asking can the first select be done, then in between the selects another process alters the table and then the second select is done.

On the isolation level serializable and snapshot isolation both tables will be read exactly the same.

On a dirty read level isolation (and a Nolock Hint), I think it is very possible that the two tables can be different.

For the isolation levels in between I am not sure. And I think it is difficult to prove that the tables CAN be different.
With Isolation level read commited, the table can be altered.
With the Isolation level repeatable read I think rows can be inserted, but not altered.

See:
[url=http://blogs.msdn.com/b/craigfr/archive/2007/05/09/repeatable-read-isolation-level.aspx][/url]
Note that the capability to insert new "phantom" rows between locked rows that have already been scanned is the principle difference between the repeatable read and serializable isolation levels. A serializable scan acquires a key range lock which prevents the insertion of any new rows anywhere within the range (as well as the update or deletion of any existing rows within the range).



With serializable, the table can not be altered at all (no deletes, no updates and no inserts). Inserts are not possible until your transaction (or query) finishes.
With snapshot isolation, you get your own 'private' copy of the table at the start of the query. Somebody can insert, but this is not visible to the query.

You could test the different isolation levels, but that will take a lot of effort (I think). (Keep us informed).

This is my fast anwser, not totaly sure.
Ben Brugman
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19349 Visits: 7410
I think you can eliminate the problem, and a full table scan, like below.

Whiile this makes your initial q moot for this specific example, it could naturally still be good to know just in general.



INSERT INTO T(...)
SELECT t.a * cj.modifier, t.b, 1
FROM sometable t
CROSS JOIN (
SELECT 1 AS modifier UNION ALL
SELECT -1
) AS cj



SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
siggemannen
siggemannen
Mr or Mrs. 500
Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)Mr or Mrs. 500 (587 reputation)

Group: General Forum Members
Points: 587 Visits: 380
Thanks, that's what i suspected, but it's hard to get how SQL Server semantics are in this case.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search