CODEWITHSUNDEEP
sqlsql-server-2008concurrency
The Dark Prince
The Dark Prince

Reputation: 43

SQL Basic Concurrency

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)".

Upvotes: 4

Views: 215

Answers (4)

jop
jop

Reputation: 2316

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

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.

Upvotes: 2

Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

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

Upvotes: 1

user2299169
user2299169

Reputation:

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 :)

Upvotes: 0

Related Questions