SQL Basic Concurrency

Question

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

Basically, if a million users all start this at the same time, can there ever be more than one line 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 it guaranteed to never happen, because this is all one statement?

If this is unsafe, how about this?

Table T2 (GoNorth and GoSouth should never 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 cannot?

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

Answer 1

No. It is written as a single statement, so SQL rules ensure that the entire statement is atomic.

However, there are some reservations. First of all, this can mean creating quite a lot of locks to such an extent that your table becomes inaccessible until the query is completed. In other words, to guarantee security, you throw out concurrency. Another caveat is that this applies only to the default isolation level. A weaker isolation level may allow the request to be executed without creating the appropriate locks. A very weak isolation level may allow it to ignore locks.

Answer 2

Answer the original question where the #temp: tables were used . If you only work with C # tables in a temporary table, your script will not happen. # the tables are connection-specific, and I believe that your million users will not use the same connection at the same time.

Updated answer:

If you use specific tables, then yes, there can be more than one row in T1, and several users work with insert instructions. However, there are other things. For more details, you should read SQL SERVER - Concurrency Basics - Guest Post by Vinod Kumar

Answer 3

I think you need this information in order to understand what might happen: concurrent access to SQL server
I also recommend reading the concurrency basics from Pinal Dave (someone like the walking god of SQL Server :)

Answer 4

Your scripts are examples of recording distortion anomalies. They may or may not be possible depending on the level of insulation used. They are possible with blocking snapshots .