> > The point is that when two threads or > > processes are trying to write at the same time, one of the two > > must back off, abandon their transaction (using ROLLBACK) and let > > the other proceed. > > And how can this be done? What if there are more threads involved? Who > decides? >
I found the document http://www.sqlite.org/lockingv3.html provides a very good discussion of the locking for Version 3. Only one thread can hold a PENDING lock, this is the thread that is trying to commit it's transaction (i.e. get an EXCLUSIVE lock). Other threads trying to update can only have SHARED locks and be trying to get RESERVED locks. The change in 3.0.4 means that when using a busy handler (e.g. sqlite3_busy_timeout) the threads trying to get RESERVED locks will not retry, but instead will return immediately with SQLITE_BUSY. If these transactions are rolled back the thread with the PENDING lock is free to proceed when the busy handler retries the lock.
