db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: Atomicity of using IDENTITY_VAL_LOCAL()
Date Fri, 13 May 2005 21:50:17 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Submitted.<br>
<br>
Satheesh<br>
<br>
Mamta Satoor wrote:<br>
<blockquote cite="midd9619e4a05051313475c2f6274@mail.gmail.com"
 type="cite">
  <div>Hi,</div>
  <div>&nbsp;</div>
  <div>I will file a doc JIRA entry for the IDENTITY_VAL_LOCAL()
function, so there is the crucial *connection* dependency identified.</div>
  <div>&nbsp;</div>
  <div>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?</div>
  <div>&nbsp;</div>
  <div>********svn stat************</div>
  <div>M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql<br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out</div>
  <div>*****************************</div>
  <div>&nbsp;</div>
  <div>thanks,</div>
  <div>Mamta<br>
  <br>
&nbsp;</div>
  <div><span class="gmail_quote">On 5/13/05, <b
 class="gmail_sendername">Daniel John Debrunner</b> &lt;<a
 href="mailto:djd@debrunners.com">djd@debrunners.com</a>&gt; wrote:</span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
1ex;">Mamta
Satoor wrote:<br>
    <br>
&gt; The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the
value that<br>
&gt; got into generated for _any_ table with identity column using
single row<br>
&gt; insert with values clause in the current transaction.<br>
    <br>
Except it doesn't behave like that, with respect to the *current<br>
transaction*. Derby's implementation returns the last identity value
for
    <br>
a single row INSERT statement within the same connection.<br>
See the example below, and note auto commit is true.<br>
    <br>
And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1,<br>
that will just return the same value multiple times (once per row in
the
    <br>
table) and the value will be the last identity value for a single row<br>
INSERT statement within the same connection.<br>
    <br>
Dan.<br>
    <br>
ij&gt; connect 'jdbc:derby:foo;create=true';<br>
ij&gt; create table t (id int generated always as identity, d int);
    <br>
0 rows inserted/updated/deleted<br>
ij&gt; insert into t(d) values(88);<br>
1 row inserted/updated/deleted<br>
ij&gt; values IDENTITY_VAL_LOCAL();<br>
1<br>
-------------------------------<br>
1<br>
    <br>
1 row selected<br>
ij&gt; select * from t;
    <br>
ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |D<br>
-----------------------<br>
1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|88<br>
    <br>
1 row selected<br>
ij&gt; values IDENTITY_VAL_LOCAL();<br>
1<br>
-------------------------------<br>
1<br>
    <br>
1 row selected<br>
    <br>
  </blockquote>
  </div>
  <br>
  <pre wrap="">
<hr size="4" width="90%">
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&gt; drop table t3;
 0 rows inserted/updated/deleted
-ij&gt; 
+ij&gt; -- test IDENTITY_VAL_LOCAL function with 2 different connections
+-- connection one
+connect 'wombat' as conn1;
+ij(CONN1)&gt; create table t1 (c11 int generated always as identity (start with 101,
increment by 3), c12 int);
+0 rows inserted/updated/deleted
+ij(CONN1)&gt; create table t2 (c21 int generated always as identity (start with 201,
increment by 5), c22 int);
+0 rows inserted/updated/deleted
+ij(CONN1)&gt; -- 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)&gt; commit;
+ij(CONN1)&gt; -- connection two
+connect 'wombat' as conn2;
+ij(CONN2)&gt; -- 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)&gt; insert into t2 (c22) values (1);
+1 row inserted/updated/deleted
+ij(CONN2)&gt; -- 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)&gt; set connection conn1;
+ij(CONN1)&gt; -- 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)&gt; insert into t1 (c12) values (1);
+1 row inserted/updated/deleted
+ij(CONN1)&gt; -- 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)&gt; set connection conn2;
+ij(CONN2)&gt; -- 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)&gt; -- notice that committing the transaction does not affect IDENTITY_VAL_LOCAL()
+commit;
+ij(CONN2)&gt; values IDENTITY_VAL_LOCAL();
+1                              
+-------------------------------
+201                            
+ij(CONN2)&gt; drop table t1;
+0 rows inserted/updated/deleted
+ij(CONN2)&gt; drop table t2;
+0 rows inserted/updated/deleted
+ij(CONN2)&gt; 
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message