openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ritika Maheshwari (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-182) db2 update lock syntax WITH <isolation> USE AND KEEP UPDATE LOCKS
Date Thu, 05 Apr 2007 21:06:32 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Ritika Maheshwari updated OPENJPA-182:
--------------------------------------

    Attachment: openjpa182TestCase.jar

Here is a jar containing the 2 entities on whcih I run my testcases.

em.getTransaction().begin()
Query qryA = em.createQuery("select d from DeptBean2 d where d.no = 1");
qryA.setHint("openjpa.hint.updateClause",true);
qryA.setHint("openjpa.hint.isolationLevel", "serializable");
List rsA = qryA.getResultList();

The SQL Output looks like

13109  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing
prepstmnt 694036830 SELECT t0.no, t1.empid, t2.no, t2.mgr_empid, t2.cdname, t1.ceemp_ts, t1.cehireDate,
t1.ceismanager, t1.cename, t1.cesalary, t0.cdname FROM deptab2 t0 LEFT OUTER JOIN emptab2
t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN deptab2 t2 ON t1.dept_no = t2.no WHERE (CAST(t0.no
AS BIGINT) = CAST(? AS BIGINT))  WITH RR USE AND KEEP UPDATE LOCKS  [params=(long) 1]
13119  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [10
ms] spent
27420  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing
prepstmnt 2102295886 SELECT t0.empid, t1.no, t2.empid, t2.dept_no, t2.ceemp_ts, t2.cehireDate,
t2.ceismanager, t2.cename, t2.cesalary, t1.cdname, t0.ceemp_ts, t0.cehireDate, t0.ceismanager,
t0.cename, t0.cesalary FROM emptab2 t0 LEFT OUTER JOIN deptab2 t1 ON t0.dept_no = t1.no LEFT
OUTER JOIN emptab2 t2 ON t1.mgr_empid = t2.empid WHERE t0.dept_no = ?  WITH RR USE AND KEEP
UPDATE LOCKS  [params=(int) 1]
27430  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [10
ms] spent

 Query qryB = em.createQuery("select e from EmpBean2 e where e.empid = 1");
  qryB.setHint("openjpa.hint.updateClause",true);
   qryB.setHint("openjpa.hint.isolationLevel", "read-uncommitted");
    List rsB = qryB.getResultList();

The SQL Output looks like


47969  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing
prepstmnt 1530944320 SELECT t0.empid, t1.no, t2.empid, t2.dept_no, t2.ceemp_ts, t2.cehireDate,
t2.ceismanager, t2.cename, t2.cesalary, t1.cdname, t0.ceemp_ts, t0.cehireDate, t0.ceismanager,
t0.cename, t0.cesalary FROM emptab2 t0 LEFT OUTER JOIN deptab2 t1 ON t0.dept_no = t1.no LEFT
OUTER JOIN emptab2 t2 ON t1.mgr_empid = t2.empid WHERE (CAST(t0.empid AS BIGINT) = CAST(?
AS BIGINT))  WITH RS USE AND KEEP UPDATE LOCKS  [params=(long) 1]
47969  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [0 ms]
spent

Query qryC = em.createQuery("select d from DeptBean2 d where d.no = 1");
 DeptBean2 deptC  = (DeptBean2)qryC.getSingleResult();

The SQL Out put is 

72695  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> executing
prepstmnt 742009914 SELECT t0.no, t1.empid, t2.no, t2.mgr_empid, t2.cdname, t1.ceemp_ts, t1.cehireDate,
t1.ceismanager, t1.cename, t1.cesalary, t0.cdname FROM deptab2 t0 LEFT OUTER JOIN emptab2
t1 ON t0.mgr_empid = t1.empid LEFT OUTER JOIN deptab2 t2 ON t1.dept_no = t2.no WHERE (CAST(t0.no
AS BIGINT) = CAST(? AS BIGINT))  FOR READ ONLY  optimize for 1 row [params=(long) 1]
72695  dwtest  TRACE  [main] openjpa.jdbc.SQL - <t 1094730048, conn 1593466618> [0 ms]
spent

In my persistence.xml I  had the following properties

  <property name="openjpa.LockManager" value="pessimistic" /> 
<property name="openjpa.jdbc.TransactionIsolation" value="read-committed" />

Essentially if  we are running against DB2 8.2 or Later then if update lock is true then for
all the isolation levels other than "serializable"  WITH RS USE AND KEEP UPDATE LOCKS clause
is appended to the query.In case of "serializable" isolation level "WITH RR USE AND KEEP UPDATE
LOCK" is appended.

If the updateLock is false then FOR READ ONLY clause is appended to all queries.




> db2 update lock syntax  WITH <isolation> USE AND KEEP UPDATE LOCKS
> ------------------------------------------------------------------
>
>                 Key: OPENJPA-182
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-182
>             Project: OpenJPA
>          Issue Type: New Feature
>          Components: jdbc
>         Environment: db2 database driver for zOS, AS400, Unix, Windows, Linux
>            Reporter: David Wisneski
>         Assigned To: David Wisneski
>         Attachments: OPENJPA-182.patch, openJPA182.patch, openjpa182TestCase.jar
>
>
> A while back we changed the syntax of update locking from FOR UPDATE OF  to  WITH RS
USE AND KEEP UPDATE LOCKS.   Additional changes are required because 
> 1.  if isolation=serializable is configured, then the syntax should be  WITH RR USE AND
KEEP UDPATE LOCKS
> 2.  when using DB2/400 on iSeries machines, the syntax is WITH RS USE AND KEEP EXCLUSIVE
LOCKS  or WITH RR USE AND KEEP EXCLUSIVE LOCKS because DB2/400 only supports read or exclusive
locks. 
> 3.  DB2 supports both a FETCH FIRST  ROWS and update LOCKS clauses.
> So we change supportsLockingWithSelectRange = true in the AbstractDB2Dictionary class
and change the DB2Dictionary to append the correct LOCKS syntax depending on vendor, release
and isolation level.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message