Ensuring unique constraint in business logic fails

s.goetz · **Posted:** Tue Apr 16, 2013 7:55 am

Dear board users,

I have an entity model that uses the @UniqueConstraint annotation to define restrictions on the persisted data. Now I am running into the problem that I have to manipulate data to have one of these constraints satisfied. But its hard to explain just with words so I will give you a code snippet:

1. Reader entity

Code:

@Table(name = "Reader", uniqueConstraints = @UniqueConstraint(columnNames = { Reader.PROPERTY_BUS_ADDRESS, Reader.PROPERTY_HARDWARE_INTERFACE }))
@Audited
public class Reader extends AuthComponent
{
   public static final String PROPERTY_HARDWARE_INTERFACE = "hardwareInterface";
   public static final String PROPERTY_BUS_ADDRESS = "busAddress";

   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = PROPERTY_HARDWARE_INTERFACE, nullable = false)
   private HardwareInterface hardwareInterface;

   @Column(name = PROPERTY_BUS_ADDRESS, nullable = false)
   @Range(min = -1, max = 3)
   private Integer busAddress = 0;

   // Getters, setters and some more properties
}

As you can see this entity has a relation to another one called HardwareInterface and a plain integer called busAddress. Besides that there is a unique constraint one these two columns.
Now I have a constellation that I have four of those 'readers' with busAddresses 0, 1, 2, 3. The user changes the busAddress of one of these four to a value that is already assigned to another one. So this would result in a unique constraint violation. Now I thought of first assigning a dummy address (-1) to the conflicting entity, setting the desired value and then setting the conflicting entity's address to the original one.

Example:

Code:

READER
id    hardwareInterface    busAddress
1     1                    0
2     1                    1
3     1                    2
4     1                    3

Exchange addresses of reader 3 and 4:
1. Reader [id=3].busAddress = -1 => session.saveOrUpdate()
2. Reader [id=4].busAddress = 2 => session.saveOrUpdate()
3. Reader [id=3].busAddress = 3 => session.saveOrUpdate()

All this happens within the same transaction. For some reason hibernate first issues the update for the reader [id=4] although it is the second update caused in the code sequence below:

Code:

protected void resolveAddressConflict() throws NestedException
   {
      Reader conflicting = readerService.findByInterfaceAddress(reader);
      if (conflicting != null)
      {
         final int dummyAddress = -1;
         Reader currentOldState = readerService.auditFindByIdVersion(reader.getId(), reader.getVersion());
         if (currentOldState != null)
         {
            conflicting.setBusAddress(dummyAddress);
            readerService.save(conflicting);

            readerService.save(reader);

            conflicting.setBusAddress(currentOldState.getBusAddress());
            readerService.save(conflicting);

            addGuiMessage("autoResolvedAddressConflict", conflicting.getName(), conflicting.getBusAddress());
         }
         else
         {
            addGuiError("errorResolvedAddressConflict", conflicting.getName());
         }
      }
   }

This results in the exception:

Quote:

com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint 'UQ__Reader__4394C9994BCC3ABA'. Cannot insert duplicate key in object 'dbo.Reader'.

Which is quite logical.

Can anyone assist?

Greets Sebastian

s.goetz · **Posted:** Wed Apr 17, 2013 9:33 am

Hmm, did I explain this to complicated? I did think of this as something quite simple. Unfortunately I haven't been able to resolve this yet.

s.goetz · **Posted:** Wed Jul 03, 2013 7:17 am

I added a Session.flush() to the readerService.save() and now it works. Seems like Hibernate is optimizing the updates to one single action. This might cause the problem I guess.