Use WCF to safely let lots of concurrent users access MS-Access or Sqlite or other file based DB safely?

Question

I want to write a data access layer for a file based database behind a data driven website. There would be lots of concurrent update and reads going on, I'd like at the very least that access be serialized, so that page request would get in a nice orderly line.

What WCF configurations would get me what I want? Does PerSession concurrency get me there?

Am I barking up the wrong tree and should I rely on the Database drivers to do take care of file locking for me?

EDIT: I'm trying to put together an open source calendaring website that doesn't require users to deal with MySql or SqlServer--in otherwords, it should be an xcopy deploy.

Answer 1

You will probably want to use a Single instance with Single concurrency:

[ServiceBehavior(
    InstanceContextMode = InstanceContextMode.Single,
    ConcurrencyMode = ConcurrencyMode.Single)]

This will ensure all calls 'get in line'. If you use a PerSession instance context then you will end up with concurrent calls into your back-end file. You will also want to be sure you set custom Throttling behaviors. You can do this in your config, or via code:

ServiceThrottlingBehavior throttlingBehavior = new ServiceThrottlingBehavior()
{
    MaxConcurrentCalls = 200,
    MaxConcurrentInstances = 200,
    MaxConcurrentSessions = 200,
};
ServiceHost host = ...
host.Description.Behaviors.Add(throttlingBehavior);

Answer 2

Serializing "lots of concurrent update and reads" is going to result in very bad performance for your users. I recognize it's not really an answer to you question (more of a challenge to the premise), but it would be much better to move to a resource manager more suited for this type of activity like SQL Server. Architecturally, trying to make users look through a straw at their data is the wrong way to go -- it generally results in upset users.

Answer 3

Matthew,

What is your use case? Are you allowing users to plug in databases ad-hoc? If so, you might be better off writing some code that takes their data and imports it into a SQL Server database. It's better designed to do this.

If you're worried about cost, you can use SQL Server Express or Postgres.

Answer 4

Sqlite can deal with concurrent reads so you don't have to serialize your reads. The writes however are a different story, they lock the complete sqlite database.

I don't know how ms-access deals with concurrency.

What kind of website are you building?