> 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
