Обсуждение: SQL Transaction related

Hi,

I have a transaction like following:

BEGIN

     INSERT INTO tbl_xyz VALUES (val1, val2);
   
    SELECT INTO wid MAX(val1) FROM tbl_xyz;

END;

My question is in the SELECT INTO statement, will I get the value of val1 from the INSERT INTO in the same transaction
even though the transaction has not ended yet.
I think no.
How would I get that latest value of val1 in the same transaction because its not committed yet as the transaction has not ended.

Thanks,

~Harpreeet

Yes it will. Everything INSIDE ONE transaction is visible to that exact
transaction. So in your scenario the val1 from the select will see what was
inserted - just any other transaction won't unless the current one is
committed.

Uwe


On Wednesday 09 May 2007, Harpreet Dhaliwal wrote:
> Hi,
>
> I have a transaction like following:
>
> BEGIN
>
>      INSERT INTO tbl_xyz VALUES (val1, val2);
>
>     SELECT INTO wid MAX(val1) FROM tbl_xyz;
>
> END;
>
> My question is in the SELECT INTO statement, will I get the value of val1
> from the INSERT INTO in the same transaction
> even though the transaction has not ended yet.
> I think no.
> How would I get that latest value of val1 in the same transaction because
> its not committed yet as the transaction has not ended.
>
> Thanks,
>
> ~Harpreeet



--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

am  Thu, dem 10.05.2007, um  2:24:40 -0400 mailte Harpreet Dhaliwal folgendes:
> Hi,
>
> I have a transaction like following:
>
> BEGIN
>
>      INSERT INTO tbl_xyz VALUES (val1, val2);
>
>     SELECT INTO wid MAX(val1) FROM tbl_xyz;
>
> END;
>
> My question is in the SELECT INTO statement, will I get the value of val1 from
> the INSERT INTO in the same transaction

No, you get MAX(val1). If the last inserted record contains this value,
than you get it.


> even though the transaction has not ended yet.
> I think no.

Within a transaction you see everything that has gone in this
transaction. Why not?


> How would I get that latest value of val1 in the same transaction because its
> not committed yet as the transaction has not ended.

We have MVCC, and you see your own snapshot, including all results
within this transaction.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net