[Mono-dev] Handling SQLITE_BUSY throush option in SqliteConnection

[Kamil Skalski]

Hi!

Recently I had some locking/threading problems with my multithread
application utilizing Sqlite and Mono provider.

First problem was that I utilized the same SqliteConnection in two
threads and it crashed in some situations. According to google and
sqlite docs I must use separate sqlite structure / SqliteConnection in
each thread. I used a little hack and just created getter for
connections, which checked CurrentThread.ManagedThreadId and returned
different object to each thread.
This probably solved all places where I was reading database in concurrent way.

But here comes the other problem. Assume one thread is doing some
inserts/updates and another one is also trying to read/write
something. According to http://www.sqlite.org/c_interface.html

"If the virtual machine is unable to open the database file because it
is locked by another thread or process, sqlite_step will return
SQLITE_BUSY. The calling function should do some other activity, or
sleep, for a short amount of time to give the lock a chance to clear,
then invoke sqlite_step again. This can be repeated as many times as
desired."

And indeed I got SqliteBusyException from Mono provider... But I
thought that instead of fixing all places in my code to catch this
exception and try to perform query once again, I could probably find
some better solution. And indeed, there are methods in sqlite:

void sqlite_busy_handler(sqlite*, int (*)(void*,const char*,int), void*);
void sqlite_busy_timeout(sqlite*, int ms);

which allow to configure behaviour in case of locks. So instead of
fixing my code or even making SqliteDataReader handle SQLITE_BUSY
state by sleeping and retrying, we could just configure underlaying DB
to do this for us.

As at the moment I do not need special handling of busy state, but
just waiting till lock is cleared. I thought that we could introduce
another parameter in SqliteConnection, which would pass timeout value
to sqlite_busy_timeout. I would just give it some large value and hope
that I will never see SQLITE_BUSY state again :)

So, I will prepare the implementation of say, 'busy_timeout'
parameter. Any suggestions? Some other name prefered?


--
Kamil Skalski
http://nazgul.omega.pl