Oracle 多线程批量更新造成死锁

不知道填什么 2020-07-28 11:10:25
场景：MID字段有唯一索引，数据提交给上级，上级会返回响应和报告，响应和报告都会根据MID更新到表里

问题：Oracle 多线程批量更新造成死锁

解决方案1：线程互斥锁，保证同时只会有一根线程在更新。能解决死锁问题，但更新有很高的延时，所以弃用

解决方案2：把响应和报告抽象为一个类，

避免出现上图情况，根据MID最后一位分组，线程1只会更新MID=***1，线程2更新MID=***2，以此类推，如下图

理论上就算多线程同时更新，也会互不影响。
但实际测试还是发生了死锁，直到ORA-00060: deadlock detected while waiting for resource，回滚后才看到有线程继续更新的日志
此方案比方案1快，但是未解决死锁问题，也不明白问题出在那里。

有请大佬指教下方案2的问题出在哪里，不胜感激
...全文
2826 20 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
不知道填什么 2020-09-10
  • 打赏
  • 举报
 回复
引用 21 楼 a798855256 的回复:
老哥，这个问题解决了吗？我遇到相同的问题了

就是更新之前先拿锁，拿不到锁就直接抛异常到代码，然后下次再更新
a798855256 2020-09-08
  • 打赏
  • 举报
 回复
老哥，这个问题解决了吗？我遇到相同的问题了
不知道填什么 2020-08-06
  • 打赏
  • 举报
 回复
引用 18 楼 lhdz_bj 的回复:
[quote=引用 17 楼 不知道填什么 的回复:][quote=引用 15 楼 lhdz_bj 的回复:][quote=引用 14 楼 不知道填什么 的回复:][quote=引用 13 楼 lhdz_bj 的回复:][quote=引用 11 楼 不知道填什么 的回复:][quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。[/quote]
那应该怎么处理呢，

更新之前先拿到锁，这样又会报其他错：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
感觉像是在更新同一条数据时报这个错，可是可以肯定的是不同线程更新的数据一定没有相同的
MID有唯一索引，还是说insert into的时候影响到了?[/quote]

insert 当然有影响，所有的DML都会有影响，都该算在里面。对于唯一索引来说，insert自己都可能形成死锁，这就是我上面问你是否还有其他应用在修改这张表，我说的修改包括insert,update,delete。[/quote]
我知道insert into会影响 以为也就影响update的速率而已
关键点是 我分析Oracle死锁日志时 就是上图得到X锁等S锁 就有点怀疑insert了 但是看session的sql却都是update的
所以 想不通[/quote]

1、如果死锁跟踪文件里的SQL只有update，那就和insert没关系，但你要彻底排除insert，你必须检查所有的死锁跟踪文件，如果都没有insert，那才能排除insert；
2、想到了一点，估计和你遇到的问题有关系，现在应用都会用到连接池，这样的话，应用端的线程和数据库端的会话，并非是一一对应的，可能会存在多个线程共用一个数据库端会话的问题，这样的话，虽然你应用端的多个线程间mid没重叠，但到数据库的会话端就未必了。[/quote]

一个对象一个连接，如果有Oracle异常会调用Close方法。个人感觉OracleCommand异常抛出是没有commit的
反编译看了下OracleCommand，一个方法接近1000行，有点懵，get不到重点。
受到上回复2的启发：

加上框中的语句，解决问题

虽然问题解决，却是绕过了问题，并没有解根本问题：死锁（现象：死锁；猜测：insert影响update死锁；排查：根据死锁日志并没有insert相关sql；结论：insert影响update死锁（无凭无据，很尴尬，所以结论不成立））

现在业务问题基本解决，感谢 lhdz_bj 帮助和解惑
lhdz_bj 2020-08-06
  • 打赏
  • 举报
 回复
根据楼主的反馈，应该还是不知什么原因，导致update获取锁失败，然后，会话一直持有锁，导致发生死锁，处理后的区别就是update获取锁失败后就提交了已经锁住的数据行，从而避免了死锁的发生。就如楼主所说，根本的问题还是存在，那就是导致update获取锁失败的原因没定位到，目前只是获取锁失败后对已经锁定的数据行进行了释放。
lhdz_bj 2020-07-31
  • 打赏
  • 举报
 回复
引用 17 楼 不知道填什么 的回复:
[quote=引用 15 楼 lhdz_bj 的回复:][quote=引用 14 楼 不知道填什么 的回复:][quote=引用 13 楼 lhdz_bj 的回复:][quote=引用 11 楼 不知道填什么 的回复:][quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。[/quote]
那应该怎么处理呢，

更新之前先拿到锁，这样又会报其他错：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
感觉像是在更新同一条数据时报这个错，可是可以肯定的是不同线程更新的数据一定没有相同的
MID有唯一索引，还是说insert into的时候影响到了?[/quote]

insert 当然有影响，所有的DML都会有影响，都该算在里面。对于唯一索引来说，insert自己都可能形成死锁，这就是我上面问你是否还有其他应用在修改这张表，我说的修改包括insert,update,delete。[/quote]
我知道insert into会影响 以为也就影响update的速率而已
关键点是 我分析Oracle死锁日志时 就是上图得到X锁等S锁 就有点怀疑insert了 但是看session的sql却都是update的
所以 想不通[/quote]

1、如果死锁跟踪文件里的SQL只有update，那就和insert没关系，但你要彻底排除insert，你必须检查所有的死锁跟踪文件，如果都没有insert，那才能排除insert；
2、想到了一点，估计和你遇到的问题有关系，现在应用都会用到连接池，这样的话，应用端的线程和数据库端的会话，并非是一一对应的，可能会存在多个线程共用一个数据库端会话的问题，这样的话，虽然你应用端的多个线程间mid没重叠，但到数据库的会话端就未必了。
不知道填什么 2020-07-31
  • 打赏
  • 举报
 回复
引用 15 楼 lhdz_bj 的回复:
[quote=引用 14 楼 不知道填什么 的回复:][quote=引用 13 楼 lhdz_bj 的回复:][quote=引用 11 楼 不知道填什么 的回复:][quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。[/quote]
那应该怎么处理呢，

更新之前先拿到锁，这样又会报其他错：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
感觉像是在更新同一条数据时报这个错，可是可以肯定的是不同线程更新的数据一定没有相同的
MID有唯一索引，还是说insert into的时候影响到了?[/quote]

insert 当然有影响，所有的DML都会有影响，都该算在里面。对于唯一索引来说，insert自己都可能形成死锁，这就是我上面问你是否还有其他应用在修改这张表，我说的修改包括insert,update,delete。[/quote]
我知道insert into会影响 以为也就影响update的速率而已
关键点是 我分析Oracle死锁日志时 就是上图得到X锁等S锁 就有点怀疑insert了 但是看session的sql却都是update的
所以 想不通
lhdz_bj 2020-07-31
  • 打赏
  • 举报
 回复
引用 14 楼 不知道填什么 的回复:
[quote=引用 13 楼 lhdz_bj 的回复:][quote=引用 11 楼 不知道填什么 的回复:][quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。[/quote]
那应该怎么处理呢，

更新之前先拿到锁，这样又会报其他错：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
感觉像是在更新同一条数据时报这个错，可是可以肯定的是不同线程更新的数据一定没有相同的
MID有唯一索引，还是说insert into的时候影响到了?[/quote]

另外，到底是什么SQL语句引起的死锁，死锁图下面应该有具体的SQL语句，你可以看下。
lhdz_bj 2020-07-31
  • 打赏
  • 举报
 回复
引用 14 楼 不知道填什么 的回复:
[quote=引用 13 楼 lhdz_bj 的回复:][quote=引用 11 楼 不知道填什么 的回复:][quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。[/quote]
那应该怎么处理呢，

更新之前先拿到锁，这样又会报其他错：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
感觉像是在更新同一条数据时报这个错，可是可以肯定的是不同线程更新的数据一定没有相同的
MID有唯一索引，还是说insert into的时候影响到了?[/quote]

insert 当然有影响，所有的DML都会有影响，都该算在里面。对于唯一索引来说，insert自己都可能形成死锁，这就是我上面问你是否还有其他应用在修改这张表，我说的修改包括insert,update,delete。
不知道填什么 2020-07-31
  • 打赏
  • 举报
 回复
引用 13 楼 lhdz_bj 的回复:
[quote=引用 11 楼 不知道填什么 的回复:][quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。[/quote]
那应该怎么处理呢，

更新之前先拿到锁，这样又会报其他错：ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
感觉像是在更新同一条数据时报这个错，可是可以肯定的是不同线程更新的数据一定没有相同的
MID有唯一索引，还是说insert into的时候影响到了?
lhdz_bj 2020-07-29
  • 打赏
  • 举报
 回复
那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。
不知道填什么 2020-07-29
  • 打赏
  • 举报
 回复
引用 5 楼 lhdz_bj 的回复:
拿到死锁图就更好了，你看下死锁的两个会话的SQL，就清楚了。理论是没问题的，但估计是实现过程中，并没分清楚，应该是不同线程间的数据行还是有相同或重复的情况。
另外，你的死锁图没贴全，程序里的update语句，是按照mid字段检索的吗？如果不是，即使你按照mid字段分组，并且实现没问题，也是没用的。

会话的sql都是一样的，只是数据不同，因为sql绑定的变量，所以看不到sql具体更新的数据
lhdz_bj 2020-07-29
  • 打赏
  • 举报
 回复
引用 11 楼 不知道填什么 的回复:
[quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

1、不仅仅是393和8的问题，是四个会话发生了循环死锁；
2、应该是先获得S锁，更新时会转成X锁。
lhdz_bj 2020-07-29
  • 打赏
  • 举报
 回复
引用 11 楼 不知道填什么 的回复:
[quote=引用 9 楼 lhdz_bj 的回复:][quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁[/quote]

死锁图里报出的SQL语句，都是相同一个吗？除了你的这个应用，还会有其他应用也在同时修改这个表吗？
不知道填什么 2020-07-29
  • 打赏
  • 举报
 回复
引用 9 楼 lhdz_bj 的回复:
[quote=引用 8 楼 不知道填什么 的回复:][quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？[/quote]

有报错死锁的



393 8 487 202 各自拿到了X锁
393 等 8 的S锁
8 等 487 的S锁
...

我测试了故意死锁 上图1箭头指向的地方都是X锁 才是正常的情况

想不通为什么会是 393拿到X锁 等得确实8的S锁
lhdz_bj 2020-07-29
  • 打赏
  • 举报
 回复
引用 8 楼 不知道填什么 的回复:
[quote=引用 7 楼 lhdz_bj 的回复:]那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的 [/quote]

那你这次统计过程中，报出死锁问题了吗？
不知道填什么 2020-07-29
  • 打赏
  • 举报
 回复
引用 7 楼 lhdz_bj 的回复:
那就把程序运行时，每个线程的线程号和实际处理的mid值存储到一张表的两个字段里，回头统计下看看，不同线程的mid是否有相同或重叠。


统计了下更新数据 没有相同的
lhdz_bj 2020-07-28
  • 打赏
  • 举报
 回复
拿到死锁图就更好了，你看下死锁的两个会话的SQL，就清楚了。理论是没问题的，但估计是实现过程中，并没分清楚，应该是不同线程间的数据行还是有相同或重复的情况。
另外，你的死锁图没贴全，程序里的update语句，是按照mid字段检索的吗？如果不是，即使你按照mid字段分组，并且实现没问题，也是没用的。
不知道填什么 2020-07-28
  • 打赏
  • 举报
 回复
查了当时死锁情况，

从代码逻辑上说，更新位数3的线程只会更新3，更新5的只会更新5
但是错误日志 却显示的是如帖子图1的逻辑错误

很尴尬
很诧异
很迷茫
不知道填什么 2020-07-28
  • 打赏
  • 举报
 回复
引用 1 楼 lhdz_bj 的回复:
1、你应用里有并发对一个表的修改（I,U,D）；
2、不同会话并发修改的数据行可能会相同；
3、同一会话内有多个修改SQL未提交或批量提交的情况。
4、建议：
1）每个修改提交一次；
2）批量提交时，不同会话修改的数据行做到各不相同。


是批量提交的，上面的图2对数据分组 就是为了各个线程更新的数据一定不同
lhdz_bj 2020-07-28
  • 打赏
  • 举报
 回复
1、你应用里有并发对一个表的修改（I,U,D）；
2、不同会话并发修改的数据行可能会相同；
3、同一会话内有多个修改SQL未提交或批量提交的情况。
4、建议：
1）每个修改提交一次；
2）批量提交时，不同会话修改的数据行做到各不相同。
ORA-00060: 等待资源时检测到死锁--oracle 数据库表死锁异常
有关表死锁的详细图片 博文链接：https://meteor-1988.iteye.com/blog/1568695
程序进程条 delphi 源码 数据库 多线程
程序进程条程序进程条程序进程条程序进程条程序进程条程序进程条
日积月累-oracle笔记
oracle自学过程中的笔记，及遇到问题的记录及解答
oracle oci
how to use oracle oci
IBM Thread and Monitor Dump Analyzer (TMDA)
TMDA
Oracle

17,140

社区成员

55,259

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章