Here is my Pseudo-code:
re = [select **result** from table where **condition**=key for update] if[re satisfies] { delete from table where **condition** = key; } commit
I want to ask if the row with condition equals to "key" has already been deleted, Can the lock blocked by the "select for update" be unlocked automatically, which means if another process enters at this point and select for the same "key" it can not be blocked by this one ?
A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.
The SELECT FOR UPDATE statement is used to order transactions by controlling concurrent access to one or more rows of a table. It works by locking the rows returned by a selection query, such that other transactions trying to access those rows are forced to wait for the transaction that locked the rows to finish.
FOR UPDATE on a non-existent record does not block other transactions. So, if two transactions both do a SELECT ... FOR UPDATE on the same non-existent index record, they'll both get the lock, and neither transaction will be able to update the record. In fact, if they try, a deadlock will be detected.
From mysql official doc: A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row.
Locks are taken during (usually at or near the beginning of) a command's execution. Locks (except advisory locks) are released only when a transaction commits or rolls back. There is no FOR UNLOCK
, nor is there an UNLOCK
command to reverse the effects of the table-level LOCK
command. This is all explained in the concurrency control section of the PostgreSQL documentation.
You must commit or rollback your transaction to release locks.
Additionally, it doesn't really make sense to ask "has this row already been deleted by another concurrent transaction". It isn't really deleted until the transaction that deleted the row commits... and even then, it might've deleted and re-inserted the row or another concurrent transaction might've inserted the row again.
Are you building a task queue or message queue system by any chance, because if so, that problem is solved and you shouldn't be trying to reinvent that unusually complicated wheel. See PGQ, ActiveMQ, RabbitMQ, ZeroMQ, etc. (Future PostgreSQL versions may include FOR UPDATE SKIP LOCKED
as this is being tested, but hasn't been released at time of writing).
I suggest that you post a new question with a more detailed description of the underlying problem you are trying to solve. You're assuming that the solution to your problem is "find out if the row has already been deleted" or "unlock the row". That probably isn't actually the solution. It's a bit like someone saying "where do I buy petrol" when their push-bike doesn't go so they assume it's out of fuel. Fuel isn't the problem, the problem is that push bikes don't take fuel and you have to pedal them.
Explain the background. Explain what you're trying to achieve. Above all else, don't post pseudocode, post the actual code you are having problems with, preferably in a self-contained and runnable form.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With