Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (hermes.apache.org: domain of bc4all@gmail.com designates
 64.233.166.180 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=fPU5stPwbGd513i4HKictBV9wwGUasAh7AuAg5NUYmjh3HFXUhH3slVt/NwgN3uNNRFcEV+F45foNvS9JcGUx+tyOqgqq4KXDqBYk660U2fdswM9vMiLM1fdP/HHz4I2jZ0y1w2NzIcEC+vVhj0K5wX22QhmHa41JjS8uGepNJ4=
Message-ID: <23074d1f0708010433n8bb1b66odc0efd523869cf44@mail.gmail.com>
Date: Wed, 1 Aug 2007 07:33:08 -0400
From: "Bogdan Calmac" <bc4all@gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: lock escalation and deadlocks
In-Reply-To: <23074d1f0708010416r1f25c9e2i2381cb278a80d33b@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <23074d1f0707312013u3ba3d068o769ae8c11116fd54@mail.gmail.com>
	 <46B0097E.8060302@amberpoint.com>
	 <23074d1f0708010416r1f25c9e2i2381cb278a80d33b@mail.gmail.com>

Oh, and one more observation. The IX table lock for the insert thread
mentions LOCKCOUNT=476. I can only infer the meaning of the column (so
this might perfectly normal), but the number of row locks of that
connection is about 150 (it could not exceed 200, the number of
inserts I do per transaction).

On 8/1/07, Bogdan Calmac <bc4all@gmail.com> wrote:
> Hi Brian,
>
> OK, I see. What I said about escalation is wrong. I looked with new
> eyes at the trace (see a more relevant summary below) and here's what
> appears to happen:
>
>  - 216 (the select thread) holds a lock on 1,1 and waits for a lock on 1,7
>  - 183 (the insert thread) holds many locks, including 1,7
>  - 226 (maybe some db internal) waits for a lock on 1,1
>
> 226 must probably be forked from from 183 (the insert thread), that's
> how the deadlock would make sense.
>
> So the original question remains: Is it expected behaviour for insert
> and select on the same table to deadlock each other? Can you figure
> out the relationship between 183 and 226? As a comparison, the same
> test works fine on other databases with a similar locking approach.
>
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME  |STATE|TABLETYPE
> ------------------------------------------------------------------
> *** The following row is the victim ***
> 216       |ROW          |S   |0        |(1,7)     |WAIT |T
> *** The above row is the victim ***
> 216       |ROW          |S   |1        |(1,1)     |GRANT|T
> 226       |ROW          |X   |0        |(1,1)     |WAIT |T
> 183       |ROW          |X   |1        |(1,7)     |GRANT|T
> 183       |TABLE        |IX  |476      |Tablelock |GRANT|T
> 216       |TABLE        |IS  |2        |Tablelock |GRANT|T
> ... and many more like the row below
> 183       |ROW          |X   |1        |(1,65)    |GRANT|T
>
> Another observation that might be useful for the interpretation is
> that the trace is the same on every run. The record of contention is
> always (1,7)
>
> Thanks,
>
> Bogdan.
>