SQL Basic Concurrency

Question

INSERT INTO T1 (xField) SELECT 7 AS xField WHERE (SELECT COUNT(*) FROM T1) = 0

Basically, if a million users all run this at the same time, could there ever end up being more than one row in T1?

Thread A: SELECT COUNT(*) FROM T1: 0
Thread B: SELECT COUNT(*) FROM T1: 0
Thread A: INSERT INTO T1...
Thread B: INSERT INTO T1...

Or is that guaranteed to never happen, because it's all one statement?

If that isn't safe, what about something like this?

Table T2 (GoNorth and GoSouth must never both be 1):

ID      GoNorth   GoSouth
1       0         0

Then this happens:

User A: UPDATE T2 SET GoNorth = 1 WHERE GoSouth = 0
User B: UPDATE T2 SET GoSouth = 1 WHERE GoNorth = 0

Thread A: Find rows where GoSouth = 0
Thread B: Find rows where GoNorth = 0
Thread A: Found a row where GoSouth = 0
Thread B: Found a row where GoNorth = 0
Thread A: Setting GoNorth = 1 for the located row
Thread B: Setting GoSouth = 1 for the located row

And the result:

ID      GoNorth   GoSouth
1       1         1

What are the rules for what can happen at the same time and what can't?

My database engine is "Microsoft SQL Server 2008 R2 (SP2)".

Default, which I believe is READ COMMITTED, but I can change it if necessary. :) — The Dark Prince, May 08 '13 at 13:09
Of course, with table `T2`, you just apply a suitable `CHECK` constraint to make sure that (no matter what else happens), you'll never get into the situation with both columns being `1`. Similarly, for table `T1`, the addition of a second column, constrained to only contain one possible value, and then declared as the primary key, ensures that there will only ever be one row. — Damien_The_Unbeliever, May 08 '13 at 13:41

score 2 · Accepted Answer · answered May 08 '13 at 13:37

No. It's written as a single statement, and therefore the rules of SQL provide for the entire statement to be atomic.

However, there are some caveats here. First of all, this could mean creating quite a large number of locks, to the point where your table becomes effectively unavailable until the query is completed. In other words, in order to guarantee safety, you'd be throwing away the concurrency. The other caveat is that this only holds for the default isolation level. A weaker isolation level may allow the query to run without creating the appropriate locks. A really weak isolation level might allow it to ignore locks.

Amit Rai Sharma · Answer 2 · 2013-05-08T13:32:39.723

1

Answer to the orginal question where #temp tables were used: If you only working with # tables i.e temporary table then your scenario is not going to happen. # tables are connection specific and I believe your million users won't be sharing the same connection at same time.

Updated answer:

If you are using concrete tables then yes there can be more than one row in T1 is multiple user are running insert statements. However there are other things to consider here. For more details you should read SQL SERVER – Concurrency Basics – Guest Post by Vinod Kumar

edited May 08 '13 at 13:32

answered May 08 '13 at 13:22

Amit Rai Sharma

4,015
1
28
35

I can't believe I missed that. I changed it so they're no longer temporary. – The Dark Prince May 08 '13 at 13:26

score 0 · Answer 3 · edited May 23 '17 at 10:33

0

I think you need this information to understand what may happen: SQL server concurrent accessing
I also recommend reading concurrency basics from Pinal Dave (who is sort of a walking god of SQL Server :)

edited May 23 '17 at 10:33

Community

1
1

answered May 08 '13 at 13:30

score 0 · Answer 4 · answered May 08 '13 at 13:39

0

Your scenarios are examples of the write skew anomaly. They might or might not be possible depending on the isolation level used. They are possible with snapshot isolation.

answered May 08 '13 at 13:39

jop

2,226
15
16

SQL Basic Concurrency

4 Answers4