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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
