satoshi satoshi - 1 month ago 17
Java Question

How can I make two identical read+write concurrent transactions wait for each other?

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.

Answer

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);
  }
}