Fluent NHibernate - need multi-program access to a single, lazy loaded SQLite database

Question

I'm developing a project that has multiple programs that share a single, lazy loaded SQLite database file, using Fluent Nhibernate (Auto Mapping) as the data access layer.

The first program I developed (let's call it Program 1) kept the Session open all the time, and lazy loading worked fine.

This approach failed when I got Program 2 running, and tried to write to the database from Program 2 while Program 1 was running - I got "database locked" exceptions.

I got around this by closing the Session after Program 1 starts up - e.g.

        private ISessionFactory _sessionFactory;
        private ISession _session;

        _sessionFactory = Database.CreateSessionFactory();

        _session = _sessionFactory.OpenSession(); 
        _session.BeginTransaction();

        // ... read the database here

        _session.Close();

Of course, this broke lazy loading in Program 1 when the user selected a different data data set from the user interface - which I had expected.

I thought I would be able to just open the Session again whenever the user selected new data, and then close it again - e.g.

        if ( !_session.IsOpen )
            _session = _sessionFactory.OpenSession();

        if ( !_session.IsConnected )
            _session.Reconnect();

        _session.BeginTransaction();

        // ... read the database here

        _session.Close();

But so far, have not been able to get this to work. I get "no session, or session was closed" exception when I try to read the data, even though I've just opened a session. (The test for the connection was just an experiment, because the exception trace said something about throwing lazy exceptions when disconnected, but it didn't help)

What am I doing wrong?

Can you explain why the three services need to share data at the same time? Can they be combined into one app? Is there a reason to use sqlite over another technology? — GrayWizardx
– GrayWizardx, Commented Dec 19, 2009 at 1:51
Why do you wan sql lite? sql lite does not support distributed transactions. — Paco
– Paco, Commented Dec 19, 2009 at 11:17
@GrayWizard - it's an scientific instrumentation app suite. Program1 will mainly read multiple measurements from the DB for group analysis. Simultaneously, Program2 will collect a single measurement at a time, and write it to the database. For now, both programs will run on the same Windows PC. It seemed reasonable to do them as separate programs, but maybe that was a mistake. — Tom Bushell
– Tom Bushell, Commented Dec 19, 2009 at 21:57
@Paco - to be honest, we're using SQLite because it's what came with Fluent NHibernate. My reading indicates it should be able multi-user/program access, at least in a low volume application like ours. But if it turns out to be a handicap, should be fairly easy to switch to something else, since we're using NHibernate. At least, that's my hope! — Tom Bushell
– Tom Bushell, Commented Dec 19, 2009 at 22:03
In that case, you should switch to some other database. SQL lite is good for single user apps, or for testing Nhibernate. — Paco
– Paco, Commented Dec 20, 2009 at 15:22

GrayWizardx · Accepted Answer · 2009-12-19 01:19:16Z

2

Is it possible for you to build a service layer which is initiated by the first app to call it (or registered as a windows service if you are on a windows box) and then have everyone call into that service to get their data from?

Im not talking about having a separate server, just a separate service that your programs call into.

answered Dec 19, 2009 at 1:19

GrayWizardx

21.5k2 gold badges33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tom Bushell Over a year ago

I suppose, but that would add another layer of complexity, and I'd still have to solve the same problem I have now.

Michael Brown Over a year ago

Gray Wizard is right...you're duplicating the data access portion between prog1 and prog2. If you create a service that manages the session and exposes query objects for the other two programs you won't run into contention. You could even go crazy and build a SQL parser to turn SQLite into a full DB Server (just kidding)

Tom Bushell Over a year ago

Upon reflection, (and reinforced by Mike Brown's comment), I realized you were right. Sorry for being dismissive. I've never written a service - how difficult is it?

GrayWizardx Over a year ago

Its pretty straight forward, depending on the language you are using and the requirements of your scenario. In .NET world you could use something like Named Pipes pretty easily: switchonthecode.com/tutorials/…

Community · Accepted Answer · 2017-05-23 12:27:11Z

0

The "database locked" exceptions mysteriously disappeared after I refactored some of the session management code for other reasons. (The main changes were to use Transactions for ALL DB access, and to ensure all Transaction and Session objects were properly Disposed - mainly by enclosing them in "using" blocks).

This link discusses a similar problem that was caused by a missing Dispose. I suspect that may have been my problem, but am not sure.

Another good source of things to try is Database file is inexplicably locked during SQLite commit

In any case, multiple programs are now successfully reading and writing to a single shared SQLite database.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jul 15, 2011 at 18:32

Tom Bushell

5,8754 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

Fluent NHibernate - need multi-program access to a single, lazy loaded SQLite database

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related