Databases
StackOverflow2013
Postsdbo
POUPDATE does not lock table if no rows match, race condition
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUPDATE does not lock table if no rows match, race condition
    text
    copied!<p>I want to increment and return a counter from a database table.</p> <p>The java code is as follows:</p> <pre><code>String sqlUpdate = "UPDATE mytable SET col3 = col3 + 1 WHERE colpk1 = ? AND colpk2 = ?"; Query queryUpdate = manager.createNativeQuery(sqlUpdate); queryUpdate.setParameter(1, ...); queryUpdate.setParameter(2, ...); int num = queryUpdate.executeUpdate(); if (num == 0) { long count = 1; String sqlInsert = "INSERT INTO mytable (colpk1, colpk2, col3) VALUES (?,?,?)"; Query queryInsert = manager.createNativeQuery(sqlInsert); queryInsert.setParameter(1, ...); queryInsert.setParameter(2, ...); queryInsert.setParameter(3, count); queryInsert.executeUpdate(); return count; } else { String sqlSelect = "SELECT col3 FROM mytable WHERE colpk1 = ? AND colpk2 = ?"; Query querySelect = manager.createNativeQuery(sqlSelect); querySelect.setParameter(1, ...); querySelect.setParameter(2, ...); Object result = querySelect.getSingleResult(); return Long.parseLong(result.toString()); } </code></pre> <p>This works well also concurrently used (creates a lock) in case there is already a row with the given primary key. However, in case that row does not exist yet (num == 0), the UPDATE does not lock, and a concurrent access can happen in between the two queries, then leading to a Unique Constraint validation when executing the INSERT as the new row was already created in the meantime.</p> <p>What's the best way to solve this problem? Would it be better to use a <code>SELECT FOR UPDATE</code> first and then depending on the result doing an UPDATE or INSERT?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload