Re: [Monotone-devel] RFC: do no exit mtn if database is locked

[Nuno Lucas]

On 10/27/06, Ulf Ochsenfahrt <address@hidden> wrote:
> Nuno Lucas wrote:
> > Also, by always retrying, you can get into a deadlock, as explained in
> > the documentation you have linked to.
>
> I was very surprised by this, and looked at the linked page:
> http://www.sqlite.org/capi3ref.html#sqlite3_busy_handler
>
> It says that retrying could result in a deadlock, when two processes
> simultanesouly try to do lock promotion (dunno if mtn uses lock
> promotion at all, but if so, it must use two different kinds of lock
> promotion in different parts of the code), and to reduce the likelihood
> of deadlock, it will then return an error code to one of the processes
> instead of calling the busy callback.

I'm not an expert on this, but the SQLite locking operation mode is
explained on this link:
http://www.sqlite.org/lockingv3.html

Basically, it means there can be simultaneous read locks, but only one
write lock.
Put it another way, imagine you have two tables like this:

create table a ( id, y );
create table b ( id, z );

where the id field is common reference.

Now if you want to find some id where y=X (for simplicity imagine y is
unique) and then return all z's with that id, and you require
atomicity, you would need to run two selects inside a transaction
(meaning a shared read lock).

If inside that transaction you then do an insert or update, the read
lock is promoted to a write lock, and only a write lock can exist in a
given time.

Now imagine two processes doing exactly the same thing and you can see
that both can have a read lock at the same time and then both try to
acquire a write lock (inside the transaction).

One solution to avoid this is to have an "exclusive" transaction (in
SQL terms: "BEGIN EXCLUSIVE"), so the second simply fails, without
ever acquiring the first read lock.

The downside is that the concurrency level get's lower with this
technique (no one else can read until the transaction ends), but can
be effective for short times (or when it's expected to be small
concurrency).

> I read that as meaning that you can avoid deadlocks in two different ways:
> - don't use lock promotion (I'm guessing that mtn doesn't use that anyway)
> - when you get an error code of SQLITE_BUSY from sqlite, back off and
> release the read lock

You got it right, but refining it a bit:
- You can avoid lock promotions by making all transactions "exclusive"
- You release your read lock by doing a "rollback" on your current
transaction (or retry your SQL query latter).


Well, I hope I got it right, as I'm no expert in this subject too.

Best regards,
~Nuno Lucas