Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: open-jpa-dev@incubator.apache.org
Message-ID: <27508541.1163052517702.JavaMail.jira@brutus>
Date: Wed, 8 Nov 2006 22:08:37 -0800 (PST)
From: "Igor Fedorenko (JIRA)" <jira@apache.org>
To: open-jpa-dev@incubator.apache.org
Subject: [jira] Commented: (OPENJPA-63) Better pessimistic lock support for
 DB2 v8.2+
In-Reply-To: <22077218.1159985899524.JavaMail.root@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/OPENJPA-63?page=comments#action_12448377 ] 
            
Igor Fedorenko commented on OPENJPA-63:
---------------------------------------

I guess I have not explained the problem I was trying to solve, so let me do that before I answer your specific questions.

Reading objects with ReadLockMode set to LockModeType.WRITE should create database locks necessary to prevent other concurrent transactions from using the same data. Here is a couple of relevant quotes from OpenJPA documentation: "pessimistic transactions generally lock the datastore records they act on, preventing other concurrent transactions from using the same data" and "PessimisticLockManager ... uses SELECT FOR UPDATE statements (or the database's equivalent) to lock the database rows corresponding to locked objects". 

OpenJPA does NOT create any database locks for more complex SELECT statements (with MultipleTables, InnerJoin or OuterJoin). So I am asking to change OpenJPA such that it would be possible to prevent other concurrent transactions from using the same data with SELECT statements with MultipleTables, InnerJoin or OuterJoin for DB2 versions that support this.

Now to your questions.

o EXCLUSIVE vs UPDATE locks. Exposing fine-grained lock levels supported by DB2 is not necessary to solve this particular problem and should be tracked as a separate bugreport (if there is a need to provide such support in OpenJPA). Using UPDATE locks does seem more appropriate (see http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0005274.htm) and I will provide updated patch.

o Use FOR READ ONLY with optimistic transactions. Indeed, this should be a separate enhancement request. I will update the patch not to include this (potential) performance optimization.

o Different isolation levels. Exposing different isolation levels is not necessary to solve this particular problem. For consistancy sake, I will update the patch to use the same isolation level as used in AbstractDB2Dictionary.

o Solid generic clause. I believe that "WITH RR USE AND KEEP EXCLUSIVE LOCKS" is the solid generic clause to address this particular locking problem. At very least it is much more generic than the very limited "FOR UPDATE WITH RR" clause current in use by OpenJPA.

o Use of JDBC 3.0 methods. Judging by "IBM DB2 JDBC 2.0 Type 2" log message, you're using old CLI DB2 JDBC which is not supported according to OpenJPA documentation (and is going to be removed from DB2 soon, according to the rumors). I will update the patch to fall back to current lock behaviour with JDBC 2.x and earlier drivers. I will test the patch with both CLI and JCC drivers with 8.2 and 9.1 and mayby with 8.1 too (I think I still have it around somewhere).

Hope this helps.

> Better pessimistic lock support for DB2 v8.2+
> ---------------------------------------------
>
>                 Key: OPENJPA-63
>                 URL: http://issues.apache.org/jira/browse/OPENJPA-63
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: jdbc
>         Environment: IBM DB2 UDB v8.2 or later
>            Reporter: Igor Fedorenko
>         Attachments: db2-selectForUpdate.patch
>
>
> There is new SELECT "FOR READ ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS" syntax in DB2 v8.2 and later that can be used to implement pessimistic locks for selects with multiple from tables, subselects, inner/outer joins and so forth. I'll attach simple patch shortly.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira