Release of a SELECT… FOR UPDATE lock

My question is very simple: what is the inverse SQL statement for SELECT ID FROM TABLE FOR UPDATE NOWAIT? How do I release a lock during the same transaction that acquired it before committing?

Expanding: I am writing portable API code that leverages Hibernate to apply row-level locks to entities. The following API is available to implementors

@Override
public void lock(T object)
{
    try
    {
        getHibernateTemplate().lock(object, LockMode.UPGRADE_NOWAIT);
    }
    catch (Throwable e)
    {
        log.error(e.getMessage(), e);
        throw e;
    }
}

@Override
public void unlock(T object)
{
    try
    {
        getHibernateTemplate().lock(object, LockMode.NONE);
    }
    catch (Throwable e)
    {
        log.error(e.getMessage(), e);
        throw e;
    }
}

I use the lock code in my transactions. Lock method works as a charm and I get the expected concurrency exceptions where I expect them! I see that after you have fetched an entity from the persistence layer, Hibernate does the SQL query shown above to lock the object. I wrote the unlock method as natural dual to lock method.

Apart from committing/rolling the transaction back, is it possible to voluntarily release a lock held on an object? Or to downgrade it?

I have googled around but can neither find what is the inverse SQL statement for SELECT FOR UPDATE nor what Hibernate does when downgrading a lock. I have the doubt that releasing a lock is something actually not possible in major SQL databases, so the unlock() API should not be used by any calling code. Projects I am in charge of do not require to voluntary release a lock during a transaction (they will commit a read-only transaction in the worst case), but I question myself about the usefulness of the unlock API.

I have willingly omitted what database my application runs on because this is portable API code for MS Sql, MySql, Postgres and Oracle

Answer

In most DBs – it is NOT possible to release the lock without COMMIT or ROLLBACK.

As far as I’ve read, when you SELECT FOR UPDATE, the DB (under the covers) treats this as an update that occurred as part of the transaction. Therefore, just like any other update, the locks are released only at COMMIT or ROLLBACK. Imagine a scenario where the same transaction that acquired the lock also made an update to the record. In this case, it just becomes too complex to support an explicit unlock and check if the transaction did actually do anything else to the record while it was locked. Different isolation levels would further increase the complexity.