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 wfalby <wf...@hotmail.com> on 2009/05/01 16:16:53 UTC
Row Locking
My application processes events. These events are sent to registered users.
These events can be deleted when they reach a certain age. There is a thread
to send events to registered users and there is another thread to delete old
events. The thread sending an event should lock it from the thread that
deletes old events. Each thread uses its own connection to the database. In
the sending thread, I've tried using a SELECT ... FOR UPDATE and setting the
ResultSet.CONCUR_UPDATABLE on the prepare statement. In both cases, the
thread that deletes events deleted the event being held by the sending
thread. The default isolation level and autocommit options are used.
Thanks in advance...Walter
--
View this message in context: http://www.nabble.com/Row-Locking-tp23333804p23333804.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Row Locking
Posted by Kristian Waagan <Kr...@Sun.COM>.
wfalby wrote:
>
> Kristian Waagan-4 wrote:
>
>> wfalby wrote:
>>
>>> My application processes events. These events are sent to registered
>>> users.
>>> These events can be deleted when they reach a certain age. There is a
>>> thread
>>> to send events to registered users and there is another thread to delete
>>> old
>>> events. The thread sending an event should lock it from the thread that
>>> deletes old events. Each thread uses its own connection to the database.
>>> In
>>> the sending thread, I've tried using a SELECT ... FOR UPDATE and setting
>>> the
>>> ResultSet.CONCUR_UPDATABLE on the prepare statement. In both cases, the
>>> thread that deletes events deleted the event being held by the sending
>>> thread. The default isolation level and autocommit options are used.
>>>
>>>
>> Hi Walter,
>>
>> If you're using the read committed isolation level, I think you need to
>> use SELECT ... FOR UPDATE WITH RS.
>> Which behavior do you see if you do that?
>>
>> I think Derby treats SELECT ... FOR UPDATE a bit differently than some
>> other database systems, and I also believe there is at least one Jira
>> [1] issue logged for changing the behavior.
>>
>>
>> Regards,
>> --
>> Kristian
>>
>> [1] https://issues.apache.org/jira/browse/DERBY
>>
>>> Thanks in advance...Walter
>>>
>>>
>>
>>
>
> Kristian:
>
> Thanks for the info. I tried your suggestion, but all rows are deleted. I'll
> check the Jira issue to see if it fits my situation.
>
Reading your post again, I see that you're using the default autocommit
option. The default in Derby is that autocommit is on.
I haven't seen your code, but doesn't that mean that the locks will be
released immediately, most likely before the sending thread has finished
its work?
One way to debug this would be to query the lock table to verify that
the sending thread actually causes an exclusive lock to be set. There is
some information about this at http://wiki.apache.org/db-derby/LockDebugging
HTH,
--
Kristian
> Walter
>
Re: Row Locking
Posted by wfalby <wf...@hotmail.com>.
Kristian Waagan-4 wrote:
>
> wfalby wrote:
>> My application processes events. These events are sent to registered
>> users.
>> These events can be deleted when they reach a certain age. There is a
>> thread
>> to send events to registered users and there is another thread to delete
>> old
>> events. The thread sending an event should lock it from the thread that
>> deletes old events. Each thread uses its own connection to the database.
>> In
>> the sending thread, I've tried using a SELECT ... FOR UPDATE and setting
>> the
>> ResultSet.CONCUR_UPDATABLE on the prepare statement. In both cases, the
>> thread that deletes events deleted the event being held by the sending
>> thread. The default isolation level and autocommit options are used.
>>
>
> Hi Walter,
>
> If you're using the read committed isolation level, I think you need to
> use SELECT ... FOR UPDATE WITH RS.
> Which behavior do you see if you do that?
>
> I think Derby treats SELECT ... FOR UPDATE a bit differently than some
> other database systems, and I also believe there is at least one Jira
> [1] issue logged for changing the behavior.
>
>
> Regards,
> --
> Kristian
>
> [1] https://issues.apache.org/jira/browse/DERBY
>> Thanks in advance...Walter
>>
>
>
>
Kristian:
Thanks for the info. I tried your suggestion, but all rows are deleted. I'll
check the Jira issue to see if it fits my situation.
Walter
--
View this message in context: http://www.nabble.com/Row-Locking-tp23333804p23366698.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Row Locking
Posted by Kristian Waagan <Kr...@Sun.COM>.
Dag H. Wanvik wrote:
> Kristian Waagan <Kr...@Sun.COM> writes:
>
>
>> [1] https://issues.apache.org/jira/browse/DERBY
>>
> DERBY-?
> The issue number fell out here?
>
Not really, I just didn't remember the issue number off the top of my
head :)
FYI, here it is: https://issues.apache.org/jira/browse/DERBY-3900
--
Kristian
> Dag
>
Re: Row Locking
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Kristian Waagan <Kr...@Sun.COM> writes:
> [1] https://issues.apache.org/jira/browse/DERBY
DERBY-?
The issue number fell out here?
Dag
Re: Row Locking
Posted by Kristian Waagan <Kr...@Sun.COM>.
wfalby wrote:
> My application processes events. These events are sent to registered users.
> These events can be deleted when they reach a certain age. There is a thread
> to send events to registered users and there is another thread to delete old
> events. The thread sending an event should lock it from the thread that
> deletes old events. Each thread uses its own connection to the database. In
> the sending thread, I've tried using a SELECT ... FOR UPDATE and setting the
> ResultSet.CONCUR_UPDATABLE on the prepare statement. In both cases, the
> thread that deletes events deleted the event being held by the sending
> thread. The default isolation level and autocommit options are used.
>
Hi Walter,
If you're using the read committed isolation level, I think you need to
use SELECT ... FOR UPDATE WITH RS.
Which behavior do you see if you do that?
I think Derby treats SELECT ... FOR UPDATE a bit differently than some
other database systems, and I also believe there is at least one Jira
[1] issue logged for changing the behavior.
Regards,
--
Kristian
[1] https://issues.apache.org/jira/browse/DERBY
> Thanks in advance...Walter
>