kubawieczorek

} catch (Exception e) {
    connection.rollback();
    System.out.printf("Error when performing transfer %s \n", e.getMessage());
    throw e;
}

kubawieczorek

  ResultSet accountFrom;
  ResultSet accountTo;

  int comparison = bankTransfer.getFrom().compareTo(bankTransfer.getTo());
  if (comparison <= 0) {
      accountFrom = findAccount(bankTransfer.getFrom(), selectAccountStatementFrom);
      accountTo = findAccount(bankTransfer.getTo(), selectAccountStatementTo);
  } else {
      accountTo = findAccount(bankTransfer.getTo(), selectAccountStatementTo);
      accountFrom = findAccount(bankTransfer.getFrom(), selectAccountStatementFrom);

kubawieczorek

  decreaseAccountBalance(updateAccountStatement, accountFrom, amount);
  if (updateAccountStatement.executeUpdate() == 0) {
      throw new RuntimeException("Optimistic locking exception on source account");
  }

kubawieczorek

 private static void decreaseAccountBalance(PreparedStatement updateAccountStatement, ResultSet account, BigDecimal amount) throws SQLException {
      BigDecimal accountBalance = account.getBigDecimal("balance");
      updateAccountStatement.setBigDecimal(1, accountBalance.subtract(amount));
      updateAccountStatement.setLong(2, account.getLong("version") + 1);
      updateAccountStatement.setLong(3, account.getLong("account_id"));
      updateAccountStatement.setLong(4, account.getLong("version"));
  }

kubawieczorek

private static final String UPDATE_ACCOUNT_SQL = "UPDATE accounts SET balance = ?, version = ? WHERE account_id = ? AND version = ?";

kubawieczorek

ALTER TABLE accounts ADD COLUMN version bigint DEFAULT 0;

kubawieczorek

Statement statement = connection.createStatement()) {
List<BankAccount> accountList = new ArrayList<>();
ResultSet resultSet = statement.executeQuery("SELECT * FROM accounts");
while (resultSet.next()) {
    BankAccount bankAccount = new BankAccount();
    bankAccount.setBalance(resultSet.getBigDecimal("balance"));
    bankAccount.setOwner(resultSet.getString("owner"));
    accountList.add(bankAccount);
}
return accountList;

kubawieczorek

  private static final String SELECT_ACCOUNT_BY_OWNER_SQL = "SELECT * FROM accounts WHERE owner = ? FOR UPDATE";

kubawieczorek

connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

kubawieczorek

ALTER DATABASE postgres SET default_transaction_isolation TO 'repeatable read';