SELECT...FOR UPDATE - Will only an UPDATE release the lock?

Question

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.

Answer 1

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.

Answer 2

Don't know about MySQL, but in Postgresql, the lock is only valid for the duration of the current transaction, so:

• you must have started a transaction
• the lock will be released if you commit or rollback the transaction

If you don't start an explicit transaction, then the lock will be released right away.