On Tue, 2006-08-29 at 21:50 +0200, Frank Schönheit - Sun Microsystems Germany wrote:
[snipped] > > You might have noticed in the lists that there is ongoing work in the > course of the Google Summer of Code - one project we got was exactly a > dedicated SQLite driver. Actually I hadn't. :-) > While the results look pretty good so far, and > the student volunteered to spend time after the SOC to finish the driver > (which is necessary), there are some problems with SQLite which, in my > current understanding, disqualify it for serious use in OOo. Most > notably, in SQLite 3, if you open a prepared statement, the complete > database is locked for further queries until this statement is closed. > This is *hardly* to workaround in OOo. We'll need more investigations on > this, however. This really is a question that should be asked on [email protected] and I recommend that the person doing the work do so. In the meantime, I think that there is a misunderstanding that I will try to dispell with my limited knowledge. I will bet the confusion is with the BEGIN variations. BEGIN IMMEDIATE can avoid deadlocks when used consistently for all transactions that begin with SELECT then proceed to write. Thus, BEGIN IMMEDIATE is better because it allows the writer to work in parallel with readers up to the point where it actually needs to physically write to disk (upon commit, or when in-memory cache fills up and has to written out). However, one problem with BEGIN IMMEDIATE is that you can get a busy timeout at pretty much any point, including COMMIT. So you need to write complex error handling logic to deal with this situation. Imagine a transaction where you need to read some data, perform long a complex calculations and finally update the data. What are you going to do if update times out? Wait for arbitrarily long time, restart from scratch, fail the whole operation altogether? The above scenario can be sort of solved by using BEGIN EXCLUSIVE which moves the point of failure up to the beginning of the transaction. BEGIN EXCLUSIVE statement itself may time out, but once it succeeded, you are the only one touching the database and no timeouts may occur. The downside is, of course, that nobody else may read from the database while you perform your calculations and prepare for writing. So yes, you can lock down any transaction in a prepared statement by locking but I think you would agree that doing so would be bad coding practice. I mean you only want locks for he the duration of any write. I guess the secret to SQLite is that you write code to prevent simultaneous commits. -- G. Roderick Singleton <[EMAIL PROTECTED]> OpenOffice.org
smime.p7s
Description: S/MIME cryptographic signature
