Re: [sqlite] SQLITE3 in multi-thread server
On Tue, Mar 16, 2010 at 4:44 PM, Simon Slavin <slav...@bigfraud.org> 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
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 <paiva...@gmail.com> 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 <sant9...@gmail.com> 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 <slav...@bigfraud.org> 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
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 <slav...@bigfraud.org> 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
On Tue, Mar 16, 2010 at 1:01 PM, HLS <sant9...@gmail.com> 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
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 <paiva...@gmail.com> 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 <sant9...@gmail.com> 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 <paiva...@gmail.com> 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 <sant9...@gmail.com> 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 rea
Re: [sqlite] SQLITE3 in multi-thread server
On Tue, Mar 16, 2010 at 11:54 AM, Olaf Schmidt <s...@online.de> wrote:
>
> "HLS" <sant9...@gmail.com> 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
Re: [sqlite] SQLITE3 in multi-thread server
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 <paiva...@gmail.com> 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 <sant9...@gmail.com> 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
[sqlite] SQLITE3 in multi-thread server
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
