Is sqlite thread-safety sufficient for use with "Go" language ?

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

Is sqlite thread-safety sufficient for use with "Go" language ?

nicolas riesch
I would like to use sqlite with "Go" (golang) language.
There are already several drivers available in this language. But before
using them, I would like to ensure it is really safe.

The normal sequence to access a database is:

  sqlite3_prepare()
  loop
     sqlite3_step()
     sqlite3_column()
  sqlite3_finalize()

Normally, all functions in this sequence are called from the same OS thread.

But Go is inherently a multithreaded language, with many threads running
inside.
Even if the user writes a Go program with only one logical thread, he has
no control about which OS thread will process a function call.

     This means that EACH SUCCESSIVE function in the sequence above can be
processed on a DIFFERENT OS THREAD.

It means that to run safely, sqlite source code should not depend in any
way on the identity of the threads, which must be fully interchangeable.
So, the following conditions should be true. Are these sentences correct ?

1) no local-thread-storage is used in sqlite code.
2) thread id (gettid()) are not used.
3) when a function of the API enters a mutex, it leaves it before the
function returns.
   Between two API function calls, no mutex should be locked (else, it
would be impossible to ensure that the mutex is unlocked by the same thread
that locked it).
4) all file locking information is attached to connections, and not to
threads.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

Simon Slavin-3

On 5 Nov 2014, at 10:05pm, nicolas riesch <[hidden email]> wrote:

> Even if the user writes a Go program with only one logical thread, he has
> no control about which OS thread will process a function call.
>
>     This means that EACH SUCCESSIVE function in the sequence above can be
> processed on a DIFFERENT OS THREAD.
>
> It means that to run safely, sqlite source code should not depend in any
> way on the identity of the threads, which must be fully interchangeable.
> So, the following conditions should be true. Are these sentences correct ?
>
> 1) no local-thread-storage is used in sqlite code.
> 2) thread id (gettid()) are not used.
> 3) when a function of the API enters a mutex, it leaves it before the
> function returns.
>   Between two API function calls, no mutex should be locked (else, it
> would be impossible to ensure that the mutex is unlocked by the same thread
> that locked it).
> 4) all file locking information is attached to connections, and not to
> threads.

Since you don't already refer to it, can I ask that you read this page

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

and then ask any questions which remain, plus any new ones ?  You should probably tell us which threading mode you intend to use based on the needs you outline above.

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

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

nicolas riesch
Pardon me, I will try to reformulate my question more clearly.

My scenario:

  - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or Serialized
mode (SQLITE_THREADSAFE=1)
  - I create N logical threads in my "Go" program.
  - Each logical thread creates a database connection, for its "exclusive"
usage.
    Logical thread LT1 creates connection C1, logical thread LT2 creates
connection C2, etc.
    Logical thread LT1 only makes call to connection C1, never to
connection C2, C3, etc. Same for other threads.

Normally, in any mainstream language (C, PHP, etc), the same OS thread
makes the successive calls to sqlite3_prepare(), sqlite3_step(),
sqlite3_column(), sqlite3_finalize(), etc.
In the loop to retrieve all records in a table, there is no reason to call
sqlite3_step() on a different OS thread each time.

But in Go, it is possible that each call to sqlite3_step() is scheduled to
run on a different OS thread.
Indeed, the execution of a logical Go thread (called a Goroutine) can
switch from one OS thread to another one, without the user being aware of
it, at each function call.

E.g. logical thread LT1 can dispatch function calls on connection C1 like
this:
OS thread a --sqlite3_prepare(C1)--
--sqlite3_column(C1)--
OS thread b
--sqlite3_step(C1)--
--sqlite3_column(C1)--
OS thread
c
--sqlite3_step(C1)--                      --sqlite3_finalize(C1)--

For each connection, function calls always occur sequentially, but possibly
on a different OS thread each time.

Logical thread LT2 executes simultaneously, but calling functions only on
connection C2.
Logical thread LT3 executes simultaneously, but calling functions only on
connection C3.
etc...

So, in this scenario, I imagine that with SQLITE_THREADSAFE=1 or
SQLITE_THREADSAFE=2, there should be no problem ?

Is it correct to say that each function of the C API doesn't care on which
OS thread it is run, as long as the sequence of calls is correct ?

I know that in www.sqlite.org/threadsafe.html, it is written that "In
serialized mode, SQLite can be safely used by multiple threads with no
restriction.", but I just wanted to have a confirmation that it really
applies in the particular scenario above.


2014-11-05 23:13 GMT+01:00 Simon Slavin <[hidden email]>:

>
> On 5 Nov 2014, at 10:05pm, nicolas riesch <[hidden email]>
> wrote:
>
> > Even if the user writes a Go program with only one logical thread, he has
> > no control about which OS thread will process a function call.
> >
> >     This means that EACH SUCCESSIVE function in the sequence above can be
> > processed on a DIFFERENT OS THREAD.
> >
> > It means that to run safely, sqlite source code should not depend in any
> > way on the identity of the threads, which must be fully interchangeable.
> > So, the following conditions should be true. Are these sentences correct
> ?
> >
> > 1) no local-thread-storage is used in sqlite code.
> > 2) thread id (gettid()) are not used.
> > 3) when a function of the API enters a mutex, it leaves it before the
> > function returns.
> >   Between two API function calls, no mutex should be locked (else, it
> > would be impossible to ensure that the mutex is unlocked by the same
> thread
> > that locked it).
> > 4) all file locking information is attached to connections, and not to
> > threads.
>
> Since you don't already refer to it, can I ask that you read this page
>
> <https://www.sqlite.org/threadsafe.html>
>
> and then ask any questions which remain, plus any new ones ?  You should
> probably tell us which threading mode you intend to use based on the needs
> you outline above.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

Maxim Khitrov
On Wed, Nov 5, 2014 at 7:10 PM, nicolas riesch <[hidden email]> wrote:

> Pardon me, I will try to reformulate my question more clearly.
>
> My scenario:
>
>   - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or Serialized
> mode (SQLITE_THREADSAFE=1)
>   - I create N logical threads in my "Go" program.
>   - Each logical thread creates a database connection, for its "exclusive"
> usage.
>     Logical thread LT1 creates connection C1, logical thread LT2 creates
> connection C2, etc.
>     Logical thread LT1 only makes call to connection C1, never to
> connection C2, C3, etc. Same for other threads.
>
> Normally, in any mainstream language (C, PHP, etc), the same OS thread
> makes the successive calls to sqlite3_prepare(), sqlite3_step(),
> sqlite3_column(), sqlite3_finalize(), etc.
> In the loop to retrieve all records in a table, there is no reason to call
> sqlite3_step() on a different OS thread each time.
>
> But in Go, it is possible that each call to sqlite3_step() is scheduled to
> run on a different OS thread.
> Indeed, the execution of a logical Go thread (called a Goroutine) can
> switch from one OS thread to another one, without the user being aware of
> it, at each function call.

I'm the author of https://github.com/mxk/go-sqlite. You can't use a
single connection and its derived objects from multiple goroutines
without external synchronization, but as far as I know, there are no
problems with the same goroutine being scheduled to different OS
threads. I'm pretty sure that this applies to all other Go SQLite
drivers as well.

You actually can lock the current goroutine to a specific OS thread by
calling runtime.LockOSThread(). Some things, like OpenGL, need this
for thread-local storage and the other reasons that you mentioned. I
don't think SQLite falls into this category.

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

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

nicolas riesch
Hello, Maxim,

I use your excellent driver, indeed ;-)

About my post, the poison of doubt has just crept into my mind when I read
this:

  http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

  There is a bug in some Linux implementations (RedHat9 is the canonical
example) that prevents fcntl() locks created by one thread from being
modified in a different thread.
  If you are running on one of those systems, then you should always use an
SQLite database connection in the same thread in which it was originally
created.
  It is not safe to open the database in one thread and then pass the
connection off to a different thread for processing.

  The restriction of the previous paragraph has been relaxed somewhat as of
SQLite version 3.3.1.
  Beginning with version 3.3.1, you should be able to move database
connections from one thread to another as long as there are no locks
outstanding at the time you move the thread.
  If you are not running on one of the systems effected by the fcntl()
locking bug, then you can move your database connections at any time you
want.
  But for portability, you probably should assume your system has the bug.

It is an old issue, 8 years ago, and has certainly been resolved.
As I have a quite paranoid personality, I prefer to ask to be sure that
there is no similar problem I am not aware of.
But I have found nothing so far, so I think you are right and there is no
problem.


2014-11-06 3:06 GMT+01:00 Maxim Khitrov <[hidden email]>:

> On Wed, Nov 5, 2014 at 7:10 PM, nicolas riesch <[hidden email]>
> wrote:
> > Pardon me, I will try to reformulate my question more clearly.
> >
> > My scenario:
> >
> >   - sqlite is set to Multi-thread mode (SQLITE_THREADSAFE=2), or
> Serialized
> > mode (SQLITE_THREADSAFE=1)
> >   - I create N logical threads in my "Go" program.
> >   - Each logical thread creates a database connection, for its
> "exclusive"
> > usage.
> >     Logical thread LT1 creates connection C1, logical thread LT2 creates
> > connection C2, etc.
> >     Logical thread LT1 only makes call to connection C1, never to
> > connection C2, C3, etc. Same for other threads.
> >
> > Normally, in any mainstream language (C, PHP, etc), the same OS thread
> > makes the successive calls to sqlite3_prepare(), sqlite3_step(),
> > sqlite3_column(), sqlite3_finalize(), etc.
> > In the loop to retrieve all records in a table, there is no reason to
> call
> > sqlite3_step() on a different OS thread each time.
> >
> > But in Go, it is possible that each call to sqlite3_step() is scheduled
> to
> > run on a different OS thread.
> > Indeed, the execution of a logical Go thread (called a Goroutine) can
> > switch from one OS thread to another one, without the user being aware of
> > it, at each function call.
>
> I'm the author of https://github.com/mxk/go-sqlite. You can't use a
> single connection and its derived objects from multiple goroutines
> without external synchronization, but as far as I know, there are no
> problems with the same goroutine being scheduled to different OS
> threads. I'm pretty sure that this applies to all other Go SQLite
> drivers as well.
>
> You actually can lock the current goroutine to a specific OS thread by
> calling runtime.LockOSThread(). Some things, like OpenGL, need this
> for thread-local storage and the other reasons that you mentioned. I
> don't think SQLite falls into this category.
>
> - Max
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

Stephan Beal-3
On Thu, Nov 6, 2014 at 4:12 AM, nicolas riesch <[hidden email]>
wrote:

>   http://www.sqlite.org/cvstrac/wiki?p=MultiThreading


Be aware that the cvstrac pages are all historical, possibly outdated, and
no longer maintained.

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

justin
In reply to this post by nicolas riesch
On 2014-11-06 03:12, nicolas riesch wrote:
<snip>
>   There is a bug in some Linux implementations (RedHat9 is the
> canonical
> example) that prevents fcntl() locks created by one thread from being
> modified in a different thread.

As a data point, "Red Hat 9" is incredibly ancient, from 2003:

   http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history

While there is probably some someone - somewhere - still running on it
an ancient PC under a desk... it's not worth worrying about.

Just saying. ;)

Regards and best wishes,

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

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

Roger Binns
In reply to this post by nicolas riesch
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/05/2014 02:05 PM, nicolas riesch wrote:
> This means that EACH SUCCESSIVE function in the sequence above can
> be processed on a DIFFERENT OS THREAD.

That works just fine with SQLite, with one caveat.  You should also
make sure the wrapper itself is threadsafe.  For example what does the
wrapper do if you call close/finalize in one thread and step in
another concurrently?

The caveat is an implementation decision in SQLite and its error apis.
 Usually the OS error model is to have the errors be per thread.
SQLite instead has them be attached to the connection.  This means the
error apis return the most recent error for a connection no matter
which thread it happened on.  (In your case that is possibly
desireable.)  An example of how this can give the wrong information is
if thread 1 does a step followed by looking at the error info, but
between those two calls thread 2 does a sqlite operation on the same
database connection.  The error info thread 1 looks at could be from
its earlier call or from the the thread 2 call.

The second error issue is the api that returns a pointer to the error
string (sqlite3_errmsg).  By the time the pointer is used it could be
pointing to garbage or even now unmapped memory because a SQLite call
elsewhere on the connection caused that pointer to be freed.  Unmapped
memory will cause a crash, and who knows what the garbage will result in.

You can tell if a wrapper got multithreading right if the code looks
like this around every SQLite API call:

  // acquire db mutex
  sqlite3_db_mutex(db)
    // make sqlite call
    sqlite3_step()
    // copy error details if previous gave an error
    if (error) {
        // make a copy of the error message
        saved=strdup(sqlite3_errmsg(db));
    }
  // release mutex

Note this has to be done for every sqlite call that can set the error
message which is approximately all of them.  Here for example is the
macro I use in my Python wrapper to do this:

https://github.com/rogerbinns/apsw/blob/master/src/util.c#L36

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlRbsk0ACgkQmOOfHg372QQMNQCfTfLUpodmuqnqUhe3tlXRAUBf
N7EAoJeUlu4Ir2h5WCHY9k1Ey9U7icm/
=vmiH
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

John McKown
In reply to this post by justin
On Thu, Nov 6, 2014 at 7:51 AM, <[hidden email]> wrote:

> On 2014-11-06 03:12, nicolas riesch wrote:
> <snip>
>
>>   There is a bug in some Linux implementations (RedHat9 is the canonical
>> example) that prevents fcntl() locks created by one thread from being
>> modified in a different thread.
>>
>
> As a data point, "Red Hat 9" is incredibly ancient, from 2003:
>
>   http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history
>
> While there is probably some someone - somewhere - still running on it
> an ancient PC under a desk... it's not worth worrying about.
>

​Present and accounted for! Yes, I have an old Pentium machine which is
from 2002. It is still running a small service for me. And it still running
RH9.​ And you're right. It's not worth worrying about.



>
> Just saying. ;)
>
> Regards and best wishes,
>
> Justin Clift
>


--
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is sqlite thread-safety sufficient for use with "Go" language ?

jose isaias cabrera
In reply to this post by justin

[hidden email] wrote...

> On 2014-11-06 03:12, nicolas riesch wrote:
> <snip>
>>   There is a bug in some Linux implementations (RedHat9 is the
>> canonical
>> example) that prevents fcntl() locks created by one thread from being
>> modified in a different thread.
>
> As a data point, "Red Hat 9" is incredibly ancient, from 2003:
>
>   http://en.wikipedia.org/wiki/Red_Hat_Linux#Version_history
>
> While there is probably some someone - somewhere - still running on it
> an ancient PC under a desk... it's not worth worrying about.

Ahem... and what is wrong with that? :-)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users