Re: [sqlite] Using sqlite in multi fork() without threads

[Eduardo Morras]

On Mon, 07 Jan 2013 20:09:02 +0700
Dan Kennedy  wrote:

> On 01/07/2013 03:22 PM, Eduardo Morras wrote:
> >
> > Hi, I use sqlite in some of my projects. In one it follows a
> > parent/multichild model (multifork). The database is managed by the
> > parent, open close backups etc, opened before the fork, and after it
> > the childs use the database connection. I don't want to corrupt the
> > database, but the documentation only talks about the use with
> > threads. I use a version compiled with -DSQLITE_THREADSAFE=1 and
> > shared_cache mode on setted by parent before open db.
> 
> I'm not sure I follow this exactly.
> 
> You don't want to open a connection in the parent, call fork,
> then continue to use the same connection in the child process.
> That will lead to problems.
> 
> On the other hand, if you are calling fork(), then exec() from
> within the child process, then opening a new connection to the
> same db, that's fine.

I'm still thinking about it, because now (5 minutes since last reply to 
thread), i want to used shared cache but it will not work (ref from 
http://www.sqlite.org/sharedcache.html) so each child will have it's own shared 
cache for its threads/subchilds and not a global cache shared with parent and 
all childs.

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

Re: [sqlite] Using sqlite in multi fork() without threads

[Eduardo Morras]

On Mon, 7 Jan 2013 11:11:01 +0100
Pavlos Christoforou  wrote:

> Hello Eduardo,
> 
> We do (succesfully) use sqlite in a similar manner as you describe in your
> post but the code ensures the DB is opened in each individual child process
> *after* a child is successfully forked. It is not a good idea to carry open
> database connection across the fork (or any other open file descriptor for
> that matter).
> 
> Cheers

I used it that way because when i made my multifork server the INET connection 
must b setted by the parent and propagated to childs, otherwise we get errors 
because port is already in use by another pid. I thought the same may happened 
with the db connector and/or db file descriptor.

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

Re: [sqlite] Using sqlite in multi fork() without threads

[Eduardo Morras]

On Mon, 7 Jan 2013 12:27:54 +
Simon Slavin  wrote:

> 
> On 7 Jan 2013, at 8:22am, Eduardo Morras  wrote:
> 
> > The app works but, is anyone working with sqlite this way? Any advice to 
> > avoid db corrupts, deadlocks, whatever?
> 
> Read this page:
> 
> 
> 
> and the parts of this page which mention threads:
> 
> 
> 
> Apart from that you should be okay.

I read the advice on FAQ Rogers sent, it seems to work using serialized mode, 
but in future when i don't remember about it or i don't work/maintain the code 
it may work as it shouldn't and corrupt the db. I prefer to open the db 
connection after the fork.

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


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

Re: [sqlite] Using sqlite in multi fork() without threads

[Eduardo Morras]

On Mon, 07 Jan 2013 05:43:50 -0800
Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 07/01/13 00:22, Eduardo Morras wrote:
> > opened before the fork, and after it the childs use the database
> connection. I don't want to corrupt the database,
> 
> https://sqlite.org/faq.html#q6
> 
> You can't use the database across forks.  Parent and child would both
> consider themselves owners of open databases and silently corrupt each
> other's work and interfere with each other's locks.  You will corrupt the
> database. You need to close all databases and then do the fork, and then
> open databases in the children.

Years using sqlite (since 3.0 days) and never seen that advice about fork. It's 
the first time i use it with fork. Nothing wrong happened yet because i tested 
the app wrongly, perhaps it worked for now because -DSQLITE_THREADSAFE=1 
(serialized) was setted and may bang my foot in future. I'll change it, so 
childs and parent will open the connection after the fork. 

> In APSW I provide a fork checker.  This is done by providing an
> alternative mutex implementation that records the process id a mutex was
> created in and then checking the mutex is only ever used in the same
> process id.  (Each database connection has its own mutex.)
> 
> The code that does this:
> 
>   https://code.google.com/p/apsw/source/browse/src/apsw.c#704
> 
> In my benchmark tests I measured a slowdown of 1%.  ie if your code did
> nothing but SQLite calls then you can expect it to be about 1% slower.
> 
> I strongly recommend you do something like this to ensure that no
> developer accidentally has databases used across forks.

Interesting way to bypass the fork() problem and enhace sqlite. It may work 
with other types of applications architectures, not only fork(), f.ex. using 
exec*() after fork so you get 3 different apps using the same db, but i prefer 
to fix the bug.

> Roger

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

Re: [sqlite] Using sqlite in multi fork() without threads

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/01/13 00:22, Eduardo Morras wrote:
> opened before the fork, and after it the childs use the database
connection. I don't want to corrupt the database,

https://sqlite.org/faq.html#q6

You can't use the database across forks.  Parent and child would both
consider themselves owners of open databases and silently corrupt each
other's work and interfere with each other's locks.  You will corrupt the
database. You need to close all databases and then do the fork, and then
open databases in the children.

In APSW I provide a fork checker.  This is done by providing an
alternative mutex implementation that records the process id a mutex was
created in and then checking the mutex is only ever used in the same
process id.  (Each database connection has its own mutex.)

The code that does this:

  https://code.google.com/p/apsw/source/browse/src/apsw.c#704

In my benchmark tests I measured a slowdown of 1%.  ie if your code did
nothing but SQLite calls then you can expect it to be about 1% slower.

I strongly recommend you do something like this to ensure that no
developer accidentally has databases used across forks.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDq0REACgkQmOOfHg372QQC6QCfX7wa7eIq4okyJRT74LN8X4xI
xlgAoOSwC5dDzyvfb4S30nivW/rLEYIk
=tlN5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using sqlite in multi fork() without threads

[Krzysztof]

Few days ago I posted questions about sqlite multi threading on sqlite
mailing list. I could paste link to this thread but it is for members only.
So here is content, maybe it wil help you in some cases:


Hi,

I'm reading documentation about multi threading. I just want to be sure:
1. I can safely create another thread (with new handle / connection) in my
application and modify SAME database at the same time
2. I want use second connection in thread for importing while main thread
working "normally". By import I mean not backup, but importing documents
(CSV)
3. Can user modify table while another thread importing to it (in
transaction)? What are restrictions? What should I look out for?
4. Records inserted by second thread will be "visible" for first connection
after second thread commit transaction?

Regards

Answers:

> Hi,
>
> I'm reading documentation about multi threading. I just want to be sure:
> 1. I can safely create another thread (with new handle / connection) in my
> application and modify SAME database at the same time
>

Yes, as long as you don't take compile-time or run-time actions to make
SQLite single-threaded.  The default is to be multi-threaded.



> 2. I want use second connection in thread for importing while main thread
> working "normally". By import I mean not backup, but importing documents
> (CSV)
>

Yes.


> 3. Can user modify table while another thread importing to it (in
> transaction)? What are restrictions? What should I look out for?
>

Only one thread can be in a write transaction at a time.  If you have two
threads that need to write, they have to take turns.


> 4. Records inserted by second thread will be "visible" for first
connection
> after second thread commit transaction?
>

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

Re: [sqlite] Using sqlite in multi fork() without threads

[Dan Kennedy]

On 01/07/2013 03:22 PM, Eduardo Morras wrote:


Hi, I use sqlite in some of my projects. In one it follows a
parent/multichild model (multifork). The database is managed by the
parent, open close backups etc, opened before the fork, and after it
the childs use the database connection. I don't want to corrupt the
database, but the documentation only talks about the use with
threads. I use a version compiled with -DSQLITE_THREADSAFE=1 and
shared_cache mode on setted by parent before open db.


I'm not sure I follow this exactly.

You don't want to open a connection in the parent, call fork,
then continue to use the same connection in the child process.
That will lead to problems.

On the other hand, if you are calling fork(), then exec() from
within the child process, then opening a new connection to the
same db, that's fine.




The app works but, is anyone working with sqlite this way? Any advice
to avoid db corrupts, deadlocks, whatever? Please, don't talk about
changing to multithread model, i don't want to use them for this
project.


Thanks

---   --- Eduardo Morras
___ 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

Re: [sqlite] Using sqlite in multi fork() without threads

[Pavlos Christoforou]

Hello Eduardo,

We do (succesfully) use sqlite in a similar manner as you describe in your
post but the code ensures the DB is opened in each individual child process
*after* a child is successfully forked. It is not a good idea to carry open
database connection across the fork (or any other open file descriptor for
that matter).

Cheers

Pavlos



On 7 January 2013 09:22, Eduardo Morras  wrote:

>
> Hi, I use sqlite in some of my projects. In one it follows a
> parent/multichild model (multifork). The database is managed by the parent,
> open close backups etc, opened before the fork, and after it the childs use
> the database connection. I don't want to corrupt the database, but the
> documentation only talks about the use with threads. I use a version
> compiled with -DSQLITE_THREADSAFE=1 and shared_cache mode on setted by
> parent before open db.
>
> The app works but, is anyone working with sqlite this way? Any advice to
> avoid db corrupts, deadlocks, whatever? Please, don't talk about changing
> to multithread model, i don't want to use them for this project.
>
>
> Thanks
>
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Pavlos Christoforou

Point Nine Financial Technologies Ltd
Mobile: +44 (0)20 7193 5843

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

Re: [sqlite] Using sqlite in multi fork() without threads

[Simon Slavin]

On 7 Jan 2013, at 8:22am, Eduardo Morras  wrote:

> The app works but, is anyone working with sqlite this way? Any advice to 
> avoid db corrupts, deadlocks, whatever?

Read this page:



and the parts of this page which mention threads:



Apart from that you should be okay.

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

[sqlite] Using sqlite in multi fork() without threads

[Eduardo Morras]

Hi, I use sqlite in some of my projects. In one it follows a parent/multichild 
model (multifork). The database is managed by the parent, open close backups 
etc, opened before the fork, and after it the childs use the database 
connection. I don't want to corrupt the database, but the documentation only 
talks about the use with threads. I use a version compiled with 
-DSQLITE_THREADSAFE=1 and shared_cache mode on setted by parent before open db.

The app works but, is anyone working with sqlite this way? Any advice to avoid 
db corrupts, deadlocks, whatever? Please, don't talk about changing to 
multithread model, i don't want to use them for this project.


Thanks

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