Re: [QUESTIONS] Re: locking

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(at)MCS(dot)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(at)ied(dot)com
>
>