On 3 Nov 2013, at 1:24pm, Raheel Gupta <raheel...@gmail.com> wrote: > In order to avoid this, I had to use journal_mode=wal so that two threads > dont have to wait when they both are doing SELECTs which might be taking > 3-5 seconds to process.
I assume you have designed your indexes specifically for your WHERE and ORDER BY clauses. And that you have set an appropriate SQLite time-out so you don't have to handle time-outs in your own programming. > I was wondering if Row Level Locking would be introduced in > journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects > or inserts Row Level rocking should be possible as neither modify the > existing rows. It /might/ be possible to introduce table-level locking, but row-level locking is actually very complicated. Not only do you have to lock that row of the table data, you have to lock that chunk in all the indexes. You also have to make sure that no operation makes changes to any of the indexes which might cause that row to be skipped or duplicated in a scan, which means that locking a row really means locking a range of rows in certain indexes. There are also opportunities for deadlock which need to be dealt with, introducing lots of extra programming. SQLite gets a lot of its speed because it doesn't have to do complicated checking whenever it does things, it just tests to see whether the entire database is locked. Having to continually check for locking on each row would make it far slower. There's a good chance that having to check for locking in every _step() rather than just once per SELECT would actually give you slower results than you currently have. > journal_mode=wal is a little slower and has its own limitations over NFS. NFS access is not suitable for SQLite because locking under NFS is untrustworthy. Please see <http://www.sqlite.org/faq.html#q5> If you really need concurrent multi-computer access then your project may be more suited to a DBMS with server/client architecture. Those are inherently better for NFS because they don't use the NFS for database transactions: the database is actually accessed only from software running on a computer which accesses the file locally. See the last section of <http://www.sqlite.org/whentouse.html> Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
