db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Fri, 13 May 2005 18:10:20 GMT
Please see the answers inline.

On 5/13/05, Paul J. Lucas <pauljlucas@mac.com> wrote:
> On Fri, 13 May 2005, Daniel John Debrunner wrote:
> 
> > So I think the answer to Paul's question is not answered by that
> > documentation.
> 
> Right.
> 
> > Does it mean the most recent INSERT for that connection or the most
> > recent insert to the database.
> 
> What about the most recent insert for a table? Does:
> 
> SELECT IDENTITY_VAL_LOCAL() FROM mytable1;
> 
> really only return IDENTITY_VAL_LOCAL() for *that* table or is
> the above deceptively equivalent to:
> 
> VALUES IDENTITY_VAL_LOCAL();
> 
The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the value that got 
into generated for *any* table with identity column using single row insert 
with values clause in the current transaction. And hence, return from SELECT 
IDENTITY_VAL_LOCAL() FROM mytable1 depends on what kind of insert statements 
went in before the select in the transaction.
 > ? More explicitly, if I do:
> 
> INSERT INTO mytable1 ... ;
> INSERT INTO mytable2 ... ;
> SELECT IDENTITY_VAL_LOCAL() FROM mytable1;
> SELECT IDENTITY_VAL_LOCAL() FROM mytable2;
> 
> where both tables have an IDENTITY column, so I get the correct
> values back from both selects?
> 
Let me repeat following line of the documentation again here.
"The IDENTITY_VAL_LOCAL function is a non-deterministic function that 
returns *the most recently assigned value for an identity column*, where the 
assignment occurred as a result of a single row INSERT statement using a 
VALUES clause."
 So, for your particular example (assuming both mytable1 and mytable2 have 
identity column and assuming that the inserts are single row inserts using a 
VALUES clause), both the select sqls will return the the value that went 
into mytable2's identity column.

> - Paul
>

Mime
View raw message