简单的春季数据JPA事务到MySQL数据库上的小表
我有一个弹簧数据JPA查询，该查询从一个带有悲观锁的MySQL表中读取一行。在我的环境中，该表仅包含一行，因此应该很快。
Spring Data JPA函数命名为Findtopbyiscurrent（boolean iscurrent），该功能应仅检索表的唯一元素。
当命令运行时，它会无限期地悬挂。检查Show Engine Innodb状态的输出
---TRANSACTION 170279307,
ACTIVE 3859 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11665, OS thread handle 140237095196416, query id 770252923 10.51.5.32 root
TABLE LOCK table `mydb`.`mytable` trx id 170279307 lock mode IS
RECORD LOCKS space id 722 page no 3 n bits 72 index PRIMARY of table `mydb`.`mytable` trx id 170279307 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 34303030; asc 4000;;
1: len 4; hex 80000000; asc ;;
2: len 6; hex 0000000bf801; asc ;;
3: len 7; hex b2000001260110; asc & ;;
4: len 1; hex 01; asc ;;
此表仅用于该应用程序，并且Show Engine列表中没有其他交易。性能模式线程表没有ID 11665的线程。此外，当我登录DB并运行
SELECT * Mytable
时，我会立即收到我需要的任何信息，因此该表不是以防止阅读的方式锁定。
这是表（is_current是一个真实的位）
***************************
1. row
***************************
version: 4000
local_index: 0
is_current:
1 row in set (0.00 sec)
，这是一个随机的问题。在大约3/4的情况下，此错误没有出现。在1/4中，这在服务尝试的每笔交易中出现。进一步的调试表明，悬挂发生在查询的中间，块发生在我们有机会提交之前发生
I have a spring data jpa query that reads a row from a mysql table with a pessimistic lock. In my environment, this table only contains one row so it should be pretty fast.
The spring data jpa function is named FindTopByIsCurrent(boolean iscurrent) which should just retrieve the only element of the table.
When the command runs, it hangs indefinitely. Checking the output of SHOW ENGINE INNODB STATUS
---TRANSACTION 170279307,
ACTIVE 3859 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11665, OS thread handle 140237095196416, query id 770252923 10.51.5.32 root
TABLE LOCK table `mydb`.`mytable` trx id 170279307 lock mode IS
RECORD LOCKS space id 722 page no 3 n bits 72 index PRIMARY of table `mydb`.`mytable` trx id 170279307 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 34303030; asc 4000;;
1: len 4; hex 80000000; asc ;;
2: len 6; hex 0000000bf801; asc ;;
3: len 7; hex b2000001260110; asc & ;;
4: len 1; hex 01; asc ;;
This table is only for use by this one application and there are no other transactions in the list from show engine. The performance schema thread table doesn’t have a thread with id 11665. Additionally, when i log onto the db and run
select * from mytable
I immediately receive any information I need, so the table isn’t being locked in a way that prevents reads.
Here is the table (is_current is a true bit)
***************************
1. row
***************************
version: 4000
local_index: 0
is_current:
1 row in set (0.00 sec)
Additionally, this is a random issue. In about 3/4 of cases this error does not appear. In 1/4 this appears in every transaction the service attempts. Further debugging has shown that the hang occurs in the middle of the query, the block occurs before we have the opportunity to commit
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
因此，显然这是一个僵局，但与SQL无关。
我在另一个线程中运行另一笔交易。交易涉及从不同数据库（同一服务器）中的其他表中读取的交易。此外，我正在进行的这两个交易都是在其弹簧豆的后结构方法中进行的。我对SQL相关的原因有一种隧道的愿景，而忽略了无关阅读可能会导致这些交易显然需要锁定特定的单次供应和同时进行的问题的可能性
，并且它们各自锁定了两者之一，并互相僵持。
So apparently it is a deadlock, but it has nothing to do with SQL.
I was running another transaction in another thread. The transaction involved reading from a different table in a different database (same server). Additionally, both transactions I was running occurred in a PostConstruct method of their spring beans. I kind of tunnel visioned on SQL related causes and ignored the possibility that the unrelated read could cause an issue
These transactions apparently need to lock a specific SingletonSupplier and ConcurrentHashmap, and they each locked one of the two and deadlocked against each other.