首页新闻找找看学习计划

SQL2005连接Oracle死锁的问题,在线等。。。

0
悬赏园豆:20 [待解决问题]

各位大神们,最近遇到一挺棘手的问题,服务器上SQL2005需要每天凌晨取另一台Oracle上的数据,使用的是作业的方式,但是最近老是会死锁,作业一直在执行中,有时又很快执行完成,这种情况最近老是出现,求大神帮忙看看语句是否有问题,拜谢!

 

DECLARE @ST_DATE AS varchar(10)
DECLARE @ED_DATE AS varchar(10)
DECLARE @TEMP_DATE varchar(10)

set @ED_DATE=convert(varchar(10),getdate(),120)
SET @ST_DATE=CONVERT(VARCHAR(10),dateadd(day,-1,@ED_DATE),120)



begin tran

    delete hisdb2.dbo.his_charge_acc_i where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_acc_i
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag from data_hrp.HIS_CHARGE_ACC_I')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

begin tran

    delete hisdb2.dbo.his_charge_acc_mz where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_acc_mz
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type from data_hrp.HIS_CHARGE_ACC_MZ')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

begin tran

    delete hisdb2.dbo.his_charge_acc_tj where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_acc_tj
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type from data_hrp.HIS_CHARGE_ACC_TJ')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

begin tran

    delete hisdb2.dbo.his_charge_acc_zy where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_acc_zy
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag from data_hrp.HIS_CHARGE_ACC_ZY')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

begin tran

    delete hisdb2.dbo.his_charge_pre_i where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_pre_i
          (charge_date, pre_record, oper_code, oper_name, deposit_pre, pay_type_code, patient_type_code, patient_area, patient_code, charge_money, charge_status, old_recipeno)
    select charge_date, pre_record, oper_code, oper_name, deposit_pre, pay_type_code, patient_type_code, patient_area, patient_code, charge_money, charge_status, old_recipeno
    from openquery(HIS,'select charge_date, pre_record, oper_code, oper_name, deposit_pre, pay_type_code, patient_type_code, patient_area, patient_code, charge_money, charge_status, old_recipeno from data_hrp.HIS_CHARGE_PRE_I')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

begin tran

    delete hisdb2.dbo.his_charge_pre_o where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_pre_o
          (charge_date, pre_record, oper_code, oper_name, deposit_pre, pay_type_code, patient_type_code, patient_area, patient_code, charge_money, charge_status)
    select charge_date, pre_record, oper_code, oper_name, deposit_pre, pay_type_code, patient_type_code, patient_area, patient_code, charge_money, charge_status
    from openquery(HIS,'select charge_date, pre_record, oper_code, oper_name, deposit_pre, pay_type_code, patient_type_code, patient_area, patient_code, charge_money, charge_status from data_hrp.HIS_CHARGE_PRE_O')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

begin tran

    delete hisdb2.dbo.his_outp_clinic where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_outp_clinic
          (charge_date, dept_code, patient_type_code, outp_num)
    select charge_date, dept_code, patient_type_code, outp_num
    from openquery(HIS,'select charge_date, dept_code, patient_type_code, outp_num from data_hrp.HIS_OUTP_CLINIC')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

commit tran

BEGIN TRAN

     delete hisdb2.dbo.his_charge_acc_o where charge_date>=@ST_DATE and charge_date<@ED_DATE

    insert into hisdb2.dbo.his_charge_acc_o
          (charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type)
    select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type
    from openquery(HIS,'select charge_date, deposit_pre, pay_type_code, patient_type_code, his_irecord, charge_money, patient_code, invoice_no, operator, acc_flag, balance_flag, invoice_type from data_hrp.HIS_CHARGE_ACC_O')
    where charge_date>=@ST_DATE and charge_date<@ED_DATE

COMMIT TRAN
胡明hoo的主页 胡明hoo | 初学一级 | 园豆:84
提问于:2013-12-24 19:59
< >
分享
所有回答(1)
0

一个事务会需要持有2个锁,就会造成死锁.

吧删除的从事务里去掉,或者nolock应该就行.还有批量插入里的子查询也是,

吴瑞祥 | 园豆:28736 (高人七级) | 2013-12-26 11:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册