Mysql – SELECT…FOR UPDATE – Will only an UPDATE release the lock

lockingMySQLpostgresql

I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement. I may or may not need to execute the update depending on the results of the SELECT.

If I execute a rs.close(); or pStmt.close(); without doing an update, will MySQL release the lock? Is there another way to release it, or do I have to execute the update?

Same question if I used Postgres, too.

Not sure if it matters, but I plan to use rs.updateRow(); for the update.
Also, does MySQL support column-level locking?

Basically, I want to increment a counter in the selected row, but only if the row's status column is ACTIVE. If it is not ACTIVE, then I just want to release the lock without an update so other threads/processes can read/write that row.

Best Answer

I want to increment a counter in the selected row, but only if the row's status column is ACTIVE. If it is not ACTIVE, then I just want to release the lock without an update so other threads/processes can read/write that row.

Don't have the SELECT; simply do

UPDATE tbl
    WHERE ...
      AND status = 'active'

The UPDATE will either change the row or do nothing.

Related Question