Thread: locking

locking

From
Jan Vicherek
Date:
  Hi,

   I'm trying to figgure out how does the locking work.

  If I get a ``Result Table'' from a select with, say, only one row which
comes from only one ``Schema Table'', and I lock the ``Result Table'',
will it not prevent updates of the row in ``Schema Table'' that appeared
in the ``Result Table'' ?

   Thanx,

       Jan

 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com



Re: locking

From
Jan Vicherek
Date:
  *Any* answers would be appreciated :

 I'll rephrase the original questions :

pg doesn't have "row-level locking" but has "table locking".
a result from a SELECT * FROM TESTTABLE; is a table.
I lock the table (which is result of a SELECT).

 Q : will this locking prevent update of the rows in TESTTABLE that have
been SELECTed ?

     Thanx,

          Jan

On Mon, 19 Jan 1998, Jan Vicherek wrote:

>    I'm trying to figgure out how does the locking work.
>
>   If I get a ``Result Table'' from a select with, say, only one row which
> comes from only one ``Schema Table'', and I lock the ``Result Table'',
> will it not prevent updates of the row in ``Schema Table'' that appeared
> in the ``Result Table'' ?

 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com


Re: [QUESTIONS] Re: locking

From
Karl Denninger
Date:
If you FIRST use "BEGIN" to start a transaction, and THEN do the select,
yes.

The SELECT will cause a table lock to be asserted (a read lock, allowing
further SELECTs - but updates must come from INSIDE this transaction stream
at that point).

You release the lock with either COMMIT or ROLLBACK (which either makes your
changes "visible to others", or discards them, respectively.

A SELECT coming from elsewhere in the middle of a transaction sees the data
as it existed at the time you sent the BEGIN.

This is the basic premise of transaction processing and being able to insure
that the data has is "correct", in that all in-process transactions are
complete and not half-done, when someone comes browsing through the tables.

Without this you don't have a transaction system.

If you DO NOT use BEGIN/COMMIT|ROLLBACK, then each SQL statement is a
transaction in and of itself (ie: "UPDATE" locks during the execution of the
command, and releases it when the update is complete).

--
--
Karl Denninger (karl@MCS.Net)| MCSNet - Serving Chicagoland and Wisconsin
http://www.mcs.net/          | T1's from $600 monthly to FULL DS-3 Service
                 | NEW! K56Flex support on ALL modems
Voice: [+1 312 803-MCS1 x219]| EXCLUSIVE NEW FEATURE ON ALL PERSONAL ACCOUNTS
Fax:   [+1 312 803-4929]     | *SPAMBLOCK* Technology now included at no cost

On Tue, Jan 20, 1998 at 11:19:48PM -0500, Jan Vicherek wrote:
>
>   *Any* answers would be appreciated :
>
>  I'll rephrase the original questions :
>
> pg doesn't have "row-level locking" but has "table locking".
> a result from a SELECT * FROM TESTTABLE; is a table.
> I lock the table (which is result of a SELECT).
>
>  Q : will this locking prevent update of the rows in TESTTABLE that have
> been SELECTed ?
>
>      Thanx,
>
>           Jan
>
> On Mon, 19 Jan 1998, Jan Vicherek wrote:
>
> >    I'm trying to figgure out how does the locking work.
> >
> >   If I get a ``Result Table'' from a select with, say, only one row which
> > comes from only one ``Schema Table'', and I lock the ``Result Table'',
> > will it not prevent updates of the row in ``Schema Table'' that appeared
> > in the ``Result Table'' ?
>
>  -- Gospel of Jesus is the saving power of God for all who believe --
> Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
>     >>>    Free Software Union President  ...  www.fslu.org    <<<
> Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com
>
>

Re: [HACKERS] Re: locking

From
Bruce Momjian
Date:
>
>
>   *Any* answers would be appreciated :
>
>  I'll rephrase the original questions :
>
> pg doesn't have "row-level locking" but has "table locking".
> a result from a SELECT * FROM TESTTABLE; is a table.
> I lock the table (which is result of a SELECT).
>
>  Q : will this locking prevent update of the rows in TESTTABLE that have
> been SELECTed ?
>

While the SELECT is running, no one can update the table.  If the SELECT
is in a transaction, no one can update the table until the transaction
completes.  Other people can read from the table, though.

--
Bruce Momjian
maillist@candle.pha.pa.us

Re: [QUESTIONS] Re: [HACKERS] Re: locking

From
Leslie Mikesell
Date:
> >
> >  Q : will this locking prevent update of the rows in TESTTABLE that have
> > been SELECTed ?
> >
>
> While the SELECT is running, no one can update the table.  If the SELECT
> is in a transaction, no one can update the table until the transaction
> completes.  Other people can read from the table, though.

Does only one transaction run at a time?  That is, if you have a
transaction from client A that does a select followed by an update
and client B attempts the same thing, will the read portion of
B's transaction be deferred until A's commit completes, while
a non-transaction select (read only) from B would be allowed to
proceed?

  Les Mikesell