Locking a table with hibernate

Question

I have a Client-Server app, and in my server I'm using hibernate for database handling. Now, my app requires among all the database tables, a simple table with only one row of one Biginteger field (which is the key in this row) in it. This table will actually contain only a global number (starting from 1) which I use every time a user performing some action, and when he does, I need to get this value, and increment the value in the database. (the table shoud contain only one row with only one value all the time)

I'm using the following code to accomplish that:

 Biginteger func() {
        Session s = null;
        Biginteger idToReturn=null;
        try{
           s=factory.openSession();
           s.beginTransaction();
           Query queryResult =  s.createQuery("from GlobalId");
           List<GlobalID> theId=queryResult.list();
           idToReturn=theId.get(0).get_id();                     //getting the value from db to return
           GlobalID toSave=new GlobalId();
           toSave.set_id(idToReturn.add(BigInteger.valueOf(1))); //incrementing the id from db inorder to save it
           s.delete(theId.get(0));                               //deleting old id
           s.save(toSave);                                       //saving new id
           s.getTransaction().commit();
        }
        catch(Exception e){
            throw e;
        }
        finally{
            if (s!=null)
               s.close();
            return idToReturn;
        }
 }

This code works fine. My concern is about if I'll need to use more than one server to approach a central database. In that case, if two seperate servers will run this function, I need to eliminate the case that the two of them will get the same value. I need to make sure the entire read and write will be "atomic", I need to lock this table so no more than one session will be able to read the value, and I also need to make sure in case the session ended unexpectedly, the lock will be removed.

I'm using the xampp bundle including MySQL 5.6 database.

The informationI found online regarding this issue is confusing to me- the information I found is "high level" and I could not find any examples.

Answer 1

You need to use pessimistic locking, which can be achieved by

setLockMode(String alias, LockMode lockMode) 

on the query and use LockMode.UPGRADE.

See Query.setLockMode

However, this will certainly kill scalability and performance if you are doing a lot of access on this table. You are better either using a sequence or another strategy is to create a service to allocate numbers (e.g., an SSB) which grabs 100 numbers at a time, updates the database, and hands them out. That saves you 198 database accesses.

UPDATE:

You will also have to modify your table design slightly. It is better to have a single row with a known ID and to store the number you are incrementing in another column. Then you should update the row rather than deleting the old row and adding a new one. Otherwise, the row locking strategy won't work.

UPDATE2:

OP found that the following worked:

session.get(class.Class, id, lockOption)