Re: [sqlite] SQLITE3 in multi-thread server

2010-03-17 Thread Pavel Ivanov
No TLS is used in SQLite. So you can open connection in one thread and use it in another. It's just a general suggestion to have one connection per thread. Otherwise you'll need to have some synchronization code or allow SQLite to do full synchronization for you. Pavel On Tue, Mar 16, 2010 at

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
On Tue, Mar 16, 2010 at 4:44 PM, Simon Slavin wrote: > One of the things that makes SQLite very simple is that it doesn't try to do > this, it has either everything or nothing locked. Yes, that is coming to realization now. -- hls

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
Do you know if TLS is used? How does it workout splite3_open_v2() per thread? Can the handle be used globally? In other words, is open per thread considered a "different connection?" On Tue, Mar 16, 2010 at 3:57 PM, Pavel Ivanov wrote: > Please be advised that

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread Simon Slavin
On 16 Mar 2010, at 6:12pm, HLS wrote: > It just seem so simplistic that SQLITE3 does not allow for > > Open Cursor > for each fetch > Issue Update based on ROWID > endfor > Close Cursor One reason you cannot do this is that changing the value of a field may change how you step from row to

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread Pavel Ivanov
Please be advised that updating/inserting/deleting from the table while you're executing select on it has undefined behavior in SQLite (if you do that on the same connection) and is considered dangerous. Doing that from different connections is possible only if have shared cache turned on and

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
Thanks Simon.  It just seem so simplistic that SQLITE3 does not allow for Open Cursor for each fetch     Issue Update based on ROWID endfor Close Cursor The row fetched is already complete, or the rowid in the table is no longer "sensitive" to anything but a update whether it was opened or not.

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread Simon Slavin
On 16 Mar 2010, at 5:17pm, HLS wrote: > Once approach is to queue any updates/deletes when the database is > locked with a select request. So when a fetch ends (like in the > GetNext function), it will check to see for any pending updates > and process them. > > Does that sound like a

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
On Tue, Mar 16, 2010 at 1:01 PM, HLS wrote: > >   dim f as TFileRecord >   dim tid as integer >   dim n as integer = 0 >   LoginSystem() >   if GetFirstFileRec(FileNameAreaKey, f, tid) then >      do >       inc(n) >      ... do something ... >      loop while

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
Thanks Pavel, it adds a lot of weight to the reality of where SQLITE3 fits. On Tue, Mar 16, 2010 at 12:04 PM, Pavel Ivanov wrote: >> But >> SQLITE3 locks the whole request.  It would be seem to me that this >> could be an improvement to release locks at the record/row level

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
On Tue, Mar 16, 2010 at 11:54 AM, Olaf Schmidt wrote: > > "HLS" schrieb im > Newsbeitrag > news:9cf5931e1003160705v38335b5g47a3d91193c28...@mail.gmail.com... > >> ...we have a ISAM/BTREE database under our full >> reader/writer and exclusive locking

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread Pavel Ivanov
> But > SQLITE3 locks the whole request.  It would be seem to me that this > could be an improvement to release locks at the record/row level (once > the fetch was done).  But then again, that is probably what VFS is > there for. SQLite locks the whole database when reading cursor is open because

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread Olaf Schmidt
"HLS" schrieb im Newsbeitrag news:9cf5931e1003160705v38335b5g47a3d91193c28...@mail.gmail.com... > ...we have a ISAM/BTREE database under our full > reader/writer and exclusive locking controls... >From your posted code-snippet I don't see any "heavy SQL-Query-usage" - so,

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread HLS
Pavel, thanks for your response. I will read up again and pay more attention to the technical VFS details (try it out).To answer your question, maybe I am beating a dead horse. :) This is an exploratory project. Right now, the server ISAM/BTREE is open in exclusive mode (no process

Re: [sqlite] SQLITE3 in multi-thread server

2010-03-16 Thread Pavel Ivanov
I didn't understand how you make everything to work, but can answer at the following question: > So how do I make it so there is no locking? I guess you already know that there's concept of VFS in SQLite (http://www.sqlite.org/c3ref/vfs.html). In VFS there's structure sqlite3_io_methods