gpt4 book ai didi

mysql - InnoDB 死锁未提交读! - Java - Glassfish - EJB3 (JPA/Hibernate)

转载 作者:行者123 更新时间:2023-11-29 03:48:46 25 4
gpt4 key购买 nike

几天前我在使用 Glassfish - EJB3 和 Mysql InnoDB 的 Java 应用程序上遇到死锁问题

配置:Mysql InnoDB:版本 14.12 Distrib 5.0.51a,适用于使用 readline 5.2 的 debian-linux-gnu (i486)

应用服务器:Glassfish v2.1

持久化 EJB3 - JPA - Hibernate

为了简单起见,我有- 带有 servlet 的 SOA 系统,用于处理用户订阅服务、登录、注销、支付和注册等...- quartz 作业系统(cron 触发器)处理这些服务的每日递减、“低信用”警告的生成、付款验证等......

我的问题:在负载测试期间我到处都有死锁(100 000 个用户模拟 - 30 个请求/秒)

返回的堆栈样本:

Message ID: 
Could not synchronize database state with session org.hibernate.exception.LockAcquisitionException

Complete Message:
Could not execute JDBC batch update at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:105) at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at
org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) at
org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114) at
org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109) at
org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244) at
org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2382) at
org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2335) at
org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2635) at
org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:115) at
org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) at
org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168) at
org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at
org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64) at
org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:996) at
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1141) at
org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at
org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:67) at
net.xxx.server.dao.impl.PaymentDAOImpl.listPaymentsByStateAndCompany(PaymentDAOImpl.java:270)

注意最后,这是我完成的代码:net.xxx.server.dao.impl.PaymentDAOImpl.listPaymentsByStateAndCompany(PaymentDAOImpl.java:270)

这个函数:

private static final String QUERY_FOR_PAYMENTS_BY_STATE_AND_COMPANY = " FROM " + Payment.class.getName()
+ " p WHERE p.serviceDefinition.company=:company"
+ " AND p.state = :state";

@SuppressWarnings("unchecked")
public List<Payment> listPaymentsByStateAndCompany(Company company,Constants.PaymentState state) {
List<Payment> payments = this.getEntityManager()
.createQuery(QUERY_FOR_PAYMENTS_BY_STATE_AND_COMPANY)
.setParameter("state",state.ordinal())
.setParameter("company",company)
.getResultList();
return payments;
}

此功能在未进行负载测试时运行良好,例如我们每 5 秒有 1 个请求。

在负载测试期间,我们的作业运行频率很高(例如每 5 秒一次)。

我不仅得到这个错误,还有其他一些人在其他工作中也遇到过(仍然是死锁)!

在 MYSQL 上:

Example of deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
090428 12:21:11
*** (1) TRANSACTION:
TRANSACTION 0 14286818, ACTIVE 0 sec, process no 21872, OS thread id 802850 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1024, undo log entries 2
MySQL thread id 298, query id 11843357 localhost 127.0.0.1 root Updating
/* */ update service set balance=40.0, company_id=2, last_on='2009-04-28 12:19:55', modified_by='server', modified_on='2009-04-28 12:21:11', service_definition_id=3, state=1, subscriber_id=13578, valid_until='2010-02-22 12:13:52' where service_id=693
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 62 n bits 176 index `PRIMARY` of table `xxx/service` trx id 0 14286818 lock_mode X locks rec but not gap waiting
Record lock, heap no 98 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 8; hex 80000000000002b5; asc ;; 1: len 6; hex 000000d9faa0; asc ;; 2: len 7; hex 0000000cc91e70; asc p;; 3: len 4; hex 00001c42; asc B;; 4: len 8; hex 80001245aad4e363; asc E c;; 5: len 6; hex 736572766572; asc server;; 6: len 8; hex 80001245aad4e3c9; asc E ;; 7: len 1; hex 81; asc ;; 8: len 8; hex 80001247f200df08; asc G ;; 9: len 8; hex 8000000000000002; asc ;; 10: len 8; hex 8000000000000003; asc ;; 11: len 8; hex 800000000000350a; asc 5 ;;

*** (2) TRANSACTION:
TRANSACTION 0 14286798, ACTIVE 1 sec, process no 24963, OS thread id 393239 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
17 lock struct(s), heap size 1024, undo log entries 16
MySQL thread id 253, query id 11843359 localhost 127.0.0.1 root Updating
/* */ update payment set credit=1.0, currency='EUR', modified_by='9999900092', modified_on='2009-04-28 12:21:11', payment_definition_id=7, price=1.0, service_definition_id=3, state=0, subscriber_id=13578, transaction_id=11463 where payment_id=15914
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 62 n bits 176 index `PRIMARY` of table `xxx/service` trx id 0 14286798 lock mode S locks rec but not gap
Record lock, heap no 47 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 8; hex 8000000000000286; asc ;; 1: len 6; hex 000000d9ffce; asc ;; 2: len 7; hex 0000000cc90683; asc ;; 3: len 4; hex 0000f841; asc A;; 4: len 8; hex 80001245aad4e3b2; asc E ;; 5: len 6; hex 736572766572; asc server;; 6: len 8; hex 80001245aad4e3ff; asc E ;; 7: len 1; hex 81; asc ;; 8: len 8; hex 80001245d450fed8; asc E P ;; 9: len 8; hex 8000000000000002; asc ;; 10: len 8; hex 8000000000000003; asc ;; 11: len 8; hex 80000000000034db; asc 4 ;;

事务隔离

我在互联网上阅读了有关事务隔离的内容。

在 glassfish 上我们可以设置事务隔离级别,我把它设置为未提交读。

不行,那我在mysql中设置同级:

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED |
+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

SVP 谁能告诉我可能是什么问题?我真的不知道!!!!

顺便说一句,我在互联网上看到您可以为每个请求选择事务隔离级别...是否可以直接为 JPA 上的方法设置事务隔离级别?因为我认为只有进行全局数据更新的作业(例如递减 15000 项服务)才应该未提交读取,我错了吗?

最佳答案

对于您的问题,我没有确切的答案,但这可能会帮助您缩小范围。

死锁可能发生在任何事务隔离级别,因为 innodb 会在更新上设置锁,即使是在“未提交读”时也是如此。

你可以用这个简单的场景来测试它:

CREATE TABLE locktest (a INT(11), b INT(11), PRIMARY KEY (a)) ENGINE=INNODB;
INSERT INTO locktest VALUE (1, 1);
INSERT INTO locktest VALUE (2, 1);

然后,打开 2 个 mysql 控制台(C1 和 C2)并按顺序运行这些命令:

C1> BEGIN;
C2> BEGIN;
C1> UPDATE locktest SET b = b + 1 WHERE a = 1;
C2> UPDATE locktest SET b = b + 1 WHERE a = 2;
C1> UPDATE locktest SET b = b + 1 WHERE a = 2;
C2> UPDATE locktest SET b = b + 1 WHERE a = 1;

您会看到 C2 上出现死锁,而 C1 即使在读取未提交时也能成功完成。如果您查看引擎日志,您会看到类似的报告。

如果删除表上的主键,命令会更早阻塞,这是因为如果有索引覆盖正在设置锁的查询,innodb 锁定会更好地工作。

所以,回到你的问题。

您应该检查以死锁结束的事务中涉及的所有查询,并确保存在适当的索引。如果 MySQL 必须进行全表扫描,它将结束锁定超过它需要的数量。

这些 tips帮助我解决了应用程序中的一些死锁。防止死锁的一个好方法是使用“SELECT ... FOR UPDATE”设置写锁以锁定某些父行。

因此,例如,如果您有多个事务试图更新某些特定的客户数据,您可以发出“SELECT id FROM customer WHERE id=123 FOR UPDATE”,他们将在此时按顺序等待,而不是结束持有锁定彼此需要。

关于mysql - InnoDB 死锁未提交读! - Java - Glassfish - EJB3 (JPA/Hibernate),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/797883/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com