Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT FOR UPDATE - release lock?

SELECT FOR UPDATE - release lock?

From
"Christian Hofmann"
Date:
Hello,

for some tables I am using a "select a,b,c from mytable where x='abc' FOR
UPDATE" query to prevent other servers also modify this data while I am
reading the data and decide to modify the data.

Now sometimes I don't want to make an update. Is there a way to release the
lock. Or is the lock released by simply call commit?

The problem is, that we are using collection pooling. So these datasets
(where x='xyz') would be blocked forever.
How would you solve this? What can i do?

Thank you,

Christian

Re: SELECT FOR UPDATE - release lock?

From
Bruno Wolff III
Date:
On Thu, May 04, 2006 at 22:08:16 +0200,
  Christian Hofmann <christian.hofmann@gmx.de> wrote:
> Hello,
>
> for some tables I am using a "select a,b,c from mytable where x='abc' FOR
> UPDATE" query to prevent other servers also modify this data while I am
> reading the data and decide to modify the data.
>
> Now sometimes I don't want to make an update. Is there a way to release the
> lock. Or is the lock released by simply call commit?

Locks are released when transactions end.

Re: SELECT FOR UPDATE - release lock?

From
Luís Silva
Date:
Locks only affects the lines in the table selected in the sql query, right?

-----Mensagem original-----
De: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] Em nome de Bruno Wolff III
Enviada: quinta-feira, 4 de Maio de 2006 20:21
Para: Christian Hofmann
Cc: pgsql-novice@postgresql.org
Assunto: Re: [NOVICE] SELECT FOR UPDATE - release lock?

On Thu, May 04, 2006 at 22:08:16 +0200,
  Christian Hofmann <christian.hofmann@gmx.de> wrote:
> Hello,
>
> for some tables I am using a "select a,b,c from mytable where x='abc' FOR
> UPDATE" query to prevent other servers also modify this data while I am
> reading the data and decide to modify the data.
>
> Now sometimes I don't want to make an update. Is there a way to release
the
> lock. Or is the lock released by simply call commit?

Locks are released when transactions end.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: SELECT FOR UPDATE - release lock?

From
Bruno Wolff III
Date:
On Fri, May 05, 2006 at 14:49:25 +0100,
  Luís Silva <lfs12@hotmail.com> wrote:
> Locks only affects the lines in the table selected in the sql query, right?

There are both table locks and row locks. You can check the documentation to
be sure, but I think that SELECT FOR UPDATE takes both locks on the rows
and some sort of shared lock on the table.

>
> -----Mensagem original-----
> De: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] Em nome de Bruno Wolff III
> Enviada: quinta-feira, 4 de Maio de 2006 20:21
> Para: Christian Hofmann
> Cc: pgsql-novice@postgresql.org
> Assunto: Re: [NOVICE] SELECT FOR UPDATE - release lock?
>
> On Thu, May 04, 2006 at 22:08:16 +0200,
>   Christian Hofmann <christian.hofmann@gmx.de> wrote:
> > Hello,
> >
> > for some tables I am using a "select a,b,c from mytable where x='abc' FOR
> > UPDATE" query to prevent other servers also modify this data while I am
> > reading the data and decide to modify the data.
> >
> > Now sometimes I don't want to make an update. Is there a way to release
> the
> > lock. Or is the lock released by simply call commit?
>
> Locks are released when transactions end.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster