Note that you probably do need to think about concurrency in the way you write your SQL insert or update statements - that's got nothing to do with AOLserver nor Tcl per se. But Oracle and PostgreSQL both have MVCC and Transactions, which help enormously in dealing with database concurrency.
AOLserver uses multi-threading implicitly and rather pervasively. However, as an OpenACS or AOLserver/Tcl programmer you normally don't need to think about this. This is the beauty of the AOLserver and Tcl thread model and APIs vs. nasty low-level stuff like the POSIX thread APIs.
Note that Tcl's threading model is default shared-nothing. (While the POSIX threads underneath are default shared everything. The C code deals with that.)
In AOLserver each page hit runs in its own connection thread with its own Tcl interpretor. (The actual architecture is a bit more complex than that, but that statement is true as far as it goes.) You program what each page does essentially as if it was a single threaded application. When you as the programmer use nsv_get, nsv_set, or ad_schedule_proc, you are using AOLserver's multi-threaded services, but typically there's nothing special you need to do because of that. Each single nsv_* operation is atomic, the mutex locking is done automatically underneath.
With OpenACS you would typically not use lower level Tcl multi-threading stuff like ns_mutex or ns_cond - but those features are there if you need them.
Now, say you want to read a the value 5 from and nsv, increment it by 1, and then write it back. Or more realistically, use nsv_get to read a Tcl list, lappend to it, and then use nsv_set to write it back. Ah, now you do need to think about threads and concurrency, at least a little bit. Another thread could be reading or writing that nsv at the same time, and correct operation depends on a sequence of two nsv_* commands being atomic, not just one. So you need to manually protext access to the nsv with a mutex lock. Or better yet, upgrade to Zoran's Tcl Threads Extension which gives you an atomic tsv::lappend command, etc.