pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: SzczÄ…chor Tomasz <tszczachor@zke.com.pl>
To: postgres <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Cursors and waiting users
Date: Wed, 9 Jun 1999 16:50:49 +0300
Message-ID: <l03130300b3842087c030@[147.233.159.109]> (raw)
In-Reply-To: <01beb278$73179ea0$020018ac@szczachor.zke.com.pl>

At 16:03 +0300 on 09/06/1999, =?iso-8859-2?Q?Szcz=B1chor_Tomasz?= wrote:


> I can't finish this command until I close transaction on first session.
> Why?
> Does the declaring of cursor lock table for insert and I can't insert into
> table? But I use cursor only for viewing of table.
>
>
> Than,  if one user use cursor on the table, second one can't  insert data to
> that table?
>
> I am beginner in postgress. Help me find my mistakes.
> (Sorry for my English).

Yes. In all releases up to and including 6.4.2, the entire table is locked
for operations that require sharing of data.

You ask why it locks if it only reads the table. Well, the reason is that
the other person, doing the insert, is asking for an exclusive lock. And
you are holding a shared lock. You can have several people reading the
table at the same time, but if there is one that wants to write, he can
only get his exclusive lock when they all finish to read.

Why? Because if he gets a permission to write before you finish the read,
and updates some of the rows, you may get different data in the middle of
your cursor transaction. You start reading, then you go back to the same
row - but it's different, in the same transaction. The way PostgreSQL works
now prevents such differences in data in the middle of a transaction.

In the coming version, there is a different locking logic.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (2+ messages)

Message-ID: <l03130300b3842087c030@[147.233.159.109]>
Permalink:  ../l03130300b3842087c030@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130300b3842087c030@[147.233.159.109]

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, tszczachor@zke.com.pl
  Subject: Re: [SQL] Cursors and waiting users
  In-Reply-To: <l03130300b3842087c030@[147.233.159.109]>
  References: <01beb278$73179ea0$020018ac@szczachor.zke.com.pl> <l03130300b3842087c030@[147.233.159.109]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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