Generating the next sequential key...

[timasmith]

Hi,

I have this app which is relatively database independent. I would like
to serve the smallest scale (MS Access) up to the largest database
server (Oracle).

I recoginize that I will need to write database specific code for
Oracle i.e. generate the next number from a sequence.

For the rest I pondered this idea...

a) Have a table with a row per database table and the last number used
in it.
pro's: nice sequential number
con's: contention to get the next number (maybe grab a few at a time
ugh)

b) Generate a random number between 1 and 1000000000
pro's: very fast
con's 1 in a billion chance to create a duplicate (maybe try the
insert again if it happens?)

Are there other strategies I am missing?

thanks

Tim

[stevedhoward]

I understand where you are trying to go. If you are a vendor of an
app, you may have to support multiple RDBMS if customers won't pay for
Oracle, DB2, etc. as your backend. However, be prepared for an
onslaught of of...ummm...alternative ideas? :)

a) The con will get really big really fast.

Look at the following simple JDBC code.

/************************************************** **************************************

//Cut out all thread code for brevity...

conn.setAutoCommit(false);
sql = "select c, rowid from my_sequence for update";
PreparedStatement pstmt = conn.prepareStatement ("update
my_sequence set c = ? where rowid = ?");
PreparedStatement stm = conn.prepareStatement(sql);
ResultSet rset = null;
for (int i = 1; i < 50; i++) {
rset = stm.executeQuery();
while (rset.next ()) {
int c = rset.getInt(1);
String rowid = rset.getString (2);
pstmt.setInt (1, c + 1);
pstmt.setString (2, rowid);
pstmt.executeUpdate ();
conn.commit();
}
}
ResultSet rst = conn.createStatement().executeQuery("select
event,time_waited " +
"from
v$session_event " +
"where sid
= (select distinct sid " +

"from v$mystat) " +
"and
event = 'enq: TX - row lock contention'");
while(rst.next())
System.out.println(num + " waited " + rst.getInt("time_waited")
+ " centiseconds on the " + rst.getString("event") + " event.");
conn.close();
************************************************** **************************************/
It threads 20 connections that are each trying to update the same row
to its next value. I did it in a loop of 50, as my small test system
can't handle that many concurrent connections.

SQL> create table my_sequence(c number);

Table created.

SQL> insert into my_sequence values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\SCRIPTS\java>java runSeqContention 20 "@localhost:1521/test10g" rep
rep
Using URL @localhost:1521/test10g
1 waited 10 centiseconds on the enq: TX - row lock contention event.
2 waited 5 centiseconds on the enq: TX - row lock contention event.
3 waited 5 centiseconds on the enq: TX - row lock contention event.
4 waited 2 centiseconds on the enq: TX - row lock contention event.
5 waited 0 centiseconds on the enq: TX - row lock contention event.
6 waited 1 centiseconds on the enq: TX - row lock contention event.
7 waited 2 centiseconds on the enq: TX - row lock contention event.
8 waited 1 centiseconds on the enq: TX - row lock contention event.
9 waited 1 centiseconds on the enq: TX - row lock contention event.
10 waited 1 centiseconds on the enq: TX - row lock contention event.
11 waited 3 centiseconds on the enq: TX - row lock contention event.
12 waited 1 centiseconds on the enq: TX - row lock contention event.
13 waited 5 centiseconds on the enq: TX - row lock contention event.
14 waited 1 centiseconds on the enq: TX - row lock contention event.
15 waited 3 centiseconds on the enq: TX - row lock contention event.
17 waited 11 centiseconds on the enq: TX - row lock contention event.
18 waited 1 centiseconds on the enq: TX - row lock contention event.
19 waited 0 centiseconds on the enq: TX - row lock contention event.
20 waited 0 centiseconds on the enq: TX - row lock contention event.
16 waited 0 centiseconds on the enq: TX - row lock contention event.

That isn't bad, but it just a simple example to show how you might
test. This doesn't even consider the fact that you will be doing a
commit after every update (Unless you cache like you suggested).

b) This depends on your app. What is two people use the same ID at
the same time (not a duplicate from a historical perspective, but a
current one)?

At the risk of being beaten with a dry noodle, you may be able to do
this, it just depends on your application.

What is you data access code writen in? Why not create an overloaded
method for each RDBMS to support its own unique identifier algorithm
(if necessary), that returns the value?

Regards,

Steve

[Brian Peasland]

> I have this app which is relatively database independent. I would like

to serve the smallest scale (MS Access) up to the largest database
server (Oracle).
No app is truly database independent. Even if you use ANSI-SQL syntax
supported on all of your database platforms, the moment you send that
SQL statement to the database, you are at the mercy of that specific
RDBMS. No two RDBMS's will execute that statement the same. Try running
EXISTS/NOT EXISTS and IN/NOT IN on both SQL Server and Oracle to see how
the two engines treat these differently.
I recoginize that I will need to write database specific code for
Oracle i.e. generate the next number from a sequence.
If you are going to write database specific code, then what are the
other options for?
For the rest I pondered this idea...

a) Have a table with a row per database table and the last number used
in it.
pro's: nice sequential number
con's: contention to get the next number (maybe grab a few at a time
ugh)

b) Generate a random number between 1 and 1000000000
pro's: very fast
con's 1 in a billion chance to create a duplicate (maybe try the
insert again if it happens?)

Are there other strategies I am missing?

In Oracle, I'd use a sequence. In SQL Server and MS Access, I'd use the
autoincrementing column in a table. In SQL Server, you can also use a
Globally Unique Identifier (GUID).

You can implement your own solutions, but you would be reinventing the
wheel and in many cases, creating an application bottleneck.
HTH,
Brian
--
================================================== =================

Brian Peasland
or********@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

[timasmith]

Neat test. Although I am always suspect that 20 machines accessing the
database concurrently would have a different effect to 1 machine
spawning 20 threads - the latter would probably generate less
contension.

Data access code is Java and there is no problem writing a method per
RDBMS - except I would rather write for a couple I can test and then
ship some generic code for every other database so people could use it
and not have to wait for the next release.

[timasmith]

I didnt think the autoincrementing column was any use in MSAccess
because I need the value generated for child tables and you cant get
the value until it is generated - and even then you have to do a lookup
to get it (by some *other* unique index since you dont have the pkey
yet...eeesh).

Please correct me if MSAccess has progressed beyond that.

Not familiar with SQL Server. As I said above I dont mind coding for a
few databases I can test - but I would like to ship a generic solution
for all other databases - in which case a or b would probably work
though neither is elegant.

[David Lightstone]

<ti*******@hotmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...

I didnt think the autoincrementing column was any use in MSAccess
because I need the value generated for child tables and you cant get
the value until it is generated - and even then you have to do a lookup
to get it (by some *other* unique index since you dont have the pkey
yet...eeesh).

Please correct me if MSAccess has progressed beyond that.
If perchance you are creating the records under program control (which I am)
you can get the key when the record is created, but before it has been
populated with values (other than the key)

Not familiar with SQL Server. As I said above I dont mind coding for a
few databases I can test - but I would like to ship a generic solution
for all other databases - in which case a or b would probably work
though neither is elegant.

[Lyle Fairfield]

ti*******@hotmail.com wrote:

Please correct me if MSAccess has progressed beyond that.

Sub temp()
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
..ConnectionString = CurrentProject.BaseConnectionString
..Open
..Execute "INSERT INTO [Order Details] " _
& "(OrderID, ProductID, UnitPrice, Quantity, Discount) " _
& "Values (12, 23, 15.5, 12, 3/1000)"
Debug.Print .Execute("SELECT @@Identity").Collect(0)
' prints the autonumber of the newly inserted record
..Close
End With
Set c = Nothing
End Sub

[stevedhoward]

I'm not sure if this is what you are asking, since you referenced
getting the _child_ key, but you should be able to do the following to
get the key (autonumber in Access) from JDBC. AFAIK, the JDBC bridge
to ODBC still doesn't support getGeneratedKeys()...

C:\SCRIPTS\java>type msAccessKey.java
import java.sql.*;

public class msAccessKey {
public static void main(String args []) {
String dataSourceName = "t0501";
String dbURL = "jdbc:odbc:" + dataSourceName;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(dbURL, "","");
Statement stm = con.createStatement();
stm.executeUpdate("insert into t0501(textval) values('test')");
int pk = -1;
ResultSet rs = stm.executeQuery("SELECT @@IDENTITY");
while (rs.next()) {
pk = rs.getInt(1);
}
System.out.println(pk);
con.commit();
con.close();
}
catch (Exception err) {
System.out.println(err);
}
}
}
C:\SCRIPTS\java>javac msAccessKey.java

C:\SCRIPTS\java>java msAccessKey
1

C:\SCRIPTS\java>java msAccessKey
2

C:\SCRIPTS\java>java msAccessKey
3

C:\SCRIPTS\java>java msAccessKey
4

Regards,

Steve