Database – Application Design with a Shared Database

I have a generic design question that relates to enterprise applications but I think that software developers from other types of projects may have experience with this as well.

The scenario is following – you have a database and you are about to write an application that provides access to its data. The database also contains data about user accounts, their roles and authorization. The problem is that other processes are accessing the database as well and any one of these processes may change any data at any moment (e.g., delete a user).

In other projects I used to have exclusive access to the database and so I usually wrapped the database with a layer that exposed the related domain functionality in a SOA-style. This wrapper had a rich domain model and could utilize caching of the database data because it was aware of any potential change (by intercepting domain events, CQRS) that could modify it.

With a shared database I don't know how to approach the design. Imagine this situation:

1. HTTP request arrives to you application and you are to authenticate this request.
2. You query the database for the claimed user account and its login data. If the supplied credential matches then a session is established (e.g., in ASP.NET a secure cookie is sent to the client).
3. But then a subsequent request from that user arrives. The problem is that instead of doing something useful at this moment, it seems like that one needs to check whether the associated user account still exists in the database. Any information can stop being valid at any point because some other process may alter the database.

This is of course a more generic problem. The data may not only change between requests but even between two separate database calls. The easy answer here is to use locks/transactions. But the truth is that this would require huge changes to the database schema. For example, every user account would require an additional column 'InUse' – but that is just a very simple scenario. I can imagine that completely new tables would need to be created. Also, the transactions would typically span across many HTTP requests and thus render the database useless for long periods of time for other users.

I have read the PoEAA book and I am well aware of various synchronization techniques. What I am looking for here is some generic technique or methodology how to achieve the so called "good-enough-design" that does not require changes to the database schema and can handle the mentioned situations where anything can change between two requests.

Right now I think I will go with the following approach – I will authenticate the user. Then I will not perform any subsequent checks to ensure that he was not deleted in the meanwhile. But when I perform some operation that relies on the existence of the user I will receive an exception and deduce that the user must have been deleted. The response will then be some kind of error message or error page.

I am sure this is a common problem that many of you must have encountered. I will appreciate any solution or even a reference to some book or paper. Thanks.