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.

Why don't you use a (native database) sequence - much better performance and more scalable. — , May 19 '15 at 11:08
Check this link https://docs.jboss.org/hibernate/orm/4.0/devguide/en-US/html/ch05.html . There is a section for pessimistic locking. As I understood it ends up in SQLs SELECT FROM UPDATE and so on transformation which should be your case — Stan, May 19 '15 at 11:11
@a_horse_with_no_name can I use a native sql sequence to get and increment the value in a way no parallel sequence (of get and increment) will get the value? — izac89, May 19 '15 at 13:10
yes of course, that's what (native) sequences are all about (provided the DBMS you are using supports them - you have not told us which DBMS are you using though) — , May 19 '15 at 13:41
@a_horse_with_no_name I'm using the xampp bundle including MySQL 5.6 database. Can you give me an example for such sequence? the table has only one row with only one field (`Biginteger` type. This only field is a key ofcourse) that is actualy a single global value for me, should be get and incremented for a specific use. — izac89, May 19 '15 at 14:03
Rather hard to answer, your basic problem description is that possibly valid solutions you found are 'confusing to you'. What can be done to solve that? Was Stan's link among the information you found? — Gimby, May 19 '15 at 14:12
@Gimby the information in the link lack examples and does not refer to what happens if the session with the lock is destroyed for some reason? does the lock comes free? — izac89, May 19 '15 at 14:15

rghome · Accepted Answer · 2019-07-26T16:59:46.690

7

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)

edited Jul 26 '19 at 16:59

answered May 19 '15 at 14:38

rghome

8,529
8
43
62

What happens if the session destroyed while the table was locked by it? does the lock become free? – izac89 May 19 '15 at 14:49
The lock is held by the transaction. You should issue a rollback in the case of error conditions, but if you exit unexpectedly, it should rollback anyway. You can set a transaction timeout (`Transaction.setTimeOut`) if you want to be super sure. – rghome May 19 '15 at 14:54
@ rghome I read the doc, but I still don't fully undersand the `String alias` part. Could you explain that for me? – izac89 May 20 '15 at 09:37
The alias is the short name after a table-name in a query. If you don't have one, you can add one. I presume that just allows you to restrict the locking to a single table, in the case you have multiple tables in your query. – rghome May 20 '15 at 09:39
What happens when one thread tried to open a session and read the value while its locked already? will I get an exception? will the session block the thread until the lock is free? – izac89 May 22 '15 at 08:25
The session will block until the first commits or rolls back, although you can specify a transaction time-out. – rghome May 22 '15 at 08:28
I just now tested it and it did not work. I issued two threads, each thread opened a session to the DB, set the lock as described, read the data from database and update the database. Both threads read the same data and the second thread didn't wait for the first one to close its session. My goal is to keep the reading and updating (incrementing the number) atomic in a way that two sessions won't read the same data (the number I'm keeping in my table), and this lock not working for me. Any advice? – izac89 Jun 18 '15 at 17:02
There are various reasons why it could fail. The lock is freed when you do a commit (not close the session) so maybe a commit is being done before the session close (autocommit?). Maybe the database is not supporting this lock mode. Maybe it is just a logic bug. Try getting hibernate to show the SQL (http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values). Try doing a "SELECT FOR UPDATE" directly and see if it works. – rghome Jun 18 '15 at 21:23
Ok, so using `session.get(class.Class, id, lockOption)` (thus simulating "SELECT FOR UPDATE") did the job. Thanks – izac89 Jun 19 '15 at 13:13

Locking a table with hibernate

1 Answers1

Linked