Hibernate and database lock 1. Why use locks?
To figure out why the lock mechanism exists, you must first understand the concept of transactions.
A transaction is a series of related operations on a database, and it must have ACID characteristics:
Our commonly used relational database RDBMS implements these characteristics of transactions. Among them, atomicity,
Consistency and persistence are guaranteed by logging. The isolation is achieved by the locking mechanism we are concerned about today, which is why we need the locking mechanism.
If there is no lock and no control over isolation, what consequences may be caused?
Let’s take a look at the example of Hibernate. Two threads start two transaction operations respectively. The same row of data in the tb_account table is col_id=1.
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 end result may be 0 or 200, and the update of transaction 1 or 2 may be lost. The log output also confirms this, transactions 1 and 2
log cross-print.
T1 - Begin trasactionT2 - Begin trasactionHibernate: 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=100T2 - balance=100T2 - Change balance:0T1 - Change balance:200Hibernate: update tb_account set col_balance=? where col_id=?Hibernate: update tb_account set col_balance=? where col_id=?T1 - Commit transactionT2 - Commit transactionBoth T1 and T2 are dead.
It can be seen that isolation is a matter that needs careful consideration and it is necessary to understand locks.
2. How many types of locks are there?
Common ones are shared locks, update locks and exclusive locks.
1. Shared lock: used for reading data operations, allowing other transactions to be read simultaneously. 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 be read or modified. When the transaction executes insert,
When update and delete are updated, the database will be automatically allocated.
3. Update lock: used to avoid deadlocks caused by shared locks during update operations, such as transactions 1 and 2 holding shared locks at the same time and waiting to obtain exclusive locks. When performing update, the transaction first acquires the update lock and then upgrades the update lock to an exclusive lock, thus avoiding deadlock.
In addition, these locks can all be applied to different objects in the database, i.e., 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 types of locks. It is too difficult to fully master and use so many locks flexibly. We are not DBAs.
what to do? Fortunately, the lock mechanism is transparent to ordinary users. The database will automatically add appropriate locks and automatically upgrade and downgrade various locks at the right time. It's 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 provides four transaction isolation levels for users to choose from:
1.Serializable: When two transactions manipulate the same data at the same time, transaction 2 can only stop and wait.
2.Repeatable Read (repeatable): Transaction 1 can see newly inserted data from Transaction 2, and cannot see updates to existing data.
3.Read Committed (read committed data): Transaction 1 can see newly inserted and updated data from Transaction 2.
4.Read Uncommitted (read uncommitted data): Transaction 1 can see insertion and update data that transaction 2 has not committed.
4. Locks in the application
When the database adopts the Read Commission isolation level, pessimistic locks or optimistic locks can be used in the application.
1. Pessimistic lock: Assume that the data of the current transaction operation will definitely have other transaction access, so pessimistically specify that the exclusive lock is used in the application to lock the data resources. Support the following forms in MySQL and Oracle:
select ... for update
Explicitly let the select use exclusive lock lock to lock the records of the query. For other transactions to query, update or delete these locked data, they must wait until the transaction is over.
In Hibernate, you can pass in LockMode.UPGRADE when loading to adopt pessimistic lock. Modify the previous example,
At the get method calls of transactions 1 and 2, an additional LockMode parameter is passed in. As can be seen from the log, transactions 1 and 2
It is no longer cross-running, transaction 2 can wait for transaction 1 to finish before reading the data, so the final col_balance value is correct 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 trasactionT2 - Begin trasactionHibernate: 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=100T2 - Change balance:0Hibernate: update tb_account set col_balance=? where col_id=?T2 - Commit transactionT1 - balance=0T1 - Change balance:100Hibernate: update tb_account set col_balance=? where col_id=?T1 - Commit transactionBoth T1 and T2 are dead.
Hibernate executes SQL for SQLServer 2005:
The code copy is as follows:
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=?
2. Optimistic lock: Assume that the data of the current transaction operation will not be accessed at the same time by other transactions, so the isolation level of the database is completely relied on the database to automatically manage the lock's work. Adopt version control in applications to avoid concurrency problems that may occur at low probability.
In Hibernate, use Version annotations to define the version number field.
Replace the Account object in DirtyLock with AccountVersion, and the other code remains unchanged, and an exception occurs when the execution occurs.
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; } }The log is as follows:
T1 - Begin trasactionT2 - Begin trasactionHibernate: 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=1000T2 - balance=1000T1 - Change balance:900T2 - Change balance:1100Hibernate: 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 transaction2264 [Thread-2] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with sessionorg.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.
Since the optimistic lock completely leaves transaction isolation to the database for control, transactions 1 and 2 run cross-run, transaction 1 is successfully committed and col_version is changed to 1. However, when transaction 2 commits, data with col_version of 0 can no longer be found, so an exception was thrown.
Comparison of Hibernate query methods
There are three main query methods for Hibernate:
1.HQL (Hibernate Query Language)
It is very similar to SQL, and supports features such as paging, connection, grouping, aggregation functions and subquery.
But HQL is object-oriented, not tables in relational databases. Because query statements are oriented towards Domain objects, using HQL can gain cross-platform benefits. Hibernate
It will automatically help us translate into different SQL statements according to different databases. This is very convenient in applications that need to support multiple databases or database migrations.
But while getting it convenient, since SQL statements are automatically generated by Hibernate, this is not conducive to the efficiency optimization and debugging of SQL statements. When the amount of data is large, there may be efficiency problems.
If there is a problem, it is not convenient to investigate and resolve it.
2.QBC/QBE (Query by Criteria/Example)
QBC/QBE performs query by assembling query conditions or template objects. This is convenient in applications that require flexible support for many free combinations of query conditions. The same problem is that since the query statement is freely assembled, the code to create a statement can be long and contains many branching conditions, which is very inconvenient for optimization and debugging.
3.SQL
Hibernate also supports query methods that directly execute SQL. This method sacrifices the advantages of Hibernate cross-database and manually writes the underlying SQL statements to achieve the best execution efficiency.
Compared with the first two methods, optimization and debugging are more convenient.
Let’s take a look at a set of simple examples.
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]
From the log, you can clearly see Hibernate's control over the generated SQL statements. The specific query method to choose depends on the specific application.