• Welcome, Guest. Please login.
 
August 14, 2020, 08:08:43 pm

News:

Welcome to the SQLitening support forums!


Locking w/ multiple connections

Started by ressystems, April 21, 2011, 07:53:46 pm

Previous topic - Next topic

ressystems

We've ended up in a very complicated situation, where I was hoping that SQLite and SQLitening would excel, but that doesn't seem to be the case just yet.

We have a single database file (about 3MB in size), which mostly contains clock in / clock out information.  This database is accessed locally by two programs:
  - One program, every hour, polls the database and sends data to our remote server (about 60 seconds of database reading / writing).
  - Another program, every so often, allows users to clock in and out.

Both of these programs are using the .NET SQLite controls, and are opening / closing the file when they are "done".

Another program, which is running on a remote computer (or multiple remote computers), allows the user to clock in / out using SQLitening.

Everything works "fine" when only one component is tested.  However, when two or three are running, database lock ups start occurring, and/or the programs are crashing.

Can you help us debug / figure out what needs to be done in regards to the locking of the database?  Is it possible to do table level locking?  What happens if SQLitening has a database 'open', and we try and open it with the .NET ADO connector?

Thanks!


--
Anthony

Fred Meier

QuoteBoth of these programs are using the .NET SQLite controls, and are opening / closing the file when they are "done".
Are these .Net controls accessing the SQLite API directly?  Do both programs run on the same computer as the database?

QuoteAnother program, which is running on a remote computer (or multiple remote computers), allows the user to clock in / out using SQLitening.
I assume these porgrams are using SQLitening in remote mode?  If true, I assume you have SQLitening server running on the above computer?

QuoteEverything works "fine" when only one component is tested.  However, when two or three are running, database lock ups start occurring, and/or the programs are crashing.
What do you mean by "one component"?  What errors are you getting?  How is SQLite/SQLitening causing programs to crash?

QuoteIs it possible to do table level locking?
No, SQLite only locks at the database(file) level.  Below are excerpts from SQLite doc:


QuoteMultiple processes can have the same database open at the same time. 
Multiple processes can be doing a SELECT at the same time.  But only one
process can be making changes to the database at any moment in time,
however. 

We are aware of no other embedded SQL database engine that supports as
much concurrency as SQLite.  SQLite allows multiple processes to have the
database file open at once, and for multiple processes to read the
database at once.  When any process wants to write, it must lock the
entire database file for the duration of its update.  But that normally
only takes a few milliseconds.  Other processes just wait on the writer to
finish then continue about their business.  Other embedded SQL database
engines typically only allow a single process to connect to the database
at once. 

When SQLite tries to access a file that is locked by another process, the
default behavior is to return SQLITE_BUSY.  You can adjust this behavior
from C code using the sqlite3_busy_handler() or sqlite3_busy_timeout() API
functions.



QuoteWhat happens if SQLitening has a database 'open', and we try and open it with the .NET ADO connector?
I have no knowledge of .NET ADO.  If it does "standard" SQLite there should be no problem.

D. Wilson

I have a server running on my system. And I have had no issues with mutliple users. I use the Special DLL with vb6. Looking at your original posting I think you are using ADO to access the database. By accessing the database through the DLL you should have no issue. I know that using a connector makes it easier to create a data entry form. Personally I am not a fan of ADO. That is what I really like about SQLitening. I simply add the DLL to the program directory and my software works. There is no need to ensure the database connector is 'registered' with Windows. Try using the DLL you will love it.