Re: [sqlite] Locking errors on network

[Eduardo Morras]

On Mon, 10 Nov 2014 20:33:04 +0200
RSmith  wrote:

> 
> On 2014/11/10 20:22, Mike McWhinney wrote:
> > So SQLite shouldn't be used at all on a network?  Aren't there any
> > other provisions to handled the locking errors if/when they occur?
> 
> It is not about SQLite, it is about the Networking systems lying
> about whether a file is locked or not. No RDBMS can trust the
> network, but the client-server types do not care since they control
> the locking and do not depend on the OS / file status. SQLite however
> depends on it and as such cannot accurately (or timeously I should
> say) verify such status via a Network. On a local drive this is never
> a problem.
> 
> If you need Networking or User-control, please use a client-server
> type database.
> 
> There is one Client-Server implementation of SQLite (SQLightening I
> think) but it is neither free nor easy to convert to. You can write
> your own server too, but the best bet is using MySQL or PostGres in
> these cases.

You can create your own sqlite server (I did and use it, with nanomsg for 
client-server communication), it's medium-hard and for tiny hardware, near 
embedded, works.

A good file to start with, as I did, is in Sqlite repository, check 
http://www.sqlite.org/src/artifact/a2615049954cbb9cfb4a62e18e2f0616e4dc38fe 
a.k.a. src/test_server.c

But, as others aim and hit, you should use a real C/S RDBMS, my preference, 
PostgreSQL server.

HTH

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Scott Robison]

On Mon, Nov 10, 2014 at 12:56 PM,  wrote:

> On 2014-11-10 18:33, RSmith wrote:
> 
>
>> There is one Client-Server implementation of SQLite (SQLightening I
>> think) but it is neither free nor easy to convert to.
>>
>
> Doing some Googling, this looks like the thing:
>
>   http://sqlitening.com
>
> They don't seem to sell it any more (last version was released Dec
> 2012), though the support forums are still online.
>
>
>  You can write
>> your own server too, but the best bet is using MySQL or PostGres in
>> these cases.
>>
>
> Use PostgreSQL (www.postgresql.org). :)
>
> This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
> Richard Hipp:
>
>   SQLite: Protégé of PostgreSQL
>   https://www.youtube.com/watch?v=ZvmMzI0X7fE


Sorry for the previous noise. Clicked send instead of expanding the quotes.

Anyway, I've given a little thought in the past to creating a VFS for
SQLite that actually communicates with a "server". That server would be a
replacement for the buggy network file system implementations that plague
SQLite for networked use. Of course, it would no longer be a zero
configuration completely embedded system, but it would be a fairly minimal
shim.

Of course, it would itself require debugging, so it's not like it would be
a magical solution. Still, if the SQLite "service" just exposed a virtual
block device with accurate locking, I could see it being a useful
complement.

-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Tim Streater]

On 10 Nov 2014 at 19:38, Mike McWhinney  wrote: 

> I am using Sqlite.NET client in C#. How would I go about defining a sqlite
> busy timeout handler?

Is the API not documented somewhere (I don't know what Sqlite.NET client or C# 
are, so can't help)? Surely you must have some doc or how else do you use the 
API at the moment?


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Scott Robison]

On Mon, Nov 10, 2014 at 12:56 PM,  wrote:

> On 2014-11-10 18:33, RSmith wrote:
> 
>
>> There is one Client-Server implementation of SQLite (SQLightening I
>> think) but it is neither free nor easy to convert to.
>>
>
> Doing some Googling, this looks like the thing:
>
>   http://sqlitening.com
>
> They don't seem to sell it any more (last version was released Dec
> 2012), though the support forums are still online.
>
>
>  You can write
>> your own server too, but the best bet is using MySQL or PostGres in
>> these cases.
>>
>
> Use PostgreSQL (www.postgresql.org). :)
>
> This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
> Richard Hipp:
>
>   SQLite: Protégé of PostgreSQL
>   https://www.youtube.com/watch?v=ZvmMzI0X7fE
>
> Just saying. ;)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[justin]

On 2014-11-10 18:33, RSmith wrote:


There is one Client-Server implementation of SQLite (SQLightening I
think) but it is neither free nor easy to convert to.


Doing some Googling, this looks like the thing:

  http://sqlitening.com

They don't seem to sell it any more (last version was released Dec
2012), though the support forums are still online.



You can write
your own server too, but the best bet is using MySQL or PostGres in
these cases.


Use PostgreSQL (www.postgresql.org). :)

This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
Richard Hipp:

  SQLite: Protégé of PostgreSQL
  https://www.youtube.com/watch?v=ZvmMzI0X7fE

Just saying. ;)

Regards and best wishes,

Justin Clift

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Mike McWhinney]

I am using Sqlite.NET client in C#. How would I go about defining a sqlite busy 
timeout handler?  


Thanks
Mike



On Monday, November 10, 2014 1:35 PM, Tim Streater  
wrote:
 


On 10 Nov 2014 at 18:22, Mike McWhinney  wrote: 

> So SQLite shouldn't be used at all on a network?  Aren't there any other
> provisions to handled the locking errors if/when
> they occur?

You tried setting a timeout as pointed to here?

  https://www.sqlite.org/faq.html#q5

(not that this would avoid any potential corruption issue).


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Tim Streater]

On 10 Nov 2014 at 18:22, Mike McWhinney  wrote: 

> So SQLite shouldn't be used at all on a network?  Aren't there any other
> provisions to handled the locking errors if/when
> they occur?

You tried setting a timeout as pointed to here?

   https://www.sqlite.org/faq.html#q5

(not that this would avoid any potential corruption issue).


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 10:22 AM, Mike McWhinney wrote:
> So SQLite shouldn't be used at all on a network?  Aren't there any
> other provisions to handled the locking errors if/when they occur?

Network filesystems do not implement locking and other operations
*exactly* the same as for (most) local filesystems.  This is done due
to the protocols involved, race conditions, performance
considerations, latencies, and various other reasons.  You are seeing
the locking errors as a symptom of this.

If you use SQLite with a network then your data will eventually end up
corrupted.  Yes it is possible to sweep some stuff under the rug but
that does not mean corruption won't happen.  SQLite won't be able to
prevent it, and often may not detect it for a while.

This page describes how SQLite does locking as well as pointing to
some newer alternatives:

  https://www.sqlite.org/lockingv3.html

See also:

  https://www.sqlite.org/whentouse.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRhBm8ACgkQmOOfHg372QTcLgCfblMaFauIRgE83WOcF9z2M6BV
BMYAnRSP1KwC+69vb5fUMsGeGbdImHU1
=1mbq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Simon Slavin]

On 10 Nov 2014, at 6:22pm, Mike McWhinney  wrote:

> So SQLite shouldn't be used at all on a network?

SQLite is not designed for hosting a database on a server for access by lots of 
different computers at the same time.  To do that efficiently you need a 
client/server design and SQLite doesn't have it.

> Aren't there any other provisions to handled the locking errors if/when
> they occur?

The problem is at the operating system and Network File System level.  The 
required support is often just too buggy to be usable.  See "Client/Server 
Applications" on this page:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[RSmith]

On 2014/11/10 20:22, Mike McWhinney wrote:

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?


It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS can trust the 
network, but the client-server types do not care since they control the locking and do not depend on the OS / file status. SQLite 
however depends on it and as such cannot accurately (or timeously I should say) verify such status via a Network. On a local drive 
this is never a problem.


If you need Networking or User-control, please use a client-server type 
database.

There is one Client-Server implementation of SQLite (SQLightening I think) but it is neither free nor easy to convert to. You can 
write your own server too, but the best bet is using MySQL or PostGres in these cases.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Mike McWhinney]

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?




On Monday, November 10, 2014 12:10 PM, Roger Binns  
wrote:
 


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
ail_
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Locking errors on network

[Mike McWhinney]

Hello,

I continue to have "database is locked errors" when running an application 
which accesses a SQLite dabase on a network.


I have tried many of the connection string options: pooling, changing default 
time out.


I just recently tried a block of code which does a BeginTransaction and Commit. 
Before I did not use this. However it does not seem to have an effect
on the locking problem.


  string sql;

sql = "UPDATE APPOINTMENTS SET " +
 // "PatientID=" + myPatientID + "," +
  "TimeStampIsSeenByMA='" + nowString + "'" +

  " WHERE (" +
  "PatientID=" + myPatientID + " AND Date=" + "'" +
  dateString + "'" + ")";

   
SQLiteCommand command = new SQLiteCommand(sql, OMConnection.sqConn);
int numRowsAffected = 0;
SQLiteTransaction tran;
tran = OMConnection.sqConn.BeginTransaction(false);
numRowsAffected = command.ExecuteNonQuery();
command.Dispose();
tran.Commit();


Please let know if there are any other solutions to this database locking 
problem as used on a network.

Thanks
Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users