Hello, I read your paper and it was interesting. I am an advocate for improving the speed of SQLite where ever possible and after reading your paper and applying the experiences of using MS SQL server these are my thoughts.
1. All transactions should be Read Only (no locking) with an option to turn them into write transaction 2. Writes are not applied until the COMMIT and then only after a lock is obtained 3. Refine the locking down to the table level 1. If you change all transactions to be READ Only by default this would cut down the number of locks. The process that parses the query text could then decide if a transaction is write or read-only. So this BEGIN; SELECT * FROM t1; SELECT * FROM t2; COMMIT; Would always be a read-only transaction but this BEGIN; SELECT * FROM t1; -- Some processing occurs here UPDATE t1 SET ... WHERE ...; COMMIT; Would be a write transaction and require a lock. It should add much time to the parser process to determine this up front. There could be an option to override the parser and turn the transaction into a write lock like this BEGIN WRITE; SELECT * FROM t1; SELECT * FROM t2; COMMIT; Would be a write transaction and need to obtain a write lock I would see this as something that would cut down the number of write lock required 2. In MS SQL server we have two types of READs . A normal read and a dirty READ. A clean read is data that is not in the process of changing (i.e in someone else's transaction and therefore locked) A dirty READ may contain data in a transaction and may not be valid. If the transaction rolls back then the read may contain data that is not valid or doesn't exist. Please see the attached Concurrency_Problems.PDF document. If you only apply the changes to the base tables at the commit stage then a write lock is only needed at that time. What the process could be is some thing like this Write go to a temp table in the SQLite DB file. So the write happen as they occur but just not in the base table. Then a write lock is obtained. Some sort of priority locking may be required to force a write lock could be used Then the once the lock is obtained the base table is updated. The temp table is removed and the lock releases The benefits of this is that READs can still occur while writes are taking place (in the temp table) The updating of the base table should be very quick because it would use the PK (ROWID) and not contain complex where statements. 3. Refining the locking from file to table could see more locking take place while reads are going on. I would think that option 1 and 2 are pretty simple option to be put in place (simple in comparing them against table locking) . One premise that I have always held on to with databases are that they are read far more than written too. Allowing read to occur more often and without delay give the impression of a faster database. Kind regards Greg O -----Original Message----- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, 23 November 2003 1:09 AM To: [EMAIL PROTECTED] Subject: [sqlite] Improving concurrency in SQLite Feedback is requested on the paper found at: http://www.sqlite.org/concurrency.html Your analysis and ideas will help to make SQLite a better database engine. Thanks. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Concurrency_Problem.zip
Description: Zip compressed data
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
