Обсуждение: Select for update
Hello
May i know if i run the update below if the row select in SELECT FOR UPDATE will not be available for selection during update?
UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where status_id=1 LIMIT 1) RETURNING id into v_id;
On Wednesday, May 26, 2021, Yambu <hyambu@gmail.com> wrote:
HelloMay i know if i run the update below if the row select in SELECT FOR UPDATE will not be available for selection during update?UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where status_id=1 LIMIT 1) RETURNING id into v_id;
Which update? The way it works is by locking - Ihe row is available but locked and may require waiting.
David J.
This update, sorry forgot to include FOR UPDATE part
UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where status_id=1 LIMIT 1 FOR UPDATE) RETURNING id into v_id;
On Wed, May 26, 2021 at 2:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, May 26, 2021, Yambu <hyambu@gmail.com> wrote:HelloMay i know if i run the update below if the row select in SELECT FOR UPDATE will not be available for selection during update?UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where status_id=1 LIMIT 1) RETURNING id into v_id;Which update? The way it works is by locking - Ihe row is available but locked and may require waiting.David J.
On Wed, May 26, 2021, 08:20 Yambu <hyambu@gmail.com> wrote:
This update, sorry forgot to include FOR UPDATE partUPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where status_id=1 LIMIT 1 FOR UPDATE) RETURNING id into v_id;On Wed, May 26, 2021 at 2:27 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, May 26, 2021, Yambu <hyambu@gmail.com> wrote:HelloMay i know if i run the update below if the row select in SELECT FOR UPDATE will not be available for selection during update?UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where status_id=1 LIMIT 1) RETURNING id into v_id;Which update? The way it works is by locking - Ihe row is available but locked and may require waiting.David J.
I don't think there is a point in saying for update when you are executing an update command.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, May 26, 2021, 08:20 Yambu <hyambu@gmail.com> wrote:
>> UPDATE table1 set status_id=13 WHERE id= ( SELECT id FROM table2 where
>> status_id=1 LIMIT 1 *FOR UPDATE*) RETURNING id into v_id;
> I don't think there is a point in saying for update when you are executing
> an update command.
The FOR UPDATE is in a subselect, so what it's doing is locking
rows of table2. That seems fairly sensible if what you want to
do is make sure those rows don't change before the UPDATE
commits.
regards, tom lane