Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When does "select for update" lock and unlock?

Tags:

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 ?

like image 297
venus.w Avatar asked Apr 01 '13 01:04

venus.w


People also ask

Does SELECT for update lock row?

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.

How does SELECT for update work?

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.

Does SELECT for update block SELECT?

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.

Does MySQL lock a row for update?

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.


1 Answers

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.

like image 88
Craig Ringer Avatar answered Sep 23 '22 18:09

Craig Ringer