On 01.06.2012 21:50, Pavel Bortnovskiy wrote:
Hello, David, thanks for your quick response.
Usually it's one thread "per" in-memory table. Tables can be updated at random times and
their random rows may be updated, some rows deleted or new rows inserted. In some other
configuration, to avoid deletions, updates and inserts, the in-memory table is truncated and then
all the records (the new "state" of the source data) are inserted into it.
For clarity, are you referring to using Derby's memory subprotocol when
talking about in-memory tables? As an example, that would be
'jdbc:derby:memory:mydb;create=true', as opposed to the on-disk version
that would be 'jdbc:derby:mydb;create=true'.
In terms of locking there is nothing special about in-memory database in
Derby, except for the likely event that some operations may be faster
in-memory than on-disk (which could affect timing, but many other things
can do that too).
Two common pieces of advice when it comes to locking is to reduce the
duration of the locks, and to reduce the scope/granularity of the locks.
There may also be application specific considerations to take, like
acceptable isolation levels, access patterns and schema design.
In general your application should be prepared to handle lock timeouts,
whereas deadlocks indicate that the access/lock patterns of your
application need to be improved.
--
Kristian
The thread which runs SQL against all those tables frequently may do a scan of
the whole table.
-----Original Message-----
From: David Zanter [mailto:[email protected]]
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/indexes/etc are causing contention between each other.
On Fri, Jun 1, 2012 at 3:16 PM, Pavel Bortnovskiy<[email protected]>
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 SQLs
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 authorised
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 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 authorised and regulated by the
Financial Services Authority.
