Achieving at least the illusion of concurrency is important for many apps. (GUIs, moderate traffic web servers, etc.) Currently, the main ways to get it are either are various flavors of single-cpu user threads, or event driven code.
Achieving real concurrency - multiple threads of execution running at the same time on multiple processor cores - is less widely needed - but if you need it, you may really, really need it. The trick is that you may not know ahead of time whether you need it or not. ("Oh awesome, my web service just got hugely popular! Oh shit, my web service just got hugely popular...")
(Note also that there are many different - some wildly different - approaches in the basic multiple-threads-of-execution bin. User threads, OS threads, processes with shared memory, programs on different physical boxes exchanging messages with MPI or with TCP/IP sockets, etc.)
In practice I've been quite happy with Tcl's (and AOLserver's) high-level apartment thread model built on top of real, OS-driven POSIX threads. So I am biased in that direction. (Note though that these can be fairly heavyweight threads, often much heavier than the basic POSIX threads underneath.) There are definitely other concurrency models in real world production use worth checking out, though. Erlang is one of those, there are no doubt others.
Also, there is definitely ongoing, much needed research on better models and tools for concurrent programming. (E.g., there seem to be academic folks seriously working on replacing locks with transactions. Also lots of lower level stuff, trying to make efficient use of "weird" cpu architectures like graphics cards and IBM's Cell, etc.) My bet is that 15 years from now, we'll be using concurrent programming tools quite a lot different than the POSIX threads, MPI-based message passing, etc. that we're using now.
But to get back to the real world of the here and now...
Code that is thread-safe and reentrant is always a plus, because it gives you the option of using it in a multi-threaded environment, even if you don't really care to at the moment. But naturally, software development has trade-offs. Some developers choose to punt on thread-safety in favor of spending their time elsewhere. Whether that's a wise trade-off or not in their particular case, I don't know.
I do know I would try to avoid having to make that trade-off. I tend to write most of my code in a re-entrant style even if I'm using it single-threaded. ("Repeat after me class, global variables are EEEEEvil.")
Multi-threaded Tcl and AOLserver do work very well. This not so much because they just "use threads", but because they use POSIX threads in a rather well thought out way, and provide lots of nice APIs to make doing the right thing fairly easy, and difficult things possible.
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.