Re: [sqlite] Query on primary key not using primary key?

[Brad House]

Without additional information, SQLite guesses that the data_idx index will
narrow down the search to about 7 entries in the table.  This is, of
course, a guess, but it is a reasonable guess for most indices.  The
primary key, even though it is unique, has an IN clause with 50 entries, it
SQLite guesses it will narrow the search down to 50 entries.  SQLite picks
the index that leads to the least amount of searching: 7 entries versus 50.

In your case, I'm guessing that data_idx is really not a very good index
and might ought to be dropped for doing little more than taking up space.
What does the sqlite_stat1 entry for data_idx say?


I simply narrowed down an example from my application.  The data_idx actually
represents an index with 3 columns for a very-commonly used query in the
application which wasn't intended to be used for the query provided.

It sounds like the solution is to just run ANALYZE, then both queries
choose the right index.  I was just shocked to find the primary key
not used when referencing rows by primary key.

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

Re: [sqlite] Query on primary key not using primary key?

[Brad House]

On 06/07/2013 12:46 PM, Simon Slavin wrote:


On 7 Jun 2013, at 5:37pm, Brad House <b...@monetra.com> wrote:


I've modified my code to run an Analyze on startup to work around this,
but it obviously takes time to run and slows down startup.


I can't answer your question about why this happens in the first place, but I 
can tell you that the results of ANALYZE are saved in the database file through 
closing and reopening.  If you have run ANALYZE once on data which looks like 
the data that will be in your database in normal use, then you don't have to 
run it again.  SQLite will continue to use that information about table sizes 
and 'chunkiness' when devising query plans in the future.


Unfortunately we can't easily predict when analyze might be useful to run
due to transformations in the data that might affect the query planner.
We just temporarily added it as a workaround until we come up with a
better solution which might be something more cron-like.

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

Re: [sqlite] Query on primary key not using primary key?

[Brad House]

I would expect all queries which specify the primary key components
in the WHERE clause to use the Primary Key in the query plan, regardless
of if ANALYZE has been run or not.



SQLite examines many different strategies for evaluating each query.  For
each strategy it tries to estimate the total run-time.  It then selects the
strategy that gives the least run-time.  Whether or not the PRIMARY KEY is
used as part of that strategy is not a consideration.

ANALYZE does not change this.  The purpose of ANALYZE is merely to provide
additional information to help SQLite give a better estimate of the
run-time for each of the query strategies under consideration.


I guess I just don't understand how it would come up with a run-time strategy
to NOT use a primary key (or any unique index) when the WHERE clause _exactly_
matches such an index.  It also seemed to 'guess' that there'd be 2 result
records without ANALYZE data and thus somehow chose a non-unique index utilizing
fewer columns over a unique index ...

The performance penalty is huge in my example, it's the difference of ~4s vs 
~0.005s.

Is this really not considered an issue/bug?

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

[sqlite] Query on primary key not using primary key?

[Brad House]

I would expect all queries which specify the primary key components
in the WHERE clause to use the Primary Key in the query plan, regardless
of if ANALYZE has been run or not.

I would also think it would assume any index which covers the most
where-clause components would be the most efficient if analyze had
never been ran.

This doesn't appear to be the case...


Example data:

CREATE TABLE data (c1 INT, c2 INT, c3 INT, c4 INT, c5 TEXT, PRIMARY KEY(c1, 
c2));
CREATE INDEX data_idx ON data (c1, c3, c4);
INSERT INTO data VALUES(1, 1, 0, 0, "test");
...1,000,000 records later...
INSERT INTO data VALUES(1, 100, 0, 0, "test");


Then:

EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50);
0|0|0|SEARCH TABLE data USING INDEX data_idx (c1=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 0


If I run ANALYZE, I get the expected result afterward:

EXPLAIN QUERY PLAN UPDATE data SET c3 = 5, c4 = 3 WHERE c1 = 1 AND c2 IN 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50);
0|0|0|SEARCH TABLE data USING COVERING INDEX sqlite_autoindex_data_1 (c1=? AND 
c2=?) (~50 rows)
0|0|0|EXECUTE LIST SUBQUERY 0


Tested on 3.7.15, 3.7.16, 3.7.17 ... didn't go back too far to see
if this issue was introduced at some point or if it has always
been this way.

I've modified my code to run an Analyze on startup to work around this,
but it obviously takes time to run and slows down startup.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!

[Brad House]

On 04/28/2012 09:36 AM, Richard Hipp wrote:

On Sat, Apr 28, 2012 at 8:24 AM, Black, Michael (IS)

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!

[Brad House]

On 04/27/2012 04:38 PM, Richard Hipp wrote:

Disable shared cache mode and you should be good to go.

If two database connections share the same cache, and one connection rolls
back, that means it will be changing cache content out from under the other
database connection, so any queries ongoing in the other connection have to
abort.

Two database connections in shared cache mode behave like a single database
connection in many ways, especially when you are talking about the cache
that they share.


Disabling shared cache mode definitely appears to fix it, but the behavior
is not the same as it was with 3.7.10 and shared cache enabled.  With 3.7.10,
I would get a locked table error on the insert, which caused the test code to 
roll
back the insert.  But when I disable shared cache mode, the insert in the test
case never gets blocked, it succeeds on the first attempt ... as does the 
select.
The insert finishes before the select finishes, but the new row does not show up
in the select either (not that I'm sure if it should or not, I guess that might
be a dirty read). I'm not really sure if this is fully intended behavior or
not ... I'd need to think about it a little to see if it might cause issues.

Also, shared cache mode just talks about schema caching and more efficient
locking, don't see anything really suggesting behavioral differences like this.
I'd definitely be concerned that other applications in the wild might
not expect the new behavior ... especially since this seems to be a fairly
significant behavioral change for a minor version bump like this.

-Brad


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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!

[Brad House]

Ok, I guess attachments don't come through.

I've uploaded it here:
http://www.brad-house.com/other/sqlite_test.c

-Brad

On 04/27/2012 03:50 PM, Brad House wrote:

On 04/27/2012 02:00 PM, Brad House wrote:



Only the connection that does the rollback has its queries aborted.


That is not the behavior I am seeing in 3.7.11, but was the behavior
I saw in 3.7.10.


If you are seeing other connections get queries aborted, that is something
new that I have not seen before and will need to investigate.


Correct.


If you do a ROLLBACK in the middle of a query, why would you ever want to
keep going with that query? What would you expect to see?


I wouldn't expect to keep going on that query.

I'll try to write a test case.

-Brad



As promised, I've attached a test case which uses the SQLITE amalgamation.

Sorry about how ugly the code is, I know it is bad, but it should prove the
point. I don't know if I'd consider this a _minimal_ test case, but I
tried to simulate everything we do like the options used to build the 
amalgamation,
and the fact that we register threading callbacks.

This test case creates a table, adds 100 rows, then spawns 2 threads
each with their own independent db handle.

One is a reader, the other is a writer. It tries to make sure the
reader obtains its read lock on the table first, and cycles through the
rows. The writer simultaneously tries to insert another row... I've
added some synchronization between the threads on sqlite3_step() so
they go back and forth (failure happens quicker this way).

What you'll see happen on 3.7.11 is the writer rolls back, and all of
a sudden, the reader is aborted (with message:
my_sqlite3_query(): sqlite3 returned 4: abort due to ROLLBACK).

On 3.7.10, the writer does not cause the reader to rollback ... just
the writer keeps rolling back until the reader finishes, then the writer
is able to retry and complete.

When compiling, copy sqlite3.c and sqlite3.h from the amalgamation into
the same directory as the source file and run (on Linux):

gcc -Wall -W -o sqlite_test sqlite_test.c -lpthread -ldl

(yeah, it has an #include "sqlite3.c" ... bad, but if you look at
it, you'll see why).

Then to run it, just run:

./sqlite_test

It will create a "./db.sqlite" database. This db must be removed for
each subsequent run or it will error out with a create table failure.

Thanks.
-Brad


___
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] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug?? -- test case!

[Brad House]

On 04/27/2012 02:00 PM, Brad House wrote:



Only the connection that does the rollback has its queries aborted.


That is not the behavior I am seeing in 3.7.11, but was the behavior
I saw in 3.7.10.


If you are seeing other connections get queries aborted, that is something
new that I have not seen before and will need to investigate.


Correct.


If you do a ROLLBACK in the middle of a query, why would you ever want to
keep going with that query? What would you expect to see?


I wouldn't expect to keep going on that query.

I'll try to write a test case.

-Brad



As promised, I've attached a test case which uses the SQLITE amalgamation.

Sorry about how ugly the code is, I know it is bad, but it should prove the
point.   I don't know if I'd consider this a _minimal_ test case, but I
tried to simulate everything we do like the options used to build the 
amalgamation,
and the fact that we register threading callbacks.

This test case creates a table, adds 100 rows, then spawns 2 threads
each with their own independent db handle.

One is a reader, the other is a writer.  It tries to make sure the
reader obtains its read lock on the table first, and cycles through the
rows.  The writer simultaneously tries to insert another row... I've
added some synchronization between the threads on sqlite3_step() so
they go back and forth (failure happens quicker this way).

What you'll see happen on 3.7.11 is the writer rolls back, and all of
a sudden, the reader is aborted (with message:
my_sqlite3_query(): sqlite3 returned 4: abort due to ROLLBACK).

On 3.7.10, the writer does not cause the reader to rollback ... just
the writer keeps rolling back until the reader finishes, then the writer
is able to retry and complete.

When compiling, copy sqlite3.c and sqlite3.h from the amalgamation into
the same directory as the source file and run (on Linux):

gcc -Wall -W -o sqlite_test sqlite_test.c -lpthread -ldl

(yeah, it has an #include "sqlite3.c" ... bad, but if you look at
 it, you'll see why).

Then to run it, just run:

./sqlite_test

It will create a "./db.sqlite" database.  This db must be removed for
each subsequent run or it will error out with a create table failure.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

Only the connection that does the rollback has its queries aborted.


That is not the behavior I am seeing in 3.7.11, but was the behavior
I saw in 3.7.10.


If you are seeing other connections get queries aborted, that is something
new that I have not seen before and will need to investigate.


Correct.


If you do a ROLLBACK in the middle of a query, why would you ever want to
keep going with that query?  What would you expect to see?


I wouldn't expect to keep going on that query.

I'll try to write a test case.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 04:38 PM, Jos Groot Lipman wrote:

As far as I understand this means: you will not see changes made by other
connections (committed or uncommited) after your transaction started.
If another connections commits a change, you will not see it.
I would expect: If another connections rollbacks the change, you will not
see it either.

Why whould anyone want an aborted read-transaction in this case?


I would agree ... I'd like to hear the other side of the story here
so we understand why this change was made if it was indeed intentional.

What purpose does this behavior serve?  Not saying it is wrong at
this point, just lacking information.

Also would need to understand the scope of this behavior.  Does
that mean if any connection rolls back that immediately all other
connections abort?  Or is it only one very specific case that this
occurs?

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 05:11 PM, Richard Hipp wrote:

On Thu, Apr 26, 2012 at 4:38 PM, Jos Groot Lipman  wrote:


 From the docs: 'The default isolation level for SQLite is SERIALIZABLE'

As far as I understand this means: you will not see changes made by other
connections (committed or uncommited) after your transaction started.



My understanding was that Brad is using a single database connection shared
between both threads.  You are correct that if he had been using separate
database connections in each thread, this problem would not come up.


As stated in my original request:

"I've got 2 threads with different connections to the same database."

The 2 threads are explicitly not sharing the same connection.  We are
using a connection "pool" where each thread that needs DB access
will pull an available connection out of the pool.

Also, we have set sqlite3_enable_shared_cache(1) and use WAL mode.

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 04:09 PM, Black, Michael (IS) wrote:

Would WAL mode prevent this?



We're using WAL mode :)

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

Re: [sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

On 04/26/2012 03:44 PM, Richard Hipp wrote:

On Thu, Apr 26, 2012 at 3:34 PM, Brad House<b...@monetra.com>  wrote:



I've got 2 threads with different connections to the same database.

This is approximately what I am seeing:
  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
  INSERT INTO foo VALUES (...);
  **sqlite3_step returns 6 (SQLITE_LOCKED)
  ROLLBACK TRANSACTION
  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK


So why, if Thread 2 rolls back does Thread 1 get aborted?



A rollback deletes content out from under other queries.  So if you have a
query pending in thread 1 and thread 2 tries to rollback, there are two
options:  (A) The rollback fails  (B) The pending query is aborted.  It
used to be that we did (A).  (If you had checked the return codes from your
"ROLLBACK TRANSACTION" statement you would have been seeing it fail.)  The
latest code does (B) instead.

Your software depends upon behavior (A).  Other users prefer behavior (B).
I don't know how to make everyone happy


I just ran another test, ensuring I check return codes specifically on
the ROLLBACK (And I was), and from what I can tell, I'm not getting
a failure on either either 3.7.11 or 3.7.10, it is returning SQLITE_OK.
NOTE: I'm using sqlite3_exec() to send the ROLLBACK... Rollback is actually
the only time we don't use sqlite3_prepare/_step/etc.

So I'm not exactly sure what you mean by I would see it failing.

What logic should we be using if we receive an SQLITE_LOCKED and we
should not ROLLBACK?  Do we simply perform an sqlite3_reset() then
retry the sqlite3_step() ... and keep doing that until it succeeds
after the SELECT has released its locks (I'm assuming if we do that
the select WILL succeed, right?)?

The sqlite3_step() documentation provides no guidance on what to do
if SQLITE_LOCKED is returned ... we had assumed the same guidance
as SQLITE_BUSY applied which says explicitly to perform a ROLLBACK.

That said, I'm still struggling to see the merit of a rollback in
one thread causing an abort in another thread.  We always took
rollback to mean we are conceding execution to the other thread,
not the other way around.  We use MySQL, Oracle, Microsoft SQL Server,
PostgreSQL ... never seen such a behavior.

Sorry if I'm being dense here.

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

[sqlite] SQLite 3.7.11 behavioral change from 3.7.10 with concurrency -- bug??

[Brad House]

We just noticed a behavioral change in SQLite 3.7.11 that caused a
regression in our software.  This change did not exist in 3.7.10.
Looking back at the changelog, I notice this:

"Pending statements no longer block ROLLBACK. Instead, the pending statement will 
return SQLITE_ABORT upon next access after the ROLLBACK."

But I do not know exactly what that means or why that change was
made.  I've got to assume this is not an intended behavioral change
as it is very significant at least from our standpoint as we've
been using the same logic since SQLite 3.3.

I've got 2 threads with different connections to the same database.

This is approximately what I am seeing:
  Thread 1:   SELECT bar,baz FROM foo WHERE ...;
  Thread 2:   BEGIN IMMEDIATE TRANSACTION;
  INSERT INTO foo VALUES (...);
  **sqlite3_step returns 6 (SQLITE_LOCKED)
  ROLLBACK TRANSACTION
  Thread 1:   **sqlite3_step returns 4 (SQLITE_ABORT): abort due to ROLLBACK


So why, if Thread 2 rolls back does Thread 1 get aborted?  This is
where the 3.7.11 logic doesn't make sense, Thread 2 rolls back
specifically so Thread 1 can continue since it "got there first".

I can probably write up a test case if necessary, but wanted to
pass it by you all first.

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

[sqlite] 3.7.6.1 build error on platforms without pread/pwrite

[Brad House]

It appears there is a bad check when setting up the
pread (and pwrite) callback:

#if defined(USE_PREAD) || defined(SQLITE_ENABLE_LOCKING_STYLE)
   { "pread",(sqlite3_syscall_ptr)pread,  0  },
#else
   { "pread",(sqlite3_syscall_ptr)0,  0  },
#endif


SQLite forces SQLITE_ENABLE_LOCKING_STYLE to _always_ be defined:

#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif
#endif


So locking style is defined as '0', so I'm thinking the check
_should_ have been:

#if defined(USE_PREAD) || SQLITE_ENABLE_LOCKING_STYLE


There are 2 other instances of checks for
   defined(SQLITE_ENABLE_LOCKING_STYLE)
that should also be changed in the code.

If you want, I can provide a patch against trunk to fix the build
issue.  I've currently, though, only modified the amalgamation 3.7.6.1
build in my local repo.

Thanks.
-Brad

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

[sqlite] 3.7.6.1 build error on platforms without pread/pwrite

[Brad House]

It appears there is a bad check when setting up the
pread (and pwrite) callback:

#if defined(USE_PREAD) || defined(SQLITE_ENABLE_LOCKING_STYLE)
   { "pread",(sqlite3_syscall_ptr)pread,  0  },
#else
   { "pread",(sqlite3_syscall_ptr)0,  0  },
#endif


SQLite forces SQLITE_ENABLE_LOCKING_STYLE to _always_ be defined:

#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif
#endif


So locking style is defined as '0', so I'm thinking the check
_should_ have been:

#if defined(USE_PREAD) || SQLITE_ENABLE_LOCKING_STYLE


There are 2 other instances of checks for
   defined(SQLITE_ENABLE_LOCKING_STYLE)
that should also be changed in the code.

If you want, I can provide a patch against trunk to fix the build
issue.  I've currently, though, only modified the amalgamation 3.7.6.1
build in my local repo.

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

Re: [sqlite] Most wanted features of SQLite ?

[Brad House]

Simon Slavin wrote:
> 
>> I definitely don't agree here as we're talking about these additional
>> locks existing _only_ in memory, not on disk.
> 
> Which requires client/server architecture.  Which SQLite3 doesn't  
> have.  Once you require concurrent access features in your DBMS (i.e.  
> multi-user, lots of locking) the things you nned to implement start to  
> be easier with a client/server architecture, whether it's a standalone  
> client application that must be launched manually or just a unix-style  
> daemon running in the background which is launched automatically when  
> needed and quits when nothing has used it in a while.

Uhh, no it doesn't.  Unless your definition of client/server is completely
different than mine in the context of what we're talking about here.  In
this context, the 'client and server' would share the same address space
(they're the same process!), hence there is no client/server separation.
It would mean the exact same amount of process separation as SQLite currently
employs.  It would just have additional code to optimize for concurrent
writes by multiple threads just as sqlite3_enable_shared_cache() does for
reads:
http://sqlite.org/c3ref/enable_shared_cache.html
http://sqlite.org/sharedcache.html

What I'm suggesting would be an extension of that shared cache, but for
managing access for writes.  We're just talking finer-grained locks here ...
we're not talking some elaborate scheme which requires IPC and
client/server communication.  It doesn't need to spawn off any other daemon
process here, that would be just plain stupid to do within the context of
what I'm talking about.

I'm just wondering if you're confused on the difference between a thread and
a process 

One of these days I just need to write a patch to do this and see if it
gets accepted.  Unfortunately, my spare time these days is around zilch.

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

Re: [sqlite] Most wanted features of SQLite ?

[Brad House]

>> It could probably benefit a large number of integrations to
>> have finer grained locking even if it could not be implemented for all
>> integration types.
> 
> It makes the system a great deal slower, since you need to keep  
> checking all the levels of lock you have implemented.  For instance,
> 
> UPDATE props SET colour='black',condition='poor' WHERE  
> description='fake sword'
> 
> needs to check for locks on the file, the record, and three fields.   
> That's five operations before you can even start to modify the data.   
> Could easily double the amount of time it takes to perform the  
> update.  And if you implement column locks there are even more.  And  
> implementing fine-grain locks leads to lock-contention: if someone  
> locks a record and you try to lock a field in that record, what should  
> happen ?  Now before trying to modify data and having locks interfere,  
> you're trying to modify locks and having lock-interaction interfere.

I definitely don't agree here as we're talking about these additional
locks existing _only_ in memory, not on disk.  There'd be no reason to
implement on-disk locking or even notifying the OS of sections of the file
which are locked since we're only talking about multiple threads in the
same process.  Any other process would hit the OS file lock and be forced
to wait.  The overhead of in-memory locking going to be extremely minimal,
and only affect those who specifically enable this fine-grained locking.

That said, I do think the on-disk journal file format might need to
change to accomplish even this, and I think that is probably the
biggest show stopper.

I'm not suggesting that this would be easy to implement either, and yes,
you'd need to figure out if SQLite will block on a lock, or return BUSY,
but by limiting the implementation scope to multithreaded applications,
it at least makes the implementation feasible, and would provide great benefit
to many users of SQLite.  In our own synthetic benchmark of our application,
which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but
1000 txns/sec with multiple connections to MySQL.  Biggest difference here
is MySQL allows multiple writers. (That said, I need to actually try to
benchmark MySQL with only 1 connection to be able to normalize those
numbers a bit).  Typically though, those with large transaction volumes
are going to go to some other database besides SQLite for other features
of a server-based engine, like replication.

Not complaining here though, SQLite definitely fits the bill for the
default database of our application.  What it does, it does well!

-Brad

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

Re: [sqlite] Most wanted features of SQLite ?

[Brad House]

>> Fine for me.  It seems to be everybody else that wants their favorite
>> feature imbedded in the core :-)
>>
>> Fine grained locking would be a great "asset" I feel.  Notice I did  
>> not
>> request a "feature."
> 
> 
> On my to-do list is to write a paper that explains why fine-grain  
> locking is not practical without either (1) a dedicated server process  
> to manage the locks or (2) enhancements to OS locking primitives that  
> are not currently available on any OS that I am aware of.  There is a  
> widely held belief that since OSes provide byte-level locking of files  
> it should be a simple matter to provide row-level locking in a  
> serverless database engine.  The proposed paper will explain why that  
> belief is incorrect.

It could probably benefit a large number of integrations to
have finer grained locking even if it could not be implemented for all
integration types.

I could see the implementation of a per-process lock (full DB lock), but
with finer-grained row-level locking on a per-thread basis within the
same application.  Probably enabled through the use of 
sqlite3_enable_shared_cache().

This would actually be of great benefit to our use of SQLite which is
strictly from a single multi-threaded process, no other application or
process would touch the database simultaneously, just multiple threads
from a single process would interact through multiple 'connections'.

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

[sqlite] SQLITE_MUTEX_APPDEF doc issue

[Brad House]

As of SQLite 3.6, SQLITE_MUTEX_APPDEF is no longer valid, but it is
still referenced here:
http://www.sqlite.org/c3ref/mutex_alloc.html

Probably want to make that change in the docs, specifically the
section which states:

"If SQLite is compiled with the SQLITE_MUTEX_APPDEF preprocessor macro defined 
(with "-DSQLITE_MUTEX_APPDEF=1"), then no mutex implementation is included with 
the library."

Should probably read:

"If SQLite is compiled with the SQLITE_MUTEX_NOOP preprocessor macro defined 
(with "-DSQLITE_MUTEX_NOOP"), then a stub implementation will be included with 
the library."

The remaining portion of that paragraph (not shown here) appears to be correct
and relevant.

This actually caught me off guard when migrating from 3.5 to 3.6, luckily
this page: http://www.sqlite.org/35to36.html mentioned that SQLITE_MUTEX_APPDEF
is no longer recognized.

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

Re: [sqlite] Break compatibility with linux 2.4 in SQLite 3.6.7?

[Brad House]

>> When you said breaking compatibility with 2.4, you meant
>> NPTL vs LinuxThreads, right?
> 
> My thought as well.  There are still some architectures which do not
> support NPTL, even with 2.6 kernels (hppa comes to my mind).  But lack
> of NPTL support causes pain in other areas, too, so it's probably time
> to fix these architectures or ditch them.

That's true, didn't even think of that.  If it is infact an NPTL vs
LinuxThreads issue to which D. Richard Hipp is referring, last I
knew, uclibc didn't support NPTL either.

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

Re: [sqlite] Break compatibility with linux 2.4 in SQLite 3.6.7?

[Brad House]

As a follow-up, RHEL 3 did include the NPTL backport as per:
http://www.redhat.com/docs/manuals/enterprise/RHEL-3-Manual/release-notes/as-x86/

RHEL 2 did not, it was first introduced in RHEL 3.

RHEL AS 2.1 is still supported by Red Hat until May 31, 2009:
http://www.redhat.com/security/updates/errata/

I believe that will the the longest standing supported Linux
distro out there without NPTL since they 'support' it for 7 years.

When you said breaking compatibility with 2.4, you meant
NPTL vs LinuxThreads, right?  Or am I totally going off on a
tangent.

That said, personally (selfishly?), my company does not support
RHEL2 anyhow so if it is just a matter of requiring NPTL and not
LinuxThreads (instead of as stated 2.4 vs 2.6 kernels), I'm
fine with that.  (For those wondering, we don't support RHEL2
because it uses glibc 2.2, and we only support glibc 2.3.2
or higher).

Realistically though, you should probably wait until after
May 31, 2009 to do the cleanups.

When the cleanups are performed, you should definitely have a
runtime check to see if the system is running on NPTL or not
as someone could have defined LD_ASSUME_KERNEL=2.4.1 on an
NPTL-capable system to force it to use LinuxThreads (I think
a lot of Oracle install docs recommend setting that flag, so
if someone set it globally, well, that wouldn't be good).
Pretty sure RHEL5 was the first release to remove LinuxThreads
support all-together, RHEL3/4 allowed the LD_ASSUME_KERNEL trick.

As far as the run-time check, the only thing I can think of to
do is to spawn a thread that calls getpid() and return it
so pthread_join() can fetch it.  If it matches the parent, it's
running NPTL, otherwise, LinuxThreads and some failure should
occur.  Though getconf/sysconf(3) might be able to determine it
as well, but I'm not sure.

-Brad

Brad House wrote:
> I'm pretty sure both RHEL 2 & 3 both use 2.4 kernels and are
> still actively supported by RedHat if that sways your decision.
> I know I have clients that are on RHEL3 still so I'd prefer this
> change not to be made if there will be negative impact.  We do
> share connections across threads, but not during a transaction,
> we just use a 'connection pool' and it grabs an inactive connection.
> 
> That said, I'm pretty sure RHEL backported NPTL to 2.4, so it
> may not be relevant but I don't have the info on that currently,
> I can look that up.
> 
> Thanks.
> -Brad
> 
> D. Richard Hipp wrote:
>> Many systems built on the linux 2.4 kernels contain a bug in their  
>> thread implementation:  A posix advisory lock created by thread A  
>> could not be overridden or modified by thread B.  In essence, linux  
>> was treating different threads within the same process as if they were  
>> different processes.  Long-time users of SQLite may recall that we  
>> used to publish the restriction that SQLite database connections  
>> created in one thread could not be used in a different thread.  That  
>> restriction was entirely a result of the afore mentioned bug in linux  
>> 2.4.
>>
>> The unix drivers for SQLite contain a pile of ugly code to work around  
>> this bug.  I would very much like to delete that code for SQLite  
>> version 3.6.7, due out in December.  My question:  would this cause  
>> anyone any serious hardship?
>>
>> My impression is that everybody who runs linux upgraded to a version  
>> 2.6 kernel at least two years ago.  And even for those rare people who  
>> have not, SQLite will still continue to work correctly provided that:
>>
>> (1) you do not attempt to move database connections across threads.
>> (2) you do not open connections to the same database file in two  
>> different threads of the same process.
>>
>> So my questions is this, really:  Is there anybody who runs SQLite on  
>> a linux 2.4 kernel who either moves database connections across  
>> threads or who opens multiple connections to the same database file in  
>> separate threads of the same process?
>>
>> I am hoping that the answer to the previous question is "no" because I  
>> really do want to simplify the SQLite unix drivers by deleting the  
>> code that implements the linux thread/posix-lock bug work-around.
>>
>>
>> D. Richard Hipp
>> [EMAIL PROTECTED]
>>
>>
>>
>> ___
>> 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] Break compatibility with linux 2.4 in SQLite 3.6.7?

[Brad House]

I'm pretty sure both RHEL 2 & 3 both use 2.4 kernels and are
still actively supported by RedHat if that sways your decision.
I know I have clients that are on RHEL3 still so I'd prefer this
change not to be made if there will be negative impact.  We do
share connections across threads, but not during a transaction,
we just use a 'connection pool' and it grabs an inactive connection.

That said, I'm pretty sure RHEL backported NPTL to 2.4, so it
may not be relevant but I don't have the info on that currently,
I can look that up.

Thanks.
-Brad

D. Richard Hipp wrote:
> Many systems built on the linux 2.4 kernels contain a bug in their  
> thread implementation:  A posix advisory lock created by thread A  
> could not be overridden or modified by thread B.  In essence, linux  
> was treating different threads within the same process as if they were  
> different processes.  Long-time users of SQLite may recall that we  
> used to publish the restriction that SQLite database connections  
> created in one thread could not be used in a different thread.  That  
> restriction was entirely a result of the afore mentioned bug in linux  
> 2.4.
> 
> The unix drivers for SQLite contain a pile of ugly code to work around  
> this bug.  I would very much like to delete that code for SQLite  
> version 3.6.7, due out in December.  My question:  would this cause  
> anyone any serious hardship?
> 
> My impression is that everybody who runs linux upgraded to a version  
> 2.6 kernel at least two years ago.  And even for those rare people who  
> have not, SQLite will still continue to work correctly provided that:
> 
> (1) you do not attempt to move database connections across threads.
> (2) you do not open connections to the same database file in two  
> different threads of the same process.
> 
> So my questions is this, really:  Is there anybody who runs SQLite on  
> a linux 2.4 kernel who either moves database connections across  
> threads or who opens multiple connections to the same database file in  
> separate threads of the same process?
> 
> I am hoping that the answer to the previous question is "no" because I  
> really do want to simplify the SQLite unix drivers by deleting the  
> code that implements the linux thread/posix-lock bug work-around.
> 
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] sqlite 3.5.8 database corruption

[Brad House]

D. Richard Hipp wrote:
> On Jul 23, 2008, at 1:08 PM, Brad House wrote:
> 
>> I'm just investigating an issue now.  This is the first ever
>> incident of a corrupt database we've had on a few thousand
>> installations,
> 
> Have you read the background information at
> 
> http://www.sqlite.org/atomiccommit.html
> 
> See especially section 9.0:  Things That Can Go Wrong.

Yes, I've read that.
We're using the VFS layer that sqlite provides for windows
(since XP-E is just XP with a bunch of dlls and auxiliary
applications removed).

The database always resides on the same disk as the software
accessing it (no network transfers).

The database file wouldn't have been manipulated outside
of our application.

According to your section 9, that leaves a 'rouge' process
(read: virus), or buffers not actually being flushed to
disk (either because of a disk controller issue or a
FlushFileBuffers() issue).

Obviously other options exist such as hardware failure
(RAM, harddrive), or an SQLite bug.

Hardware failure definitely hasn't been ruled out here.
The main reason for reporting this issue is to make sure
if there is an issue, there is enough 'history' of it
to justify researching it.  I'm definitely not blaming
SQLite at this point.

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

Re: [sqlite] sqlite 3.5.8 database corruption

[Brad House]

Guess the mailing list stripped the attachment, I've uploaded
it here:
http://www.monetra.com/~brad/integrity_check.txt.bz2

Brad House wrote:
> I'm just investigating an issue now.  This is the first ever
> incident of a corrupt database we've had on a few thousand
> installations, though most of our installations are on
> SQLite 3.4, our latest release is now using 3.5.8.
> We have deployments on just about every OS...
> 
> The OS that experienced the corruption was Windows XP-E
> (embedded).
> 
> The error message SQLite is returning is:
> database or disk is full
> 
> But I am told there are 45G free on the partition that
> the database file resides.
> 
> We do use SQLite in a multithreaded environment, and it
> is compiled with Threadsafe. Infact, we modify the
> amagalmation and put:
> #define SQLITE_THREADSAFE 1
> At the top of the file just to make sure.
> We also use 'sqlite3_enable_shared_cache(1)'.  I don't
> think it really provides that much benefit to us though
> as we are more commit-heavy, so I can disable it if
> it might be a point of concern.
> 
> I've attached the output of PRAGMA integrity_check;
> (which looks pretty bad)...
> 
> I can make the database available if necessary.
> 
> Thanks for any insight.
> -Brad
> 
> 
> 
> 
> ___
> 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] sqlite 3.5.8 database corruption

[Brad House]

I'm just investigating an issue now.  This is the first ever
incident of a corrupt database we've had on a few thousand
installations, though most of our installations are on
SQLite 3.4, our latest release is now using 3.5.8.
We have deployments on just about every OS...

The OS that experienced the corruption was Windows XP-E
(embedded).

The error message SQLite is returning is:
database or disk is full

But I am told there are 45G free on the partition that
the database file resides.

We do use SQLite in a multithreaded environment, and it
is compiled with Threadsafe. Infact, we modify the
amagalmation and put:
#define SQLITE_THREADSAFE 1
At the top of the file just to make sure.
We also use 'sqlite3_enable_shared_cache(1)'.  I don't
think it really provides that much benefit to us though
as we are more commit-heavy, so I can disable it if
it might be a point of concern.

I've attached the output of PRAGMA integrity_check;
(which looks pretty bad)...

I can make the database available if necessary.

Thanks for any insight.
-Brad
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64bit Version

[Brad House]

> Hi Brad
> Thanks for the prompt reply the error I am getting is that the sqlite files 
> (see below) could not be copied to the system32 folder? I have tried all 
> sorts,
> I do have administrative rights but just have no luck with this...
> 
> Sqliteodbc.dll
> Sqliteodbcu.dll
> Sqlite3.odbc.dll
> 
> Any Ideas

I've never used the SQLite ODBC Driver.
Really can't help you there.  Seems odd that you'd
e-mail this list though since SQLite doesn't provide
an ODBC driver interface, though there are 3rd party
ODBC interfaces.  That said, maybe you're confused and
you somehow think it _is_ an ODBC driver on Windows?
You might want to check out this Wiki page:
http://www.sqlite.org/cvstrac/wiki?p=SqliteOdbc

The only ways I've used it on Windows is to either have
the standard sqlite dll file in the same directory as
the program which depends on it, or use the amagalmation
and compile it directly into my program.

Both of those work fine.

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

Re: [sqlite] 64bit Version

[Brad House]

Yes, we utilize 64bit versions of SQLite on:
Linux (x86_64)
FreeBSD (x86_64)
Windows (x64 -- XP, Vista -- should also work on 2008)
Solaris (sparc64)
AIX (ppc64)

All work fine.

-Brad

Nic wrote:
> Hi All
> 
> Has anyone got Sqlite to run on a 64bit Operating System ? I am having issues 
> with server 2008
> 
> Regards
> Nic
> ___
> 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] SQLite version 3.5.9

[Brad House]

> You may see some performance increase by setting pragma page_size to a 
> larger value so that SQLite transfers fewer, but larger, blocks across 
> the network. I would try benchmark tests with page sizes of 8K and 32K 
> to see if there is a substantial difference.

Good point Dennis, though you should probably mention that he would
need to be using at least 3.5.8 and Vacuum after setting the page size
pragma for it to actually take effect on an existing database.

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

Re: [sqlite] SQLite version 3.5.9

[Brad House]

> I would disagree with this, unless I misunderstand.  File copies (from the
> Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50%
> of theoretical max on our Gbit LAN, whereas reading the records from the
> same file via SQLite is 20-25x slower (—2MB/sec at best, terrible
> performance).  So there is plenty of raw I/O bandwidth across the LAN and
> network drive, but for some reason SQLite access to its remote files is
> extremely slow (to be clear: these are single users accessing single files).

Peter, there is a lot more latency over a network than just hitting
a local disk as well, so you've got potentially hundreds of requests
from disk to perform a single select on the database (traversing
the Btree, etc).  Your OS may perform some read-aheads and caching
which would reduce the latency to nearly nothing for the disk access
(on a local machine), but you're having to deal with network latency
and protocol overhead on _each_ of those hundreds of requests
when you're working over a network. Raw sequential throughput you
mentioned really has no relevance here at all.

Like Richard said, use the right tool for the job.  You need a
database that resides on the server and communicates using its
own network protocol.  If you'd like to continue using SQLite
you might check out some of the server/client wrappers out there:
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

You've got to realize that no other (non-server based) database would
be able to perform better in this situation.

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

Re: [sqlite] Cannot get amalgamation built from CVS to compile

[Brad House]

We ran into the same problem here.  It seems as though maybe the
amalgamation is hand-edited for distribution to remove the contents
of the config.h to be system agnostic.  When we built ours from CVS,
we just did the same hand-edit and packaged it and it compiled fine on the
dozen or so OS's we distribute binaries for (Windows (32 & 64), MacOSX,
Linux, FreeBSD, Solaris, SCO, AIX, ...).

I'd actually like to know the consequences of this though, especially
in relation to the reentrant functions (HAVE_GMTIME_R, HAVE_LOCALTIME_R),
also I'd be interested to know what it does without UINT64_T or UINTPTR_T...

-Brad

Samuel Neff wrote:
> We're trying to build an amalgamation from CVS to use within our application
> for the first time.  However, when we try to compile we get an error on this
> line:
> 
> 
> #ifdef HAVE_STDINT_H
> #include 
> #endif
> 
> fatal error C1083: Cannot open include file: 'stdint.h': No such file or
> directory
> 
> 
> We tracked back the difference between that distribution and the
> amalgamation that we build and the major changes start here
> 
> From sqlite3.c in 3.5.8 distribution:
> 
> #ifndef _CONFIG_H_
> #define _CONFIG_H_
> 
> /* We do nothing here, since no assumptions are made by default */
> 
> #endif
> 
> 
> From sqlite3.c in our amalgamation built from CVS:
> 
> 
> #ifndef _CONFIG_H_
> #define _CONFIG_H_
> 
> 
> 
> /*
> ** Data types
> */
> 
> /* Define as 1 if you have the int8_t type */
> #define HAVE_INT8_T 1
> 
> ...
> 
> /* Define as 1 if you have the stdint.h header */
> #define HAVE_STDINT_H 1
> 
> ...
> 
> /* End of header */
> #endif
> 
> 
> Is this related to a change in the CVS source or is there something we're
> doing wrong in building the amalgamation?
> 
> We're building the amalgmation on Fedora Core release 4 (Stentz),
> 2.6.17-1.2142_FC4smp #1 SMP i686 i686 i386 GNU/Linux
> 
> We're compiling sqlite in Microsoft Visual Studio 2008 as part of
> System.Data.SQLite (.NET) which uses sqlite3.c and compiles fine with
> sqlite3.c from the 3.5.8 distribution on the sqlite.org website.
> 
> Any help would be appreciated.
> 
> Thanks,
> 
> Sam
> 
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] restricting access to sqlite database

[Brad House]

I'm assuming you're using mod_php.  For a virtual-hosted environment,
I don't think that can be made 'secure'.  You probably need to switch
to suexec and fastcgi php.  That way the php scripts are run as your
user rather than the 'www' user.  You might glance at this:
http://www.k4ml.com/wiki/server/apache/php-fastcgi

-Brad

Thomas Robitaille wrote:
>> Just because "apache" the user account on your compute can access the
>> db, doesn't mean apache the webserver is serving that file.
>>
>> My webserver runs as user "www"
>>
>> My db is under ~/Data//database.db owned by me, but chmod- 
>> ed to 666
>>
>> The webserver serves only files under ~/Sites//
> 
> I understand what you mean, but if your database file is chmod-ed to  
> 666, any other user logged in to your web server can edit it,  
> correct? If you are the only user on your web server, then indeed  
> placing it outside the web directory is enough, but what I am asking  
> about is for cases when there are 100 or 1000 users that can all log  
> in to the same web server.
> 
> Thomas
> 
>>
>>
>>>  Thomas
>>>
>>>
>>>  On 22 Apr 2008, at 15:14, P Kishor wrote:
>>>
>>>
 On 4/22/08, Thomas Robitaille <[EMAIL PROTECTED]> wrote:

> Hi everyone,
>
>  I am in the process of setting up a forum which uses SQLite on  
> a web
>  server which has ~50 other users. I can create a directory for the
>  sqlite database, which I chown to 'apache' (the user under  
> which the
>  web server is run). However, because the database is then  
> writable by
>  apache, could other users not potentially write web applications
>  which could edit that database (and potentially remove all  
> tables?).
>  In MySQL for example, this is not a problem because of the  
> different
>  users/privileges, but what is the common way around this in  
> SQLite?
>
 Nothing specific to SQLite, but common good web programming  
 practice.
 Don't keep the db in a web accessible path.

 My web root is /path/to/web/root/

 my db is in

 /a/totally/different/path/to/db



 --
 Puneet Kishor http://punkish.eidesis.org/
 Nelson Institute for Environmental Studies http:// 
 www.nelson.wisc.edu/
 Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org

>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> -- 
>> Puneet Kishor http://punkish.eidesis.org/
>> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
>> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> 
> ___
> 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] [newbie] SQLite and VB.Net?

[Brad House]

>   I don't know anything about .Net, and I'd like to build a quick app
> with Visual Studio 2005 or 2008 to check how well it performs with
> SQLite. If performance and deployment prove to be good enough, we'll
> use VB.Net for new projects and finally dump VB6.

I have almost no experience with .Net (or any other microsoft-specific
technologies), but we've had to advise some clients on how to use
a library we provide from within Vb.Net and the InteropServices worked
for them.  Not sure if they'd meet your needs or not, but you might
give them a shot, it's as close to native as you'll get.

Basic structure I think goes like this (though it should be easy
to google):

Imports System.Runtime.InteropServices

Declare Ansi Function sqlite3_open Lib "sqlite3.dll" _
(ByVal filename As String, ByRef db As IntPtr) _
As Integer
Public SQLITE3_OK = 0


Dim db As IntPtr
If sqlite3_open("my_sqlite3.db", db) != SQLITE3_O
K
Console.WriteLine("Failed to open")
End If

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

Re: [sqlite] How to compile SQLite for Windows 32bit & 64bit?

[Brad House]

> Please Help
> 
> Is there a good tutorial that shows how to compile SQLite for both
> Windows 32 bit & 64bit? 
> 
> If ICU is required then I need the files to be statically linked rather
> than having any external dependencies.
> 
> Anyone know of a good tutorial for building  both a 32bit & 64bit DLL
> with any decencies statically linked?

Have you considered just adding the sqlite amalgamation to your project
and compiling it in?  That's how my company uses SQLite and supports both
32bit and 64bit windows.
Doesn't get much easier than a single .c file and a single .h file.

Also, there is a binary-form sqlite DLL that only depends on the standard
windows C library MSVCRT.DLL (that comes with Visual Studio 6, but I think
most versions of Windows ship with it too, afaik).  I don't think you can get
rid of that dependency without risking other issues with duplicate symbols.

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

Re: [sqlite] VFS memory leak : During lock / unlock operations

[Brad House]

>> they have put traces and identified that for 1000 lock calls
>> there are only 950 unlock calls, which is a shortage of 50 unlock
>> calls. 
> The os_unix.c backend to SQLite makes no attempt to match lock/unlock
> calls, because posix does not requires such.  If you are running
> on an operating system that does require matching lock/unlock calls,
> you will probably need to modify the os_unix.c layer in order for it
> to work properly on your system.

I'm not sure what you're getting at here.  Are you saying that it's
possible that some mutexes will _never_ be unlocked?  In what circumstance
does that occur? Is there a case where a mutex could be destroyed while
a lock is held?  I know my linux 'man pthread_mutex_destroy' says
"Attempting to destroy a locked mutex results in undefined behavior".

Under what circumstances does this "lock leakage" occur exactly?

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

[sqlite] error building amalgamation from 3.5.6 source tree

[Brad House]

I have a few patches that I apply to SQLite and need to
rebuild the amalgamation, but I get an error saying
it can't find  tsrc/fts3.h
I verified on an un-patched 3.5.6 release that the same
error occurs, both on Linux and MacOSX 10.5.1.  Am I missing
a step somewhere?  If I remove all the fts3 references
from mksqlite3c.tcl, it appears to build, but the generated
sqlite3.c is missing the fts3 files, though it appears to
compile and work without them...

Here's how I reproduce the error:

$ wget http://www.sqlite.org/sqlite-3.5.6.tar.gz
$ tar -zxvpf sqlite-3.5.6.tar.gz
$ cd sqlite-3.5.6
$ ./configure --enable-threadsafe
$ make sqlite3.c
gcc -g -O2 -o lemon ./tool/lemon.c
cp ./tool/lempar.c .
cp ./src/parse.y .
./lemon  parse.y
mv parse.h parse.h.temp
gawk -f ./addopcodes.awk parse.h.temp >parse.h
cat parse.h ./src/vdbe.c | gawk -f ./mkopcodeh.awk >opcodes.h
sort -n -b -k 3 opcodes.h | gawk -f ./mkopcodec.awk >opcodes.c
gcc -g -O2 -o mkkeywordhash  ./tool/mkkeywordhash.c
./mkkeywordhash >keywordhash.h
sed -e s/--VERS--/3.5.6/ ./src/sqlite.h.in | \
 sed -e s/--VERSION-NUMBER--/3005006/ >sqlite3.h
rm -rf tsrc
mkdir -p tsrc
cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c 
./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c 
./src/date.c ./src/delete.c ./src/expr.c ./src/fault.c ./src/func.c 
./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c 
./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c 
./src/mem3.c ./src/mem4.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c 
./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c 
./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c 
./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in 
./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c 
./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c 
./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c 
./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c 
./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c 
./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h 
./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h 
./src/hash.h 
./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h 
./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h 
./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc
cp: warning: source file `./src/btree.h' specified more than once
cp: warning: source file `./src/hash.h' specified more than once
cp: warning: source file `./src/sqliteInt.h' specified more than once
cp: warning: source file `./src/vdbe.h' specified more than once
cp: warning: source file `./ext/fts1/fts1.h' specified more than once
cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once
cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once
cp: warning: source file `./src/vdbeInt.h' specified more than once
rm tsrc/sqlite.h.in tsrc/parse.y
cp parse.c opcodes.c keywordhash.h tsrc
tclsh ./tool/mksqlite3c.tcl
couldn't open "tsrc/fts3.h": no such file or directory
 while executing
"open $filename r"
 (procedure "copy_file" line 5)
 invoked from within
"copy_file tsrc/$hdr"
 (procedure "copy_file" line 21)
 invoked from within
"copy_file tsrc/$file"
 ("foreach" body line 2)
 invoked from within
"foreach file {
sqliteInt.h

date.c
os.c

fault.c
mem1.c
mem2.c
mem3.c
mutex.c
mutex_os2.c
mutex_unix.c
mutex_w32.c
   ..."
 (file "./tool/mksqlite3c.tcl" line 201)
make: *** [sqlite3.c] Error 1



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

Re: [sqlite] SQLite 3.5 threading & vfs discussion

[Brad House]

Crap, sent the wrong (older/bad) diff... Attached is
the right one.

Brad House wrote:

It appears as though os_unix.c references pthread routines directly
when SQLITE_THREADSAFE is defined (and not 0).

I think those routines should probably be abstracted like the mutex
routines.  The problem lies if an alternate threading implementation
is preferred on a unix-like system  (as is the case in some embedded
environments), or if the system doesn't support pthreads at all, but
an alternate threading implementation exists (ex. SCO OpenServer 5.0.6) and
you still want to maintain thread-safety.  Currently the only alternative
is to reimplement the entire VFS for Unix when all that is really
desired is to switch out the thread routines.

I've attached a patch for discussion which creates 4 new functions:
sqlite3_thread_create, sqlite3_thread_join, sqlite3_thread_self,
and sqlite3_thread_equal

Their use is roughly equivalent to the pthread_* counterparts.  The
main exception here is that I've expanded the API slightly because
Win32 differentiates between a thread id and a thread handle.
I've included a Win32 example implementation as well, even though
it is not used. 'make test' on Linux appears to return the same results
with this patch as it does without it.

Am I going about resolving this issue wrong? Any other thoughts
on this?

Thanks.
-Brad




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Index: src/mutex_unix.c
===
RCS file: /sqlite/sqlite/src/mutex_unix.c,v
retrieving revision 1.5
diff -u -r1.5 mutex_unix.c
--- src/mutex_unix.c28 Nov 2007 14:04:57 -  1.5
+++ src/mutex_unix.c6 Feb 2008 03:46:28 -
@@ -295,4 +295,27 @@
   return p==0 || p->nRef==0 || pthread_equal(p->owner, pthread_self())==0;
 }
 #endif
+
+int sqlite3_thread_create(sqlite3_threadhandle_t *threadhandle, 
sqlite3_threadid_t *threadid, void *(*start_routine)(void *), void *arg){
+   if (pthread_create((pthread_t *)threadhandle, NULL, start_routine, arg) 
!= 0)
+   return(SQLITE_ERROR);
+   if (threadid)
+   *threadid = *threadhandle;
+   return(SQLITE_OK);
+}
+
+int sqlite3_thread_join(sqlite3_threadhandle_t threadhandle, void **value_ptr){
+   if (pthread_join((pthread_t)threadhandle, value_ptr) != 0)
+   return(SQLITE_ERROR);
+   return(SQLITE_OK);
+}
+
+sqlite3_threadid_t sqlite3_thread_self(){
+   return((sqlite3_threadid_t)pthread_self());
+}
+
+int sqlite3_thread_equal(sqlite3_threadid_t t1, sqlite3_threadid_t t2){
+   return(pthread_equal((pthread_t)t1, (pthread_t)t2));
+}
+
 #endif /* SQLITE_MUTEX_PTHREAD */
Index: src/mutex_w32.c
===
RCS file: /sqlite/sqlite/src/mutex_w32.c,v
retrieving revision 1.5
diff -u -r1.5 mutex_w32.c
--- src/mutex_w32.c 5 Oct 2007 15:08:01 -   1.5
+++ src/mutex_w32.c 6 Feb 2008 03:46:28 -
@@ -216,4 +216,30 @@
 int sqlite3_mutex_notheld(sqlite3_mutex *p){
   return p==0 || p->nRef==0 || p->owner!=GetCurrentThreadId();
 }
+
+
+int sqlite3_thread_create(sqlite3_threadhandle_t *threadhandle, 
sqlite3_threadid_t *threadid, void *(*start_routine)(void *), void *arg){
+   *threadhandle = CreateThread(NULL, 0, 
(LPTHREAD_START_ROUTINE)start_routine, arg, 0, threadid);
+   if (*threadhandle == NULL)
+   return(SQLITE_ERROR);
+   return(SQLITE_OK);
+}
+
+int sqlite3_thread_join(sqlite3_threadhandle_t threadhandle, void **value_ptr){
+   if (WaitForSingleObject((HANDLE)threadhandle, INFINITE) != 
WAIT_OBJECT_O)
+   return(SQLITE_ERROR);
+   if (value_ptr)
+   GetExitCodeThread((HANDLE)threadhandle, (LPDWORD)value_ptr);
+   CloseHandle((HANDLE)threadhandle);
+   return(SQLITE_OK);
+}
+
+sqlite3_threadid_t sqlite3_thread_self(){
+   return((sqlite3_threadid_t)GetCurrentThreadId());
+}
+
+int sqlite3_thread_equal(sqlite3_threadid_t t1, sqlite3_threadid_t t2){
+   return(t1 == t2);
+}
+
 #endif /* SQLITE_MUTEX_W32 */
Index: src/os_unix.c
===
RCS file: /sqlite/sqlite/src/os_unix.c,v
retrieving revision 1.174
diff -u -r1.174 os_unix.c
--- src/os_unix.c   16 Jan 2008 17:46:38 -  1.174
+++ src/os_unix.c   6 Feb 2008 03:46:28 -
@@ -55,15 +55,6 @@
 #endif /* SQLITE_ENABLE_LOCKING_STYLE */
 
 /*
-** If we are to be thread-safe, include the pthreads header and define
-** the SQLITE_UNIX_THREADS macro.
-*/
-#if SQLITE_THREADSAFE
-# include 
-# define SQLITE_UNIX_THREADS 1
-#endif
-
-/*
 ** Default permissions when creating a new file
 */
 #ifndef SQLITE_DEFAULT_FILE_PERMISSIONS
@@ -98,7 +89,7 @@
   unsigned char locktype;   /* The type of lock hel

[sqlite] SQLite 3.5 threading & vfs discussion

[Brad House]

It appears as though os_unix.c references pthread routines directly
when SQLITE_THREADSAFE is defined (and not 0).

I think those routines should probably be abstracted like the mutex
routines.  The problem lies if an alternate threading implementation
is preferred on a unix-like system  (as is the case in some embedded
environments), or if the system doesn't support pthreads at all, but
an alternate threading implementation exists (ex. SCO OpenServer 5.0.6) and
you still want to maintain thread-safety.  Currently the only alternative
is to reimplement the entire VFS for Unix when all that is really
desired is to switch out the thread routines.

I've attached a patch for discussion which creates 4 new functions:
sqlite3_thread_create, sqlite3_thread_join, sqlite3_thread_self,
and sqlite3_thread_equal

Their use is roughly equivalent to the pthread_* counterparts.  The
main exception here is that I've expanded the API slightly because
Win32 differentiates between a thread id and a thread handle.
I've included a Win32 example implementation as well, even though
it is not used. 'make test' on Linux appears to return the same results
with this patch as it does without it.

Am I going about resolving this issue wrong? Any other thoughts
on this?

Thanks.
-Brad

diff -ruN sqlite-3.5.5.orig/src/mutex_unix.c sqlite-3.5.5/src/mutex_unix.c
--- sqlite-3.5.5.orig/src/mutex_unix.c  2008-01-22 21:13:56.0 -0500
+++ sqlite-3.5.5/src/mutex_unix.c   2008-02-05 17:45:25.0 -0500
@@ -295,4 +295,27 @@
   return p==0 || p->nRef==0 || pthread_equal(p->owner, pthread_self())==0;
 }
 #endif
+
+int sqlite3_thread_create(sqlite3_threadhandle_t *threadhandle, 
sqlite3_threadid_t *threadid, void *(*start_routine)(void *), void *arg){
+   if (pthread_create((pthread_t *)threadhandle, NULL, start_routine, arg) 
!= 0)
+   return(SQLITE_ERROR);
+   if (threadid)
+   *threadid = *threadhandle;
+   return(SQLITE_OK);
+}
+
+int sqlite3_thread_join(sqlite3_threadhandle_t threadhandle, void **value_ptr){
+   if (pthread_join((pthread_t)threadhandle, value_ptr) != 0)
+   return(SQLITE_ERROR);
+   return(SQLITE_OK);
+}
+
+sqlite3_threadid_t *sqlite3_thread_self(){
+   return((sqlite3_threadid_t)pthread_self());
+}
+
+int sqlite3_thread_equal(sqlite3_threadid_t t1, sqlite3_threadid_t t2){
+   return(pthread_equal((pthread_t)t1, (pthread_t)t2));
+}
+
 #endif /* SQLITE_MUTEX_PTHREAD */
diff -ruN sqlite-3.5.5.orig/src/mutex_w32.c sqlite-3.5.5/src/mutex_w32.c
--- sqlite-3.5.5.orig/src/mutex_w32.c   2008-01-22 21:13:56.0 -0500
+++ sqlite-3.5.5/src/mutex_w32.c2008-02-05 17:45:30.0 -0500
@@ -216,4 +216,28 @@
 int sqlite3_mutex_notheld(sqlite3_mutex *p){
   return p==0 || p->nRef==0 || p->owner!=GetCurrentThreadId();
 }
+
+
+int sqlite3_thread_create(sqlite3_threadhandle_t *threadhandle, 
sqlite3_threadid_t *threadid, void *(*start_routine)(void *), void *arg){
+   *threadhandle = CreateThread(NULL, 0, 
(LPTHREAD_START_ROUTINE)start_routine, arg, 0, threadid);
+   if (*threadhandle == NULL)
+   return(SQLITE_ERROR);
+   return(SQLITE_OK);
+}
+
+int sqlite3_thread_join(sqlite3_threadhandle_t threadhandle, void **value_ptr){
+   if (WaitForSingleObject((HANDLE)threadhandle, INFINITE) != 
WAIT_OBJECT_O)
+   return(SQLITE_ERROR);
+   GetExitCodeThread((HANDLE)threadhandle, (LPDWORD)value_ptr);
+   return(SQLITE_OK);
+}
+
+sqlite3_threadid_t *sqlite3_thread_self(){
+   return((sqlite3_threadid_t)GetCurrentThreadId());
+}
+
+int sqlite3_thread_equal(sqlite3_threadid_t t1, sqlite3_threadid_t t2){
+   return(t1 == t2);
+}
+
 #endif /* SQLITE_MUTEX_W32 */
diff -ruN sqlite-3.5.5.orig/src/os_unix.c sqlite-3.5.5/src/os_unix.c
--- sqlite-3.5.5.orig/src/os_unix.c 2008-01-22 21:13:56.0 -0500
+++ sqlite-3.5.5/src/os_unix.c  2008-02-05 17:45:20.0 -0500
@@ -55,15 +55,6 @@
 #endif /* SQLITE_ENABLE_LOCKING_STYLE */
 
 /*
-** If we are to be thread-safe, include the pthreads header and define
-** the SQLITE_UNIX_THREADS macro.
-*/
-#if SQLITE_THREADSAFE
-# include 
-# define SQLITE_UNIX_THREADS 1
-#endif
-
-/*
 ** Default permissions when creating a new file
 */
 #ifndef SQLITE_DEFAULT_FILE_PERMISSIONS
@@ -98,7 +89,7 @@
   unsigned char locktype;   /* The type of lock held on this fd */
   int dirfd;/* File descriptor for the directory */
 #if SQLITE_THREADSAFE
-  pthread_t tid;/* The thread that "owns" this unixFile */
+  sqlite3_threadid_t tid;/* The thread that "owns" this unixFile */
 #endif
 };
 
@@ -139,7 +130,7 @@
 ** testing and debugging only.
 */
 #if SQLITE_THREADSAFE
-#define threadid pthread_self()
+#define threadid sqlite3_thread_self()
 #else
 #define threadid 0
 #endif
@@ -161,9 +152,9 @@
 ** transferOwnership() function below for additional information
 */
 #if SQLITE_THREADSAFE
-# define SET_THREADID(X)   (X)->tid = pthread_self()
+# 

Re: [sqlite] thread concurrency, inserts using transactions, bug?

[Brad House]

[EMAIL PROTECTED] wrote:
>> It appears that if 2 threads start transactions at the same time,
>> both inserting into the same table, neither thread can finish until
>> one has rolled back.
> The behavior is deliberate because it gives you, the programmer,
> more control and better concurrency in some situations.  But it
> can also result in the deadlock behavior that you observe.
>
> The plain BEGIN will succeed even if another process is
> already expressed and interest in writing to the database.
> This allows you to read from the database concurrently 
> with the writer, if that is what you want to do. But 
> because another process is already writing, you will not be
> able to write.  And if you try to write, you will get into a 
> deadlock.
> 
> The BEGIN IMMEDIATE, on the other hand, will fail with SQLITE_BUSY
> if another process has already started writing.  It will not
> succeed until the other process commits.  This is the perferred
> thing to do if you intend to write within your transaction.

Hmm, I just modified my test to make each thread write to a separate
table, and the same symptom occurs.  I guess I assumed that this wouldn't
occur with multiple tables, but apparently the lock happens on a
database-wide level.

Now I'm just confused at the reason why transactions have any other
mode besides IMMEDIATE (or EXCLUSIVE) in SQLite.  I don't think any
implementations would use transactions for read-only work, as I
don't believe there is any benefit to using transactions in that
scenario.  I don't understand how _not_ using IMMEDIATE would give
you better concurrency in any situation... If you were performing
read-only queries, and not using a transaction at all, you should
get the same  behavior [as a standard BEGIN transaction, then
performing the read-only query], if there was an outstanding
RESERVED or EXCLUSIVE lock.

Please enlighten me if I am wrong here, I'm still pretty new to
SQLite.

Thanks!
-Brad





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] thread concurrency, inserts using transactions, bug?

[Brad House]

>  Here are my results after modifying the "begin transaction" to a "begin 
> exclusive"
>  Begin transaction is a bit Lazy in that the lock escalation doesnt occur 
> until the pager escalates the lock due to a write.
>  
>  You'll see that the begin exclusive acquires a lock immediately and avoids 
> the behavoir.

Hi Ken, thanks for the reply.

Begin exclusive is not a sufficient solution though as that would have an effect
on _every_ transaction, not just a transaction modifying the same table.  If the
solution is indeed to use 'BEGIN EXCLUSIVE' why then are there different 
transactional
modes at all, considering that would mean SQLite cannot handle anything else?

BTW- I had actually switched my production code to use BEGIN EXCLUSIVE after
finding this bug, that was a few weeks ago, until recently when I had the
time to write the test case, so I was actually aware of that 'workaround'.

Thanks.
-Brad

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] thread concurrency, inserts using transactions, bug?

[Brad House]

I've read http://www.sqlite.org/cvstrac/wiki?p=MultiThreading under the
"Case in point: a benchmark application I've written for this purpose"
and found that current releases of SQLite do not appear to behave in
this manner.  I cannot find any documentation which clearly states
the intended behavior.

It appears that if 2 threads start transactions at the same time,
both inserting into the same table, neither thread can finish until
one has rolled back.

The first thread succeeds until the COMMIT is issued, then returns BUSY
on the COMMIT. The second thread keeps returning BUSY on the INSERT
statement.  I've created a test case which will retry on BUSY (up to
25x to prevent infinite loops).  You'll notice the test ultimately fails.
Also in this test case, I've created a test which does a ROLLBACK when
a BUSY is hit just to show that it does succeed.

Is this intended functionality?  It appears at least a release at some
point in time did not behave this way (Jan 10, 2003 from the Wiki).  Considering
the second thread never gets a successful response to an INSERT statement,
it would seem that it should not have tried to obtain a lock on that table,
preventing the COMMIT from succeeding... but it is... It seems to be a bug
to me.

I have attached the test case.

Any insight would be appreciated.


Here are the results (for both RETRY_BUSY scenarios and ROLLBACK):

$ gcc -Wall -D RETRY_BUSY=1 -W -o sqlitetest sqlitetest.c -l sqlite3
[EMAIL PROTECTED] ~ $ ./sqlitetest
Creating a table
0 => Executing: CREATE TABLE test_table(threadnum INT, cnt INT, testcol TEXT)
0 => started 
1 => started 
all threads started
0 => Executing: BEGIN TRANSACTION
1 => Executing: BEGIN TRANSACTION
0 => Executing: INSERT INTO test_table VALUES(0, 0, 'test0_0')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 1, 'test0_1')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 2, 'test0_2')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 3, 'test0_3')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 4, 'test0_4')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 5, 'test0_5')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 6, 'test0_6')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 7, 'test0_7')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 8, 'test0_8')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: INSERT INTO test_table VALUES(0, 9, 'test0_9')
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => BUSY
0 => Executing: COMMIT
0 => BUSY
1 => Executing: INSERT INTO test_table VALUES(1, 0, 'test1_0')
1 => MAX BUSY CNT
1 => thread failed ...
0 => Executing: COMMIT
0 => finished.
exiting...(test failed)


[EMAIL PROTECTED] ~ $ gcc -Wall -D RETRY_BUSY=0 -W -o sqlitetest sqlitetest.c 
-l sqlite3
[EMAIL PROTECTED] ~ $ ./sqlitetest
Creating a