Re: [sqlite] Thread sync issue

[Keith Medcalf]

Access to the SQLite engine on a connection is not concurrently entrant -- it 
is serially entrant -- per connection.  You may only make one call into the 
SQLite engine per connection at a time.  The only way this can be violated is 
with multiple threads.  This is enforced either by the default "serialized" 
setting, in which case the SQLite engine itself will manage the single-entrance 
requirement per connection by using a mutex to ensure that you do not make 
multiple calls into the engine simultaneously on the same connection from 
multiple threads.

Alternatively, you can tell the SQLite engine to operate in "multithreaded" 
mode.  In this case the engine does NOT enforce the single entrance per 
connection requirement -- you must do so yourself.  It does not do anything to 
alleviate the restriction or to make the code concurrently entrant on a 
per-connection basis.

Notwithstanding whether you set single-thread, multi-thread, or serialized 
operation within the engine, the limitation that you may only make one call at 
a time into the SQLite engine library code per connection still holds.  All you 
are doing is telling SQLite how much "help" you need in complying with this 
requirement:

1)  Single-threaded means "violation of the entrance requirements is impossible 
because there is only a single thread of execution -- neither I (the 
programmer) nor you (the SQLite engine library) need take any precautions to 
prevent concurrent entrance because there is no point in doing so."

2)  Multithreaded   means "I (the programmer) will take responsibility for 
ensuring that the single entrance requirements are met.  If I fail to do so, 
then AHWBL, and it will be my (the programmers') fault for improper use of the 
SQLite engine library."

3)  Serialized  means "I (the programmer) do not take responsibility for 
ensuring that the single entrance requirements are met, so please, Mr. SQLite 
engine library, help me to ensure that I do not inadvertently break the rules 
and cause AHWBL."

So, as you see, the setting for the multithreading option only tells the SQLite 
engine library how responsibility for compliance with the 
single-entrance-per-connection will be managed.  The options are that there is 
no need (singlethreaded), that the programmer of the application will ensure 
compliance (multithreaded), or that the SQLite engine library code will do it 
(Serialized).  The default is serialized, and if you change the default setting 
then you are responsible meeting the requirements or for the results (AHWBL) if 
you fail to do so.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Ashok Pitambar
> Sent: Sunday, 24 February, 2013 06:44
> To: Roger Binns; General Discussion of SQLite Database
> Subject: Re: [sqlite] Thread sync issue
> 
> Read point 2 multithread carefully it says "provided no single db
> connection is  used simultaneously in two or more threads".  I am
> creating one db connection and it is accessed by multiple threads.
>   I am facing while reading the count from tables. There is
> mismatch due sync issue.
> 
> Regards,
> Ashok
> 
> - Reply message -
> From: "Roger Binns" <rog...@rogerbinns.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Subject: [sqlite] Thread sync issue
> Date: Sun, Feb 24, 2013 1:57 pm
> 
> 
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 24/02/13 00:14, Roger Binns wrote:
> > On 23/02/13 21:23, Ashok Pitambar wrote:
> >> Sqlite doc says it thread safe ...
> >
> > http://www.sqlite.org/threadsafe.html
> >
> > What makes you think the doc is wrong?
> 
> One big gotcha: errors - if you use one connection per thread then you are
> fine.  If you use the same connection across threads then read on:
> 
> The one thing not mentioned is that retrieving SQLite errors is not
> threadsafe.  The operating system does errors as thread specific (eg
> errno, GetLastError).  SQLite does errors as connection wide (I really
> wish this was fixed).
> 
> That means getting integer error codes is a race condition as a different
> thread can change it before your thread has a chance to retrieve it.  If
> you intend to retrieve the error string then your program *could crash*
> since the pointer you get could end up with invalid memory access when you
> treat it as a string.
> 
> Consequently the *only* safe way to make API calls where you intend to
> look at the error code or string is to add yet another level of locking.
> You can use sqlite3_db_mutex to get the mutex for a connection, an

Re: [sqlite] Thread sync issue

[Michael Black]

Are you misreading that lineit's not worded very well.

To rephrase in the positive sense instead of the negative.

"provided every thread has its own db connection"


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ashok Pitambar
Sent: Sunday, February 24, 2013 7:44 AM
To: Roger Binns; General Discussion of SQLite Database
Subject: Re: [sqlite] Thread sync issue

Read point 2 multithread carefully it says "provided no single db
connection is  used simultaneously in two or more threads".  I am
creating one db connection and it is accessed by multiple threads.
  I am facing while reading the count from tables. There is
mismatch due sync issue.

Regards,
Ashok

- Reply message -
From: "Roger Binns" <rog...@rogerbinns.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Subject: [sqlite] Thread sync issue
Date: Sun, Feb 24, 2013 1:57 pm



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/02/13 00:14, Roger Binns wrote:
> On 23/02/13 21:23, Ashok Pitambar wrote:
>> Sqlite doc says it thread safe ...
>
> http://www.sqlite.org/threadsafe.html
>
> What makes you think the doc is wrong?

One big gotcha: errors - if you use one connection per thread then you are
fine.  If you use the same connection across threads then read on:

The one thing not mentioned is that retrieving SQLite errors is not
threadsafe.  The operating system does errors as thread specific (eg
errno, GetLastError).  SQLite does errors as connection wide (I really
wish this was fixed).

That means getting integer error codes is a race condition as a different
thread can change it before your thread has a chance to retrieve it.  If
you intend to retrieve the error string then your program *could crash*
since the pointer you get could end up with invalid memory access when you
treat it as a string.

Consequently the *only* safe way to make API calls where you intend to
look at the error code or string is to add yet another level of locking.
You can use sqlite3_db_mutex to get the mutex for a connection, and
sqlite3_mutex_enter/leave to keep it locked while you extract codes and
messages.  For example to safely call a single function (sqlite3_blobopen
in this example but it applies to every sqlite3 call that returns an error):

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

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

iEYEARECAAYFAlEpztEACgkQmOOfHg372QTfLgCfVzKDib6ExfEhUO7StM5XYWT1
YSAAnRUWckgS95dF+7Rvq1RtU+DS8U7X
=LB1g
-END PGP SIGNATURE-
___
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

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

Re: [sqlite] Thread sync issue

[Ashok Pitambar]

Read point 2 multithread carefully it says "provided no single db
connection is  used simultaneously in two or more threads".  I am
creating one db connection and it is accessed by multiple threads.
  I am facing while reading the count from tables. There is
mismatch due sync issue.

Regards,
Ashok

- Reply message -
From: "Roger Binns" 
To: "General Discussion of SQLite Database" 
Subject: [sqlite] Thread sync issue
Date: Sun, Feb 24, 2013 1:57 pm



-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/02/13 00:14, Roger Binns wrote:
> On 23/02/13 21:23, Ashok Pitambar wrote:
>> Sqlite doc says it thread safe ...
>
> http://www.sqlite.org/threadsafe.html
>
> What makes you think the doc is wrong?

One big gotcha: errors - if you use one connection per thread then you are
fine.  If you use the same connection across threads then read on:

The one thing not mentioned is that retrieving SQLite errors is not
threadsafe.  The operating system does errors as thread specific (eg
errno, GetLastError).  SQLite does errors as connection wide (I really
wish this was fixed).

That means getting integer error codes is a race condition as a different
thread can change it before your thread has a chance to retrieve it.  If
you intend to retrieve the error string then your program *could crash*
since the pointer you get could end up with invalid memory access when you
treat it as a string.

Consequently the *only* safe way to make API calls where you intend to
look at the error code or string is to add yet another level of locking.
You can use sqlite3_db_mutex to get the mutex for a connection, and
sqlite3_mutex_enter/leave to keep it locked while you extract codes and
messages.  For example to safely call a single function (sqlite3_blobopen
in this example but it applies to every sqlite3 call that returns an error):

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

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

iEYEARECAAYFAlEpztEACgkQmOOfHg372QTfLgCfVzKDib6ExfEhUO7StM5XYWT1
YSAAnRUWckgS95dF+7Rvq1RtU+DS8U7X
=LB1g
-END PGP SIGNATURE-
___
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] Thread sync issue

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 24/02/13 00:14, Roger Binns wrote:
> On 23/02/13 21:23, Ashok Pitambar wrote:
>> Sqlite doc says it thread safe ...
> 
> http://www.sqlite.org/threadsafe.html
> 
> What makes you think the doc is wrong?

One big gotcha: errors - if you use one connection per thread then you are
fine.  If you use the same connection across threads then read on:

The one thing not mentioned is that retrieving SQLite errors is not
threadsafe.  The operating system does errors as thread specific (eg
errno, GetLastError).  SQLite does errors as connection wide (I really
wish this was fixed).

That means getting integer error codes is a race condition as a different
thread can change it before your thread has a chance to retrieve it.  If
you intend to retrieve the error string then your program *could crash*
since the pointer you get could end up with invalid memory access when you
treat it as a string.

Consequently the *only* safe way to make API calls where you intend to
look at the error code or string is to add yet another level of locking.
You can use sqlite3_db_mutex to get the mutex for a connection, and
sqlite3_mutex_enter/leave to keep it locked while you extract codes and
messages.  For example to safely call a single function (sqlite3_blobopen
in this example but it applies to every sqlite3 call that returns an error):

  int rc=SQLITE_OK;
  char *errmsg=NULL;
  sqlite3_mutex_enter(sqlite_db_mutex(db));
  rc=sqlite3_blobopen(.);
  if(rc!=SQLITE_OK) {
 rc=sqlite3_extended_errcode(db);
 errmsg=strdup(sqlite3_errmsg(db);
  }
  sqlite3_mutex_leave(sqlite_db_mutex(db));
  // now you can safely use rc and errmsg

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

iEYEARECAAYFAlEpztEACgkQmOOfHg372QTfLgCfVzKDib6ExfEhUO7StM5XYWT1
YSAAnRUWckgS95dF+7Rvq1RtU+DS8U7X
=LB1g
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Thread sync issue

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/02/13 21:23, Ashok Pitambar wrote:
> Sqlite doc says it thread safe ...

   http://www.sqlite.org/threadsafe.html

What makes you think the doc is wrong?

Roger

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

iEYEARECAAYFAlEpy+UACgkQmOOfHg372QRd+wCfVf2pjzJU6ewY+WyvUmWK4APE
OOsAoNOGXeEhO5QvxIfh5nTSnGpWxUF4
=Abjr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users