views:

84

answers:

2

I am using Java SE and learning about the use of a persistence API (toplink-essentials) to manage entities in a Derby DB. Note: this is (distance learning) university work, but it is not 'homework' this issue crops up in the course materials.

I have two threads operating on the same set of entities. My problem is that every way I have tried, the entities within a query result set (query performed within a transaction) in one thread can be modified so that the result set is no longer valid for the rest of the transaction.

e.g. from one thread this operation is performed:

static void updatePrices(EntityManager manager, double percentage) {
    EntityTransaction transaction = manager.getTransaction();

    transaction.begin();
    Query query = manager.createQuery("SELECT i FROM Instrument i where i.sold = 'no'");
    List<Instrument> results = (List<Instrument>) query.getResultList();

    // force thread interruption here (testing non-repeatable read)
    try { Thread.sleep(2000); } catch (Exception e) { }

    for (Instrument i : results) {
        i.updatePrice(percentage);
    }
    transaction.commit();
    System.out.println("Price update commited");
}

And if it is interrupted from another thread with this method:

private static void sellInstrument(EntityManager manager, int id)
{
    EntityTransaction transaction = manager.getTransaction();
    transaction.begin();
    Instrument instrument = manager.find(Instrument.class, id);
    System.out.println("Selling: " + instrument.toFullString());
    instrument.setSold(true);
    transaction.commit();
    System.out.println("Instrument sale commited");
}

What can happen is that when the thread within updatePrices() resumes it's query resultSet is invalid, and the price of a sold item ends up being updated to different price to that at which it was sold. (The shop wishes to keep records of items that were sold in the DB). Since there are concurrent transactions occuring I am using a different EntityManager for each thread (from the same factory).

Is it possible (through locking or some kind of context propagation) to prevent the results of a query becoming 'invalid' during a (interrupted) transaction? I have an idea that this kind of scenario is what Java EE is for, but what I want to know is whether its doable in Java SE.


Edit:

Taking Vineet and Pascal's advice: using the @Version annotation in the entity's Class (with an additional DB column) causes the large transaction ( updatePrices() ) to fail with OptimisticLockException. This is very expensive if it happens at the end of a large set of query results though. Is there any way to cause my query (inside updatePrices() ) to lock the relevant rows causing the thread inside sellInstrument() to either block or abort throw an exception (then abort)? This would be much cheaper. (From what I understand I do not have pessimistic locking in Toplink Essentials).

+1  A: 

You might want to take a look at the EntityManager.lock() method, which allows you to obtain an optimistic or a pessimistic lock on an entity once a transaction has been initialized.

Going by your description of the problem, you wish to lock the database record once it has been 'selected' from the database. This can be achieved via a pessimistic lock, which is more or less equivalent to a SELECT ... FROM tbl FOR UPDATE statement.

Vineet Reynolds
Thanks Vineet, for the 1st answer to my first SO question! btw I have no ability to up-vote yet! In that case I need to lock all 'selected' records/entities within updatePrices(), but how can I be sure that I get the locks before another thread interrupts and modifies one of the selected records? Do you know of a way to cause the lock to happen atomically with the query?
willjcroz
You might need to acquire locks in all such scenarios, not just one. That way, should any thread acquire a lock before another, the second transaction will in effect be processed second. You would have to leave it to the JPA provider and the database to sort the transaction order. There is no benefit obtained by attempting to sort the transactions yourself; just acquire the locks before the objects are queried and you should be good to go. However, this brings up the interesting part of ensuring that locks are not held for too long. That would be another question...
Vineet Reynolds
The thing is, there is a large transaction and a small transaction occurring concurrently here. If possible I want the small transaction (sellInstrument() ) to fail as a result of it accessing an entity already part of another transaction's query results.
willjcroz
+2  A: 

Thread safety

I have a doubt about the way you manage your EntityManager. While a EntityManagerFactory is thread-safe (and should be created once at the application startup), an EntityManager is not and you should typically use one EntityManager per thread (or synchronize accesses to it but I would use one per thread).

Concurrency

JPA 1.0 supports (only) optimistic locking (if you use a Version attribute) and two lock modes allowing to avoid dirty read and non repeatable read through the EntityManager.lock() API. I recommend to read Read and Write Locking and/or the whole section 3.4 Optimistic Locking and Concurrency of the JPA 1.0 spec for full details.

PS: Note that Pessimistic locking is not supported in JPA 1.0 or only through provider specific extensions (it has been added to JPA 2.0, as well as other locking options). Just in case, Toplink supports it through the eclipselink.pessimistic-lock query hint.


As written in the JPA wiki, TopLink Essentials is supposed to support pessimistic locking in JPA 1.0 via a query hint:

// eclipselink.pessimistic-lock
Query Query = em.createQuery("select f from Foo f where f.bar=:bar");
query.setParameter("bar", "foobar");
query.setHint("eclipselink.pessimistic-lock", "Lock");
query.getResultList();

I don't use TopLink so I can't confirm this hint is supported in all versions. If it isn't, then you'll have to use a native SQL query if you want to generate a "FOR UPDATE".

Pascal Thivent
Thanks for the pointers Pascal, FWIW I am using one EntityManager per thread (both from the same EntityManagerFactory). Since I am currently stuck with toplink-essentials/JPA 1.0 I will try to do the optimistic locking then.
willjcroz
Thanks again, I got mixed up by JPA 1.0 spec and thinking only Toplink paid for product could do pessimistic locking.
willjcroz