You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Ronald Rudy <ro...@gmail.com> on 2010/01/29 13:53:46 UTC
Derby Statements
I hope you can help me with what's probably a basic question, but one I haven't found an answer to elsewhere.
I haven't used Derby extensively, but I do have experience with multithreaded applications with MySQL. I've noticed some interesting but surprising behavior with Derby I was hoping I could get around.
Typically in my app I have a large number of inserts happening from multiple threads as the system functions. I have another thread that runs occasionally updating the status of these records based on queries to an external source; this update may be a record update or a delete of the record.
Initially I had it set up so this "updating" thread was iterating over a scroll insensitive updatable resultset, deleting/updating as needed. But this resulted in a lot more blocking than I can afford: the inserts, which happen frequently - up to say 5-8 a second at times - but nothing that seems it should terribly overburden Derby, would block out the resultset from even stepping to the first record. So instead I had it iterating over a read-only resultset then separately deleting/updating records - with inserts happening from various other threads all the while. (Each thread also has its own pooled connection from DBCP)
What I'm seeing is that under heavy load, I occasionally get "lock could not be obtained.." exceptions. I could ramp up the lock timeout, but I feel like this would be just kicking the problem down the road, and that there shouldn't be any problem with what I'm doing.
To this point, all the inserts are under autocommitting connections, and there's really no way to batch those; they need to be inserted real-time. I can try batching the updates/deletes from iterating over the resultset, but I really would prefer (for requirements of my app) that these be performed real-time. The only solution I've come up with so far is to select a "page" of data to update at a time (like 100 records), then close that resultset/statement and reselect the next "page". It's not critical that the resultset I iterate over include all the data that's been inserted since the initial query was ran, though it would be preferable I suppose.
Any thoughts on what I might be doing wrong or what I can do to make this work a little more reliably? Performance is important, but stability and lower overhead is more important.
-Ron
Re: Derby Statements
Posted by Sai Pullabhotla <sa...@jmethods.com>.
Ron,
Thanks for the hint. I'll have to try it out. Unfortunately, I cannot
batch the inserts due to our app requirements. But I'm pretty sure I
did try it with as little as 5 threads constantly inserting and
deleting records and was able to produce the lock timeout errors. Our
app does not normally read the rows (select statements) from the same
table. A record is inserted. When it is not needed any more, it is
deleted with "delete where primarykey=?". It seems this is the main
difference between your case and mine. When I get a chance, I will put
together a simple repro code and post it.
Regards,
Sai Pullabhotla
On Fri, Jan 29, 2010 at 7:24 AM, Ronald Rudy <ro...@gmail.com> wrote:
> Sai,
>
> When I have large numbers of inserts happening that I can't batch, I end up funneling them through a method that enforces throttling with a Semaphore, so something like:
>
> // 10 permits, fair set to true
> Semaphore throttle = new Semaphore(10, true);
>
> void insertMethod(/* whatever params I need*/) {
> throttle.acquire();
> try {
> // do insert stuff
> }
> finally {
> throttle.release();
> }
> }
>
> That way only 10 concurrent inserts at a time will happen (you can adjust this accordingly) and it will not slam Derby quite so much. It's crude, but effective if you need to avoid lock errors. Perhaps it will help you? It's always better to batch inserts when you can though.
>
> My inserts seem to work fine on their own, but when I have the resultset iterating over the table's contents - a resultset that may be open for a minute or more - with some deletes happening on items within that resultset it seems the 'lock' issues rear their head with more frequency than I can afford.
>
> -Ron
>
>
> On Jan 29, 2010, at 8:14:12 AM, Sai Pullabhotla wrote:
>
>> Ron,
>>
>> Even though I do not have a solution for your problem, I just wanted
>> to let the community know I ran into the same issue. Essentially, I've
>> Tomcat connection pool (DBCP) with 20 max connections connecting to
>> Embedded Derby. Under heavy load (or during load testing), inserting
>> and deleting concurrently from the same table resulted in numerous
>> "Could not obtain lock" errors. The lock timeout was set to 1 min.,
>> which I think should be enough. The table is not very huge either. The
>> errors occur randomly both on inserts and deletes. After a while, none
>> of these inserts and deletes seem to work (meaning all of them end up
>> waiting for a lock, and give up after the lock timeout).
>>
>> Regards,
>> Sai Pullabhotla
>>
>>
>>
>>
>>
>> On Fri, Jan 29, 2010 at 6:53 AM, Ronald Rudy <ro...@gmail.com> wrote:
>>> I hope you can help me with what's probably a basic question, but one I haven't found an answer to elsewhere.
>>>
>>> I haven't used Derby extensively, but I do have experience with multithreaded applications with MySQL. I've noticed some interesting but surprising behavior with Derby I was hoping I could get around.
>>>
>>> Typically in my app I have a large number of inserts happening from multiple threads as the system functions. I have another thread that runs occasionally updating the status of these records based on queries to an external source; this update may be a record update or a delete of the record.
>>>
>>> Initially I had it set up so this "updating" thread was iterating over a scroll insensitive updatable resultset, deleting/updating as needed. But this resulted in a lot more blocking than I can afford: the inserts, which happen frequently - up to say 5-8 a second at times - but nothing that seems it should terribly overburden Derby, would block out the resultset from even stepping to the first record. So instead I had it iterating over a read-only resultset then separately deleting/updating records - with inserts happening from various other threads all the while. (Each thread also has its own pooled connection from DBCP)
>>>
>>> What I'm seeing is that under heavy load, I occasionally get "lock could not be obtained.." exceptions. I could ramp up the lock timeout, but I feel like this would be just kicking the problem down the road, and that there shouldn't be any problem with what I'm doing.
>>>
>>> To this point, all the inserts are under autocommitting connections, and there's really no way to batch those; they need to be inserted real-time. I can try batching the updates/deletes from iterating over the resultset, but I really would prefer (for requirements of my app) that these be performed real-time. The only solution I've come up with so far is to select a "page" of data to update at a time (like 100 records), then close that resultset/statement and reselect the next "page". It's not critical that the resultset I iterate over include all the data that's been inserted since the initial query was ran, though it would be preferable I suppose.
>>>
>>> Any thoughts on what I might be doing wrong or what I can do to make this work a little more reliably? Performance is important, but stability and lower overhead is more important.
>>>
>>> -Ron
>
>
Re: Derby Statements
Posted by Ronald Rudy <ro...@gmail.com>.
On Jan 29, 2010, at 11:33:28 AM, Dag H. Wanvik wrote:
> Derby is always transactional, so even with autocommit, when you open
> a read-only result set for iterating over it, any read locks it sets
> for a row would block another connection trying to modify that row
> (Update or delete). If you use repeatable read, the locks will not be
> released for the rows you have already visited as long as the
> transaction is live, so this would significantly increase the chances
> of update connections blocking for as long as the iteration is going
> on. By moving to read committed isolation level, you should (AFAIK)
> only have one row level read lock set for the row the cursor is
> presently positioned at. It would be interesting to know to do the
> kind of debugging Bryan suggested to determine why the update
> tranactions block, if you could find the time :)
>
> Dag
I've used read_committed (that was the primary one I was trying as it seemed best suited), but I'll see if I can get back to it and find some time. I'd much prefer to iterate over a resultset, deleting those (transactionally) as I iterate over them, all while doing concurrent inserts. I just want to do it without getting lock timeout exceptions :)
Re: Derby Statements
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Ronald Rudy <ro...@gmail.com> writes:
> In my code as it is written today I don't use transactions. When
> I've enabled transactions for the thread iterating over resultsets,
> with various isolations, it didn't seem to help - it either blocked
> out others or never started.
Derby is always transactional, so even with autocommit, when you open
a read-only result set for iterating over it, any read locks it sets
for a row would block another connection trying to modify that row
(Update or delete). If you use repeatable read, the locks will not be
released for the rows you have already visited as long as the
transaction is live, so this would significantly increase the chances
of update connections blocking for as long as the iteration is going
on. By moving to read committed isolation level, you should (AFAIK)
only have one row level read lock set for the row the cursor is
presently positioned at. It would be interesting to know to do the
kind of debugging Bryan suggested to determine why the update
tranactions block, if you could find the time :)
Dag
Re: Derby Statements
Posted by Ronald Rudy <ro...@gmail.com>.
Thanks Bryan, I'm looking into doing a bit more debugging, though I found a solution that throttles my activity sufficiently while I work on other problems. Unfortunately I don't have a lot of time to devote to this particular issue, I was just hoping that perhaps there was something obvious I was doing incorrectly and could adjust.
Nothing like changing the tires while you're racing around the track!
On Jan 29, 2010, at 10:27:39 AM, Bryan Pendleton wrote:
>> The specific exception I'm getting is:
>> org.apache.derby.client.am.SqlException: A lock could not be obtained within the time requested
>
> Derby can be configured to provide a lot of additional
> information which can help you understand why you are
> getting lock timeouts and deadlocks in your application.
>
> Here's a great place to start:
> http://wiki.apache.org/db-derby/LockDebugging
>
> thanks,
>
> bryan
>
>
Re: Derby Statements
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> The specific exception I'm getting is:
>
> org.apache.derby.client.am.SqlException: A lock could not be obtained within the time requested
Derby can be configured to provide a lot of additional
information which can help you understand why you are
getting lock timeouts and deadlocks in your application.
Here's a great place to start:
http://wiki.apache.org/db-derby/LockDebugging
thanks,
bryan
Re: Derby Statements
Posted by Ronald Rudy <ro...@gmail.com>.
I've tried a couple of different isolation levels, specifically read committed and repeatable read since they seemed to be most applicable.
In my code as it is written today I don't use transactions. When I've enabled transactions for the thread iterating over resultsets, with various isolations, it didn't seem to help - it either blocked out others or never started. All my inserts are autocommitting.
There's a fair amount of concurrency with the inserts/deletes (in actuality I rarely use updates - these are small records used for tracking items awaiting confirmation that are purged when confirmation is acknowledged from an external source), so this might be as simple as my code simply burdening Derby with more concurrent inserts/deletes than it can handle (hence the "lock could not be obtained" exceptions)
The specific exception I'm getting is:
org.apache.derby.client.am.SqlException: A lock could not be obtained within the time requested
These are thrown on the threads executing update statements (which are delete or insert SQL statements). When I'm using a read_only resultset it continues reading, I just see a small amount of these errors appear under load. It doesn't seem to happen when I'm not iterating however, only while I'm iterating over the read_only resultset and deleting some portion of those items while doing so.
So there are many threads inserting concurrently, one thread that is iterating over a resultset and deleting records as it prunes acknowledged items. It should be noted that due to some object persistence laying that delete happens using a different connection. When I just have the threads inserting, there are no issues.
On Jan 29, 2010, at 9:58:26 AM, Dag H. Wanvik wrote:
>
> What isolation level are you using? If you just use read committed
> isolation level (default in JDBC/Derby) and a read only result set
> when you iterate, locks should not be kept on the records you have
> already read.
>
> http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts30291.html
>
> Dag
Re: Derby Statements
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
What isolation level are you using? If you just use read committed
isolation level (default in JDBC/Derby) and a read only result set
when you iterate, locks should not be kept on the records you have
already read.
http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts30291.html
Dag
Re: Derby Statements
Posted by Ronald Rudy <ro...@gmail.com>.
Sai,
When I have large numbers of inserts happening that I can't batch, I end up funneling them through a method that enforces throttling with a Semaphore, so something like:
// 10 permits, fair set to true
Semaphore throttle = new Semaphore(10, true);
void insertMethod(/* whatever params I need*/) {
throttle.acquire();
try {
// do insert stuff
}
finally {
throttle.release();
}
}
That way only 10 concurrent inserts at a time will happen (you can adjust this accordingly) and it will not slam Derby quite so much. It's crude, but effective if you need to avoid lock errors. Perhaps it will help you? It's always better to batch inserts when you can though.
My inserts seem to work fine on their own, but when I have the resultset iterating over the table's contents - a resultset that may be open for a minute or more - with some deletes happening on items within that resultset it seems the 'lock' issues rear their head with more frequency than I can afford.
-Ron
On Jan 29, 2010, at 8:14:12 AM, Sai Pullabhotla wrote:
> Ron,
>
> Even though I do not have a solution for your problem, I just wanted
> to let the community know I ran into the same issue. Essentially, I've
> Tomcat connection pool (DBCP) with 20 max connections connecting to
> Embedded Derby. Under heavy load (or during load testing), inserting
> and deleting concurrently from the same table resulted in numerous
> "Could not obtain lock" errors. The lock timeout was set to 1 min.,
> which I think should be enough. The table is not very huge either. The
> errors occur randomly both on inserts and deletes. After a while, none
> of these inserts and deletes seem to work (meaning all of them end up
> waiting for a lock, and give up after the lock timeout).
>
> Regards,
> Sai Pullabhotla
>
>
>
>
>
> On Fri, Jan 29, 2010 at 6:53 AM, Ronald Rudy <ro...@gmail.com> wrote:
>> I hope you can help me with what's probably a basic question, but one I haven't found an answer to elsewhere.
>>
>> I haven't used Derby extensively, but I do have experience with multithreaded applications with MySQL. I've noticed some interesting but surprising behavior with Derby I was hoping I could get around.
>>
>> Typically in my app I have a large number of inserts happening from multiple threads as the system functions. I have another thread that runs occasionally updating the status of these records based on queries to an external source; this update may be a record update or a delete of the record.
>>
>> Initially I had it set up so this "updating" thread was iterating over a scroll insensitive updatable resultset, deleting/updating as needed. But this resulted in a lot more blocking than I can afford: the inserts, which happen frequently - up to say 5-8 a second at times - but nothing that seems it should terribly overburden Derby, would block out the resultset from even stepping to the first record. So instead I had it iterating over a read-only resultset then separately deleting/updating records - with inserts happening from various other threads all the while. (Each thread also has its own pooled connection from DBCP)
>>
>> What I'm seeing is that under heavy load, I occasionally get "lock could not be obtained.." exceptions. I could ramp up the lock timeout, but I feel like this would be just kicking the problem down the road, and that there shouldn't be any problem with what I'm doing.
>>
>> To this point, all the inserts are under autocommitting connections, and there's really no way to batch those; they need to be inserted real-time. I can try batching the updates/deletes from iterating over the resultset, but I really would prefer (for requirements of my app) that these be performed real-time. The only solution I've come up with so far is to select a "page" of data to update at a time (like 100 records), then close that resultset/statement and reselect the next "page". It's not critical that the resultset I iterate over include all the data that's been inserted since the initial query was ran, though it would be preferable I suppose.
>>
>> Any thoughts on what I might be doing wrong or what I can do to make this work a little more reliably? Performance is important, but stability and lower overhead is more important.
>>
>> -Ron
Re: Derby Statements
Posted by Sai Pullabhotla <sa...@jmethods.com>.
Ron,
Even though I do not have a solution for your problem, I just wanted
to let the community know I ran into the same issue. Essentially, I've
Tomcat connection pool (DBCP) with 20 max connections connecting to
Embedded Derby. Under heavy load (or during load testing), inserting
and deleting concurrently from the same table resulted in numerous
"Could not obtain lock" errors. The lock timeout was set to 1 min.,
which I think should be enough. The table is not very huge either. The
errors occur randomly both on inserts and deletes. After a while, none
of these inserts and deletes seem to work (meaning all of them end up
waiting for a lock, and give up after the lock timeout).
Regards,
Sai Pullabhotla
On Fri, Jan 29, 2010 at 6:53 AM, Ronald Rudy <ro...@gmail.com> wrote:
> I hope you can help me with what's probably a basic question, but one I haven't found an answer to elsewhere.
>
> I haven't used Derby extensively, but I do have experience with multithreaded applications with MySQL. I've noticed some interesting but surprising behavior with Derby I was hoping I could get around.
>
> Typically in my app I have a large number of inserts happening from multiple threads as the system functions. I have another thread that runs occasionally updating the status of these records based on queries to an external source; this update may be a record update or a delete of the record.
>
> Initially I had it set up so this "updating" thread was iterating over a scroll insensitive updatable resultset, deleting/updating as needed. But this resulted in a lot more blocking than I can afford: the inserts, which happen frequently - up to say 5-8 a second at times - but nothing that seems it should terribly overburden Derby, would block out the resultset from even stepping to the first record. So instead I had it iterating over a read-only resultset then separately deleting/updating records - with inserts happening from various other threads all the while. (Each thread also has its own pooled connection from DBCP)
>
> What I'm seeing is that under heavy load, I occasionally get "lock could not be obtained.." exceptions. I could ramp up the lock timeout, but I feel like this would be just kicking the problem down the road, and that there shouldn't be any problem with what I'm doing.
>
> To this point, all the inserts are under autocommitting connections, and there's really no way to batch those; they need to be inserted real-time. I can try batching the updates/deletes from iterating over the resultset, but I really would prefer (for requirements of my app) that these be performed real-time. The only solution I've come up with so far is to select a "page" of data to update at a time (like 100 records), then close that resultset/statement and reselect the next "page". It's not critical that the resultset I iterate over include all the data that's been inserted since the initial query was ran, though it would be preferable I suppose.
>
> Any thoughts on what I might be doing wrong or what I can do to make this work a little more reliably? Performance is important, but stability and lower overhead is more important.
>
> -Ron