Apache Derby Database Select-for-Update

Abstract

Select-for-update is an SQL feature which I used to use all the time, but it seems like it’s use has fallen out of favor. However, there are valid use cases for it.

My ferris-resiste project is an RSS to email system. The system keeps track of all RSS entries it encounters to prevent emailing duplicates. However, how long do you keep this history of RSS entries? RSS data isn’t 100% reliable, so the system has its own way of determining when to delete RSS entries. Each time an RSS entry is encountered, the date it’s encountered is saved in the database. If an RSS entry isn’t encountered, that date isn’t updated. Deleting RSS entries is then a simple query which use this last encountered date to delete entries older than 6 months. If the RSS feed hasn’t had that RSS entry for the past 6 months, it’s probably safe to assume the system will not encounter it again.

This is a perfect use case for a select-for-update SQL statement. The purpose of this post is to demonstrate how a select-for-update statement works for the Apache Derby database.

Disclaimer

This post is solely informative. Critically think before using any information presented. Learn from it but ultimately make your own decisions at your own risk.

Code

Listing 1 is Java code demonstrating select-for-update for Apache Derby.

Listing 1 - Java select for update code for Apache Derby

public Optional<RssHistory> find(String feedId, String entryId) {
  
  log.info(String.format("Find RSS entry history feedId=\"%s\", entryId=\"%s\"", feedId, entryId));
  
  Optional<RssHistory> retval
    = Optional.empty();

  StringBuilder sp = new StringBuilder();
  sp.append(" select ");
  sp.append("     feed_id, entry_id, published_on, last_found_on ");
  sp.append(" from ");
  sp.append("     rss_entry_history ");
  sp.append(" where ");
  sp.append("     feed_id=? ");
  sp.append("     and ");
  sp.append("     entry_id=? ");
  sp.append(" for update of ");
  sp.append("     last_found_on ");

  PreparedStatement stmt = null;
  ResultSet rs = null;
  try {
    stmt = conn.prepareUpdatableStatement(sp.toString());
    
    stmt.setString(1, feedId);
    stmt.setString(2, entryId);

    rs = stmt.executeQuery();
    if (rs.next()) {
      retval = Optional.of(
        new RssHistory(feedId, feedId, rs.getTimestamp("published_on").toInstant())
      );
      
      rs.updateDate(4, Date.valueOf(LocalDate.now()));
      rs.updateRow();
    }

  } catch (Throwable t) {
    throw new RuntimeException(
      String.format("Problem finding feed entry in history table feedId=\"%s\", entryId=\"%s\", sql=\"%s\""
        , feedId, entryId, sp.toString()
      ), t
    );
  } finally {
    conn.close(stmt, rs);
  }

  return retval;
}

Lines 17,18 These lines make this a select-for-update query. Line 18 specifies the last_found_on field is being updated.

Line 23 Uses the prepareUpdatableStatement() method to get a Statement object.

Line 34 Uses the updateDate() method to set the new value for the last_found_on field.

Line 35 Uses the updateRow() method to save the updated data to the database within the select-for-update and without having to execute a separate update statement.

Summary

That’s it. Pretty simple. I hope you enjoyed learning how to run a select-for-update SQL statement in Apache Derby.