Re: [GENERAL] Does a row lock taken out in a CTE stay in place?

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] Does a row lock taken out in a CTE stay in place?
Дата
Msg-id CAKFQuwY4cgVXyKo5Uorgm4EMOrYvQNT973gj5ZHHRwGjKxhBQQ@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] Does a row lock taken out in a CTE stay in place?  (Seamus Abshere <seamus@abshere.net>)
Список pgsql-general
Дерево обсуждения
On Tue, Jul 11, 2017 at 8:36 AM, Seamus Abshere <seamus@abshere.net> wrote:
Given an update that uses CTEs like this:

WITH
lock_rows AS (
  SELECT 1 FROM tbl WHERE [...] FOR UPDATE
)
UPDATE [...]

Will the rows in `tbl` remain locked until the UPDATE is finished?


​Yes​ - locks persist to the end of the transaction.  Using a CTE doesn't constitute creating a new statement.

Also, does it matter if `lock_rows` is referenced? (IIUC the query
wouldn't be run if the CTE isn't referenced if it was for a SELECT, but
since it's an UPDATE, it will be run anyway
​.

​Pretty sure it will not be.  The EXPLAIN​ command should be able to provide a more definitive answer.

If the UPDATE was inside the CTE it definitely would be run regardless of outer query references.  I'm not sure if the FOR UPDATE impacts whether the select needs to be executed by I'm thinking no since it doesn't change the semantics of the query.

David J.

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Does a row lock taken out in a CTE stay in place?
Следующее
От: Jason Dusek
Дата:
Сообщение: Re: [GENERAL] Imperative Query Languages