>> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere.
I definitely don't agree here as we're talking about these additional locks existing _only_ in memory, not on disk. There'd be no reason to implement on-disk locking or even notifying the OS of sections of the file which are locked since we're only talking about multiple threads in the same process. Any other process would hit the OS file lock and be forced to wait. The overhead of in-memory locking going to be extremely minimal, and only affect those who specifically enable this fine-grained locking. That said, I do think the on-disk journal file format might need to change to accomplish even this, and I think that is probably the biggest show stopper. I'm not suggesting that this would be easy to implement either, and yes, you'd need to figure out if SQLite will block on a lock, or return BUSY, but by limiting the implementation scope to multithreaded applications, it at least makes the implementation feasible, and would provide great benefit to many users of SQLite. In our own synthetic benchmark of our application, which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but 1000 txns/sec with multiple connections to MySQL. Biggest difference here is MySQL allows multiple writers. (That said, I need to actually try to benchmark MySQL with only 1 connection to be able to normalize those numbers a bit). Typically though, those with large transaction volumes are going to go to some other database besides SQLite for other features of a server-based engine, like replication. Not complaining here though, SQLite definitely fits the bill for the default database of our application. What it does, it does well! -Brad _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users