Dealing with deadlocks in long-running Hibernate transactions

I have a Hibernate application that may produce concurrent inserts and updates (via Session.saveOrUpdate) to records with the same primary key, which is assigned. These transactions are somewhat long-running, perhaps 15 seconds on average (since data is collected from remote sources and persisted as it comes in). My DB isolation level is set to Read Committed, and I'm using MySQL and InnoDB.

The problem is this scenario creates excessive lock waits which timeout, either as a result of a deadl开发者_开发百科ock or the long transactions. This leads me to a few questions:

• Does the database engine only release its locks when the transaction is committed?
• If this is the case, should I seek to shorten my transactions?
• If so, would it be a good practice to use separate read and write transactions, where the write transaction could be made short and only take place after all of my data is gathered (the bulk of my transaction length involves collecting remote data).

Edit:

Here's a simple test that approximates what I believe is happening. Since I'm dealing with long running transactions, commit takes place long after the first flush. So just to illustrate my situation I left commit out of the test:

@Entity
static class Person {
    @Id
    Long id = Long.valueOf(1);
    @Version
    private int version;
}

@Test
public void updateTest() {
    for (int i = 0; i < 5; i++) {
        new Thread() {
            public void run() {
                Session s = sf.openSession();
                Transaction t = s.beginTransaction();
                Person p = new Person();
                s.saveOrUpdate(p);
                s.flush(); // Waits...
            }
        }.run();
    }
}

And the queries that this expectantly produces, waiting on the second insert:

select id, version from person where id=?
insert into person (version, id) values (?, ?)
select id, version from person where id=?
insert into person (version, id) values (?, ?)

---

That's correct, the database releases locks only when the transaction is committed. Since you're using hibernate, you can use optimistic locking, which does locks the database for long periods of time. Essentially, hibernate does what you suggest, separating the reading and writing portions into separate transactions. On write, it checks that the data in memory has not been changed concurrently in the database.

• Hibernate Reference - Optimistic Transactions

---

Opportunistic locking: Base assumption: update conflicts do occur seldom. Mechanic:

1. Read dataset with version field

2. Change dataset

3. Update dataset 3.1.Read Dataset with current Version field and key If you get it, nobody has changed the record. Apply the next version field value. update the record. If you do not get it, the record has been changed, return en aproriate message to the caller and you are done

4. Inserts are not affected, you either have a separate primary key anyway or you accept multiple record with identical values. Therefore the example given above is not a case for optimistic locking.