Once the client session holding the lock disconnects, closing the session, the lock is released. You can also set client options like idle_in_transaction_session_timeout to force transactions to close after a certain amount of idle time, which also releases the lock.
It means another process could grab it, yes. In the parent post's model, the "scheduler" operates in a "at least once" model", as unless the transaction is committed, a premature failure would result in the message will be picked up again by a scheduler, which could result in some duplicate work.
The parent post's model also tracks "attempts", but this only captures known or acknowledged failures -- unacknowledged failures (i.e. crashes) will not be recorded, so a task which explodes the process would be re-run ad infinitum.
An alternative method could be to record an attempt in a separate transaction, so that the next scheduler execution can detect that the job/message was serviced before, even if the message itself appears fresh.
