This is getting ugly. Consider the following given the server.c example: and using counters to track when a txn is begun/committed. a.. Begin exclusive b.. Begin exclusive (succeeds??? ) a. Insert b. Insert (should be ok since we are in a txn.. however this is on a seperate connection and only one can write at a time... Get a sqlite busy .. b. rollback (because of sqlite busy) This undoes the changes for a... insert.. and terminates the transactions running... a. commit... get a no transaction. So all this does is point glaringly that although Sqlite seems to be "threadsafe" it is far from transcational within a threaded environment.. Any ideas on how to "patch this up"?? The server running statements on behalf of clients in a multi threaded enviornment is appealing since it allows a shared cache for each connection and given "read uncommitted" allows parallel read operations while a write is occuring... I think there needs to be some form of GLOBAL write lock. That would cause the calling client thread to block, when another client thread has the lock. These locks should obviously be enqued such that writer starvation will not occur. It occurs to me that the Sample server.c module is broken. The recomendation is to use transactoins (the doc page on multi threading) however when starting a transaction in this mannter it is not really honored. This lends itself to the idea that the server.c code should allow a "callback function", wherin the callback function could execute begin/ statemtnst....etc... then finally a commit. This would allow functions to enque work, perform their work autonomously and independently. This is really no different than enabling a global reader/writer lock, at the server level opon entry to "begin" however it does have the advantage of guaranteeing no writer starvation and FirstCome First Serve into the sqlite server thread. It would be nicer if sqlite implemented this internally, considering concurrency levels for attached databaes would be improved over a full write lock at a global level. Any other paradigms or thoughts about this? Thanks
Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ken wrote: > I've been looking at the server.c file. And have started some > testing. I'd like to know if the server.c (single process, single > server thread handling incoming requests) can handle SQL statements > such as Begin TXn and Commit? > > From what I've gathered doing a begin exclusive doesnt really seem > to work. I get success on 4 concurrent threads. Then later get a > commit failure indicating "no transaction active".. Be aware that SQLite doesn't support nested transactions. Suppose you get a sequence like BEGIN BEGIN COMMIT COMMIT First BEGIN starts a transaction. Second is simply ignored. First COMMIT ends the transaction. Second commit produces the error you quote. The usual solution is to handle BEGIN and COMMIT statements by maintaining a counter. Increment it when you see a BEGIN, decrement on COMMIT. Only actually execute BEGIN statement when counter moves from 0 to 1, and COMMIT when counter moves from 1 to 0. Igor Tandetnik ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------