I really think locking rows with triggers is the way to go. In fact, even if it were built into SQLite itself, I would think the implementation would be something like a trigger. To be more specific, triggers basically let you insert your own hooks right into the VM, so you are guaranteed that the execution of an UPDATE or DELETE that fails because of a locked record will unwind itself correctly.
The other cool thing about using triggers to do record locking is that there is zero overhead if there are no locks.
I can see some advantages to this, which is basically making the application implement the locks, while having this done in an elegant fashion.
SQLite itself is kept a lot simpler. Also, each application can easily customize the granularity of the locks and other related details, so that they work best for the situation; eg, one can simply mark a parent record as locked and the trigger will enforce that its children are also locked at the same time.
That said, if this were a large database engine, there wouldn't be any excuse to leave this feature out of the core.
-- Darren Duncan
