Re: [sqlite] Is sqlite thread-safety sufficient for use with "Go" language ?
On Wed, Nov 5, 2014 at 7:10 PM, nicolas riesch 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segfault in sqlite3DbMallocRaw
Hi all, I don't think this is a problem with sqlite, but I'm also not sure what external behavior could trigger this, so I'm looking for help from someone more familiar with the sqlite internals. One of the users of my SQLite binding for Go is reporting a non-deterministic segfault in the 3.8.5 amalgamation on line 20086, which is "db->lookaside.pFree = pBuf->pNext;" in sqlite3DbMallocRaw: https://github.com/mxk/go-sqlite/issues/6 The segfault always seems to happen at the same place, but it happens infrequently, and the actual call sequence may be different. There don't seem to be any concurrent calls to any sqlite functions, yet building the package with either SQLITE_THREADSAFE=1 (instead of 2) or SQLITE_OMIT_LOOKASIDE resolves, or maybe just hides the issue. This feels like a concurrency problem, but I can't reproduce it or figure out the source. Is it possible to corrupt the lookaside allocator in some other way? - Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question about proper use of prepare_v2()
On Sat, Jul 27, 2013 at 2:04 AM, Stephan Beal wrote:
> Hi, all,
>
> i'm porting some code from one sqlite3-using project (Fossil SCM) to
> another sqlite3-using project (a prototype for Fossil v2) and i came across
> this code snippet:
>
> while( rc==SQLITE_OK && z[0] ){
> pStmt = 0; // < type=(sqlite3_stmt*)
> rc = sqlite3_prepare_v2(g.db, z, -1, &pStmt, &zEnd);
> if( rc!=SQLITE_OK ) break;
> if( pStmt ){
>...
> }
> z = zEnd;
> }
>
> My question is: is the if(pStmt) block there a case of too much error
> handling, or can it really happen that prepare() returns OK but also leaves
> pStmt as NULL (e.g. for an empty SQL statement)???
pStmt will be NULL if zSql contains only a comment or whitespace. There
is a comment about this in sqlite3_exec (legacy.c).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statement invariants
On Fri, Jun 14, 2013 at 12:56 PM, Maxim Khitrov wrote: > On Thu, Jun 13, 2013 at 9:27 PM, Igor Tandetnik wrote: >> On 6/13/2013 9:15 PM, Maxim Khitrov wrote: >>> >>> This works and also triggers SQLITE_SCHEMA with v1 interface. I did a >>> few more tests and it looks like the schema changes are ignored if the >>> statement is in the middle of iteration. As you said, only the first >>> step after a prepare/reset causes the values to change. Is that a safe >>> assumption to make? >> >> >> Well, that's how it works with the current implementation. Whether it will >> work this way forever, I don't know. > > Understood, thanks! I was just looking through the SQLite source and > it looks like sqlite3_expired would give me the information that I > need, but it's marked as deprecated. Is it unsafe to use or just no > longer necessary due to automatic recompilation? Never mind. I dug deeper into code and realized that sqlite3_expired only tells me about database changes from the same process. The statement could be invalidated after comparing the in-memory schema with what's on disk. I have no way of learning about that before the first step call and don't get any indication that recompilation happened after. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statement invariants
On Fri, Jun 14, 2013 at 1:16 PM, Simon Slavin wrote: > > > On 14 Jun 2013, at 5:56pm, Maxim Khitrov wrote: > >> Unconditionally invalidating the cache after each initial call to step >> will add a bit of overhead when repopulating the values. > > Do you think that every single user of SQLite does this ? Have you ever seen > it included in the source code of any application that uses SQLite ? > > I don’t know quite what you’re worried about. Do you actually expect your > schema to be changed when you’re in the middle of a SELECT ? And don’t you > think that if the schema did change it would be better for your application > to show the user an error code so your user would realise that the database > they were consulting was now holding different kinds of data ? There is no application. I'm writing SQLite bindings for Go [1]. Calling into C is expensive, as is the conversion from C to Go strings. I don't load any column metadata initially, but if the user asks for it, then I'd rather cache what I can and return it quickly the second time around. The current implementation assumes that column counts, names, and declarations never change, which I realized was incorrect. I'm trying to find the optimum way of invalidating the cache precisely because it is rare for the schema to change. After the initial load, the cache will likely stay valid for the lifetime of the statement. But if recompilation does happen, I need a cheap way to detect this. [1] http://code.google.com/p/go-sqlite/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statement invariants
On Thu, Jun 13, 2013 at 9:27 PM, Igor Tandetnik wrote:
> On 6/13/2013 9:15 PM, Maxim Khitrov wrote:
>>
>> This works and also triggers SQLITE_SCHEMA with v1 interface. I did a
>> few more tests and it looks like the schema changes are ignored if the
>> statement is in the middle of iteration. As you said, only the first
>> step after a prepare/reset causes the values to change. Is that a safe
>> assumption to make?
>
>
> Well, that's how it works with the current implementation. Whether it will
> work this way forever, I don't know.
Understood, thanks! I was just looking through the SQLite source and
it looks like sqlite3_expired would give me the information that I
need, but it's marked as deprecated. Is it unsafe to use or just no
longer necessary due to automatic recompilation?
Unconditionally invalidating the cache after each initial call to step
will add a bit of overhead when repopulating the values. If I could do
a simple if (sqlite3_expired(stmt)) {...} check instead, that would be
better.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statement invariants
On Thu, Jun 13, 2013 at 8:36 PM, Igor Tandetnik wrote: > On 6/13/2013 8:29 PM, Igor Tandetnik wrote: >> >> The column addition should have failed, unless you are using WAL in >> which case changes made by the writer are not visible to outstanding >> readers. A useful test would be to prepare the statement, then get >> column count (without ever stepping), then add the column, then get >> column count again. Or alternatively, step then reset then add the >> column. The bottom line is, schema change should occur while the >> statement is not active for the experiment to be meaningful. > > > Actually, I think you need to step once after the change for the statement > to realize the schema has changed, and recompile. So the sequence should be: > > 1. Start with inactive statement (either never stepped, or reset) > 2. Get column count > 3. Add a column > 4. Step once; the statement should recompile at this point > 5. Get column count - it should differ from that of step 2. This works and also triggers SQLITE_SCHEMA with v1 interface. I did a few more tests and it looks like the schema changes are ignored if the statement is in the middle of iteration. As you said, only the first step after a prepare/reset causes the values to change. Is that a safe assumption to make? If so, then I have a well-defined place where I need to invalidate my cache. Otherwise, is there any way for me to detect these recompilations without falling back to the v1 interface? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prepared statement invariants
Hello, I'd like to check my assumptions. After preparing a statement using the v2 interface, which functions are guaranteed to return the same values for that statement instance? These are the ones that I'm interested in at the moment: sqlite3_bind_parameter_count sqlite3_bind_parameter_name (for all 0 < i <= parameter_count) sqlite3_column_count sqlite3_column_name (for all 0 <= i < column_count) sqlite3_column_decltype (for all 0 <= i < column_count) I assume that parameter functions will return the same values since those are extracted from the SQL text. How do the column functions behave if there is a schema change and the statement is recompiled? I did a quick test where I prepared a query (SELECT * FROM x), started its execution, added another column to the same table, then executed the next step. My guess is that the v2 interface should have recompiled the query, but the column count didn't change (which is good for me, because I'm trying to cache some of these values). Using the v1 interface didn't result in SQLITE_SCHEMA error, so I'm not sure if I tested the right thing. - Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
