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

score 3 · Accepted Answer · answered Sep 11 '17 at 02:14

3

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 .

answered Sep 11 '17 at 02:14

Craig Ringer

307,061
76
688
778

I create the lock in method A, but commit in method B. will that still hold the lock ? sorry if i did not explain it properly earlier. – Amit Vig Sep 11 '17 at 02:17
@AmitVig I don't know what your `dbi` object is, or what its `open` method does, so it's kind of hard to answer that. The point is that *a lock is held until the transaction that acquired it commits*. If your first method still held the lock, and your second method is called within a separate DBI connection, it would just block on the first transaction's lock indefinitely. Or if you call it within the same open transaction on the same connection, it'd issue a warning about a transaction already being open, but otherwise work ok. – Craig Ringer Sep 11 '17 at 06:24
so *at a wild guess*, `getAccountBalance` isn't starting an explicit transaction, so it's autocommitted. The lock is thus not held past the return of the statement. But that's making some guesses about what your transaction boundaries are. – Craig Ringer Sep 11 '17 at 06:28
i absolutely forgot to mention what DBI is, i am using JDBI from http://jdbi.org/ to help with the database connections. – Amit Vig Sep 11 '17 at 14:54
@AmitVig So you need to look up transaction management in jdbi then – Craig Ringer Sep 12 '17 at 01:16
I've figured out an approact with using the @Transaction annotation. This annotation makes the entire set of sql i want to run in a single transaction and gets me what i am looking for. Thank you. – Amit Vig Sep 12 '17 at 02:29
@AmitVig Consider writing your own answer with JDBI specifics – Craig Ringer Sep 12 '17 at 04:05

Does closing DBI handle release lock from postgres database

1 Answers1