Question

0

445

Views

Hibernate : Lock a row while update, so user's don't retrieve a counter from it

I am working on a Spring-MVC project in which I am using Hibernate as the ORM, PostgreSQL as our DB and in one of our Objects(GroupCanvas), we have a number which is incremented everytime when user takes some action, and then the GroupCanvas object is updated in DB, and it should be unique.

THe problem we have currently is, if multiple users take action in front-end, some of them are getting duplicate numbers. We are working on fixing this now, so later we can implement a sequence and are assured that the numbers are unique.

How can I ensure that when I am updating the row, other users are waiting till the row is updated. I tried LockMode.Pessimistic_write, and a few others, none helped.

Code :

  @Override
    public void incrementNoteCounterForGroupCanvas(int canvasId) {
        Session session = this.sessionFactory.getCurrentSession();
        session.flush();
        Query query = session.createQuery("update GroupCanvas as gc set gc.noteCount=gc.noteCount+1 where gc.mcanvasid=:canvasId");
        query.setParameter("canvasId",canvasId);
        query.executeUpdate();
        session.flush();
    }

 @Override
    public GroupCanvas getCanvasById(int mcanvasid) {
        Session session = this.sessionFactory.getCurrentSession();
        session.flush();
        return (GroupCanvas) session.get(GroupCanvas.class, mcanvasid,LockMode.PESSIMISTIC_WRITE);
    }

Both methods are in DAO, which has @Transactional annotation, and annotation present in service layer as well.

Thank you.

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-06-13T16:16:30.331Z

Looking at the method you have posted the usage if the 'LOCKING' technique is not quite correct. In order for a lock to end up with the result you are looking for the sequence of actions should be similar to the ones below (in the nutshell it is similar to the Double-Checked Locking but implemented using DB locks - https://en.wikipedia.org/wiki/Double-checked_locking).

Start the transaction (eg @Transactional annotation on your service method)
Retrieve entity from database with the PESSIMISTIC_WRITE lock mode (make sure to indicate hibernate that fresh copy should be read instead of the one stored in session cache)
If required check the current value of the target field if it meets your invariants
Perform the change/update on the field (eg, increment the value of a field )
Save the entity (and make sure to flush the value to the DB if you do not want to wait for the auto-flush)
Commit the transaction (done automatically when using @Transactional)

The essential difference of this sequence when compared with the posted method is that the update of the property value is performed while your transaction holds a lock on the target entity/db row, hence preventing other transactions from reading it while your update is in progress.

Hope this helps .

UPDATE: I believe something like the code snippet bellow should work as expected :




        @Transactional
        @Override
        public void incrementNoteCounterForGroupCanvas(int canvasId) {
            final Session session = this.sessionFactory.getCurrentSession();
            final GroupCanvas groupCanvas = session.get(GroupCanvas.class, canvasId,LockMode.PESSIMISTIC_WRITE);
            session.refresh(groupCanvas);
            groupCanvas.setNoteCount(groupCanvas.getNoteCount()+1);
            session.saveOrUpdate(groupCanvas);
            session.flush();
        }

0

445

Hibernate : Lock a row while update, so user's don't retrieve a counter from it

1 answers

0

Find remote jobs

Andres GPT