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
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 364
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 4076
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1452 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1034 Visits: 2385
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
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7742 Visits: 7140
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)[size=2]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.[/size]
siggemannen
siggemannen
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 364
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