pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to explicitly lock and unlock tables in pgsql?
6+ messages / 4 participants
[nested] [flat]
* How to explicitly lock and unlock tables in pgsql?
@ 2022-03-16 20:30 Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Shaozhong SHI @ 2022-03-16 20:30 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
Table locks present a barrier for progressing queries.
How to explicitly lock and unlock tables in pgsql, so that we can guarantee
the progress of running scripts?
Regards,
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* How to explicitly lock and unlock tables in pgsql?
@ 2022-03-16 20:30 Shaozhong SHI <shishaozhong@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Shaozhong SHI @ 2022-03-16 20:30 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Table locks present a barrier for progressing queries.
How to explicitly lock and unlock tables in pgsql, so that we can guarantee
the progress of running scripts?
Regards,
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to explicitly lock and unlock tables in pgsql?
@ 2022-03-16 20:35 Fabrízio de Royes Mello <fabrizio@timbira.com.br>
parent: Shaozhong SHI <shishaozhong@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Fabrízio de Royes Mello @ 2022-03-16 20:35 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Em qua., 16 de mar. de 2022 às 17:30, Shaozhong SHI <shishaozhong@gmail.com>
escreveu:
> Table locks present a barrier for progressing queries.
>
> How to explicitly lock and unlock tables in pgsql, so that we can
> guarantee the progress of running scripts?
>
> Regards,
>
> David
>
Have a look at https://www.postgresql.org/docs/current/sql-lock.html
--
Fabrízio Mello
Consultor
fabrizio@timbira.com.br
https://www.timbira.com.br/
[image: facebook] <https://pt-br.facebook.com/Timbira/;
[image: twitter] <https://twitter.com/timbirabrasil;
[image: linkedin] <https://sg.linkedin.com/company/timbira;
[image: instagram] <https://www.instagram.com/timbirabrasil/;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to explicitly lock and unlock tables in pgsql?
@ 2022-03-17 07:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Shaozhong SHI <shishaozhong@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2022-03-17 07:51 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On Wed, 2022-03-16 at 20:30 +0000, Shaozhong SHI wrote:
> Table locks present a barrier for progressing queries.
>
> How to explicitly lock and unlock tables in pgsql, so that we can guarantee the progress of running scripts?
You cannot unlock tables except by ending the transaction which took the lock.
The first thing you should do is to make sure that all your database transactions are short.
Also, you should nevr explicitly lock tables. Table locks are taken automatically
by the SQL statements you are executing.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to explicitly lock and unlock tables in pgsql?
@ 2022-03-18 16:38 Merlin Moncure <mmoncure@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Merlin Moncure @ 2022-03-18 16:38 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On Thu, Mar 17, 2022 at 2:52 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2022-03-16 at 20:30 +0000, Shaozhong SHI wrote:
> > Table locks present a barrier for progressing queries.
> >
> > How to explicitly lock and unlock tables in pgsql, so that we can guarantee the progress of running scripts?
>
> You cannot unlock tables except by ending the transaction which took the lock.
>
> The first thing you should do is to make sure that all your database transactions are short.
>
> Also, you should nevr explicitly lock tables. Table locks are taken automatically
> by the SQL statements you are executing.
Isn't that a bit of overstatement?
LOCK table foo;
Locks the table, with the benefit you can choose the lockmode to
decide what is and is not allowed to run after you lock it. The main
advantage vs automatic locking is preemptively blocking things so as
to avoid deadlocks.
merlin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to explicitly lock and unlock tables in pgsql?
@ 2022-03-18 22:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2022-03-18 22:48 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
On Fri, 2022-03-18 at 11:38 -0500, Merlin Moncure wrote:
> > Also, you should nevr explicitly lock tables. Table locks are taken automatically
> > by the SQL statements you are executing.
>
> Isn't that a bit of overstatement?
> LOCK table foo;
>
> Locks the table, with the benefit you can choose the lockmode to
> decide what is and is not allowed to run after you lock it. The main
> advantage vs automatic locking is preemptively blocking things so as
> to avoid deadlocks.
Yes, that was an overstatement.
But I find that 90% of the time when people explicitly lock a table
it is not the correct solution.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-03-18 22:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-16 20:30 How to explicitly lock and unlock tables in pgsql? Shaozhong SHI <shishaozhong@gmail.com>
2022-03-16 20:30 ` Shaozhong SHI <shishaozhong@gmail.com>
2022-03-16 20:35 ` Fabrízio de Royes Mello <fabrizio@timbira.com.br>
2022-03-17 07:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-03-18 16:38 ` Merlin Moncure <mmoncure@gmail.com>
2022-03-18 22:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox