The use of locks and query types in Java's Hibernate framework database operations

Hibernate and Database Locks
1. Why use locks?

To understand why the lock mechanism exists, we must first understand the concept of transactions.
A transaction is a series of related operations on the database, which must have ACID characteristics:

A (atomicity): Either all succeed or all are revoked.

C (Consistency): To maintain the consistency of the database.

I (Isolation): When different transactions operate on the same data, they must have their own data spaces.

D (Durability): Once a transaction ends successfully, the updates it makes to the database must be persisted permanently.

Our commonly used relational database RDBMS implements these characteristics of transactions. Among them, atomicity,
consistency and durability are all guaranteed by logs. Isolation is achieved by the
lock mechanism we are focusing on today, which is why we need the lock mechanism.

If there is no lock and no control over isolation, what are the possible consequences?

Update lost: The data submitted by transaction 1 was overwritten by transaction 2.

Dirty read: Transaction 2 queries the uncommitted data of transaction 1.

Virtual reading: Transaction 2 queries the newly created data submitted by transaction 1.

Non-repeatable read: Transaction 2 queries the updated data submitted by transaction 1.

Let's look at the Hibernate example. Two threads start two transactions to operate the same row of data col_id=1 in
in the tb_account table.

package com.cdai.orm.hibernate.annotation; 
  
import java.io.Serializable; 
  
import javax.persistence.Column; 
import javax.persistence.Entity; 
import javax.persistence.Id; 
import javax.persistence.Table; 
  
@Entity
@Table(name = "tb_account") 
public class Account implements Serializable { 
  
  private static final long serialVersionUID = 5018821760412231859L; 
  
  @Id
  @Column(name = "col_id") 
  private long id; 
    
  @Column(name = "col_balance") 
  private long balance; 
  
  public Account() { 
  } 
    
  public Account(long id, long balance) { 
    this.id = id; 
    this.balance = balance; 
  } 
  
  public long getId() { 
    return id; 
  } 
  
  public void setId(long id) { 
    this.id = id; 
  } 
  
  public long getBalance() { 
    return balance; 
  } 
  
  public void setBalance(long balance) { 
    this.balance = balance; 
  } 
  
  @Override
  public String toString() { 
    return "Account [id=" + id + ", balance=" + balance + "]"; 
  } 
    
}

package com.cdai.orm.hibernate.transaction; 
  
import org.hibernate.Session; 
import org.hibernate.SessionFactory; 
import org.hibernate.Transaction; 
import org.hibernate.cfg.AnnotationConfiguration; 
  
import com.cdai.orm.hibernate.annotation.Account; 
  
public class DirtyRead { 
  
  public static void main(String[] args) { 
  
    final SessionFactory sessionFactory = new AnnotationConfiguration(). 
        addFile("hibernate/hibernate.cfg.xml").        
        configure(). 
        addPackage("com.cdai.orm.hibernate.annotation"). 
        addAnnotatedClass(Account.class). 
        buildSessionFactory(); 
      
    Thread t1 = new Thread() { 
        
      @Override
      public void run() { 
        Session session1 = sessionFactory.openSession(); 
        Transaction tx1 = null; 
        try { 
          tx1 = session1.beginTransaction(); 
          System.out.println("T1 - Begin trasaction"); 
          Thread.sleep(500); 
            
          Account account = (Account)  
              session1.get(Account.class, new Long(1)); 
          System.out.println("T1 - balance=" + account.getBalance()); 
          Thread.sleep(500); 
            
          account.setBalance(account.getBalance() + 100); 
          System.out.println("T1 - Change balance:" + account.getBalance()); 
            
          tx1.commit(); 
          System.out.println("T1 - Commit transaction"); 
          Thread.sleep(500); 
        } 
        catch (Exception e) { 
          e.printStackTrace(); 
          if (tx1 != null) 
            tx1.rollback(); 
        }  
        finally { 
          session1.close(); 
        } 
      } 
        
    }; 
      
    // 3.Run transaction 2 
    Thread t2 = new Thread() { 
        
      @Override
      public void run() { 
        Session session2 = sessionFactory.openSession(); 
        Transaction tx2 = null; 
        try { 
          tx2 = session2.beginTransaction(); 
          System.out.println("T2 - Begin trasaction"); 
          Thread.sleep(500); 
            
          Account account = (Account)  
              session2.get(Account.class, new Long(1)); 
          System.out.println("T2 - balance=" + account.getBalance()); 
          Thread.sleep(500); 
            
          account.setBalance(account.getBalance() - 100); 
          System.out.println("T2 - Change balance:" + account.getBalance()); 
            
          tx2.commit(); 
          System.out.println("T2 - Commit transaction"); 
          Thread.sleep(500); 
        } 
        catch (Exception e) { 
          e.printStackTrace(); 
          if (tx2 != null) 
            tx2.rollback(); 
        }  
        finally { 
          session2.close(); 
        } 
      } 
        
    }; 
      
    t1.start(); 
    t2.start(); 
      
    while (t1.isAlive() || t2.isAlive()) { 
      try { 
        Thread.sleep(2000L); 
      } catch (InterruptedException e) { 
      } 
    } 
      
    System.out.println("Both T1 and T2 are dead."); 
    sessionFactory.close(); 
      
  } 
  
}

Transaction 1 reduces col_balance by 100, while transaction 2 reduces it by 100, the final result may be 0, or
it may be 200, and the updates from transaction 1 or 2 may be lost. The log output also confirms this. The logs of transactions 1 and 2
are cross-printed.

T1 - Begin trasaction
T2 - Begin trasaction
Hibernate: select account0_.col_id as col1_0_0_, account0_.col_balance as col2_0_0_ from tb_account account0_ where account0_.col_id=?
Hibernate: select account0_.col_id as col1_0_0_, account0_.col_balance as col2_0_0_ from tb_account account0_ where account0_.col_id=?
T1 - balance=100
T2 - balance=100
T2 - Change balance:0
T1 - Change balance:200
Hibernate: update tb_account set col_balance=? where col_id=?
Hibernate: update tb_account set col_balance=? where col_id=?
T1 - Commit transaction
T2 - Commit transaction
Both T1 and T2 are dead.

It can be seen that isolation is an issue that needs careful consideration, and it is necessary to understand locks.

2. How many types of locks are there?

Common ones include shared locks, update locks and exclusive locks.

1. Shared lock: used for read data operations, allowing other transactions to read at the same time. When a transaction executes a select statement,
the database automatically assigns a shared lock to the transaction to lock the read data.
2. Exclusive lock: used to modify data, other transactions cannot read or modify it. The database is automatically allocated when a transaction executes insert,
update, and delete.
3. Update lock: Used to avoid deadlock caused by shared locks during update operations. For example, transactions 1 and 2 hold
shared locks at the same time and wait to obtain exclusive locks. When executing update, the transaction first obtains the update lock, and then upgrades the
update lock to an exclusive lock, thus avoiding deadlock.

In addition, these locks can be applied to different objects in the database, that is, these locks can have different granularities.
Such as database-level locks, table-level locks, page-level locks, key-level locks and row-level locks.

So there are many kinds of locks. It is too difficult to fully master and flexibly use so many locks. We are not DBAs.
what to do? Fortunately, the lock mechanism is transparent to us ordinary users. The database will automatically add appropriate
locks, and automatically upgrade and downgrade various locks at the appropriate time. This is so thoughtful! All we need to do is
learn to set the isolation level according to different business needs.

3. How to set the isolation level?

Generally speaking, the database system will provide four transaction isolation levels for users to choose from:

1.Serializable (serializable): When two transactions manipulate the same data at the same time, transaction 2 We can only stop and wait.

2.Repeatable Read: Transaction 1 can see the newly inserted data of transaction 2, but cannot see updates to
existing data.

3.Read Committed (read committed data): Transaction 1 can see the newly inserted and updated data of transaction 2.

4.Read Uncommited (read uncommitted data): Transaction 1 can see the uncommitted insert and update
data of transaction 2.

4. Locks in Applications

When the database adopts the Read Commited isolation level, pessimistic locking or optimistic locking can be used in the application.

1. Pessimistic lock: It is assumed that the data operated by the current transaction will definitely be accessed by other transactions, so pessimistically specify an exclusive lock in the application
program to lock the data resources. The following forms are supported in MySQL and Oracle:

select ... for update

Explicitly let select use an exclusive lock to lock the queried records. Other transactions must wait until the
locked data to query, update or delete the data. Only after the transaction is over.

In Hibernate, you can pass LockMode.UPGRADE during load to use pessimistic locking. Modify the previous example,
Pass in one more LockMode parameter at the get method call of transactions 1 and 2. As can be seen from the log, transactions 1 and 2
are no longer running crosswise, and transaction 2 waits for transaction 1 to end before it can read the data, so the final col_balance value is the correct
of 100.

package com.cdai.orm.hibernate.transaction; 
  
import org.hibernate.LockMode; 
import org.hibernate.Session; 
import org.hibernate.SessionFactory; 
import org.hibernate.Transaction; 
  
import com.cdai.orm.hibernate.annotation.Account; 
import com.cdai.orm.hibernate.annotation.AnnotationHibernate; 
  
public class UpgradeLock { 
  
  @SuppressWarnings("deprecation") 
  public static void main(String[] args) { 
  
    final SessionFactory sessionFactory = AnnotationHibernate.createSessionFactory();  
  
    // Run transaction 1 
    Thread t1 = new Thread() { 
        
      @Override
      public void run() { 
        Session session1 = sessionFactory.openSession(); 
        Transaction tx1 = null; 
        try { 
          tx1 = session1.beginTransaction(); 
          System.out.println("T1 - Begin trasaction"); 
          Thread.sleep(500); 
            
          Account account = (Account)  
              session1.get(Account.class, new Long(1), LockMode.UPGRADE); 
          System.out.println("T1 - balance=" + account.getBalance()); 
          Thread.sleep(500); 
            
          account.setBalance(account.getBalance() + 100); 
          System.out.println("T1 - Change balance:" + account.getBalance()); 
            
          tx1.commit(); 
          System.out.println("T1 - Commit transaction"); 
          Thread.sleep(500); 
        } 
        catch (Exception e) { 
          e.printStackTrace(); 
          if (tx1 != null) 
            tx1.rollback(); 
        }  
        finally { 
          session1.close(); 
        } 
      } 
        
    }; 
      
    // Run transaction 2 
    Thread t2 = new Thread() { 
        
      @Override
      public void run() { 
        Session session2 = sessionFactory.openSession(); 
        Transaction tx2 = null; 
        try { 
          tx2 = session2.beginTransaction(); 
          System.out.println("T2 - Begin trasaction"); 
          Thread.sleep(500); 
            
          Account account = (Account)  
              session2.get(Account.class, new Long(1), LockMode.UPGRADE); 
          System.out.println("T2 - balance=" + account.getBalance()); 
          Thread.sleep(500); 
            
          account.setBalance(account.getBalance() - 100); 
          System.out.println("T2 - Change balance:" + account.getBalance()); 
            
          tx2.commit(); 
          System.out.println("T2 - Commit transaction"); 
          Thread.sleep(500); 
        } 
        catch (Exception e) { 
          e.printStackTrace(); 
          if (tx2 != null) 
            tx2.rollback(); 
        }  
        finally { 
          session2.close(); 
        } 
      } 
        
    }; 
      
    t1.start(); 
    t2.start(); 
      
    while (t1.isAlive() || t2.isAlive()) { 
      try { 
        Thread.sleep(2000L); 
      } catch (InterruptedException e) { 
      } 
    } 
      
    System.out.println("Both T1 and T2 are dead."); 
    sessionFactory.close(); 
  
  } 
  
}

T1 - Begin trasaction
T2 - Begin trasaction
Hibernate: select account0_.col_id as col1_0_0_, account0_.col_balance as col2_0_0_ from tb_account account0_ with (updlock, rowlock) where account0_.col_id=?
Hibernate: select account0_.col_id as col1_0_0_, account0_.col_balance as col2_0_0_ from tb_account account0_ with (updlock, rowlock) where account0_.col_id=?
T2 - balance=100
T2 - Change balance:0
Hibernate: update tb_account set col_balance=? where col_id=?
T2 - Commit transaction
T1 - balance=0
T1 - Change balance:100
Hibernate: update tb_account set col_balance=? where col_id=?
T1 - Commit transaction
Both T1 and T2 are dead.

Hibernate will execute SQL for SQLServer 2005:

select account0_.col_id as col1_0_0_, account0_.col_balance as col2_0_0_ from tb_account account0_ with (updlock, rowlock) where account0_.col_id=?

Add row locks and update locks to the selected data row with col_id 1.

2. Optimistic locking: It is assumed that the data operated by the current transaction will not be accessed by other transactions at the same time, so it completely relies on the isolation level of the database
to automatically manage the lock work. Use version control in your application to avoid concurrency problems that may occur with low probability
.

In Hibernate, use the Version annotation to define the version number field.

将DirtyLock中的Account对象替换成AccountVersion，其他代码不变，执行出现异常。

package com.cdai.orm.hibernate.transaction; 
  
import javax.persistence.Column; 
import javax.persistence.Entity; 
import javax.persistence.Id; 
import javax.persistence.Table; 
import javax.persistence.Version; 
  
@Entity
@Table(name = "tb_account_version") 
public class AccountVersion { 
  
  @Id
  @Column(name = "col_id") 
  private long id; 
    
  @Column(name = "col_balance") 
  private long balance; 
    
  @Version
  @Column(name = "col_version") 
  private int version; 
  
  public AccountVersion() { 
  } 
  
  public AccountVersion(long id, long balance) { 
    this.id = id; 
    this.balance = balance; 
  } 
  
  public long getId() { 
    return id; 
  } 
  
  public void setId(long id) { 
    this.id = id; 
  } 
  
  public long getBalance() { 
    return balance; 
  } 
  
  public void setBalance(long balance) { 
    this.balance = balance; 
  } 
  
  public int getVersion() { 
    return version; 
  } 
  
  public void setVersion(int version) { 
    this.version = version; 
  } 
    
}

log如下：

T1 - Begin trasaction
T2 - Begin trasaction
Hibernate: select accountver0_.col_id as col1_0_0_, accountver0_.col_balance as col2_0_0_, accountver0_.col_version as col3_0_0_ from tb_account_version accountver0_ where accountver0_.col_id=?
Hibernate: select accountver0_.col_id as col1_0_0_, accountver0_.col_balance as col2_0_0_, accountver0_.col_version as col3_0_0_ from tb_account_version accountver0_ where accountver0_.col_id=?
T1 - balance=1000
T2 - balance=1000
T1 - Change balance:900
T2 - Change balance:1100
Hibernate: update tb_account_version set col_balance=?, col_version=? where col_id=? and col_version=?
Hibernate: update tb_account_version set col_balance=?, col_version=? where col_id=? and col_version=?
T1 - Commit transaction
2264 [Thread-2] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [com.cdai.orm.hibernate.transaction.AccountVersion#1]
   at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1934)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2578)
   at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2478)
   at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2805)
   at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:114)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:268)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:260)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:180)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
   at com.cdai.orm.hibernate.transaction.VersionLock$2.run(VersionLock.java:93)
Both T1 and T2 are dead.

由于乐观锁完全将事务隔离交给数据库来控制，所以事务1和2交叉运行了，事务1提交
成功并将col_version改为1，然而事务2提交时已经找不到col_version为0的数据了，所以
抛出了异常。

Hibernate查询方法比较
Hibernate主要有三种查询方法：

1.HQL (Hibernate Query Language)

和SQL很类似，支持分页、连接、分组、聚集函数和子查询等特性，
但HQL是面向对象的，而不是面向关系数据库中的表。正因查询语句
是面向Domain对象的，所以使用HQL可以获得跨平台的好处，Hibernate
会自动帮我们根据不同的数据库翻译成不同的SQL语句。这在需要支持
多种数据库或者数据库迁移的应用中是十分方便的。

但得到方便的同时，由于SQL语句是由Hibernate自动生成的，所以这不
利于SQL语句的效率优化和调试，当数据量很大时可能会有效率问题，
出了问题也不便于排查解决。

2.QBC/QBE (Query by Criteria/Example)

QBC/QBE是通过组装查询条件或者模板对象来执行查询的。这在需要
灵活地支持许多查询条件自由组合的应用中是比较方便的。同样的问题
是由于查询语句是自由组装的，创建一条语句的代码可能很长，并且
包含许多分支条件，很不便于优化和调试。

3.SQL

Hibernate也支持直接执行SQL的查询方式。这种方式牺牲了Hibernate跨
数据库的优点，手工地编写底层SQL语句，从而获得最好的执行效率，
相对前两种方法，优化和调试方便了一些。

下面来看一组简单的例子。

package com.cdai.orm.hibernate.query; 
  
import java.util.Arrays; 
import java.util.List; 
  
import org.hibernate.Criteria; 
import org.hibernate.Query; 
import org.hibernate.Session; 
import org.hibernate.SessionFactory; 
import org.hibernate.cfg.AnnotationConfiguration; 
import org.hibernate.criterion.Criterion; 
import org.hibernate.criterion.Example; 
import org.hibernate.criterion.Expression; 
  
import com.cdai.orm.hibernate.annotation.Account; 
  
public class BasicQuery { 
  
  public static void main(String[] args) { 
  
    SessionFactory sessionFactory = new AnnotationConfiguration(). 
                      addFile("hibernate/hibernate.cfg.xml").        
                      configure(). 
                      addPackage("com.cdai.orm.hibernate.annotation"). 
                      addAnnotatedClass(Account.class). 
                      buildSessionFactory(); 
  
    Session session = sessionFactory.openSession(); 
  
    // 1.HQL 
    Query query = session.createQuery("from Account as a where a.id=:id"); 
    query.setLong("id", 1); 
    List result = query.list(); 
    for (Object row : result) { 
      System.out.println(row); 
    } 
  
    // 2.QBC 
    Criteria criteria = session.createCriteria(Account.class); 
    criteria.add(Expression.eq("id", new Long(2))); 
    result = criteria.list(); 
    for (Object row : result) { 
      System.out.println(row); 
    } 
      
    // 3.QBE 
    Account example= new Account(); 
    example.setBalance(100); 
    result = session.createCriteria(Account.class). 
            add(Example.create(example)). 
            list(); 
    for (Object row : result) { 
      System.out.println(row); 
    } 
      
    // 4.SQL 
    query = session.createSQLQuery( 
        " select top 10 * from tb_account order by col_id desc "); 
    result = query.list(); 
    for (Object row : result) { 
      System.out.println(Arrays.toString((Object[]) row)); 
  } 
      
    session.close(); 
  } 
  
}

Hibernate: select account0_.col_id as col1_0_, account0_.col_balance as col2_0_ from tb_account account0_ where account0_.col_id=?
Account [id=1, balance=100]
Hibernate: select this_.col_id as col1_0_0_, this_.col_balance as col2_0_0_ from tb_account this_ where this_.col_id=?
Account [id=2, balance=100]
Hibernate: select this_.col_id as col1_0_0_, this_.col_balance as col2_0_0_ from tb_account this_ where (this_.col_balance=?)
Account [id=1, balance=100]
Account [id=2, balance=100]
Hibernate: select top 10 * from tb_account order by col_id desc
[2, 100]
[1, 100]

从log中可以清楚的看到Hibernate对于生成的SQL语句的控制，具体选择
哪种查询方式就要看具体应用了。

更多Java的Hibernate框架数据库操作中锁的使用和查询类型相关文章请关注PHP中文网！