Re: Generating the next sequential key...

From: <stevedhoward_at_gmail.com>
Date: 1 May 2006 10:06:53 -0700
Message-ID: <1146503213.211338.157090@i39g2000cwa.googlegroups.com>

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

1. 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 Received on Mon May 01 2006 - 12:06:53 CDT