I'm building a small application (in Java) and I have arrived to a point where, depending on a field in one my database table the action to be executed has to be completely unique.

I have a Reservation table: user_id, total_price, status (can be ACCEPTED, CANCELED, DENIED or PENDING)

My aim is to ONLY and ONLY refund a reservation when the reservation's status = ACCEPTED. to avoid a reservation to be wrongly refunded twice or more because of multi-threading

Basically my java code would look like this:

private void refund() {
        // simple select
    Reservation r = resevrationDao.get(id)
        if (reservation.getStatus() == ACCEPTED) {
          // 1) call my payment process to make the refund
          // 2) do some DB operations
          // 3) set the reservation as CANCELED and save it in the DB 

Basically I was looking to some mechanism than would set a LOCK when I get the reservation from the DB. So that every other thread/transaction that tries to access (read) that resevrationId are put on 'hold' until the session that has the lock released it by doing a commit or rollback.

I have looked around and with MySQL there is the SELECT ... FOR UPDATE that seems to fit with my problem.

Could someone with more experience than me in this domain confirm that SELECT .. FOR UPDATE is a good way to go ? In case it is, does the transaction isolation of the refund has to be something specific ?

  • 1
    Yes, do that statement to prevent any other transaction from modifying whatever you are selecting.
    – Rick James
    Feb 17, 2016 at 6:40
  • 1
    And surround with whatever Java does for BEGIN...COMMIT.
    – Rick James
    Feb 17, 2016 at 6:41


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.