Multi-User confusion

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Multi-User confusion

jgodfrey
Hi All,

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.

Thanks for any details you can provide.

Jeff

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

developir@yahoo.com
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 <[hidden email]> 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

RB Smissaert
In reply to this post by jgodfrey
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


> Hi All,
>
> 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.
>
> Thanks for any details you can provide.
>
> Jeff
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
> -----------------------------------------------------------------------------
>
>
>




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

jgodfrey
In reply to this post by developir@yahoo.com
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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

jgodfrey
In reply to this post by RB Smissaert
[hidden email] 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

developir@yahoo.com
In reply to this post by jgodfrey
--- Jeff Godfrey <[hidden email]> 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&v=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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

jgodfrey


Joe Wilson wrote:
> --- Jeff Godfrey <[hidden email]> 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&v=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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

developir@yahoo.com
--- Jeff Godfrey <[hidden email]> 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Zbigniew Baniewski
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@.../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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Trevor Talbot-2
On 9/16/07, Zbigniew Baniewski <[hidden email]> 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@.../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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Zbigniew Baniewski
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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

developir@yahoo.com
--- Zbigniew Baniewski <[hidden email]> 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Zbigniew Baniewski
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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

D. Richard Hipp
Zbigniew Baniewski <[hidden email]> 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 <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Zbigniew Baniewski
On Mon, Sep 17, 2007 at 02:29:58PM +0000, [hidden email] 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Zbigniew Baniewski
In reply to this post by D. Richard Hipp
On Mon, Sep 17, 2007 at 02:29:58PM +0000, [hidden email] wrote:

> > 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

Either I'm doing something incorrectly - or there's something wrong with
that(?).

I made a following test:

1. Ran tclsh in the xterm console, typing the commands:

        sqlite3 dbcomm "/home/myacc/databasefile"
        dbcomm eval {CREATE TABLE version( number VARCHAR(10) )}
        dbcomm eval {INSERT INTO version VALUES('V1.1')}
        dbcomm eval {PRAGMA locking_mode = EXCLUSIVE} ;# lock mode
        dbcomm eval {SELECT number FROM version} ;# read access
        dbcomm eval {UPDATE version SET number='V1.2'} ;# write access

2. Then I ran second tclsh, in another xterm window:

        sqlite3 dbcomm "/home/myacc/databasefile"
        dbcomm eval {SELECT * from version}
        V1.2
        ^^^^
No problem with access! Although the first connection set EXCLUSIVE - the
second one accessed the database without any problem. Then I went out of
tclsh, made su to "root" (to be different user), and tried to connect with
"exclusively locked" database again. Still no problems with connection, as
if there wasn't any lock(?).

Debian Etch, kernel 2.6.22, SQLite 3.3.8.

What exactly did I wrong?
--
                                pozdrawiam / regards

                                                Zbigniew Baniewski

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: Multi-User confusion

Zbigniew Baniewski
Yes, found it: no support for that in 3.3.8. :( Must compile newer version.
--
                                pozdrawiam / regards

                                                Zbigniew Baniewski

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------