Does closing DBI handle release lock from postgres database

Question

I am trying to write a web application that will run on multiple servers but share a single database for financial transactions.

In simple terms i want to transfer money from account A to B. But there could be multiple requests to transfer money from the same account.

The balance can never be negative, hence i decided to use SELECT FOR UPDATE to fetch the balance to lock the row.

I am using JDBI for a connection to the database : http://jdbi.org/

The code flow is as follows :

Controller :

DBI dbi = new DBI(datasource);

.....
getAccountBalance();

....

transfer()

Here is the DOA part

public int getAccountBalance(String id) {
        Handle h = dbi.open();
        try{
            return h.createQuery("SELECT balance FROM accounts " +
                                " WHERE id=:id FOR UPDATE;")
                    .bind("id", id)
                    .mapTo(Integer.class)
                    .first();
        } finally {
            h.close();
        }
    }

The DAO returns the balance, i run a balance check to see if the transfer can be made and then call another method to perform the transfer.

public void transfer(String fromAccountId, String toAccountId, int transferAmount) {
        Handle h  = dbi.open();
        try{
            h.begin();
            h.createStatement("UPDATE accounts SET balance = balance - :transferAmount WHERE id = :fromAccountId")
                .bind("fromAccountId", fromAccountId)
                .bind("transferAmount", transferAmount)
                .execute();
            h.createStatement("UPDATE accounts SET balance = balance + :transferAmount WHERE id = :toAccountId")
                .bind("toAccountId", toAccountId)
                .bind("transferAmount", transferAmount)
                .execute();
            h.commit();
        } finally {
            h.close();
        }
    }

My question is if i close the handle in getAccountBalance() will it release the lock on the row it selected ? if that's the case how do i hold the lock ? I am new to DBI. thanks

Answer 1

It's actually released before the h.close(), at h.commit(). Row locks are not held beyond commit.

If you need locks that are held past commit you can use advisory locks, but they're a bit awkward, and not something I'd recommend for something like this.

Otherwise read up on pessimistic vs optimistic locking. See also Optimistic vs. Pessimistic locking .