后台频繁创建事务修改数据库，导致频繁锁表

在后台多个线程频繁建立事务修改数据，经常造成数据库锁表，导致其它线程无法读取或修改表中数据而报错，例如：

线程1：从a表中读取一批数据到dataset中，循环处理每一条数据
       foreach(datarow row in dataset.tables[0].Rows){
      //对每一条数据进行处理
  ....
  //建立事务
  
  修改b表数据
  修改其它表中相关数据
  
  //提交事务
   }

线程2：从b表中读取一批数据到dataset中，循环处理每一条数据
       foreach(datarow row in dataset.tables[0].Rows){
      //对每一条数据进行处理
  ....
  //建立事务
  
  修改b表数据
  修改其它表中的相关数据
  
  //提交事务
   }

在后台有一个定时器，每隔1分钟执行一次线程1，每隔1分钟执行一次线程2。由于两个线程都频繁创建事务修改b表，b表经常被锁，使得其中某一线程无法读取或者执行事务失败。
想把所有的update拼接在一起（用分号分隔），批量执行，不用频繁建立事务，但是经常由于字段太多，sql太长不能执行，
或者由于其中一条执行失败，这一批数据都要回滚，下一次又要从头执行，重新处理这一批数据。

所以想请问各位大侠，有什么办法可以尽可能减小锁表的频率，是否有别的方式可以进行批量处理？？

32 个解决方案

#1

每条判断  未免非常麻烦。可以统一提交，也就是 在提交之前 就验证数据完整性。将那些正确的数据存在一个dt或者拼接成sql，再一次性更新。

#2

循环处理每一条数据
这种操作为什么要用事务?
单条记录update不成功跟其他数据有什么关系?

大不了哪个执行不成功,就重试那一条SQL就行了,为啥要都回滚?

#3

如果不是数据库或网络原因造成的操作不成功,而是因为数据本身格式有问题
这你应该在执行之前就先判断,而不是让数据库去判断SQL语句是否合法

#4

引用 2 楼 Z65443344 的回复:

循环处理每一条数据
这种操作为什么要用事务?
单条记录update不成功跟其他数据有什么关系?

大不了哪个执行不成功,就重试那一条SQL就行了,为啥要都回滚?

人家的事务是再循环内的大哥。

#5

这个问题有两种解决方案，第一个解决方案是休眠，一个循环跑完休眠一会释放time
第二个解决方案就是你这个不是因为事务锁表导致的问题，而是因为死锁的问题，
可以通过优化sql语句的方式解决
例如update 的更新条件，具体怎么做可以参考sql 死锁之类的文档。

#6

引用 3 楼 Z65443344 的回复:

如果不是数据库或网络原因造成的操作不成功,而是因为数据本身格式有问题
这你应该在执行之前就先判断,而不是让数据库去判断SQL语句是否合法

数据库的原因是不可避免的，所以事务是必须的。

#7

http://blog.csdn.net/ajianchina/article/details/46807131
药到病除！

#8

引用 楼主 green66 的回复:

想把所有的update拼接在一起（用分号分隔），批量执行，不用频繁建立事务，

你不显示地写事务语句，那么数据库默认地为每一条sql语句启动一个事务。
你多条语句拼成一个字符串，数据库也还是认识它是多条语句。

所以拼成一个字符串，只是为了应用程序写代码时的一种“手工”上少敲几行代码，说成是“批量执行、不用建立事务”都是错误的！

#9

#10

另外谁知道上述文字哪里”包含非法词组“？

什么语言伤害到csdn哪位垃圾程序员了？

#11

用存储过程,这个最简单直接.

#12

引用 11 楼 caoqinghua 的回复:

用存储过程,这个最简单直接.

不会是指得把“foreach(datarow row in dataset.tables[0].Rows)”语句也给“用存储过程”吧？

如果写5、6行sql语句（在一个显示的事务中），与“每当遇到这种代码都额外花很长时间去设计存储过程”相比，没有什么差别。数据库会把sql语句编译结果保存起来，并且重复使用（自动把常量变为变量从而复用编译结果）。存储过程只是一种“体验”的差别，看不出技术差别。遇到这类问题时，与之无关。

#13

在真实的项目中遇到类似问题，如果还是纠结在个别“语句优化”上，可能你这时就走不远了。

#14

引用 12 楼 sp1234 的回复:

Quote: 引用 11 楼 caoqinghua 的回复:

用存储过程,这个最简单直接.

不会是指得把“foreach(datarow row in dataset.tables[0].Rows)”语句也给“用存储过程”吧？

如果写5、6行sql语句（在一个显示的事务中），与“每当遇到这种代码都额外花很长时间去设计存储过程”相比，没有什么差别。数据库会把sql语句编译结果保存起来，并且重复使用（自动把常量变为变量从而复用编译结果）。存储过程只是一种“体验”的差别，看不出技术差别。遇到这类问题时，与之无关。

一般出现锁表都是逻辑处理上出了问题.用存储过程,可以让楼主重新梳理逻辑处理流程.

#15

引用 9 楼 sp1234 的回复:

第4条,我前一段时间碰见过你描述的超市收银问题.
客户端是flash socket接收数据 js处理数据 .后台采集数据后实时发送json到客户端,没有使用队列(随来随时服务),在客户端带5台以内设备没有问题.当带20台设备后.浏览器单线程处理js不及时 就开始间歇丢包.后来是用队列解决问题.
原则也得分具体应用场景.我觉得只要解决好读写分离,一般不会有死锁问题

#16

引用 1 楼 duanzi_peng 的回复:

每条判断  未免非常麻烦。可以统一提交，也就是 在提交之前 就验证数据完整性。将那些正确的数据存在一个dt或者拼接成sql，再一次性更新。

业务要求，每条都要进行特殊处理.

#17

引用 11 楼 caoqinghua的回复:

用存储过程,这个最简单直接.

业务决定，没办法用存储过程

#18

引用 2 楼 Z65443344 的回复:

循环处理每一条数据
这种操作为什么要用事务?
单条记录update不成功跟其他数据有什么关系?

大不了哪个执行不成功,就重试那一条SQL就行了,为啥要都回滚?

这个是看具体业务需求的，有时允许某些失败，有时不允许某些失败。
而且把失败的单独存起来下次再执行的逻辑也很麻烦。

但在执行SQL前检查是需要的，不能都交给数据库那边来检查。

#19

引用 18 楼 Raffin 的回复:

Quote: 引用 2 楼 Z65443344 的回复:

循环处理每一条数据
这种操作为什么要用事务?
单条记录update不成功跟其他数据有什么关系?

大不了哪个执行不成功,就重试那一条SQL就行了,为啥要都回滚?

这个是看具体业务需求的，有时允许某些失败，有时不允许某些失败。
而且把失败的单独存起来下次再执行的逻辑也很麻烦。

但在执行SQL前检查是需要的，不能都交给数据库那边来检查。

这基本上就象 #4 楼所说的，一时没有看清 lz 是在 foreach 循环内才使用事务，因此其实 lz 的事务并不是“不允许某些失败”。

这时候出现的问题，不是“单独两个事务卡死其中一个”出现的问题，而是两个 foreach 语句中的大量出现的“小”事务交互地相互“卡死对方”出现的问题。

#20

其实这类问题，首先应该从业务逻辑设计上去分析瓶颈问题。例如假设每一个小事务中都需要查询一下“B表中当天最后一条数据的发生时间”，那么这就足矣产生瓶颈。如果在foreach之前先查询出来，然后再在 foreach 中应用到每一个事务中去，那么这就可能让性能提高100倍了。

当然这只是瞎举一个例子而已，并不是针对 lz 的代码的。这里想说明的就是“高层次的”逻辑设计需要业务知识作为支持，从业务逻辑上先说通了逻辑的正确性，然后运用到具体的编码实施上，这比你只知道纠结个别“语句优化”这个角度往往更有效果。

#21

1.事务尽量小，执行时间尽可能短。里面最好不要包含很长和耗时的操作，比如select脏的操作最好不要包含进去；
2.设置隔离级别；
3.是否可以错开时间？是否2个线程获取的语数据杭高重复？这个业务上的东西就没法怎么说了。

#22

线程解耦，事务解耦

#23

  //建立事务 
    foreach(datarow row in dataset.tables[0].Rows){
      //对每一条数据进行处理
  ....
  修改b表数据
  修改其它表中相关数据
  
   }
  //提交事务
放外面不就行了

#24

真正解决的办法使用存储过程吧。哪有公司会让写这样的代码。你们的代码审查怎么过的。

#25

不了解为啥楼主要两个线程处理同一个而且具有排他性质的资源？
首先建议考虑清楚为啥用事务？像楼主所说拼接sql会超长，那处理的数据肯定不少了。这时候用事务是不是不合理的。
如果楼主一定要类似事务方式批量处理，如果数据库正好是sqlserver，可以考虑存储国产，把要处理的数据存为datatable,然后把datatable传递给存储过程。存储过程里用事务

#26

引用 2 楼 Z65443344 的回复:

这时候出现的问题，不是“单独两个事务卡死其中一个”出现的问题，而是两个 foreach 语句中的大量出现的“小”事务交互地相互“卡死对方”出现的问题。

sp1234 以专业开发人员为伍 ，说得对，就是多个小事务同时操作一张表，导致这张表频繁被锁

#27

引用 26 楼 green66 的回复:

Quote: 引用 2 楼 Z65443344 的回复:

这时候出现的问题，不是“单独两个事务卡死其中一个”出现的问题，而是两个 foreach 语句中的大量出现的“小”事务交互地相互“卡死对方”出现的问题。

sp1234 以专业开发人员为伍 ，说得对，就是多个小事务同时操作一张表，导致这张表频繁被锁

但是，我不可避免地要用这些小事务，才能保证数据的准确性、一致性

#28

引用 21 楼 smthgdin 的回复:

1.事务尽量小，执行时间尽可能短。里面最好不要包含很长和耗时的操作，比如select脏的操作最好不要包含进去；
2.设置隔离级别；
3.是否可以错开时间？是否2个线程获取的语数据杭高重复？这个业务上的东西就没法怎么说了。

主要是被锁的那张表，很多人的业务逻辑都要用到，而且每个人的逻辑里面可能还不止一个事务，就向我的逻辑里面，设计这张表的就至少要三个定时执行的线程，相隔时间都比较近

#29

集中处理，批量提交

#30

允许多个线程在同一时刻对同一记录进行修改，这个业务逻辑值得商榷
表中总是保存最后一次的修改，这就谈不上 数据的准确性、一致性

为什么会是频繁锁表？这种情况应该是使用 行锁 的
如果使用表锁，那么并行的作业全都变成了串行的，显然与你的设计初衷不符

#31

该回复于2015-07-10 16:52:35被管理员删除

#32

非常感谢大家的回复，从高手们的讨论中也学到了很多东西！谢谢！