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 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.XXXX = 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 >>> >> >> >> >> -- >> 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
