From derby-user-return-10937-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon May 04 13:03:05 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 51369 invoked from network); 4 May 2009 13:03:05 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 4 May 2009 13:03:05 -0000 Received: (qmail 86064 invoked by uid 500); 4 May 2009 13:03:04 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 85999 invoked by uid 500); 4 May 2009 13:03:04 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 85991 invoked by uid 99); 4 May 2009 13:03:04 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 May 2009 13:03:04 +0000 X-ASF-Spam-Status: No, hits=-4.0 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.6.21] (HELO gmp-eb-inf-1.sun.com) (192.18.6.21) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 04 May 2009 13:02:54 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-1-fe3.eu.sun.com [192.18.6.10]) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id n44D2LcS015880 for ; Mon, 4 May 2009 13:02:33 GMT MIME-version: 1.0 Content-transfer-encoding: 7BIT Content-type: text/plain; format=flowed; charset=ISO-8859-1 Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java(tm) System Messaging Server 7.0-5.01 64bit (built Feb 19 2009)) id <0KJ400K00DRQW600@fe-emea-09.sun.com> for derby-user@db.apache.org; Mon, 04 May 2009 14:02:21 +0100 (BST) Received: from [129.159.139.223] ([unknown] [129.159.139.223]) by fe-emea-09.sun.com (Sun Java(tm) System Messaging Server 7.0-5.01 64bit (built Feb 19 2009)) with ESMTPSA id <0KJ400B0SEVN2VE0@fe-emea-09.sun.com> for derby-user@db.apache.org; Mon, 04 May 2009 14:02:12 +0100 (BST) Date: Mon, 04 May 2009 15:01:09 +0200 From: Kristian Waagan Subject: Re: Row Locking In-reply-to: <23366698.post@talk.nabble.com> Sender: Kristian.Waagan@Sun.COM To: Derby Discussion Message-id: <49FEE715.6080704@Sun.COM> References: <23333804.post@talk.nabble.com> <49FD6FBD.1040109@Sun.COM> <23366698.post@talk.nabble.com> User-Agent: Thunderbird 2.0.0.21 (X11/20090323) X-Virus-Checked: Checked by ClamAV on apache.org 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 >