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 (athena.apache.org: local policy)
From: Pavel Bortnovskiy <pbortnovskiy@jefferies.com>
To: Derby Discussion <derby-user@db.apache.org>
Subject: RE: Derby Locks - best practices
Thread-Topic: Derby Locks - best practices
Thread-Index: Ac1AKTvEuWxksHZ6SK6keGIJdT2wvwAJ2mgAAAhWR4A=
Date: Fri, 1 Jun 2012 19:50:42 +0000
Message-ID: 
 <619F13B2042F204E8E8E93D73870255809D07270@EXJSQUSDAG04.ad.jefco.com>
References: 
 <619F13B2042F204E8E8E93D73870255809D07231@EXJSQUSDAG04.ad.jefco.com>
 <CAEYyPh76oMBX0HJKDy7N3-2JWergSGTBQ6pr9JhF7yD3ByT0zQ@mail.gmail.com>
In-Reply-To: 
 <CAEYyPh76oMBX0HJKDy7N3-2JWergSGTBQ6pr9JhF7yD3ByT0zQ@mail.gmail.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Hello, David, thanks for your quick response.

Usually it's one thread "per" in-memory table. Tables can be updated at ran=
dom times and their random rows may be updated, some rows deleted or new ro=
ws inserted. In some other configuration, to avoid deletions, updates and i=
nserts, the in-memory table is truncated and then all the records (the new =
"state" of the source data) are inserted into it.

The thread which runs SQL against all those tables frequently may do a scan=
 of the whole table.

-----Original Message-----
From: David Zanter [mailto:dzanter@gmail.com]
Sent: Friday, June 01, 2012 3:46 PM
To: Derby Discussion
Subject: Re: Derby Locks - best practices

Do mean the scenario of:
    Multiple threads are updating the exact same rows

or
    Multiple threads doing updates to different rows, but due to queries/in=
dexes/etc are causing contention between each other.

On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiy <pbortnovskiy@jefferies.c=
om> wrote:
> Hello, all:
>
>
>
> Derby is used in my application in the in-memory only mode. For a long
> time Derby's lock logic caused no worries, but recently some use cases
> failed with lock timeouts. Thus I'm looking for guidance on best
> practices for handling locks in Derby. A use-case which may cause
> timeouts to obtain a
> lock: one thread is executing an SQL statement which accesses two (or
> more) in-memory tables. Those two tables are being modified by  other
> threads at random times. So, situations in which the SQL is executed
> for a long time and the other threads are frequently updating the
> tables may cause lock timeouts.
>
>
>
> Besides best practices to avoid timeouts and deadlocks, I would like
> to ask the following questions:
>
> 1)      What's the default length of lock timeouts?
>
> 2)      Does my app need another layer of synchronization
> mechanism/locks to avoid attempts to update in-memory tables or execute S=
QLs against them?
>
> 3)      Can my application utilize Derby's locks through some API - to
> query their state or to use them in making a decision of whether to
> batch updates or to execute them, to wait or execute the SQLs?
>
>
>
> Your help would be greatly appreciated,
>
>
>
> Pavel.
>
> Jefferies archives and monitors outgoing and incoming e-mail. The
> contents of this email, including any attachments, are confidential to
> the ordinary user of the email address to which it was addressed. If
> you are not the addressee of this email you may not copy, forward,
> disclose or otherwise use it or any part of it in any form whatsoever.
> This email may be produced at the request of regulators or in
> connection with civil litigation. Jefferies accepts no liability for
> any errors or omissions arising as a result of transmission. Use by
> other than intended recipients is prohibited. In the United Kingdom,
> Jefferies operates as Jefferies International Limited; registered in
> England: no. 1978621; registered office: Vintners Place, 68 Upper
> Thames Street, London EC4V 3BJ. Jefferies International Limited is author=
ised and regulated by the Financial Services Authority.
Jefferies archives and monitors outgoing and incoming e-mail. The contents =
of this email, including any attachments, are confidential to the ordinary =
user of the email address to which it was addressed. If you are not the add=
ressee of this email you may not copy, forward, disclose or otherwise use i=
t or any part of it in any form whatsoever. This email may be produced at t=
he request of regulators or in connection with civil litigation. Jefferies =
accepts no liability for any errors or omissions arising as a result of tra=
nsmission. Use by other than intended recipients is prohibited. In the Unit=
ed Kingdom, Jefferies operates as Jefferies International Limited; register=
ed in England: no. 1978621; registered office: Vintners Place, 68 Upper Tha=
mes Street, London EC4V 3BJ. Jefferies International Limited is authorised =
and regulated by the Financial Services Authority.