用休眠锁定表 [英] Locking a table with hibernate

问题描述

我有一个客户端 - 服务器应用程序，在我的服务器中我使用hibernate进行数据库处理。现在，我的应用程序需要在所有数据库表中包含一行简单的表，其中只有一行 Biginteger 字段（这是此行中的关键字）。这个表格实际上只包含一个全局数字（从1开始），我每次用户执行某个动作时都会使用这个全局数字，当他这样做时，我需要获取该值并在数据库中增加值。 （这个表只包含一行，并且只有一行值）

我使用下面的代码来实现它：

  Biginteger func（）{
 Session s = null; 
 Biginteger idToReturn = null; 
尝试{
 s = factory.openSession（）; 
 s.beginTransaction（）; 
 Query queryResult = s.createQuery（from GlobalId）; 
列表< GlobalID> theId = queryResult.list（）; 
 idToReturn = theId.get（0）.get_id（）; //从db获取值返回
 GlobalID toSave = new GlobalId（）; 
 toSave.set_id（idToReturn.add（BigInteger.valueOf（1）））; //将数据库中的数据从数据库中递增以保存它
 s.delete（theId.get（0））; //删除旧的ID 
 s.save（toSave）; //保存新的ID 
 s.getTransaction（）。commit（）; 
} 
 catch（例外e）{
 throw e; 
} 
 finally {
 if（s！= null）
 s.close（）; 
 return idToReturn; 
} 
} 
  

此代码正常工作。我关心的是如果我需要使用多台服务器来处理中央数据库。在这种情况下，如果两个独立的服务器将运行此功能，则需要消除两个服务器将获得相同值的情况。我需要确保整个读写操作都是原子的，我需要锁定这张表，以便不超过一个会话将能够读取该值，而且我还需要确保在会话意外结束的情况下，锁将被删除。

我使用包含MySQL 5.6数据库的xampp包。

我在网上找到关于这个问题的信息是让我感到困惑的是，我发现的信息是高级别，我找不到任何示例。

你需要做的使用悲观锁定，可以通过

  setLockMode（String alias，LockMode lockMode）
  LockMode.UPGRADE 。
 
 
 
请参阅 Query.setLockMode  
 
 
 
然而，如果您正在进行大量访问，这肯定会破坏可伸缩性和性能在这张桌子上。您最好使用序列或其他策略来创建服务以分配数字（例如，SSB），该数字一次抓取100个数字，更新数据库并将其发送出去。这可以节省198次数据库访问。
 
 
 
更新：
 
 
 
您还必须稍微修改表格设计。最好有一个带有已知ID的单行，并将您正在递增的数字存储在另一列中。然后你应该更新行而不是删除旧行并添加一个新行。否则，行锁定策略将不起作用。
 
 
 
 UPDATE2：
 
 
 
 OP发现以下工作： 
  session.get（class.Class，id，lockOption）
  
 
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.
解决方案
You need to do 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)


                        
这篇关于用休眠锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！