Re: [sqlite] Thread sync issue
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" > 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
Re: [sqlite] Thread sync issue
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" 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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" 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
-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
-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