Re: [sqlite] SQLITE3 in multi-thread server

[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 5:55 PM, HLS  wrote:
> 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 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 read_uncommitted set to 1. If you don't use shared
>> cache and turned off locking on VFS level then you can easily get
>> database corruption.
>>
>> Pavel
>>
>> On Tue, Mar 16, 2010 at 2:12 PM, HLS  wrote:
>>> 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. The "current" cursor is at the next record ready to be fetched,
>>> not the one that just been read.  So it would seem it would be
>>> possible to implement a wrap a lock on the rowid update.
>>>
>>> I had to see if this is possible with the VFS notes Pavel pointed me
>>> to perhaps, or even been looking at the feasibility of changing code
>>> (which is the last thing I don't want to do.)
>>>
>>> On Tue, Mar 16, 2010 at 1:31 PM, Simon Slavin  wrote:

 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 viable approach with SQLITE3?
>
> Hm, this would not address possible client code that can break
> from a loop before reaching the end of select query.

 For the approach that involves queueing write commands, you do definitely 
 need to know when a SELECT has finished with the database.  But SQLite 
 depends on that anyway, and there's no reason why it shouldn't depend on 
 correct usage of the API including sqlite3_finalize() .

 I have one installation where it's okay for SELECT commands to return 
 results which are slightly out of date, but new data becomes available at 
 sparse and irregular intervals from many sources.  Consequently, this 
 system delays write commands until there has been no access to the 
 database for one minute (checked by touching a file whenever 
 sqlite3_prepare() is done).  In this particular installation this makes 
 perfect sense, because the patterns of reads and writes is well 
 understood.  However, for some random installation for a random use of 
 SQLite it would be disastrous.  You may be in a situation where you can 
 develop a protocol which fits your particular use of SQL very well even 
 though the solution would be useless for a more general use of SQLite.

 Simon.
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>>
>>>
>>>
>>> --
>>> hls
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> hls
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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 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 read_uncommitted set to 1. If you don't use shared
> cache and turned off locking on VFS level then you can easily get
> database corruption.
>
> Pavel
>
> On Tue, Mar 16, 2010 at 2:12 PM, HLS  wrote:
>> 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. The "current" cursor is at the next record ready to be fetched,
>> not the one that just been read.  So it would seem it would be
>> possible to implement a wrap a lock on the rowid update.
>>
>> I had to see if this is possible with the VFS notes Pavel pointed me
>> to perhaps, or even been looking at the feasibility of changing code
>> (which is the last thing I don't want to do.)
>>
>> On Tue, Mar 16, 2010 at 1:31 PM, Simon Slavin  wrote:
>>>
>>> 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 viable approach with SQLITE3?

 Hm, this would not address possible client code that can break
 from a loop before reaching the end of select query.
>>>
>>> For the approach that involves queueing write commands, you do definitely 
>>> need to know when a SELECT has finished with the database.  But SQLite 
>>> depends on that anyway, and there's no reason why it shouldn't depend on 
>>> correct usage of the API including sqlite3_finalize() .
>>>
>>> I have one installation where it's okay for SELECT commands to return 
>>> results which are slightly out of date, but new data becomes available at 
>>> sparse and irregular intervals from many sources.  Consequently, this 
>>> system delays write commands until there has been no access to the database 
>>> for one minute (checked by touching a file whenever sqlite3_prepare() is 
>>> done).  In this particular installation this makes perfect sense, because 
>>> the patterns of reads and writes is well understood.  However, for some 
>>> random installation for a random use of SQLite it would be disastrous.  You 
>>> may be in a situation where you can develop a protocol which fits your 
>>> particular use of SQL very well even though the solution would be useless 
>>> for a more general use of SQLite.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> hls
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
hls
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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 row.  You might change something which needs an index 
changed, for example.  When you start working out how locking by rows works you 
find that you need to lock not just that row but the rows near it, in case one 
of them suddenly moves away.  It can get quite complicated.  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.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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 read_uncommitted set to 1. If you don't use shared
cache and turned off locking on VFS level then you can easily get
database corruption.

Pavel

On Tue, Mar 16, 2010 at 2:12 PM, HLS  wrote:
> 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. The "current" cursor is at the next record ready to be fetched,
> not the one that just been read.  So it would seem it would be
> possible to implement a wrap a lock on the rowid update.
>
> I had to see if this is possible with the VFS notes Pavel pointed me
> to perhaps, or even been looking at the feasibility of changing code
> (which is the last thing I don't want to do.)
>
> On Tue, Mar 16, 2010 at 1:31 PM, Simon Slavin  wrote:
>>
>> 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 viable approach with SQLITE3?
>>>
>>> Hm, this would not address possible client code that can break
>>> from a loop before reaching the end of select query.
>>
>> For the approach that involves queueing write commands, you do definitely 
>> need to know when a SELECT has finished with the database.  But SQLite 
>> depends on that anyway, and there's no reason why it shouldn't depend on 
>> correct usage of the API including sqlite3_finalize() .
>>
>> I have one installation where it's okay for SELECT commands to return 
>> results which are slightly out of date, but new data becomes available at 
>> sparse and irregular intervals from many sources.  Consequently, this system 
>> delays write commands until there has been no access to the database for one 
>> minute (checked by touching a file whenever sqlite3_prepare() is done).  In 
>> this particular installation this makes perfect sense, because the patterns 
>> of reads and writes is well understood.  However, for some random 
>> installation for a random use of SQLite it would be disastrous.  You may be 
>> in a situation where you can develop a protocol which fits your particular 
>> use of SQL very well even though the solution would be useless for a more 
>> general use of SQLite.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> hls
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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. The "current" cursor is at the next record ready to be fetched,
not the one that just been read.  So it would seem it would be
possible to implement a wrap a lock on the rowid update.

I had to see if this is possible with the VFS notes Pavel pointed me
to perhaps, or even been looking at the feasibility of changing code
(which is the last thing I don't want to do.)

On Tue, Mar 16, 2010 at 1:31 PM, Simon Slavin  wrote:
>
> 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 viable approach with SQLITE3?
>>
>> Hm, this would not address possible client code that can break
>> from a loop before reaching the end of select query.
>
> For the approach that involves queueing write commands, you do definitely 
> need to know when a SELECT has finished with the database.  But SQLite 
> depends on that anyway, and there's no reason why it shouldn't depend on 
> correct usage of the API including sqlite3_finalize() .
>
> I have one installation where it's okay for SELECT commands to return results 
> which are slightly out of date, but new data becomes available at sparse and 
> irregular intervals from many sources.  Consequently, this system delays 
> write commands until there has been no access to the database for one minute 
> (checked by touching a file whenever sqlite3_prepare() is done).  In this 
> particular installation this makes perfect sense, because the patterns of 
> reads and writes is well understood.  However, for some random installation 
> for a random use of SQLite it would be disastrous.  You may be in a situation 
> where you can develop a protocol which fits your particular use of SQL very 
> well even though the solution would be useless for a more general use of 
> SQLite.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
hls
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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 viable approach with SQLITE3?
> 
> Hm, this would not address possible client code that can break
> from a loop before reaching the end of select query.

For the approach that involves queueing write commands, you do definitely need 
to know when a SELECT has finished with the database.  But SQLite depends on 
that anyway, and there's no reason why it shouldn't depend on correct usage of 
the API including sqlite3_finalize() .

I have one installation where it's okay for SELECT commands to return results 
which are slightly out of date, but new data becomes available at sparse and 
irregular intervals from many sources.  Consequently, this system delays write 
commands until there has been no access to the database for one minute (checked 
by touching a file whenever sqlite3_prepare() is done).  In this particular 
installation this makes perfect sense, because the patterns of reads and writes 
is well understood.  However, for some random installation for a random use of 
SQLite it would be disastrous.  You may be in a situation where you can develop 
a protocol which fits your particular use of SQL very well even though the 
solution would be useless for a more general use of SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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 GetNextFileRec(FileNameAreaKey, f, tid)
>   end if
>
> Our customers have many scripts like this for their needs.  So this is
> what I are trying to accomplish here - how to keep a persistent
> SQLITE3 cursor per thread context for the current API implementation.

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 viable approach with SQLITE3?

Hm, this would not address possible client code that can break
from a loop before reaching the end of select query.

--
hls
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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 (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 it
> was developed as light-weight file-based multi-process database
> engine. There are no row-level locks in SQLite because it's impossible
> (or almost impossible) to implement them using only OS file-locking
> mechanisms. And you won't find row-level locking API in VFS.
> BTW, if you turn on shared cache on your server and set
> read_uncommited to 1 then you'll make possible writing while readers
> are active right away. Beware though that if you have open reading
> cursor and trying to write over the same SQLite connection then your
> transaction won't be committed until cursor is closed.
>
>
> Pavel
>
> On Tue, Mar 16, 2010 at 11:36 AM, HLS  wrote:
>> 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
>> sharing) and all records and btree pages are managed with software
>> level reader/writer/exclusive locks.   SQLITE3 was suggested to low
>> footprint sql engine to improve the FTS and offer more ad-hoc
>> searching capabilities.  So I was thinking if I removed its locking
>> logic, I can manage it the sharing with incoming threads.
>>
>> One goal to maintain the client side API traversal functions and
>> record level I/O functions:
>>
>>    GetFileRecByRef()
>>    SearchFileRec()
>>    GetFirstFileRec()
>>    GetNextFileRec()
>>    GetPrevFileRec()
>>    GetLastFileRec()
>>    UpdateFileRec()
>>    AddFileRec()
>>    DeleteFileRec()
>>
>>
>> What I seeing basically if a thread is currently in, I presume *read
>> only* mode with a SELECT, any attempt before the cursor is released,
>> to update a record, a sharing violation is returned.
>>
>> I can get it to work if the select request is completed (retrieve the
>> records first), then update them.  But this request a change to
>> existing client applets using the current client side RPC API.
>>
>> I'm new to SQLITE3 so obviously I have a lot to learn about its
>> technical details.  But I was thinking if the cursor (sqlite3_stmt *),
>> if I correct in viewing it as a *cursor*, then it would be past the
>> its fetch and should be released from any update restriction.  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.
>>
>> Thanks
>>
>>
>> On Tue, Mar 16, 2010 at 10:56 AM, Pavel Ivanov  wrote:
>>> 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 (http://www.sqlite.org/c3ref/io_methods.html) which
>>> contains functions xLock and xUnlock. So if you implement your own VFS
>>> and make these functions as no-op then you'll get SQLite without any
>>> kind of locking (you should implement xCheckReservedLock as no-op
>>> too). And if your application is sure that nobody else accesses the
>>> same database and you synchronize threads somehow (or use shared
>>> cache) then it will work just fine (I've implemented this kind of
>>> trick in my application to boost its performance).
>>>
>>>
>>> Pavel
>>>
>>> On Tue, Mar 16, 2010 at 10:05 AM, HLS  wrote:
 Folks,

 I read the documentation suggesting to use another SQL engine for a
 multi-threaded client/server environment and I reached a point where I
 see why.   But I was thinking it could be used because currently, we
 have a ISAM/BTREE database under our full reader/writer and exclusive
 locking controls.  So my attempt was to basically plug and play
 SQLITE3.

 What I need is NO LOCKING so we can use our own thread context
 contention controls.

 I read the technical notes but I'm still getting either a error 5 or
 8.  I'm not entirely sure what steps need to be done.   I tried:

       sqlite3_config(SQLITE_CONFIG_MULTITHREAD);

 Overall, the design is the RPC server opens exclusive the database
 file, and each thread context gets a query cursor.   Since the client
 API has no concept of a "close",  the cursor is saved in the server

Re: [sqlite] SQLITE3 in multi-thread server

[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 controls...
>
> From your posted code-snippet I don't see any
> "heavy SQL-Query-usage" - so, first the question,
> why would you want to change your current system?

Thank you!  Trust me, we are trying to figure that out.  I guess the
justification is all of what you stated.   Overall:

1) Improve the search system
2) Allow for 3rd party growth (add 3rd party fields)
3) "Social Networking" ideas.
5) Low footprint installation
6) API compatibility with existing installed base of client applets.
7) Extend the API to be more "SQL" ready

> If you need only something like a "fast entry" into
> some of your DataFile-Chunks (per FileNameAreaKey)
> and then enumerate (record-)content in these
> Data-Chunks, respecting some "sort-order" - why choose
> a system which is based on SQL-query-processing?

Trying to see if we can make a transparent replacement. Right now, the API ...

http://www.winserver.com/public/wcsdk

has over 250 functions. For the databases, they are structured the
same. For example for files,  we have 5 keys:

//!
//! Files Database Function Keys
//!

const int FileNameAreaKey = 0;
const int FileAreaNameKey = 1;
const int FileAreaDateKey = 2;
const int FileUploaderKey = 3;
const int FileDateAreaKey = 4;

and a set of transversal and I/O functions.  For for example, for the
client function:

BOOL  APIENTRY GetFirstFileRec(DWORD keynum, TFileRecord , DWORD );

On the backend, the server stub is:

/* [fault_status][comm_status] */ error_status_t WcsGetFirstFileRec(
   /* [in] */ TWildcatContextHandle wch,
   /* [in] */ unsigned long keynum,
   /* [ref][out] */ TFileRecord __RPC_FAR *f,
   /* [ref][out] */ unsigned long __RPC_FAR *tid)
{
   ZeroMemory(f, sizeof(TFileRecord));
   TClientContext *cc = GetClientContext(wch);
   if (!cc->LoggedIn()) return WC_USER_NOT_LOGGED_IN;
  TUserContext *uc = cc->GetUserContext();

  TReaderGrabber grab(FileAreas);

  // release/create new SQL cursor
  if (cc->qFiles)  delete cc->qFiles;
  cc->qFiles = new CSqliteQuery(*sqlFilesDb);

  CString sql = "select * from files";
  switch (keynum) {
     case FileNameAreaKey: sql += " order by Name, Area"; break;
     case FileAreaNameKey: sql += " order by Area, Name"; break;
     case FileAreaDateKey: sql += " order by Area, FileTime"; break;
     case FileUploaderKey: sql += " order by UploaderId"; break;
     case FileDateAreaKey: sql += " order by FileTime, Area"; break;
  }
  sqlite3_stmt *stm = cc->qFiles->get_result(sql.GetString());
  if (!stm) {
     delete cc->qFiles;
     cc->qFiles = NULL;
      return WC_RECORD_NOT_FOUND;
  }

  // save tid point, not used, but checked in Next/Preve
  *tid = (unsigned long)>qFiles;

  for (;;)
  {
     if (!cc->qFiles->fetch_row()) {
        cc->qFiles->free_result();
        delete cc->qFiles;
        cc->qFiles = NULL;
        return WC_RECORD_NOT_FOUND;
     }
     DWORD area = cc->qFiles->getuval("Area");
     DWORD luds = OBJECTFLAGS_FILEAREA_LIST |
                             OBJECTFLAGS_FILEAREA_DOWNLOAD;
     BOOL bCheckFileAccess = FALSE;
     if (area < FileAreas.Count() &&
         CheckFileAreaAccess(cc,area,luds,bCheckFileAccess)) {
        if (uc && bCheckFileAccess) {
           // Private Files Logic
           DWORD pvtid = cc->qFiles->getuval("PrivateUserId");
           DWORD upid  = cc->qFiles->getuval("UploaderId");
           if (!CheckFileOwnership(pvtid,upid,uc->User.Info.Id)) {
              continue;
           }
        }
        // perform full SQL conversion and return record
        TFullFileRecord ff = {0};
        SqliteToFileRecord(cc->qFiles,);
        GetOfflineInfo(ff);
        *f = ff.Info;
        return 0;
     }
  }
   return 0;
}

And for the Next (and Prev) functions, the logic is the same except
the cc->qFiles context cursor is expected.

So yes, the methodology is not fitting for SQL which requires a cursor
release concept. It is expected that a new level of API functions will
do this, add a GetFileRecClose() function.

But as you said, the direction is to more more of the work to the
client, so there is a lot of truth that we battling older design I/O
framework.

I think SQLITE3 is just a way to explore this because it may not
matter what sql engine we use.  But we want to use "generic" SQL so
that different engines can be used.

I explored berkerley-db back in 1998 or so and it appears that we
could do something, but it never happen - the goals above were not as
strong.

> We also use an RPC-server, with the SQLite-engine
> as the serverside backend - but we "completely isolate"
> the serverside-cursors from the clientside - meaning, we
> perform our SQL-query at the serverside - and serialize
> (copy) all the records, according to the "set 

Re: [sqlite] SQLITE3 in multi-thread server

[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 it
was developed as light-weight file-based multi-process database
engine. There are no row-level locks in SQLite because it's impossible
(or almost impossible) to implement them using only OS file-locking
mechanisms. And you won't find row-level locking API in VFS.
BTW, if you turn on shared cache on your server and set
read_uncommited to 1 then you'll make possible writing while readers
are active right away. Beware though that if you have open reading
cursor and trying to write over the same SQLite connection then your
transaction won't be committed until cursor is closed.


Pavel

On Tue, Mar 16, 2010 at 11:36 AM, HLS  wrote:
> 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
> sharing) and all records and btree pages are managed with software
> level reader/writer/exclusive locks.   SQLITE3 was suggested to low
> footprint sql engine to improve the FTS and offer more ad-hoc
> searching capabilities.  So I was thinking if I removed its locking
> logic, I can manage it the sharing with incoming threads.
>
> One goal to maintain the client side API traversal functions and
> record level I/O functions:
>
>    GetFileRecByRef()
>    SearchFileRec()
>    GetFirstFileRec()
>    GetNextFileRec()
>    GetPrevFileRec()
>    GetLastFileRec()
>    UpdateFileRec()
>    AddFileRec()
>    DeleteFileRec()
>
>
> What I seeing basically if a thread is currently in, I presume *read
> only* mode with a SELECT, any attempt before the cursor is released,
> to update a record, a sharing violation is returned.
>
> I can get it to work if the select request is completed (retrieve the
> records first), then update them.  But this request a change to
> existing client applets using the current client side RPC API.
>
> I'm new to SQLITE3 so obviously I have a lot to learn about its
> technical details.  But I was thinking if the cursor (sqlite3_stmt *),
> if I correct in viewing it as a *cursor*, then it would be past the
> its fetch and should be released from any update restriction.  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.
>
> Thanks
>
>
> On Tue, Mar 16, 2010 at 10:56 AM, Pavel Ivanov  wrote:
>> 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 (http://www.sqlite.org/c3ref/io_methods.html) which
>> contains functions xLock and xUnlock. So if you implement your own VFS
>> and make these functions as no-op then you'll get SQLite without any
>> kind of locking (you should implement xCheckReservedLock as no-op
>> too). And if your application is sure that nobody else accesses the
>> same database and you synchronize threads somehow (or use shared
>> cache) then it will work just fine (I've implemented this kind of
>> trick in my application to boost its performance).
>>
>>
>> Pavel
>>
>> On Tue, Mar 16, 2010 at 10:05 AM, HLS  wrote:
>>> Folks,
>>>
>>> I read the documentation suggesting to use another SQL engine for a
>>> multi-threaded client/server environment and I reached a point where I
>>> see why.   But I was thinking it could be used because currently, we
>>> have a ISAM/BTREE database under our full reader/writer and exclusive
>>> locking controls.  So my attempt was to basically plug and play
>>> SQLITE3.
>>>
>>> What I need is NO LOCKING so we can use our own thread context
>>> contention controls.
>>>
>>> I read the technical notes but I'm still getting either a error 5 or
>>> 8.  I'm not entirely sure what steps need to be done.   I tried:
>>>
>>>       sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
>>>
>>> Overall, the design is the RPC server opens exclusive the database
>>> file, and each thread context gets a query cursor.   Since the client
>>> API has no concept of a "close",  the cursor is saved in the server
>>> thread context and reused for NEXT/PREV client function,  so on the
>>> client side, a example code:
>>>
>>>     TFileRecord rec = {0};
>>>     DWORD tid = 0;
>>>      if (SearchFileRec(FileNameAreaKey, rec, tid) do {
>>>         ... do something with rec ...
>>>      

Re: [sqlite] SQLITE3 in multi-thread server

[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, first the question,
why would you want to change your current system?

If you need only something like a "fast entry" into
some of your DataFile-Chunks (per FileNameAreaKey)
and then enumerate (record-)content in these
Data-Chunks, respecting some "sort-order" - why choose
a system which is based on SQL-query-processing?

There are other engines, which fit better for such
scenarios - berkeley-db comes to mind, which in
its latest incarnations should be usable threadsafe too
(not sure about concurrency and locking in that engine).
Or just "write your own thing", if the goal is only,
to achieve fast "ordered enumeration" of more or less
simple records-structs, hosted in files.

If SQL-based querying is (becoming) something which
would be "nice to have", then maybe consider other engines,
which work "better over sockets" (since this mode is
built-in) and have not that much "locking-restrictions"
as SQLite in concurrent scenarios (and also support
record-level-locking directly).

If it has to be SQLite, because it is nice, small, fast and
easy to deploy - then you should consider a completely
different approach with regards to your current client-
side locking-handling.

We also use an RPC-server, with the SQLite-engine
as the serverside backend - but we "completely isolate"
the serverside-cursors from the clientside - meaning, we
perform our SQL-query at the serverside - and serialize
(copy) all the records, according to the "set the SQL-string
describes" into a "transferrable container-object" (a Recordset,
or ResultSet) first.  This container-objects content is then
compressed and transferred over sockets to the clientside
(after all the sqlite-handles for the query in question were
freed).

This works fast and robust, no sqlite-handles are kept open
between requests - in fact this works faster than "serializing
and transferring each record separately over the sockets,
whilst keeping a bunch of serverside SQLite-cursors alive,
which can only be moved forward".

At the clientside, the transferred container (the Recordset)
is freely navigatable (back and forth) - supports its own
Find-, Sort- methods - contains all the Field-Type-
descriptions and so on...

If we need Record-Level-locking at the clientside, we
just update a (serverside) LockedRecords-Table with
the currently locked RecordIDs (+UserID - and some
extra-fields for some "timeout-handling") - and based on
queries against these "locking-tables", we can "colorize"
the currently locked Records appropriately in our GUI.

I understand, that you "don't want to rewrite, what you
currently have" - but (at least for an *SQLite*-engine in the
backend) you will have to IMO.

Nonetheless, such a "disconnected approach" is in the
meantime something like "common-practise" - given all
the Browser-based clients which work against
"RPC-Servers, or Application-Servers - or let's call them
*WebServers* ;-) ...over sockets (e.g. receiving JSON-
serialized Resultsets over http) - ... no matter what SQL-
engine is working in the backend of such a WebSite ...
"clientside-controlled Servercursors" are "a thing of the
past" IMO, since they "introduce more problems than
they solve".

Olaf Schmidt



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE3 in multi-thread server

[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
sharing) and all records and btree pages are managed with software
level reader/writer/exclusive locks.   SQLITE3 was suggested to low
footprint sql engine to improve the FTS and offer more ad-hoc
searching capabilities.  So I was thinking if I removed its locking
logic, I can manage it the sharing with incoming threads.

One goal to maintain the client side API traversal functions and
record level I/O functions:

GetFileRecByRef()
SearchFileRec()
GetFirstFileRec()
GetNextFileRec()
GetPrevFileRec()
GetLastFileRec()
UpdateFileRec()
AddFileRec()
DeleteFileRec()


What I seeing basically if a thread is currently in, I presume *read
only* mode with a SELECT, any attempt before the cursor is released,
to update a record, a sharing violation is returned.

I can get it to work if the select request is completed (retrieve the
records first), then update them.  But this request a change to
existing client applets using the current client side RPC API.

I'm new to SQLITE3 so obviously I have a lot to learn about its
technical details.  But I was thinking if the cursor (sqlite3_stmt *),
if I correct in viewing it as a *cursor*, then it would be past the
its fetch and should be released from any update restriction.  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.

Thanks


On Tue, Mar 16, 2010 at 10:56 AM, Pavel Ivanov  wrote:
> 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 (http://www.sqlite.org/c3ref/io_methods.html) which
> contains functions xLock and xUnlock. So if you implement your own VFS
> and make these functions as no-op then you'll get SQLite without any
> kind of locking (you should implement xCheckReservedLock as no-op
> too). And if your application is sure that nobody else accesses the
> same database and you synchronize threads somehow (or use shared
> cache) then it will work just fine (I've implemented this kind of
> trick in my application to boost its performance).
>
>
> Pavel
>
> On Tue, Mar 16, 2010 at 10:05 AM, HLS  wrote:
>> Folks,
>>
>> I read the documentation suggesting to use another SQL engine for a
>> multi-threaded client/server environment and I reached a point where I
>> see why.   But I was thinking it could be used because currently, we
>> have a ISAM/BTREE database under our full reader/writer and exclusive
>> locking controls.  So my attempt was to basically plug and play
>> SQLITE3.
>>
>> What I need is NO LOCKING so we can use our own thread context
>> contention controls.
>>
>> I read the technical notes but I'm still getting either a error 5 or
>> 8.  I'm not entirely sure what steps need to be done.   I tried:
>>
>>       sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
>>
>> Overall, the design is the RPC server opens exclusive the database
>> file, and each thread context gets a query cursor.   Since the client
>> API has no concept of a "close",  the cursor is saved in the server
>> thread context and reused for NEXT/PREV client function,  so on the
>> client side, a example code:
>>
>>     TFileRecord rec = {0};
>>     DWORD tid = 0;
>>      if (SearchFileRec(FileNameAreaKey, rec, tid) do {
>>         ... do something with rec ...
>>      } while (GetNextFilerec(FileNameAreaKey, rec, tid));
>>
>> Here, in the current system, the tid holds a pointer to the btree page
>> allowing for the traversal.   But for the SQLITE3 implementation,  the
>> search cursor is saved in the context and recalled in the Next call.
>>
>> So as you can imagine what happen now  if there any record updating:
>>
>>      if (SearchFileRec(FileNameAreaKey, rec, tid) do {
>>           rec. = change some fields
>>           UpdateFileRec(rec);
>>      } while (GetNextFilerec(FileNameAreaKey, rec, tid));
>>
>> I am now getting a sharing issue with the UpdateFileRec().
>>
>> I think before we punt on SQLITE3,  we can use it if we had full
>> control of the I/O access to the database.  No other process will be
>> allowed access to the database because I am going to open it in none
>> sharing more.  All access will be through the client API.
>>
>> So how do I make it so there is no locking?
>>
>> --
>> hls
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> 

Re: [sqlite] SQLITE3 in multi-thread server

[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 (http://www.sqlite.org/c3ref/io_methods.html) which
contains functions xLock and xUnlock. So if you implement your own VFS
and make these functions as no-op then you'll get SQLite without any
kind of locking (you should implement xCheckReservedLock as no-op
too). And if your application is sure that nobody else accesses the
same database and you synchronize threads somehow (or use shared
cache) then it will work just fine (I've implemented this kind of
trick in my application to boost its performance).


Pavel

On Tue, Mar 16, 2010 at 10:05 AM, HLS  wrote:
> Folks,
>
> I read the documentation suggesting to use another SQL engine for a
> multi-threaded client/server environment and I reached a point where I
> see why.   But I was thinking it could be used because currently, we
> have a ISAM/BTREE database under our full reader/writer and exclusive
> locking controls.  So my attempt was to basically plug and play
> SQLITE3.
>
> What I need is NO LOCKING so we can use our own thread context
> contention controls.
>
> I read the technical notes but I'm still getting either a error 5 or
> 8.  I'm not entirely sure what steps need to be done.   I tried:
>
>       sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
>
> Overall, the design is the RPC server opens exclusive the database
> file, and each thread context gets a query cursor.   Since the client
> API has no concept of a "close",  the cursor is saved in the server
> thread context and reused for NEXT/PREV client function,  so on the
> client side, a example code:
>
>     TFileRecord rec = {0};
>     DWORD tid = 0;
>      if (SearchFileRec(FileNameAreaKey, rec, tid) do {
>         ... do something with rec ...
>      } while (GetNextFilerec(FileNameAreaKey, rec, tid));
>
> Here, in the current system, the tid holds a pointer to the btree page
> allowing for the traversal.   But for the SQLITE3 implementation,  the
> search cursor is saved in the context and recalled in the Next call.
>
> So as you can imagine what happen now  if there any record updating:
>
>      if (SearchFileRec(FileNameAreaKey, rec, tid) do {
>           rec. = change some fields
>           UpdateFileRec(rec);
>      } while (GetNextFilerec(FileNameAreaKey, rec, tid));
>
> I am now getting a sharing issue with the UpdateFileRec().
>
> I think before we punt on SQLITE3,  we can use it if we had full
> control of the I/O access to the database.  No other process will be
> allowed access to the database because I am going to open it in none
> sharing more.  All access will be through the client API.
>
> So how do I make it so there is no locking?
>
> --
> hls
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE3 in multi-thread server

[HLS]

Folks,

I read the documentation suggesting to use another SQL engine for a
multi-threaded client/server environment and I reached a point where I
see why.   But I was thinking it could be used because currently, we
have a ISAM/BTREE database under our full reader/writer and exclusive
locking controls.  So my attempt was to basically plug and play
SQLITE3.

What I need is NO LOCKING so we can use our own thread context
contention controls.

I read the technical notes but I'm still getting either a error 5 or
8.  I'm not entirely sure what steps need to be done.   I tried:

      sqlite3_config(SQLITE_CONFIG_MULTITHREAD);

Overall, the design is the RPC server opens exclusive the database
file, and each thread context gets a query cursor.   Since the client
API has no concept of a "close",  the cursor is saved in the server
thread context and reused for NEXT/PREV client function,  so on the
client side, a example code:

 TFileRecord rec = {0};
 DWORD tid = 0;
     if (SearchFileRec(FileNameAreaKey, rec, tid) do {
 ... do something with rec ...
     } while (GetNextFilerec(FileNameAreaKey, rec, tid));

Here, in the current system, the tid holds a pointer to the btree page
allowing for the traversal.   But for the SQLITE3 implementation,  the
search cursor is saved in the context and recalled in the Next call.

So as you can imagine what happen now  if there any record updating:

     if (SearchFileRec(FileNameAreaKey, rec, tid) do {
   rec. = change some fields
   UpdateFileRec(rec);
     } while (GetNextFilerec(FileNameAreaKey, rec, tid));

I am now getting a sharing issue with the UpdateFileRec().

I think before we punt on SQLITE3,  we can use it if we had full
control of the I/O access to the database.  No other process will be
allowed access to the database because I am going to open it in none
sharing more.  All access will be through the client API.

So how do I make it so there is no locking?

--
hls
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users