Fix for bug 460740. SQLSyntaxException when trying to use
PESSIMISTIC_READ with setFirst/MaxResults against DB2.
Signed-off-by: Rick Curtis <curtisr7@gmail.com>
diff --git a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/DB2Platform.java b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/DB2Platform.java
index b426043..091aae6 100644
--- a/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/DB2Platform.java
+++ b/foundation/org.eclipse.persistence.core/src/org/eclipse/persistence/platform/database/DB2Platform.java
@@ -13,6 +13,8 @@
* - 357533: Allow DDL queries to execute even when Multitenant entities are part of the PU
* 02/19/2015 - Rick Curtis
* - 458877 : Add national character support
+ * 02/24/2016-2.6.0 Rick Curtis
+ * - 460740: Fix pessimistic locking with setFirst/Max results on DB2
*****************************************************************************/
package org.eclipse.persistence.platform.database;
@@ -789,6 +791,10 @@
return true;
}
+ @Override
+ public boolean shouldPrintForUpdateClause() {
+ return false;
+ }
/**
* INTERNAL:
* Print the SQL representation of the statement on a stream, storing the fields
@@ -807,22 +813,26 @@
if ( !(this.shouldUseRownumFiltering()) || ( !(max>0) && !(firstRow>0) ) ){
super.printSQLSelectStatement(call, printer, statement);
+ statement.appendForUpdateClause(printer);
return;
} else if ( max > 0 ){
statement.setUseUniqueFieldAliases(true);
printer.printString("SELECT * FROM (SELECT * FROM (SELECT ");
- printer.printString("EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (");
+ printer.printString("EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (");
call.setFields(statement.printSQL(printer));
printer.printString(") AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ");
printer.printParameter(DatabaseCall.MAXROW_FIELD);
printer.printString(") AS EL_TEMP3 WHERE EL_ROWNM > ");
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
+ // If we have a ForUpdate clause, it must be on the outermost query
+ statement.appendForUpdateClause(printer);
} else {// firstRow>0
statement.setUseUniqueFieldAliases(true);
- printer.printString("SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (");
+ printer.printString("SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (");
call.setFields(statement.printSQL(printer));
printer.printString(") AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM > ");
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
+ statement.appendForUpdateClause(printer);
}
call.setIgnoreFirstRowSetting(true);
call.setIgnoreMaxResultsSetting(true);
diff --git a/jpa/eclipselink.jpa.test.jse/src/org/eclipse/persistence/jpa/test/locking/TestPessimisticLocking.java b/jpa/eclipselink.jpa.test.jse/src/org/eclipse/persistence/jpa/test/locking/TestPessimisticLocking.java
new file mode 100644
index 0000000..99e5cbb
--- /dev/null
+++ b/jpa/eclipselink.jpa.test.jse/src/org/eclipse/persistence/jpa/test/locking/TestPessimisticLocking.java
@@ -0,0 +1,173 @@
+/*******************************************************************************
+ * Copyright (c) 2015 IBM Corporation. All rights reserved.
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
+ * which accompanies this distribution.
+ * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
+ * and the Eclipse Distribution License is available at
+ * http://www.eclipse.org/org/documents/edl-v10.php.
+ *
+ * Contributors:
+ * 02/24/2016-2.6.0 Rick Curtis
+ * - 460740: Fix pessimistic locking with setFirst/Max results on DB2
+ ******************************************************************************/
+package org.eclipse.persistence.jpa.test.locking;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.concurrent.Callable;
+import java.util.concurrent.CountDownLatch;
+import java.util.concurrent.ExecutorService;
+import java.util.concurrent.Executors;
+import java.util.concurrent.Future;
+
+import javax.persistence.EntityManager;
+import javax.persistence.EntityManagerFactory;
+import javax.persistence.LockModeType;
+
+import org.eclipse.persistence.jpa.test.framework.Emf;
+import org.eclipse.persistence.jpa.test.framework.EmfRunner;
+import org.eclipse.persistence.jpa.test.framework.Property;
+import org.eclipse.persistence.jpa.test.locking.model.LockingDog;
+import org.junit.AfterClass;
+import org.junit.Assert;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+
+@RunWith(EmfRunner.class)
+public class TestPessimisticLocking {
+ @Emf(/* createTables = DDLGen.DROP_CREATE,**/ classes = { LockingDog.class, }, properties = { @Property(
+ name = "eclipselink.cache.shared.default", value = "false")})
+ private EntityManagerFactory emf;
+
+ static ExecutorService executor = null;
+
+ @BeforeClass
+ public static void beforeClass() {
+ executor = Executors.newFixedThreadPool(5);
+ }
+
+ @AfterClass
+ public static void afterClass() {
+ executor.shutdownNow();
+ }
+
+ List<LockingDog> dogs;
+
+ @Before
+ public void before() {
+ EntityManager em = emf.createEntityManager();
+ dogs = new ArrayList<LockingDog>();
+ try {
+ em.getTransaction().begin();
+ for (int i = 0; i < 10; i++) {
+ LockingDog ld = new LockingDog();
+ dogs.add(ld);
+ em.persist(ld);
+ }
+ em.getTransaction().commit();
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ }
+ }
+
+ @Test
+ public void testPessimisticFind() throws Exception {
+ EntityManager em = emf.createEntityManager();
+ final EntityManager em2 = emf.createEntityManager();
+ final CountDownLatch cdl = new CountDownLatch(2);
+ try {
+ em.getTransaction().begin();
+ em2.getTransaction().begin();
+
+ LockingDog locked = em.find(LockingDog.class, dogs.get(0).getId(), LockModeType.PESSIMISTIC_READ);
+ Assert.assertNotNull(locked);
+ Callable<LockingDog> blocked = new Callable<LockingDog>() {
+ @Override
+ public LockingDog call() {
+ cdl.countDown();
+ return em2.find(LockingDog.class, dogs.get(0).getId(), LockModeType.PESSIMISTIC_READ);
+ }
+ };
+ Future<LockingDog> future = executor.submit(blocked);
+ cdl.countDown();
+ // Make sure worker is blocked
+ Assert.assertFalse(future.isDone());
+ // Rolling back of tran should allow worker to complete
+ em.getTransaction().rollback();
+ Assert.assertEquals(locked.getId(), future.get().getId());
+
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ if (em2.getTransaction().isActive()) {
+ em2.getTransaction().rollback();
+ }
+ }
+ }
+
+ @Test
+ public void testFirstResultPessimisticRead() throws Exception {
+ EntityManager em = emf.createEntityManager();
+ final EntityManager em2 = emf.createEntityManager();
+ try {
+ em.getTransaction().begin();
+ em2.getTransaction().begin();
+ List<LockingDog> dogs = em.createNamedQuery("find.lockingdogs", LockingDog.class).setLockMode(LockModeType.PESSIMISTIC_READ).setMaxResults(1).setFirstResult(4).getResultList();
+ // This worker should block as he is trying to lock already locked
+ // rows
+ Future<Boolean> result = executor.submit(new Callable<Boolean>() {
+ @Override
+ public Boolean call() throws Exception {
+ List<LockingDog> dogs2 = em2.createNamedQuery("find.lockingdogs", LockingDog.class).setLockMode(LockModeType.PESSIMISTIC_READ).setMaxResults(1).setFirstResult(5).getResultList();
+ return true;
+ }
+ });
+ // Make sure the worker isn't done
+ Assert.assertFalse(result.isDone());
+ em.getTransaction().rollback();
+ // Make sure that the worker obtained the locks and completed
+ Assert.assertTrue(result.get());
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ if (em2.getTransaction().isActive()) {
+ em2.getTransaction().rollback();
+ }
+ em.close();
+ em2.close();
+ }
+ }
+
+ @Test
+ public void testMaxResultPessimisticRead() {
+ EntityManager em = emf.createEntityManager();
+ try {
+ em.getTransaction().begin();
+ List<LockingDog> dogs = em.createNamedQuery("find.lockingdogs", LockingDog.class).setLockMode(LockModeType.PESSIMISTIC_READ).setMaxResults(5).getResultList();
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ }
+ }
+
+ @Test
+ public void testFirstResultMaxResultPessimisticRead() {
+ EntityManager em = emf.createEntityManager();
+ try {
+ em.getTransaction().begin();
+ List<LockingDog> dogs = em.createNamedQuery("find.lockingdogs", LockingDog.class).setLockMode(LockModeType.PESSIMISTIC_READ).setFirstResult(5).setMaxResults(5).getResultList();
+ } finally {
+ if (em.getTransaction().isActive()) {
+ em.getTransaction().rollback();
+ }
+ }
+ }
+}
diff --git a/jpa/eclipselink.jpa.test.jse/src/org/eclipse/persistence/jpa/test/locking/model/LockingDog.java b/jpa/eclipselink.jpa.test.jse/src/org/eclipse/persistence/jpa/test/locking/model/LockingDog.java
new file mode 100644
index 0000000..5da07db
--- /dev/null
+++ b/jpa/eclipselink.jpa.test.jse/src/org/eclipse/persistence/jpa/test/locking/model/LockingDog.java
@@ -0,0 +1,40 @@
+/*******************************************************************************
+ * Copyright (c) 2015 IBM Corporation. All rights reserved.
+ * This program and the accompanying materials are made available under the
+ * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
+ * which accompanies this distribution.
+ * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
+ * and the Eclipse Distribution License is available at
+ * http://www.eclipse.org/org/documents/edl-v10.php.
+ *
+ * Contributors:
+ * 02/24/2016-2.6.0 Rick Curtis
+ * - 460740: Fix pessimistic locking with setFirst/Max results on DB2
+ ******************************************************************************/
+package org.eclipse.persistence.jpa.test.locking.model;
+
+import javax.persistence.Entity;
+import javax.persistence.GeneratedValue;
+import javax.persistence.GenerationType;
+import javax.persistence.Id;
+import javax.persistence.NamedQueries;
+import javax.persistence.NamedQuery;
+import javax.persistence.Version;
+
+@Entity
+@NamedQueries({@NamedQuery(name="find.lockingdogs", query="SELECT d FROM LockingDog d")})
+public class LockingDog {
+ @Id
+ @GeneratedValue(strategy = GenerationType.AUTO)
+ int id;
+
+ @Version
+ int version;
+
+ String name;
+
+ public int getId() {
+ return id;
+ }
+
+}
