Is there any difference in the logic? Locking access to a variable will work the same way, even if it's multiple processes trying to access it vs multiple threads. Is the difference just in the mechanism of the lock, or is there some type of difference in behaviour in accessing shared items between threads and processes?

In a database you have to lock access at the file level, naturally. To do multi-process locks you need to store your locks either in shared memory on in an mmap block so that they're shared between processes and then you have to deal with the case of processes crashing or being killed without releasing their locks, which can then trigger a deadlock because the lock is held by a process that doesn't exist anymore.

To resolve that you can pop up to the process tree, assuming you saved the PID along with the lock and see if that process still exists (and hope its PID hasn't been reused) and if not iterate over all locks held by that process and free them to break the deadlock, but that sort of overhead at something as fundamental as locking code can be annoying.

Also note that your semaphores then have to usually be implemented in assembly or you have to have a library that implements atomic increments of reference counts. Qt's QAtomicInt will do the trick if abused a little (just because it has an assembly implementations for multiple architectures). pthread_* doesn't have support for this as far as I know.

So, yeah, it's doable and some DBs do handle multi-process concurrent access, but it can be a pain in the ass. I believe SQLite does it by just locking complete tables on write access rather than using more fine-grained locks. MySQL embedded may also support multi-process access -- not sure.

Of the embedded databases I'm familiar with, Berkeley DB with its transaction layer does support multi-process access. It requires some careful coding but does work and detects deadlocks of the type you describe (http://www.oracle.com/technology/documentation/berkeley-db/d...). No idea if this works with language bindings other than the officially-supported C, C++, and Java.

IIRC your last point is the reason that django apps get locked when using sqlite. Depending on what you're doing you may inadvertently update things like object histories etc. whilst your app is trying to update something else.

I see the problem with the process crashing. Why do you need to implement the semaphores in assembly? Threads can pre-empt the same way as processes, no? If it works there, why would it not work across processes?

Because there are no POSIX APIs for bare atomic reference counting. Presumably pthreads implementations do implement atomic reference counting in assembly, it's just not exposed through the API. For more on Linux see:

  man futex