Re: [sqlite] Multi-User confusion
Yes, found it: no support for that in 3.3.8. :( Must compile newer version. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
On Mon, Sep 17, 2007 at 02:29:58PM +, [EMAIL PROTECTED] wrote: > http://www.sqlite.org/pragma.html#pragma_locking_mode Oh, boy... missed entire set of "pragma" commands. Thanks. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Sun, Sep 16, 2007 at 08:42:44PM -0700, Joe Wilson wrote: > > > Host a shared database file on computer A, say shared.db. > > >From computer B, open shared.db remotely and execute "BEGIN EXCLUSIVE;". > > >From computer C, open shared.db remotely and execute "BEGIN EXCLUSIVE;". > > If computer C has the error "SQL error: database is locked", then > > its locking probably works. > > BTW: I'm wondering, if there's a possibility to set in similar manner > exclusive rights to access the database file for the duration of the entire > "database session", not just transaction. I mean: when I'm using a program > which is accessing the database - nobody else has access. http://www.sqlite.org/pragma.html#pragma_locking_mode -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
On Sun, Sep 16, 2007 at 08:42:44PM -0700, Joe Wilson wrote: > Host a shared database file on computer A, say shared.db. > >From computer B, open shared.db remotely and execute "BEGIN EXCLUSIVE;". > >From computer C, open shared.db remotely and execute "BEGIN EXCLUSIVE;". > If computer C has the error "SQL error: database is locked", then > its locking probably works. BTW: I'm wondering, if there's a possibility to set in similar manner exclusive rights to access the database file for the duration of the entire "database session", not just transaction. I mean: when I'm using a program which is accessing the database - nobody else has access. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
--- Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > On Sun, Sep 16, 2007 at 05:47:07PM -0700, Trevor Talbot wrote: > > > The default locking mechanism relies on the underlying filesystem to > > provide the needed locking guarantees. In this case, the OP is > > needing to access a database on a networked filesystem, and many > > networked filesystems are unable to provide proper locking. So no, if > > the underlying filesystem is "broken", the database is not protected. > > And what you mean about sharing SQLite's database file - among WinXP-driven > computers - in "network neighborhood"? Does Window's filesystem assure > enough protection? You can do a crude check to verify it with the sqlite3 commandline shell and 3 networked computers: A, B, C. (any OS). Host a shared database file on computer A, say shared.db. >From computer B, open shared.db remotely and execute "BEGIN EXCLUSIVE;". >From computer C, open shared.db remotely and execute "BEGIN EXCLUSIVE;". If computer C has the error "SQL error: database is locked", then its locking probably works. Repeat the test with just 2 computers to test local locking versus remote locking. Don't let your dream ride pass you by. Make it a reality with Yahoo! Autos. http://autos.yahoo.com/index.html - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
On Sun, Sep 16, 2007 at 05:47:07PM -0700, Trevor Talbot wrote: > The default locking mechanism relies on the underlying filesystem to > provide the needed locking guarantees. In this case, the OP is > needing to access a database on a networked filesystem, and many > networked filesystems are unable to provide proper locking. So no, if > the underlying filesystem is "broken", the database is not protected. And what you mean about sharing SQLite's database file - among WinXP-driven computers - in "network neighborhood"? Does Window's filesystem assure enough protection? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
On 9/16/07, Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > I've found a remark regarding write concurrency in SQLite: > > "All SQLite write operations obtain an exclusive lock on the whole database" > ( http://www.mail-archive.com/sqlite-users@sqlite.org/msg18342.html ) > > Doesn't it mean, that database is protected enough already by it's internal > design, and there's no need to take care about that "dot locks"? The default locking mechanism relies on the underlying filesystem to provide the needed locking guarantees. In this case, the OP is needing to access a database on a networked filesystem, and many networked filesystems are unable to provide proper locking. So no, if the underlying filesystem is "broken", the database is not protected. Dot files replace the locking mechanism with a convention that skips the filesystem, but the consquence is that if anything else accesses the database file without respecting that convention, corruption may occur. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
On Thu, Sep 06, 2007 at 10:33:56AM -0700, Joe Wilson wrote: > Without some code modification, I doubt it. > I don't see any mention of "dot" in os_win.c. > > However, in SQLite 3.5 you can define your own OS Interface File > Virtual Methods Object and create your own file lock/unlock routines > based on the dot locks in os_unix.c: > > http://sqlite.org/capi3ref.html#sqlite3_io_methods I've found a remark regarding write concurrency in SQLite: "All SQLite write operations obtain an exclusive lock on the whole database" ( http://www.mail-archive.com/sqlite-users@sqlite.org/msg18342.html ) Doesn't it mean, that database is protected enough already by it's internal design, and there's no need to take care about that "dot locks"? -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
--- Jeff Godfrey <[EMAIL PROTECTED]> wrote: > Thanks for the pointer. I should have mentioned, my application is > running under Windows (Win2000 and WinXP). A quick look at the > mentioned code makes me believe that it targets Unix-only systems > (though there are a few brief mentions of Windows, such as "The > algorithms are complicated slightly to be compatible with Windows..."). > > Do you know if the mentioned dotLockLockingStyle is compatible with a > Windows environment? Without some code modification, I doubt it. I don't see any mention of "dot" in os_win.c. However, in SQLite 3.5 you can define your own OS Interface File Virtual Methods Object and create your own file lock/unlock routines based on the dot locks in os_unix.c: http://sqlite.org/capi3ref.html#sqlite3_io_methods Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase. http://farechase.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
Joe Wilson wrote: --- Jeff Godfrey <[EMAIL PROTECTED]> wrote: Can you (or anyone else) point me to some web-based information? http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/os_unix.c=1.165 Joe, Thanks for the pointer. I should have mentioned, my application is running under Windows (Win2000 and WinXP). A quick look at the mentioned code makes me believe that it targets Unix-only systems (though there are a few brief mentions of Windows, such as "The algorithms are complicated slightly to be compatible with Windows..."). Do you know if the mentioned dotLockLockingStyle is compatible with a Windows environment? Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
--- Jeff Godfrey <[EMAIL PROTECTED]> wrote: > Joe Wilson wrote: > > If your database storage device cannot guarantee an exclusive file > > lock, then any database write can potentially result in corruption. > > > > If you control all SQLite clients' code, you could recompile sqlite > > to use the file-based dotlockLockingStyle convention via > > > > -DSQLITE_ENABLE_LOCKING_STYLE=1 > > > > But even if a single client does not use that locking convention, > > you still risk corruption. > > > Joe, > > Thanks for the input. I absolutely control all of the SQLite clients - > they are just unique instances of my (Tcl-based) application. I am > unaware of the mentioned "dotlockLockingStyle" convention. Can you (or > anyone else) point me to some web-based information? Use the Source, Luke: http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/os_unix.c=1.165 Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
[EMAIL PROTECTED] wrote: You may have a look at the dhRCPServer at: http://www.thecommon.net/2.html I am not using it, but it sounds it may do the job. RBS Bart, Thanks for the pointer. It does sound quite interesting, though I don't know if it can (easily?) be used from within a Tcl-based application. I'll have to do some research... Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
Joe Wilson wrote: If your database storage device cannot guarantee an exclusive file lock, then any database write can potentially result in corruption. If you control all SQLite clients' code, you could recompile sqlite to use the file-based dotlockLockingStyle convention via -DSQLITE_ENABLE_LOCKING_STYLE=1 But even if a single client does not use that locking convention, you still risk corruption. Joe, Thanks for the input. I absolutely control all of the SQLite clients - they are just unique instances of my (Tcl-based) application. I am unaware of the mentioned "dotlockLockingStyle" convention. Can you (or anyone else) point me to some web-based information? Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
If your database storage device cannot guarantee an exclusive file lock, then any database write can potentially result in corruption. If you control all SQLite clients' code, you could recompile sqlite to use the file-based dotlockLockingStyle convention via -DSQLITE_ENABLE_LOCKING_STYLE=1 But even if a single client does not use that locking convention, you still risk corruption. --- Jeff Godfrey <[EMAIL PROTECTED]> wrote: > I currently have a single-user SQLite-based application that, due to > customer need, is being pushed toward multi-user access. I've done some > research on the multi-user capabilities of SQLite. It seems the general > consensus is that when the database file is stored on a network drive > (as is my case), the integrity of the stored data becomes questionable > (apparently due to bugs in the various NFS file locking protocols). > > Fortunately, my application is designed such that (generally speaking) > each User of the system will be working within their own SQLite > database. However, there are a few select places in the code where a > User could trigger an action that would cause the storage of data to a > common, upper-level SQLite database. > > I think I can change portions of the application to ensure that these > common writes never happen concurrently, but I'd like to understand the > underlying situations and dangers that can occur in this environment. > So, what are the cases that could cause database corruption? > > 1. Multiple Users writing to the same table of the same open database at > the same time? > 2. Multiple Users writing to two different tables of the same open > database at the same time? > 3. Multiple Users writing to the same table of the same open database at > different times? > 4. Other cases I haven't thought about? > > I realize there also some dangers with regard to potentially writing > "stale" data to the database thus losing someone else's updates. I > still have some thinking to do in that regard, but for now I'd like to > understand the situations that could compromise the integrity of the > underlying database file itself. Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
