Row locking within a SELECT statement
| От | xrg@linux.gr |
|---|---|
| Тема | Row locking within a SELECT statement |
| Дата | |
| Msg-id | 20160816061952.30234.45751@wrigleys.postgresql.org обсуждение исходный текст |
| Ответы |
Re: Row locking within a SELECT statement
Re: Row locking within a SELECT statement |
| Список | pgsql-docs |
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.4/static/explicit-locking.html Description: After experiencing frequent deadlocks, I'd like, please, the docs to clarify the question/situation: Are FOR UPDATE locks "atomic" within the SELECT that acquires them, or do they lock rows "on the go", as they are met in the query results? Scenario: assume I have an `alerts` table which receives rows from random sources, and then they are processed by severall passes of stored pl/pgsql procedures. In order to avoid concurrent manipulation of rows, I do issue a "SELECT .. FOR UPDATE" on the sets of rows, before UPDATEing them (because UPDATEs cannot be ordered). transaction A { SELECT .. FROM alerts WHERE <clauseA> FOR UPDATE; ... decide ... UPDATE alerts ... } transaction B { SELECT .. FROM alerts WHERE <clauseB> FOR UPDATE; ... decide, sort, filter ... UPDATE / DELETE alerts } Still, those 2 transactions *do* deadlock. Otherwise, should advisory locks be used instead?
В списке pgsql-docs по дате отправления: