3

I have a Spring transaction that reads from the database and, if what's looking for doesn't exist it creates it. Example:

@Transactional
public int getUserIdCreateIfNotExistent(String email) throws Exception {
  try {
    return jdbcTemplate.queryForObject("SELECT id FROM users WHERE email = ?", Integer.class, email);
  } catch (IncorrectResultSizeDataAccessException e) {
    Thread.sleep(10000);
    return jdbcTemplate.queryForObject("INSERT INTO users (email) values(?) RETURNING id", Integer.class, email);
  }
}

When this method is called twice concurrently it will results in having two distinct users with the same email in the DB (there is no constraint on unique email, this is just for illustration purposes).

I would like the second transaction to wait for the first one so that only one user is created. I've tried changing transaction isolation level to Isolation.SERIALIZABLE but all it does is make the transaction that commits last roll back.

EDIT: Before anyone suggests locking solutions in java using synchronized or something like that, it's important to understand that this method is part of a web application and called when a specific endpoint is hit. The web app runs on several different machines and is load-balanced and the problem happens when the endpoint is called twice concurrently. This means that the code might be executed in parallel on two different machines talking to the same DB on a different machine.

satoshi
  • 3,963
  • 6
  • 46
  • 57

2 Answers2

2

Solved with advisory locks:

@Transactional
public int getUserIdCreateIfNotExistent(String email) throws Exception {
  try {
    jdbcTemplate.queryForRowSet("SELECT pg_advisory_xact_lock(hashtext('users'), hashtext(?))", email);
    return jdbcTemplate.queryForObject("SELECT id FROM users WHERE email = ?", Integer.class, email);
  } catch (IncorrectResultSizeDataAccessException e) {
    Thread.sleep(10000);
    return jdbcTemplate.queryForObject("INSERT INTO users (email) values(?) RETURNING id", Integer.class, email);
  }
}
satoshi
  • 3,963
  • 6
  • 46
  • 57
0

You can use a "locks" table. It'll contain everything you need to lock on, i.e table name (e.g "users") and row key (e.g "email").

If you need something faster then you can use memcached with replication across your servers. If your inserts aren't many then the db solution will probably be fine.

cherouvim
  • 31,725
  • 15
  • 104
  • 153