Oracle大批量更新数据导致死锁的问题

最新推荐文章于 2024-07-03 22:24:10 发布

不愿透露姓名的胡某某

最新推荐文章于 2024-07-03 22:24:10 发布

阅读量5.8k

点赞数 2

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_44355924/article/details/107396960

版权

Oracle批量更新

今天接了个需求要我更新四十万条数据,因为从来没有更新过这么多条数据,就按照自己心里面想的方法更新了,没想到把数据库干死锁了…
先上原来的sql

update mater_fac_applic_form_head head
   set head.arrtibutes2 =
       (select distinct to_char(his.creation_date,'yyyy-mm-dd')
          from dbpm_approval_history his, dbpm_documents dbpm
         where his.node_name like '%开具%'
         and dbpm.status='COMPLETED'
         and his.creation_date=(select max(his2.creation_date) from dbpm_approval_history his2 where his.document_id=his2.document_id)
         and head.document_id = dbpm.document_id
         and his.document_id=head.document_id);

原来采用这种更新方法,如果更新几千条还是可以的,但是数据量一大起来而且没有commit,就会导致数据库死锁
在经过了上一次惨痛的教训,我又回来了!!!
先上sql

select 'update mater_fac_from_head_copy head set head.arrtibutes2 = ''' ||
       to_char(dbpm.doc_create_time, 'yyyy-mm-dd') ||
       ''' where head.document_id = ' || dbpm.document_id || ';'
  from mater_fac_from_head_copy head2, dbpm_documents dbpm
 where dbpm.document_id = head2.document_id
   and head2.is_back is null

这种可以说是拼接sql,一条一条的拼接出来,然后复制到文本器里,这种方法虽然很low还慢吧~但是它稳呀,
数据库是绝对不会死锁的,拼接出来就是这种格式啦

update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258575;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258576;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258577;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258585;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258586;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258589;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258590;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258591;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258592;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258593;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258594;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258595;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258596;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258599;
update mater_fac_applic_form_head head set head.arrtibutes2 = '2020-04-09' where head.document_id = 258601;
commit;