Can I make a simple suggestion:
Do not use Firebird in “Embedded” mode in a website
In fact, I could even extend that to any embedded database, but particularly Firebird. You should stick with server-mode database (Firebird in super-server mode is pretty fantastic for simple databases).
I know it’s quite tempting to use embedded Firebird in a website. Especially if you’re running on a hosted service that otherwise charges for database access. But there are a couple of problems with this model:
- File permissions are hard to get right. Firebird requires read/write access to the data file. This isn’t a huge problem if you use the App_Data directory available to ASP.NET 2.0 sites, but not everybody has such a nifty little feature.
- The biggest problem by far, and this is especially true for hosted services, is IIS6 worker process recycling. If your service provider recycles your worker process on any kind of schedule (personally, I think that’s a bad idea, but some admins can be quite superstitious about such things) then you can run into problems during the recycle operation.
Point #2 is the killer. You see, when IIS6 recycles a worker process, what actually happens is it just switches the current context to a new process. It’s called “overlapped recycling.” The old process continues to run until it has finished servicing all of the existing requests. So there is a short time when there are two worker processing running. If the new worker process tries to open a connection to your database file while the old one is still running (which is quite possible under heavy-ish load) then you’re going to have problems. Remember that Firebird in embedded mode is basically running as a super-server, and does not allow multiple connections to the file from different processes.
Note that while you can change this behaviour, I wouldn’t recommend it – it’s a bit of a performance killer to queue up new requests while the application is restarting.
Things may work OK for a while, or you might only get intermittent errors (after all, you’ll only run into problems under heavy load at the time of a worker process recycle) but you will have problems.
One other possible work around (other than disabling the overlapped recycle) would be to try to open the database in your Application_Start event, and just keep trying until you succeed – don’t let any other requests connect to the file before then, either. Obviously, this is no good for performance, but at least it’ll work.