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