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
所有回答(1)
0
一个事务会需要持有2个锁,就会造成死锁.
吧删除的从事务里去掉,或者nolock应该就行.还有批量插入里的子查询也是,
