Safe to use SQLite over a sketchy network?

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Safe to use SQLite over a sketchy network?

Randall Smith

I have an application where remote users will be connecting to a SQLite DB over a network connection that seems to be somewhat sketchy (I can't characterize it well; I'm hearing this second-hand).

My question is: Do the commit-or-rollback semantics of SQLite transactions work if the connection to the file system is weird?  For example, do I get a rollback if the connection is lost mid-transaction or something?  Or, is the underlying assumption with transactions that the connection between SQLite code and file system is 100% reliable?

I realize this is a bit vague, but if anyone has any experience or insight I would appreciate it.

Randall.



Randall Smith
BB-106G (office)
BB-172D (lab)
858-775-5017 (cell)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Jens Alfke-2


> On Sep 24, 2019, at 2:14 PM, Randall Smith <[hidden email]> wrote:
>
> My question is: Do the commit-or-rollback semantics of SQLite transactions work if the connection to the file system is weird?

What filesystem is it — SMB, NFS, AFP, …?

I'm not an expert, but in general I think the database file should be OK if the connection is dropped, as long as the filesystem has some kind of 'fsync' equivalent. The bigger problem is file locking:

> file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.
>
> A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
(from https://www.sqlite.org/whentouse.html <https://www.sqlite.org/whentouse.html> )

From what I've heard, this is especially problematic with NFS.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Simon Slavin-3
In reply to this post by Randall Smith
On 24 Sep 2019, at 10:14pm, Randall Smith <[hidden email]> wrote:

> I have an application where remote users will be connecting to a SQLite DB over a network connection that seems to be somewhat sketchy (I can't characterize it well; I'm hearing this second-hand).
>
> My question is: Do the commit-or-rollback semantics of SQLite transactions work if the connection to the file system is weird?  For example, do I get a rollback if the connection is lost mid-transaction or something?  Or, is the underlying assumption with transactions that the connection between SQLite code and file system is 100% reliable?

I can't answer your question as stated, but here are a few points which may get you further to a conclusion.

SQLite does not support network connections.  It has no networking code.  You can't talk to a SQLite database over a network connection.  What you can do is write your own frontend and backend software.  In which case, the network connection is in your software, not SQLite.

SQLite is written to talk to a database stored in local storage to the processor it's running on.  In other words, it talks local file system, not network file system.  If your SQLite code is running on computer X, then the database should be stored in a filesystem mounted /locally/ on computer X, not across a network.  So USB connections are okay, a RAID array mounted over FireWire/Thunderbolt is okay, but anything involving IP addresses is not okay.

It might actually work.  You might luck into a network file system that correctly supports locking.  But the SQLite developers cannot recommend any.  This falls under section 2.1 of

<https://www.sqlite.org/howtocorrupt.html>

which you might find interesting.

In terms of transactions and rollback, SQLite is bulletproof in recovery from hardware disconnection (unplugging a local drive) or powerloss of the computer it's running on.  When SQLite reopens the database it figures out that a transaction was not completed and rolls it all back [1].  However, as with normal operation described above, this can be relied on only if the connection correctly supports locking.

[1] Of course this works only if the database drive has not suffered file system corruption.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Jose Isaias Cabrera-4
In reply to this post by Randall Smith

Randall Smith, on Tuesday, September 24, 2019 05:14 PM, wrote...​

> ​
> I have an application where remote users will be connecting to a SQLite DB over a network​
> connection that seems to be somewhat sketchy (I can't characterize it well; I'm hearing​
> this second-hand).​
> ​
> My question is: Do the commit-or-rollback semantics of SQLite transactions work if the​
> connection to the file system is weird?  For example, do I get a rollback if the​
> connection is lost mid-transaction or something?  Or, is the underlying assumption with​
> transactions that the connection between SQLite code and file system is 100% reliable?​
> ​
> I realize this is a bit vague, but if anyone has any experience or insight I would appreciate it.​

Even on a great network, you can have problems, so when you say "sketchy", then definitely there will be problems. You must make sure that only one writer is writing to the DB, though many can read, only one writer.  If you do a search for sqlite network usage, you may get some good advice.  Just thinking out-loud.  Thanks.​

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Jens Alfke-2


> On Sep 24, 2019, at 3:22 PM, Jose Isaias Cabrera <[hidden email]> wrote:
>
> Even on a great network, you can have problems, so when you say "sketchy", then definitely there will be problems.

And even with a perfect network and perfect networked filesystem, it's still possible to get denial-of-service behavior where one client begins a transaction, takes out a lock on the file, and then for one reason or another never ends the transaction. Maybe the client hangs, or drops into a debugger, or is waiting for user input in the middle of a transaction, or the host it's on loses its network connection and it takes a long time for the server to time out its connection and clean up after it.

Either way, you get a lengthy period where no other client can write to the database — it'll lock up, or fail with SQLITE_BUSY errors, or whatever.

There will be times when a program using SQLite finds itself running over a networked filesystem, but no one should deliberately write SQLite-based code intending to use a networked filesystem. For that you want a client/server database.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Jen Pollock
On Tue, Sep 24, 2019 at 03:30:00PM -0700, Jens Alfke wrote:
> There will be times when a program using SQLite finds itself running
> over a networked filesystem, but no one should deliberately write
> SQLite-based code intending to use a networked filesystem. For that
> you want a client/server database.

SQLite on a networked filesystem works basically fine if the SQLite
database will only ever be opened read-only. But that's admittedly an
edge case.

Jen Pollock

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Keith Medcalf
In reply to this post by Jens Alfke-2

On Tuesday, 24 September, 2019 16:30, Jens Alfke <[hidden email]> wrote:

>There will be times when a program using SQLite finds itself running over
>a networked filesystem, but no one should deliberately write SQLite-based
>code intending to use a networked filesystem. For that you want a
>client/server database.

There are not, to my knowledge, any client/server database systems that will work properly if the database resides on a network filesystem (meaning remote multi-access).  The "client" is remote from the "server" because the "client" and "server" use some sort of IPC mechanism (of which a network is an example) so that the "client" can send commands to and receive responses from the "server".  The database files must still reside on the "server"'s local filesystem.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Rowan Worth-2
In reply to this post by Randall Smith
On Wed, 25 Sep 2019 at 05:14, Randall Smith <[hidden email]> wrote:

> I have an application where remote users will be connecting to a SQLite DB
> over a network connection that seems to be somewhat sketchy (I can't
> characterize it well; I'm hearing this second-hand).
>
> My question is: Do the commit-or-rollback semantics of SQLite transactions
> work if the connection to the file system is weird?  For example, do I get
> a rollback if the connection is lost mid-transaction or something?


SQLite's transaction protocol provides durability in the face of a crash or
severed connection - a partially committed transaction will be rolled back
as you suspected. Whether that comes into play also depends on the
behaviour on the filesystem though; eg. following a "lost connection" some
file servers will still consider the file locked by the disconnected client
until they hear back from said client or a timeout expires. I've seen some
configurations where this seems to be retained indefinitely, locking all
clients out of the DB until the server is restarted.

Also SQLite relies on synchronous i/o operations to provide consistency and
durability, and if the filesystem skimps on these (to try and provide
better performance or whatever) you will likely end up with a corrupt DB.
These synchronous ops tend to become the limiting factor in DB performance
- a write transaction in this environment is quite expensive and you won't
get anywhere close to the write throughput that you would from a
traditional RDMS.

  Or, is the underlying assumption with transactions that the connection
> between SQLite code and file system is 100% reliable?
>

As long as the filesystem provides a consistent view to each client of
events surrounding locks and cache invalidation despite the sketchy network
then you ought to get consistent data coming from sqlite. As others have
said sqlite is very much at the mercy of the filesystem's locking
semantics, and if that is not robust in the face of a sketchy network then
it probably will not be a usable solution.

Source: hundreds of production sqlite DBs on NFS over many years. We have
had a few instances of DB corruption as well as some phantom lock scenarios
so you do have to plan for those, but by and large things work well. That
said we have a reliable network layer, and the write-concurrency caveat is
a real concern.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Simon Slavin-3
When I first learned the SQLite had problems with Network File Systems I read a ton of stuff to learn why there doesn't seem to be a Network File Systems that implements locking properly.  I ended up with …

A) It slows access a lot.  Even with clever hashing to check for collisions it takes time to figure out whether your range is already locked.

B) Different use-cases have different preferences for retry-and-timeout times.  It's one more thing for admins to configure and many admins don't get it right.

C) It's hard to debug.  There are numerous different orders in which different clients can lock and unlock ranges.  You have to run a random simulator to try them all.  The logic to deal with them properly is not as simple as you'd think.  Consider, for example, ranges which are not identical but do overlap.

D) It's mostly a waste of time.  Most client software doesn't care how to deal with a BUSY status and either crashes – which annoys the admin and user – or retries immediately – which makes the management CPU hot.  After all, most client software just wants to read a whole file or write a whole file.  And if two people save the same word processing document at nearly the same time, who's to say who was first ?

Still, I wonder why someone working on a Linux network file system, or APFS, or ZFS, hasn't done it.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Rowan Worth-2
On Wed, 25 Sep 2019 at 12:58, Simon Slavin <[hidden email]> wrote:

> When I first learned the SQLite had problems with Network File Systems I
> read a ton of stuff to learn why there doesn't seem to be a Network File
> Systems that implements locking properly. <snip>
>
> Still, I wonder why someone working on a Linux network file system, or
> APFS, or ZFS, hasn't done it.
>

I'm not sure what your definition of "locking properly" is or when your
research was done, but POSIX advisory locks¹ work just fine on linux over
nfs (since at least v3) and lustre.

¹ That's the F_SETLK/F_GETLK/F_SETLKW commands via the fcntl() syscall,
which is also sqlite's default locking mechanism under UNIX.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Gary R. Schmidt
On 25/09/2019 15:36, Rowan Worth wrote:

> On Wed, 25 Sep 2019 at 12:58, Simon Slavin <[hidden email]> wrote:
>
>> When I first learned the SQLite had problems with Network File Systems I
>> read a ton of stuff to learn why there doesn't seem to be a Network File
>> Systems that implements locking properly. <snip>
>>
>> Still, I wonder why someone working on a Linux network file system, or
>> APFS, or ZFS, hasn't done it.
>>
>
> I'm not sure what your definition of "locking properly" is or when your
> research was done, but POSIX advisory locks¹ work just fine on linux over
> nfs (since at least v3) and lustre.
>
> ¹ That's the F_SETLK/F_GETLK/F_SETLKW commands via the fcntl() syscall,
> which is also sqlite's default locking mechanism under UNIX.
>
I don't see it as that much of a problem, I've been locking
database-type files over NFS/RFS/DECNET since the 1980s, and SMB since
the 1990s.

Now, there have been a *lot* of crappy implementations of NFS out there,
probably the crappiest currently in use is the Linux version, but it is
better than it used to be (I wonder if sharing a file system still
causes the entire NFS server to re-start), and let's not mention the
reasoning behind, "Why should we drop back to NFSv3 if the NFSv4
initiation fails?"

Although I have had to convince a few people of the right /way/ to take
out a lock...

        Cheers,
                Gary B-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Jens Alfke-2
In reply to this post by Keith Medcalf

> On Sep 24, 2019, at 3:48 PM, Keith Medcalf <[hidden email]> wrote:
>
> There are not, to my knowledge, any client/server database systems that will work properly if the database resides on a network filesystem (meaning remote multi-access).  The "client" is remote from the "server" because the "client" and "server" use some sort of IPC mechanism (of which a network is an example) so that the "client" can send commands to and receive responses from the "server".

Well, obviously. “Client/server” means databases like MySQL or Oracle. No one would run those with the server using a networked file system.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Gary R. Schmidt
On 26/09/2019 15:00, Jens Alfke wrote:
>
>> On Sep 24, 2019, at 3:48 PM, Keith Medcalf <[hidden email]> wrote:
>>
>> There are not, to my knowledge, any client/server database systems that will work properly if the database resides on a network filesystem (meaning remote multi-access).  The "client" is remote from the "server" because the "client" and "server" use some sort of IPC mechanism (of which a network is an example) so that the "client" can send commands to and receive responses from the "server".
>
> Well, obviously. “Client/server” means databases like MySQL or Oracle. No one would run those with the server using a networked file system.
>
I might have dreamt it, but NetAPP had an add-on for Oracle, quite
probably still do, that enhanced performance and behaviour when used for
storage.

Not sure if it was applied to the server or the NAS, possibly both, but
it was an Oracle-recommended solution for large storage requirements.

        Cheers,
                Gary B-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Keith Medcalf

That is remote block storage, not a remote filesystem.  Remote block storage just has a "longer wire" going to the block storage.  iSCSI, for example, will let you put your block storage anywhere, even in orbit.  The filesystem, however, still resides on the local computer.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Gary R. Schmidt
>Sent: Wednesday, 25 September, 2019 23:13
>To: [hidden email]
>Subject: Re: [sqlite] Safe to use SQLite over a sketchy network?
>
>On 26/09/2019 15:00, Jens Alfke wrote:
>>
>>> On Sep 24, 2019, at 3:48 PM, Keith Medcalf <[hidden email]>
>wrote:
>>>
>>> There are not, to my knowledge, any client/server database systems
>that will work properly if the database resides on a network filesystem
>(meaning remote multi-access).  The "client" is remote from the "server"
>because the "client" and "server" use some sort of IPC mechanism (of
>which a network is an example) so that the "client" can send commands to
>and receive responses from the "server".
>>
>> Well, obviously. “Client/server” means databases like MySQL or Oracle.
>No one would run those with the server using a networked file system.
>>
>I might have dreamt it, but NetAPP had an add-on for Oracle, quite
>probably still do, that enhanced performance and behaviour when used for
>storage.
>
>Not sure if it was applied to the server or the NAS, possibly both, but
>it was an Oracle-recommended solution for large storage requirements.
>
> Cheers,
> Gary B-)
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Gary R. Schmidt
On 26/09/2019 15:30, Keith Medcalf wrote:

>> -----Original Message-----
>> From: sqlite-users <[hidden email]> On
>> Behalf Of Gary R. Schmidt
>> Sent: Wednesday, 25 September, 2019 23:13
>> To: [hidden email]
>> Subject: Re: [sqlite] Safe to use SQLite over a sketchy network?
>>
>> On 26/09/2019 15:00, Jens Alfke wrote:
>>>
>>>> On Sep 24, 2019, at 3:48 PM, Keith Medcalf <[hidden email]>
>> wrote:
>>>>
>>>> There are not, to my knowledge, any client/server database systems
>> that will work properly if the database resides on a network filesystem
>> (meaning remote multi-access).  The "client" is remote from the "server"
>> because the "client" and "server" use some sort of IPC mechanism (of
>> which a network is an example) so that the "client" can send commands to
>> and receive responses from the "server".
>>>
>>> Well, obviously. “Client/server” means databases like MySQL or Oracle.
>> No one would run those with the server using a networked file system.
>>>
>> I might have dreamt it, but NetAPP had an add-on for Oracle, quite
>> probably still do, that enhanced performance and behaviour when used for
>> storage.
>>
>> Not sure if it was applied to the server or the NAS, possibly both, but
>> it was an Oracle-recommended solution for large storage requirements.
>>
[Top-posting fixed.]
 >
 > That is remote block storage, not a remote filesystem.  Remote block
 > storage just has a "longer wire" going to the block storage.  iSCSI,
 > for example, will let you put your block storage anywhere, even in
 > orbit.  The filesystem, however, still resides on the local computer.
 >
No, it was over NFS, and while it could be used to give Oracle a "raw
partition," it was intended to be used as a file-system that could be
managed by the NAS, and allowed for the files to accessed by more than
one Oracle server instance.

This was before we got Oracle RAC, I should mention.

        Cheers,
                Gary B-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe to use SQLite over a sketchy network?

Rowan Worth-2
In reply to this post by Jens Alfke-2
On Thu, 26 Sep 2019 at 13:01, Jens Alfke <[hidden email]> wrote:

>
> > On Sep 24, 2019, at 3:48 PM, Keith Medcalf <[hidden email]> wrote:
> >
> > There are not, to my knowledge, any client/server database systems that
> will work properly if the database resides on a network filesystem (meaning
> remote multi-access).  The "client" is remote from the "server" because the
> "client" and "server" use some sort of IPC mechanism (of which a network is
> an example) so that the "client" can send commands to and receive responses
> from the "server".
>
> Well, obviously. “Client/server” means databases like MySQL or Oracle. No
> one would run those with the server using a networked file system.
>

Haha, you'd be surprised!

https://blogs.msdn.microsoft.com/varund/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive/

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users