Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can concurrent INSERTs mess up a UNION? Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 12:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 3:52 AM
Points: 115, Visits: 280
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?

Post #1412203
Posted Monday, January 28, 2013 12:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #1412209
Posted Monday, January 28, 2013 2:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:11 PM
Points: 1,302, Visits: 1,146
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.
Post #1412271
Posted Tuesday, January 29, 2013 8:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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
Post #1413085
Posted Tuesday, January 29, 2013 10:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, December 14, 2014 3:41 PM
Points: 257, Visits: 1,225
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

Post #1413170
Posted Tuesday, January 29, 2013 3:44 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1413366
Posted Saturday, September 14, 2013 12:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 3:52 AM
Points: 115, Visits: 280
Thanks, that's what i suspected, but it's hard to get how SQL Server semantics are in this case.
Post #1494835
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse