Page 1 of 3
SQLite Connection Limit
Posted: Mon Jun 23, 2008 4:30 pm
by Karbon
Is there any hard coded connection limit built into the PB SQLite support? I switched an app from using ODBC (with the SQlite ODBC driver) to the native PB support and now the application hangs if 3 or 4 connections are made to the db file (it seems to vary on XP and Vista?). None are explicitly asking for exclusive access but is that built-in to the PB lib somehow?
AFAIK SQlite itself enforces no limit other than whole-database locking on write (which is fine since the clients I'm speaking of only need read access)..
Posted: Mon Jun 23, 2008 5:47 pm
by bobobo
do you use databaseerror() to recognize db/file-locks ?
i do preferr a delayed loop while databaseerror() ist not ok with sqlite.
Posted: Mon Jun 23, 2008 5:51 pm
by Karbon
Yes, I do and there are no errors - the OpenDatabase call never returns.
Posted: Mon Jun 23, 2008 7:14 pm
by Num3
I just tested a huge project i am working on with 8 concurrent clients and all works well... 2 databases, 16 connections...
I have noticed sqlite plugin is very picky about DatabaseQuery() and DatabaseUpdate() so be very careful and always test each sql statement with both !
If one gives an error, try the other command with the same sql statement!
Sometimes, when i'm in SQLITE MANAGER in firefox i get db locks, specially after a begin transaction / commit, they always lock the database, so try to avoid them under sqlite PB!
They work great outside but under PB i get db locks ...
Posted: Mon Jun 23, 2008 7:34 pm
by Karbon
SQLite's locking is whole-file locking so you'll run into problems trying to write to the database from concurrent connections.
DatabaseUpdate() should be used for queries that write to the database (INSERT UPDATE DELETE, ALTER, etc), DatabaseQuery() is only used when the query returns a result set (SELECT).
In your project are you connecting to a file on a network share?
Posted: Mon Jun 23, 2008 7:43 pm
by Num3
Yes, it's on a network share and i had no troubles so far...
I also test it on my pen drive for even slower times, and it works too.
Oh, wait, but i'm using the beta revised version!
http://purebasic.com/beta/windows/
Maybe that helps!
Posted: Mon Jun 23, 2008 8:15 pm
by Karbon
I'll re-run all my tests with the newest version..
Posted: Mon Jun 23, 2008 9:06 pm
by Karbon
Unfortunately it is still happening.. It looks like the very first call to OpenDatabase on the 4th workstation hangs everything.
Posted: Tue Jun 24, 2008 12:20 am
by Fred
OpenDatabase for SQLite is just a wrapper to sqlite3_open_v2(), so i don't think it's specific to PB, you may try to test with the API to be sure..
Posted: Tue Jun 24, 2008 8:18 am
by dige
Check out "Pragmas"
http://www.sqlite.org/pragma.html
if you dont use a single access.
Posted: Tue Jun 24, 2008 8:29 am
by pdwyer
Fred wrote:OpenDatabase for SQLite is just a wrapper to sqlite3_open_v2(), so i don't think it's specific to PB, you may try to test with the API to be sure..
Thats useful to know, what params are used?
The sqlite3_open_v2() interface works like sqlite3_open() except that it acccepts two additional parameters for additional control over the new database connection. The flags parameter can be one of:
- SQLITE_OPEN_READONLY
- SQLITE_OPEN_READWRITE
- SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
The first value opens the database read-only. If the database does not previously exist, an error is returned. The second option opens the database for reading and writing if possible, or reading only if if the file is write protected. In either case the database must already exist or an error is returned. The third option opens the database for reading and writing and creates it if it does not already exist. The third options is behavior that is always used for sqlite3_open() and sqlite3_open16().
Posted: Tue Jun 24, 2008 12:55 pm
by Fred
By default, it allows many access, or i miss something ?
pdwyer: it uses SQLITE_OPEN_READWRITE
Posted: Tue Jun 24, 2008 2:09 pm
by dige
Yes, by default is: synchronous = FULL and locking_mode = NORMAL,
so my posting is redundant.
It was just an idea ... :?
Posted: Tue Jun 24, 2008 2:18 pm
by pdwyer
Cheers Fred!
Karbon, maybe as a test you can try the API method and use the readonly flag to open the DB, see it it helps.
My guess is that over time the PB sqlite implementation will mature and put more options for us to use but it might not quite be everything to everyone in this first release
Posted: Tue Jun 24, 2008 2:22 pm
by Karbon
I've solved it, or at least I think I have..
The database was being locked in between connections (at application start the DB is written to several times). Apparently the ODBC driver had additional checks built-in and that is why it was working before using ODBC.
Thanks to all!