0 Replies Latest reply on Jan 12, 2015 8:49 AM by dpocock

    Patterns for maintaining a tally / counter column with EJB?

    dpocock Newbie

      Consider the following tally entity:



      @Table(uniqueConstraints={@UniqueConstraint(columnNames={"firstname", "lastname"})})

      public class NameCount {


        String firstname;


        String lastname;


        int tally = 0;

      // setters and getters, ...



      The purpose is to count the number of people with the same first and last name.  The constraint exists in the database.


      There is a session bean with a method updateCount(String firstname, String lastname) that maintains the tally.  It checks if the record exists and adds one to the tally or creates a new record.


      When it retrieves an existing row, it calls setLockMode(LockModeType.PESSIMISTIC_WRITE) on the query object to ensure no other thread will be able to update the tally.


      Most of the operations do an UPDATE.  INSERT is more rare.


      Occasionally, however, two INSERTs are attempted concurrently by different threads and there is a constraint violation exception.  The operation is successfully rolled back and executed again.  My own code never sees the exception (so I can't catch it anywhere) as it happens during the commit.


      However, even though it is processing the data correctly, I don't really like having these constraint violation exceptions in the log.


      Is there a more elegant way to maintain a tally column like this with EJB / JPA and without using a stored procedure in the database?