Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

locking table to get the new session id

 
Bob Pettit
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic