Emereson,
And you can't do this with oracle either, That is create a connection and pass
it around between multiple threads.. Oracle would be very very unhappy if you
did that. Oracle utilizes a context variable to distinguish between threads and
utilizes precompiler flags to enable thread support.
Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,
Yes you cannot have multiple threads within the same transaction, and
you cannot pass a connection between threads.
I think we have an undestanding about the performance situation, and
we are getting to the real heart of the issue, which why it is not
possible to have a single transaction, single connection and multiple
threads.
Provided the user synchronises the access to the connection, this
should offer the highest performance. But there is some technical
reason why sqlite has this (in my opinion unusual behaviour). Perhaps
DRH can explain this...
Emerson
On 12/29/06, Ken wrote:
> The test server.c code is an example that has a single thread that performs
> the DB interactions each client thread, communicates via a queue interface.
> Each client will get serialized into the DBserver thread and get its work
> done. Thus eliminating any lower level locking and mutexing inside the
> application code.
>
> Your assumption regarding 1 thread/1 cache is pretty accurate. This is what
> the test_server.c code does, however each client thread does however get a
> database connection handle.
>
> If you have a single global transaction you can do insr/selec/upd/delete..
> The thing is that the connection may not be passed around amongst threads.
>
> You cannot have multiple threads executing within the same transaction!!! To
> my understanding, that is not allowed. Please someone else correct me if I'm
> wrong...
>
> The difference is with other DB's they utilize a finer grain of locking
> internally, either page locking, row locking etc.. Sqlite uses Database
> LOCKING, which is full file. So its really only designed to be used by a
> single thread of execution. (again DRH please correct me here if I'm wrong).
>
>
> Emerson Clarke wrote: Ken,
>
> Thanks.
>
> Ok, i think i did miss the point with your test case. I assumed that
> it was always possible to perform multiple select, insert, delete, and
> update statements within a single transaction anyway. Which i guess
> relates to you last point. I dont see why if i have a single global
> transaction i cant perform all the operations i want within that
> transaction to maximise performance. In fact i know i can. because
> thats what i do with a single thread.
>
> I have always assumed that the simplest and highest performance
> solution would be to have multiple threads with one transaction and
> one cache. I will take a look at the test_server.c code and see if i
> can understand what you are suggesting.
>
> I dont see why writer starvation would be a problem, but again i
> probably just dont understand what your suggesting there. Anyway,
> thanks for your help. I suspect that you are right and the ultimate
> solution will be to write my own synchronisation code, since that will
> allow me to have a consistent api across multiple databases like
> sqlite and mysql.
>
> I could achieve this if i could have multiple threads executing
> statements within the same transaction since that is the effective
> behaviour i get from most other databases which are not in process.
> In those cases it is the process boundary and the tcp/ip connections
> which are effectivley serialising all interactions which is equivalent
> of having only one thread when using sqlite.
>
> Emerson
