Queries within a transaction cause an update

RayDeCampo · **Joined:** Mon Nov 20, 2006 2:46 pm **Posts:** 32

I'm seeing some behavior with Hibernate I did not expect. Basically, I load an entity instance from the database, apply some changes to the fields, start a transaction, then run a (select) query; at this point the changes to the entity are being run against the database. I know this because I get a constraint violation (which I was trying to prevent via the query). I haven't called saveOrUpdate() on the modified entity at this point however, so I did not expect it to be persisted. Here's a simplified version of the code:

Code:

Account account = accountDao.loadById(accountId);
account.setEmail("existing-email@example.com");
// Start transaction via Spring annotation
List existing = accountDao.findByEmail(account.getEmail());
// Above line causes org.postgresql.util.PSQLException: 
// ERROR: duplicate key violates unique constraint "account_email_key"

Can someone give me some insight as to what is happening here?

Running Hibernate 3.3.1.GA within JBoss 5.0.1.GA.

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

sometimes it is data in the database that is causing that on the read.

Do 2 things
1. install log4jdbc and paste te exact sql that was run by hibernate
2. that unique column that you have unique via annnotations may not be unique in the database and is causing the issue

ie. it does not mean an update was actually happening.

For instance in hibernate if you have @Column(nullable=false) property
and in the database property can be null as the db is missing the constraint, then when you read it in, hibernate will fail because it violates the constraint(I don't like that feature of hibernate...I wish it only failed on writing out to the db, but it is what it is).

later,
Dean

RayDeCampo · **Joined:** Mon Nov 20, 2006 2:46 pm **Posts:** 32

Using hibernate's show_sql property, I see the update running:

Code:

update account set hversion=?, email=?, password=?, first_name=?, last_name=?, phone_number=?, status=? where account_id=? and hversion=?

I do not see by query for selecting by email running.

I'm not using annotations for hibernate. There is a unique constraint on the database column. There are two rows currently in the database, the one I am changing and the one that contains the conflicting data.

RayDeCampo · **Joined:** Mon Nov 20, 2006 2:46 pm **Posts:** 32

Can anybody help me out here? I am rather puzzled by the behavior and I would like to understand if this is expected and normal or not.

RayDeCampo · **Joined:** Mon Nov 20, 2006 2:46 pm **Posts:** 32

An update: I now realize that Hibernate was operating under flush mode AUTO for the transaction which can cause pending updates to be applied when a find query is run. So Hibernate is behaving as advertised for AUTO flush mode.