[
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.
