3

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.

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.

  • Thanks, but I need to retrieve the value for my processing, too. Just an `UPDATE' won't return the value or a recordset. But, by adding that where clause I can safely execute the update every time, thereby releasing the lock. – MaybeWeAreAllRobots Jan 25 '16 at 7:31
  • No, I'm pretty sure that won't release the lock. Only COMMIT or ROLLBACK will release it. – Rick James Jan 25 '16 at 19:41
  • Why is the transaction taking so long that the lock matters? – Rick James Jan 25 '16 at 19:41
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.