wood burning stoves 2.0*
The moose likes JDBC and the fly likes locking table to get the new session id Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "locking table to get the new session id" Watch "locking table to get the new session id" New topic
Author

locking table to get the new session id

Bob Pettit
Greenhorn

Joined: Feb 18, 2005
Posts: 16
I am trying to generate a new session id everytime someone logs in to my application. Is this the best way to do it...

con.setAutoCommit(false);
stmt = con.createStatement();

stmt.execute("LOCK TABLE SYSADMA.DL_COUNTER IN EXCLUSIVE MODE;");
int currentID = 0;

rs = stmt.executeQuery("SELECT COUNTER_ID FROM SYSADMA.DL_COUNTER WHERE COUNTER_LABEL='SESSION_ID';");

if (rs.next())
{
currentID = rs.getBigDecimal("COUNTER_ID").intValue();
}

currentID++;

stmt.executeUpdate("UPDATE SYSADMA.DL_COUNTER SET COUNTER_ID = " + currentID + " WHERE COUNTER_LABEL='SESSION_ID'");

con.commit();


The table in the DB2 database looks like this
===========================================

COUNTER_LABEL COUNTER_ID
------------------------------ --------------
SESSION_ID 48754.
PC_ID 1000704.

Thanks a lot guys.....
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
I am not sure I would look to lock the table. I would prefer to use a unique constraint.

For example if I have a table called sessions with a primary key of sessionid then I would do this...

SELECT MAX(sessionid) FROM sessions

get the value returned and add one to it

INSERT INTO sessions(sessionid) VALUES(MAXVALUE+1);

If the sessionid for some reason is not unique (and mostly it should be) then that would throw an exception based on the constraint... in that case try again.

I guess I would prefer that methodology because I am nto a big fan of locking tables. I find that getting explict locks can cause more serious problems (ie if your application crashes or deadlocks). I think that you should just let the database use it's own implict locking schemes as neccessary in general.

But I don't know much about DB2 locking so I could be wrong... I still prefer my method though.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: locking table to get the new session id
 
Similar Threads
Help HIGHLY needed with one-to-one mapping error
Display one record per current date
some advice please
static synchronized
JSF Rendered attribute question