How to prevent non-repeatable query results using persistence API in Java SE?

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 t开发者_运维百科hrow an exception (then abort)? This would be much cheaper. (From what I understand I do not have pessimistic locking in Toplink Essentials).

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

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.