sql - Atomic increment of counter column using simple update -

i trying understand how safely increment counter column, may incremented simultaneously many users (it's web api mobile app).

i've read popular questions in strategies dealing issue can't seem figure what's wrong using simple:

update table set counter = counter + 1   

i've built following code sample try , inconsistent values , prove myself using simple update statement not practice:

class program {     static void main(string[] args)         {             list<task> tasks = new list<task>();              (int = 0; < 100; i++)             {                 task t = task.factory.startnew(() =>                 {                     writetocounter();                 });                  tasks.add(t);             }              task.waitall(tasks.toarray());         }      static void writetocounter()         {             string connstring = configurationmanager.connectionstrings["defaultconnection"].connectionstring;              using (sqlconnection connection = new sqlconnection(connstring))             {                 connection.open();                 random rnd = new random();                 (int = 1; <= 100; i++)                 {                     int wait = rnd.next(1, 3);                     thread.sleep(wait);                      string sql = "update table set counter = counter + 1";                      sqlcommand command = new sqlcommand(sql, connection);                     command.executenonquery();                 }             }         } } 

in sample trying simulate scenario in many users access api simultaneously , update counter. when code runs, counter always @ 10000, means consistent.

does test correctly simulates scenario described?
, if so, how come can use update statement without special locking/transaction strategies , still consistent results?

if ever use simple this, you're fine.

the problems start when:

• you add condition - conditions fine, avoid filtering based on counter, that's great way lose determinism
• you update inside of transaction (careful - it's easy in transaction outside of scope of actual update statement, more if use e.g. transactionscope)
• you combine inserts , updates (e.g. usual "insert if not exists" pattern) - not problem if have single counter, multiple counters it's easy fall trap; not hard solve, unless have deletes, becomes whole different league :)
• maybe if rely on value of counter being unique auto-incrementing identifier. doesn't work if separate select , update (and no, update based on select doesn't - unlike plain update, select isn't serialized updates on same row; that's locking hints come in), i'm not sure if using output safe.

and of course, things might quite different if transaction isolation level changes. legitimate cause of errors, because sql connection pooling doesn't reset transaction isolation level, if ever change it, need make sure can't ever affect other sql execute on sqlconnection taken out of pool.