Atomicity of using IDENTITY_VAL_LOCAL()

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Atomicity of using IDENTITY_VAL_LOCAL()

Paul J. Lucas
  If I have a tables like:

          CREATE TABLE location (
             location_id    INT NOT NULL PRIMARY KEY
                            GENERATED ALWAYS AS IDENTITY,
             path           VARCHAR(255) NOT NULL UNIQUE
          );

          CREATE TABLE image (
             image_id       INT NOT NULL PRIMARY KEY
                            GENERATED ALWAYS AS IDENTITY,
             file_name      VARCHAR(64) NOT NULL,
             location_id    INT REFERENCES location,
             date_time      TIMESTAMP NOT NULL
          );

  I want to be able to insert a new location, obtain the
  auto-generated location_id, and use that in an insert of a new
  image.

  If I do the fisrt insert, then:

  SELECT IDENTITY_VAL_LOCAL() FROM location;

  then use that value for the second insert image.location_id,
  will I be guaranteed to get the right value for location_id,
  i.e., the one for the first insert and not some other insert
  that may have happened in between my firt insert and the
  SELECT?

  Pharsed another way, are the operations of:

  INSERT INTO location ... ;
  SELECT IDENTITY_VAL_LOCAL() FROM location;
  INSERT INTO image ... ;

  atomic so that the value I use in the second insert will be the
  correct one?

  If not, how do I do what I want?

  When I was doing this sort of thing using Oracle, Oracle has
  "sequences" that can be used for this purpose.  I suppose that
  for Derby I could have a seperate table that's just:

          CREATE TABLE location_id (
             next_id INT NOT NULL PRIMARY KEY
                     GENERATED ALWAYS AS IDENTITY,
     junk    INT

          );

  then, before I do my first insert, do:

  INSERT INTO location_id (junk) VALUES (0);
  SELECT IDENTITY_VAL_LOCAL() FROM location_id;

  then use *that* location_id for both of my inserts.  But is this
  necessary?

  Thanks.

  - Paul
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Mamta Satoor
Hi Paul,

Following is what Derby's Reference Guide says about IDENTITY_VAL_LOCAL

IDENTITY_VAL_LOCAL
Derby supports the IDENTITY_VAL_LOCAL function.

Syntax:
IDENTITY_VAL_LOCAL ( )

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.

The IDENTITY_VAL_LOCAL function has no input parameters. The result is
a DECIMAL(31,0), regardless of the actual data type of the
corresponding identity column.

The value returned by the IDENTITY_VAL_LOCAL function is the value
assigned to the identity column of the table identified in the most
recent single row INSERT statement.The INSERT statement must contain a
VALUES clause on a table containing an identity column. The assigned
value is an identity value generated by Derby. The function returns a
null value when a single row INSERT statement with a VALUES clause has
not been issued for a table containing an identity column.

The result of the function is not affected by the following:
• A single row INSERT statement with a VALUES clause for a table without an
identity column
• A multiple row INSERT statement with a VALUES clause
• An INSERT statement with a fullselect

So, as long as there is a single row INSERT statement with a VALUES
clause on the table with the generated always column, this function
should work fine for what you are trying to achieve.

Also, there are quite a few test cases for this function in
org.apache.derbyTesting.functionTests.tests.lang.autoincrement.sql to
see some examples of this function.

Hope this helps,
Mamta
On 5/12/05, Paul J. Lucas <[hidden email]> wrote:

>        If I have a tables like:
>
>                CREATE TABLE location (
>                    location_id    INT NOT NULL PRIMARY KEY
>                                   GENERATED ALWAYS AS IDENTITY,
>                    path           VARCHAR(255) NOT NULL UNIQUE
>                );
>
>                CREATE TABLE image (
>                    image_id       INT NOT NULL PRIMARY KEY
>                                   GENERATED ALWAYS AS IDENTITY,
>                    file_name      VARCHAR(64) NOT NULL,
>                    location_id    INT REFERENCES location,
>                    date_time      TIMESTAMP NOT NULL
>                );
>
>        I want to be able to insert a new location, obtain the
>        auto-generated location_id, and use that in an insert of a new
>        image.
>
>        If I do the fisrt insert, then:
>
>                SELECT IDENTITY_VAL_LOCAL() FROM location;
>
>        then use that value for the second insert image.location_id,
>        will I be guaranteed to get the right value for location_id,
>        i.e., the one for the first insert and not some other insert
>        that may have happened in between my firt insert and the
>        SELECT?
>
>        Pharsed another way, are the operations of:
>
>                INSERT INTO location ... ;
>                SELECT IDENTITY_VAL_LOCAL() FROM location;
>                INSERT INTO image ... ;
>
>        atomic so that the value I use in the second insert will be the
>        correct one?
>
>        If not, how do I do what I want?
>
>        When I was doing this sort of thing using Oracle, Oracle has
>        "sequences" that can be used for this purpose.  I suppose that
>        for Derby I could have a seperate table that's just:
>
>                CREATE TABLE location_id (
>                    next_id INT NOT NULL PRIMARY KEY
>                            GENERATED ALWAYS AS IDENTITY,
>                    junk    INT
>
>                );
>
>        then, before I do my first insert, do:
>
>                INSERT INTO location_id (junk) VALUES (0);
>                SELECT IDENTITY_VAL_LOCAL() FROM location_id;
>
>        then use *that* location_id for both of my inserts.  But is this
>        necessary?
>
>        Thanks.
>
>        - Paul
>
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Daniel John Debrunner
Mamta Satoor wrote:
> Hi Paul,
>
> Following is what Derby's Reference Guide says about IDENTITY_VAL_LOCAL
>
> IDENTITY_VAL_LOCAL
> Derby supports the IDENTITY_VAL_LOCAL function.
>

[...]

So I think the answer to Paul's question is not answered by that
documentation.

Does it mean the most recent INSERT for that connection or the most
recent insert to the database. I think it's the former, but the docs
should state that.


Dan.

Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Paul J. Lucas
In reply to this post by Mamta Satoor
On Fri, 13 May 2005, Mamta Satoor wrote:

> Following is what Derby's Reference Guide says about IDENTITY_VAL_LOCAL

  I know what it says: I read it.

> So, as long as there is a single row INSERT statement with a VALUES
> clause on the table with the generated always column, this function
> should work fine for what you are trying to achieve.

  It says nothing about about transactions or race conditions.

  - Paul
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Paul J. Lucas
In reply to this post by Daniel John Debrunner
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();

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

  - Paul
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Mamta Satoor
Please see the answers inline.

On 5/13/05, Paul J. Lucas <[hidden email]> 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
>
 
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Paul J. Lucas
On Fri, 13 May 2005, Mamta Satoor wrote:

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

  That nugget of information is not in the documentation I don't
  believe.  So that implies that if there are two open
  transactions in two different threads and each does an insert,
  then each thread will get its respective "correct" value for
  IDENTITY_VAL_LOCAL().

  Right?

  - Paul
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Daniel John Debrunner
In reply to this post by Mamta Satoor
Mamta Satoor wrote:

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

Except it doesn't behave like that, with respect to the *current
transaction*. Derby's implementation returns the last identity value for
a single row INSERT statement within the same connection.
See the example below, and note auto commit is true.

And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
that will just return the same value multiple times (once per row in the
table) and the value will be the last identity value for a single row
INSERT statement within the same connection.

Dan.

ij> connect 'jdbc:derby:foo;create=true';
ij> create table t (id int generated always as identity, d int);
0 rows inserted/updated/deleted
ij> insert into t(d) values(88);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected
ij> select * from t;
ID         |D
-----------------------
1          |88

1 row selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected




Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Mamta Satoor
Hi,
 
I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL() function, so there is the crucial *connection* dependency identified.
 
Also, I have added one more subtest to autoincrement.sql which tests the return value of this function for 2 different connections. Can someone commit the patch for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Daniel John Debrunner <[hidden email]> wrote:
Mamta Satoor wrote:

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

Except it doesn't behave like that, with respect to the *current
transaction*. Derby's implementation returns the last identity value for
a single row INSERT statement within the same connection.
See the example below, and note auto commit is true.

And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
that will just return the same value multiple times (once per row in the
table) and the value will be the last identity value for a single row
INSERT statement within the same connection.

Dan.

ij> connect 'jdbc:derby:foo;create=true';
ij> create table t (id int generated always as identity, d int);
0 rows inserted/updated/deleted
ij> insert into t(d) values(88);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected
ij> select * from t;
ID         |D
-----------------------
1          |88

1 row selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected



Identity_val_local_051305.txt (6K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Satheesh Bandaram
Submitted.

Satheesh

Mamta Satoor wrote:
Hi,
 
I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL() function, so there is the crucial *connection* dependency identified.
 
Also, I have added one more subtest to autoincrement.sql which tests the return value of this function for 2 different connections. Can someone commit the patch for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Daniel John Debrunner <[hidden email]> wrote:
Mamta Satoor wrote:

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

Except it doesn't behave like that, with respect to the *current
transaction*. Derby's implementation returns the last identity value for
a single row INSERT statement within the same connection.
See the example below, and note auto commit is true.

And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
that will just return the same value multiple times (once per row in the
table) and the value will be the last identity value for a single row
INSERT statement within the same connection.

Dan.

ij> connect 'jdbc:derby:foo;create=true';
ij> create table t (id int generated always as identity, d int);
0 rows inserted/updated/deleted
ij> insert into t(d) values(88);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected
ij> select * from t;
ID         |D
-----------------------
1          |88

1 row selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected



Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (revision 169878) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (working copy) @@ -691,3 +691,35 @@ drop table t1; drop table t2; drop table t3; + +-- test IDENTITY_VAL_LOCAL function with 2 different connections +-- connection one +connect 'wombat' as conn1; +create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int); +create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int); +-- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn1 +values IDENTITY_VAL_LOCAL(); +commit; +-- connection two +connect 'wombat' as conn2; +-- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn2 +values IDENTITY_VAL_LOCAL(); +insert into t2 (c22) values (1); +-- IDENTITY_VAL_LOCAL() will return 201 because there was single row insert into table t2 with identity column on this connection conn2 +values IDENTITY_VAL_LOCAL(); +set connection conn1; +-- IDENTITY_VAL_LOCAL() will continue to return NULL because no single row insert into table with identity column yet on this connection conn1 +values IDENTITY_VAL_LOCAL(); +insert into t1 (c12) values (1); +-- IDENTITY_VAL_LOCAL() will return 101 because there was single row insert into table t1 with identity column on this connection conn1 +values IDENTITY_VAL_LOCAL(); +set connection conn2; +-- IDENTITY_VAL_LOCAL() on conn2 not impacted by single row insert into table with identity column on conn1 +values IDENTITY_VAL_LOCAL(); +-- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL() +commit; +values IDENTITY_VAL_LOCAL(); +drop table t1; +drop table t2; + + Index: java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (revision 169878) +++ java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (working copy) @@ -1379,4 +1379,60 @@ 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted -ij> +ij> -- test IDENTITY_VAL_LOCAL function with 2 different connections +-- connection one +connect 'wombat' as conn1; +ij(CONN1)> create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int); +0 rows inserted/updated/deleted +ij(CONN1)> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int); +0 rows inserted/updated/deleted +ij(CONN1)> -- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn1 +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +NULL +ij(CONN1)> commit; +ij(CONN1)> -- connection two +connect 'wombat' as conn2; +ij(CONN2)> -- IDENTITY_VAL_LOCAL() will return NULL because no single row insert into table with identity column yet on this connection conn2 +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +NULL +ij(CONN2)> insert into t2 (c22) values (1); +1 row inserted/updated/deleted +ij(CONN2)> -- IDENTITY_VAL_LOCAL() will return 201 because there was single row insert into table t2 with identity column on this connection conn2 +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +201 +ij(CONN2)> set connection conn1; +ij(CONN1)> -- IDENTITY_VAL_LOCAL() will continue to return NULL because no single row insert into table with identity column yet on this connection conn1 +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +NULL +ij(CONN1)> insert into t1 (c12) values (1); +1 row inserted/updated/deleted +ij(CONN1)> -- IDENTITY_VAL_LOCAL() will return 101 because there was single row insert into table t1 with identity column on this connection conn1 +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +101 +ij(CONN1)> set connection conn2; +ij(CONN2)> -- IDENTITY_VAL_LOCAL() on conn2 not impacted by single row insert into table with identity column on conn1 +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +201 +ij(CONN2)> -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL() +commit; +ij(CONN2)> values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +201 +ij(CONN2)> drop table t1; +0 rows inserted/updated/deleted +ij(CONN2)> drop table t2; +0 rows inserted/updated/deleted +ij(CONN2)>
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

jboynes
In reply to this post by Daniel John Debrunner
What does this return if the table has a trigger defined which inserts
into another table which also has an identity defined? Is it the value
from the statement's table or the one modified by the trigger?

I could not see where this is explictly defined in the docs. I have seen
this cause problems with applications using SQL Server databases and so
it is probably worth spelling out.

--
Jeremy

Daniel John Debrunner wrote:

> Mamta Satoor wrote:
>
>
>>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.
>
>
> Except it doesn't behave like that, with respect to the *current
> transaction*. Derby's implementation returns the last identity value for
> a single row INSERT statement within the same connection.
> See the example below, and note auto commit is true.
>
> And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
> that will just return the same value multiple times (once per row in the
> table) and the value will be the last identity value for a single row
> INSERT statement within the same connection.
>
> Dan.
>
> ij> connect 'jdbc:derby:foo;create=true';
> ij> create table t (id int generated always as identity, d int);
> 0 rows inserted/updated/deleted
> ij> insert into t(d) values(88);
> 1 row inserted/updated/deleted
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
> ij> select * from t;
> ID         |D
> -----------------------
> 1          |88
>
> 1 row selected
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
>
>
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Mamta Satoor
Hi Jeremy,
 
I tried a simple test to see what happens in the case you brought up. The value retruned by IDENTITY_VAL_LOCAL is from the statement's table (and not from the table which got modified by the trigger).
 
Following is the ij session results

$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:c:/dellater/db1';
ij> create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int);
0 rows inserted/updated/deleted
ij> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int);
0 rows inserted/updated/deleted
ij> create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1);
0 rows inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
NULL

1 row selected
ij> insert into t1 (c12) values (1);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
101

1 row selected
ij> select * from t1;
C11        |C12
-----------------------
101        |1

1 row selected
ij> select * from t2;
C21        |C22
-----------------------
201        |1

1 row selected
ij>



 
On 5/13/05, Jeremy Boynes <[hidden email]> wrote:
What does this return if the table has a trigger defined which inserts
into another table which also has an identity defined? Is it the value
from the statement's table or the one modified by the trigger?

I could not see where this is explictly defined in the docs. I have seen
this cause problems with applications using SQL Server databases and so
it is probably worth spelling out.

--
Jeremy

Daniel John Debrunner wrote:

> Mamta Satoor wrote:
>
>
>>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.
>
>
> Except it doesn't behave like that, with respect to the *current
> transaction*. Derby's implementation returns the last identity value for
> a single row INSERT statement within the same connection.
> See the example below, and note auto commit is true.
>
> And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
> that will just return the same value multiple times (once per row in the
> table) and the value will be the last identity value for a single row
> INSERT statement within the same connection.
>
> Dan.

>
> ij> connect 'jdbc:derby:foo;create=true';
> ij> create table t (id int generated always as identity, d int);
> 0 rows inserted/updated/deleted
> ij> insert into t(d) values(88);
> 1 row inserted/updated/deleted
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
> ij> select * from t;
> ID         |D
> -----------------------
> 1          |88
>
> 1 row selected
> ij> values IDENTITY_VAL_LOCAL();
> 1
> -------------------------------
> 1
>
> 1 row selected
>
>
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

jboynes
Mamta Satoor wrote:
> Hi Jeremy,
>  I tried a simple test to see what happens in the case you brought up. The
> value retruned by IDENTITY_VAL_LOCAL is from the statement's table (and not
> from the table which got modified by the trigger).

Thanks.

As a cross-reference, this the behaviour of MS SQL Server 2000's
SCOPE_IDENTITY() function and not the older @@IDENTITY one.

--
Jeremy

Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Mamta Satoor
Hi Jeremy,
 
I didn't understand your note about SQL Server 2000's SCOPE_IDENTITY() function. Can you elaborate on it a little? In the mean time, I will update the Derby-287 documentation bug (Return value of IDENTITY_VAL_LOCAL for different connections is not related) with the Derby's behavior in case of triggers.
 
thanks,
Mamta

 
On 5/14/05, Jeremy Boynes <[hidden email]> wrote:
Mamta Satoor wrote:
> Hi Jeremy,
>  I tried a simple test to see what happens in the case you brought up. The
> value retruned by IDENTITY_VAL_LOCAL is from the statement's table (and not
> from the table which got modified by the trigger).

Thanks.

As a cross-reference, this the behaviour of MS SQL Server 2000's
SCOPE_IDENTITY() function and not the older @@IDENTITY one.

--
Jeremy


Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Mamta Satoor
In reply to this post by Mamta Satoor
Hi,
 
I have another small patch for trigger test for IDENTITY_VAL_LOCAL. Can a committer please commit it for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Mamta Satoor <[hidden email]> wrote:
Hi,
 
I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL() function, so there is the crucial *connection* dependency identified.
 
Also, I have added one more subtest to autoincrement.sql which tests the return value of this function for 2 different connections. Can someone commit the patch for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Daniel John Debrunner <[hidden email]> wrote:
Mamta Satoor wrote:

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

Except it doesn't behave like that, with respect to the *current
transaction*. Derby's implementation returns the last identity value for
a single row INSERT statement within the same connection.
See the example below, and note auto commit is true.

And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
that will just return the same value multiple times (once per row in the
table) and the value will be the last identity value for a single row
INSERT statement within the same connection.

Dan.

ij> connect 'jdbc:derby:foo;create=true';
ij> create table t (id int generated always as identity, d int);
0 rows inserted/updated/deleted
ij> insert into t(d) values(88);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected
ij> select * from t;
ID         |D
-----------------------
1          |88

1 row selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected





IDENTITY_VAL_LOCAL_Tests_051405.txt (15K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Atomicity of using IDENTITY_VAL_LOCAL()

Satheesh Bandaram
Mamta, looks like this patch was sent to wrong alias... Should it be sent to DerbyDev? :-)

Also this patch seems to include a modification to 'java/client/org/apache/derby/client/am/ResultSet.java' that I suspect should not be in the patch. If so, can you remove that and resubmit?

Satheesh

Mamta Satoor wrote:
Hi,
 
I have another small patch for trigger test for IDENTITY_VAL_LOCAL. Can a committer please commit it for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Mamta Satoor <[hidden email]> wrote:
Hi,
 
I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL() function, so there is the crucial *connection* dependency identified.
 
Also, I have added one more subtest to autoincrement.sql which tests the return value of this function for 2 different connections. Can someone commit the patch for me?
 
********svn stat************
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
*****************************
 
thanks,
Mamta

 
On 5/13/05, Daniel John Debrunner <[hidden email]> wrote:
Mamta Satoor wrote:

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

Except it doesn't behave like that, with respect to the *current
transaction*. Derby's implementation returns the last identity value for
a single row INSERT statement within the same connection.
See the example below, and note auto commit is true.

And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,
that will just return the same value multiple times (once per row in the
table) and the value will be the last identity value for a single row
INSERT statement within the same connection.

Dan.

ij> connect 'jdbc:derby:foo;create=true';
ij> create table t (id int generated always as identity, d int);
0 rows inserted/updated/deleted
ij> insert into t(d) values(88);
1 row inserted/updated/deleted
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected
ij> select * from t;
ID         |D
-----------------------
1          |88

1 row selected
ij> values IDENTITY_VAL_LOCAL();
1
-------------------------------
1

1 row selected





Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (revision 170188) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql (working copy) @@ -719,7 +719,25 @@ -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL() commit; values IDENTITY_VAL_LOCAL(); +-- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL() +values IDENTITY_VAL_LOCAL(); drop table t1; drop table t2; +-- A table with identity column has an insert trigger which inserts into another table +-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the +-- statement table and not for the table that got modified by the trigger +create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int); +create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int); +create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1); +values IDENTITY_VAL_LOCAL(); +insert into t1 (c12) values (1); +-- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1. +-- It will not return 201 which got generated for t2 as a result of the trigger fire. +values IDENTITY_VAL_LOCAL(); +select * from t1; +select * from t2; +drop table t1; +drop table t2; + Index: java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (revision 170188) +++ java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out (working copy) @@ -1431,8 +1431,46 @@ 1 ------------------------------- 201 +ij(CONN2)> -- notice that rolling the transaction does not affect IDENTITY_VAL_LOCAL() +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +201 ij(CONN2)> drop table t1; 0 rows inserted/updated/deleted ij(CONN2)> drop table t2; 0 rows inserted/updated/deleted +ij(CONN2)> -- A table with identity column has an insert trigger which inserts into another table +-- with identity column. IDENTITY_VAL_LOCAL will return the generated value for the +-- statement table and not for the table that got modified by the trigger +create table t1 (c11 int generated always as identity (start with 101, increment by 3), c12 int); +0 rows inserted/updated/deleted +ij(CONN2)> create table t2 (c21 int generated always as identity (start with 201, increment by 5), c22 int); +0 rows inserted/updated/deleted +ij(CONN2)> create trigger t1tr1 after insert on t1 for each row mode db2sql insert into t2 (c22) values (1); +0 rows inserted/updated/deleted +ij(CONN2)> values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +201 +ij(CONN2)> insert into t1 (c12) values (1); +1 row inserted/updated/deleted +ij(CONN2)> -- IDENTITY_VAL_LOCAL will return 101 which got generated for table t1. +-- It will not return 201 which got generated for t2 as a result of the trigger fire. +values IDENTITY_VAL_LOCAL(); +1 +------------------------------- +101 +ij(CONN2)> select * from t1; +C11 |C12 +----------------------- +101 |1 +ij(CONN2)> select * from t2; +C21 |C22 +----------------------- +201 |1 +ij(CONN2)> drop table t1; +0 rows inserted/updated/deleted +ij(CONN2)> drop table t2; +0 rows inserted/updated/deleted ij(CONN2)> Index: java/client/org/apache/derby/client/am/ResultSet.java =================================================================== --- java/client/org/apache/derby/client/am/ResultSet.java (revision 170188) +++ java/client/org/apache/derby/client/am/ResultSet.java (working copy) @@ -2369,6 +2369,10 @@ agent_.logWriter_.traceEntry(this, "updateRow"); } updateRowX(); + //the cursor is not positioned on the updated row after updateRow. + //User needs to issue ResultSet.next to reposition the ResultSet + //on a valid row + isValidCursorPosition_ = false; } } @@ -2379,32 +2383,55 @@ "row or if the concurrency of this ResultSet object is CONCUR_READ_ONLY."); } - // No-op if none of the columns were updated and updateRow() is called, just return. + // If no updateXXX has been called on this ResultSet object, then + // updatedColumns_ will be null and hence no action required if (updatedColumns_ == null) { return; } - if (preparedStatementForUpdate_ == null) { - getPreparedStatementForUpdate(); + // updateXXX has been called on this ResultSet object, but check if it + // has been called on the current row. If no column got updated on this + // current row, then just return. + boolean didAnyColumnGetUpdated = false; + for (int i=0; i < updatedColumns_.length; i++) { + if (columnUpdated_[i]) { + didAnyColumnGetUpdated = true; + break; + } } + if (didAnyColumnGetUpdated == false) + return; + // User might not be updating all the updatable columns selected in the + // select sql and hence every updateRow on the same ResultSet can be + // potentially different than the previous one. Because of that, we + // should get a new prepared statement to do updates every time + getPreparedStatementForUpdate(); + // build the inputs array for the prepared statement for update + int paramNumber = 0; for (int i = 0; i < updatedColumns_.length; i++) { if (resultSetMetaData_.sqlxUpdatable_[i] == 1) { + // Since user may choose not to update all the columns in the + // select list, check first if the column has been updated + if (columnUpdated_[i] == false) + continue; + paramNumber++; + // column is updated either if the updatedColumns_ entry is not null, // or if the updatedColumns_ entry is null, but columnUpdated_ boolean is // set to true, which means columns is updated to a null. if (updatedColumns_[i] != null || (updatedColumns_[i] == null && columnUpdated_[i])) { - preparedStatementForUpdate_.setInput(i + 1, updatedColumns_[i]); + preparedStatementForUpdate_.setInput(paramNumber, updatedColumns_[i]); } else { // Check if the original column is null. Calling CrossConverters.setObject on a null // column causes "Data Conversion" Exception. Object originalObj = getObject(i + 1); if (originalObj == null) { - preparedStatementForUpdate_.setInput(i + 1, null); + preparedStatementForUpdate_.setInput(paramNumber, null); } else { - preparedStatementForUpdate_.setInput(i + 1, agent_.crossConverters_.setObject(resultSetMetaData_.types_[i], originalObj)); + preparedStatementForUpdate_.setInput(paramNumber, agent_.crossConverters_.setObject(resultSetMetaData_.types_[i], originalObj)); } } } @@ -2431,6 +2458,10 @@ agent_.logWriter_.traceEntry(this, "deleteRow"); } deleteRowX(); + //the cursor is not positioned on the deleted row after deleteRow. + //User needs to issue ResultSet.next to reposition the ResultSet + //on a valid row + isValidCursorPosition_ = false; } } @@ -2502,6 +2533,11 @@ "row or if this ResultSet object has a concurrency of CONCUR_READ_ONLY."); } + // if not on a valid row, then do not accept cancelRowUpdates call + if (!isValidCursorPosition_) + throw new SqlException(agent_.logWriter_, "Invalid operation" + + "cancelRowUpdates at current cursor position."); + // if updateRow() has already been called, then cancelRowUpdates should have // no effect. updateRowCalled_ is reset to false as soon as the cursor moves to a new row. if (!updateRowCalled_) { @@ -3041,8 +3077,6 @@ boolean foundOneUpdatedColumnAlready = false; String updateString = "UPDATE " + getTableName() + " SET "; - // The update tablename ... where current of cursorname for Cloudscape has - // to provide columns as columnname1 = ?, columnname2 = ? for (column = 1; column <= resultSetMetaData_.columns_; column++) { if (columnUpdated_[column - 1]) { if (foundOneUpdatedColumnAlready) { @@ -3069,8 +3103,8 @@ private String buildDeleteString() throws SqlException { String deleteString = "DELETE FROM "; - // build the update string using the server's cursor name - deleteString += (getTableName() + " WHERE CURRENT OF " + getServerCursorName()); + // build the delete string using the server's cursor name + deleteString += (getTableName() + " WHERE CURRENT OF \"" + getServerCursorName() + "\""); if (isRowsetCursor_) { deleteString += " FOR ROW ? OF ROWSET"; @@ -3084,12 +3118,15 @@ if (resultSetMetaData_.sqlxRdbnam_[0] != null && !resultSetMetaData_.sqlxRdbnam_[0].equals("")) // catalog { - tableName += resultSetMetaData_.sqlxRdbnam_[0].trim() + "."; + tableName += "\"" + resultSetMetaData_.sqlxRdbnam_[0] + "\"."; } - if (!resultSetMetaData_.sqlxSchema_[0].equals("")) { - tableName += resultSetMetaData_.sqlxSchema_[0].trim() + "."; + //dervied column like select 2 from t1, has null schema and table name + if (resultSetMetaData_.sqlxSchema_[0] != null && !resultSetMetaData_.sqlxSchema_[0].equals("")) { + tableName += "\"" + resultSetMetaData_.sqlxSchema_[0] + "\"."; } - tableName += resultSetMetaData_.sqlxBasename_[0].trim(); + if (resultSetMetaData_.sqlxBasename_[0] != null) { + tableName += "\"" + resultSetMetaData_.sqlxBasename_[0] + "\""; + } return tableName; } @@ -3157,6 +3194,11 @@ if (resultSetMetaData_.sqlxUpdatable_ == null || resultSetMetaData_.sqlxUpdatable_[column - 1] != 1) { throw new SqlException(agent_.logWriter_, "Column not updatable"); } + + //if not on a valid row, then do not accept updateXXX calls + if (!isValidCursorPosition_) + throw new SqlException(agent_.logWriter_, "Invalid operation to " + + "update at current cursor position"); } final void checkForValidColumnIndex(int column) throws SqlException {