Horton

Thread

  1. locking

    Jan Vicherek <honza@ied.com> — 1998-01-19T22:40:33Z

      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

  2. Re: locking

    Jan Vicherek <honza@ied.com> — 1998-01-21T04:19:48Z

      *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

  3. Re: [QUESTIONS] Re: locking

    Karl Denninger <karl@mcs.net> — 1998-01-21T13:53:02Z

    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
> 
>

  4. Re: [HACKERS] Re: locking

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-01-21T15:05:17Z

    > 
> 
>   *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

  5. Re: [QUESTIONS] Re: [HACKERS] Re: locking

    Leslie Mikesell <les@mcs.net> — 1998-01-21T15:55:03Z

    > > 
> >  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