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

hm1989wz 2013-12-24 07:48:18

各位大神们，最近遇到一挺棘手的问题，服务器上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

...全文

194 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

Leon_He2014 2013-12-31

打赏
举报

你的HIS用的什么驱动？可以换个其他驱动试试

铁歌 2013-12-31

打赏
举报

引用 11 楼 hm1989wz 的回复:

[quote=引用 10 楼 ejason 的回复:] 1，先確定是死鎖還是阻賽，sp_lock或sp_who_locK查看下 2，排除法先把事務語句去掉，看運行情況，因sQL和oracle事務處理有差異，orac|e每句查詢語句都可以有顯式事務提交；把查詢語句在orale上執行一次看返回時閽，遠程調用返回慢大多數情形是原服務查絢慢需優化

通过这几天的观察，有时候直接在sql2005里面用OPENQUERY连接Oracle也会很慢，这个应该怎么解决？[/quote] OPENQUERY的SQL语句直接在oracle上面运行看性能怎么样？

唐诗三百首 2013-12-30

打赏
举报

从LZ对问题的描述看,应该不是死锁,而是进程等待,建议从系统DMV查看一下进程的等待类型(waittype)是什么,以利于问题分析. 真正的死锁是2个进程因资源互被对方锁定而进入无限期的互相等待状态,MSSQL对死锁会自动回滚其中一个进程的.

山寨DBA 2013-12-30

打赏
举报

你可以根据return的值做相应的判断和修改，反正我是觉得你这个事务包含的操作有些多，而且你那里也没有执行失败或成功的提示信息，我认为分开应该会好些。

山寨DBA 2013-12-30

打赏
举报

个人感觉你的一个事务包括的东西还是有点多，干的事情有点多。我做如下修改，从事务的机制来说，下面的修改应该可以起到一些作用，但是作用大小就难说了，还得看数据量。你先试试吧（我只修改了其中一段）：


begin tran
    delete hisdb2.dbo.his_charge_acc_i where charge_date>=@ST_DATE and charge_date<@ED_DATE
if @@ERROR<>0 
begin
	rollback tran
	return 0
end	
else
begin	
	commit tran
end

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

cyuyanzhiwen 2013-12-30

打赏
举报

你的这段代码，能否写在一个BEGIN……end之间？这样看会不会死锁?

LongRui888 2013-12-30

打赏
举报

引用 15 楼 hm1989wz 的回复:

[quote=引用 14 楼 yupeigu 的回复:] [quote=引用 13 楼 hm1989wz 的回复:] [quote=引用 5 楼 yupeigu 的回复:] [quote=引用 2 楼 hm1989wz 的回复:] [quote=引用 1 楼 yupeigu 的回复:] 这个问题估计是oracle上的问题，可能需要更新一下oracle中，表的统计信息。

具体要在Oracle上怎么做呢？[/quote] 你这样，监控一下，因为不清楚，到底是一段代码有问题，所以最好是监控一下死锁的情况，看到底是从oracle取数的问题，还是delete那个语句导致死锁的。具体如何监控死锁，这个里面开始的1.里又讲到，通过sql profiler来监控死锁：通过DBCC Page查看在SQL Server中哪行数据被锁住了 http://blog.csdn.net/sqlserverdiscovery/article/details/13291629[/quote] 使用sql2005里面用OPENQUERY连接Oracle也会很慢，300条数据大概需要7秒的样子，怀疑根本问题在这里，请问这个需要如何优化？[/quote] 哦，具体是delete那个慢，还是 insert into xxx select 。。。慢呢[/quote] 就是insert into 后面的 select ... from openquery() 这段，这段是从oracle查询数据[/quote] 你就有可能是oracle的问题，对了你试过直接在oracle上运行openquery中的 select 语句吗，快吗

hm1989wz 2013-12-30

打赏
举报

引用 14 楼 yupeigu 的回复:

[quote=引用 13 楼 hm1989wz 的回复:] [quote=引用 5 楼 yupeigu 的回复:] [quote=引用 2 楼 hm1989wz 的回复:] [quote=引用 1 楼 yupeigu 的回复:] 这个问题估计是oracle上的问题，可能需要更新一下oracle中，表的统计信息。

LongRui888 2013-12-30

打赏
举报

引用 13 楼 hm1989wz 的回复:

[quote=引用 5 楼 yupeigu 的回复:] [quote=引用 2 楼 hm1989wz 的回复:] [quote=引用 1 楼 yupeigu 的回复:] 这个问题估计是oracle上的问题，可能需要更新一下oracle中，表的统计信息。

hm1989wz 2013-12-30

打赏
举报

引用 5 楼 yupeigu 的回复:

[quote=引用 2 楼 hm1989wz 的回复:] [quote=引用 1 楼 yupeigu 的回复:] 这个问题估计是oracle上的问题，可能需要更新一下oracle中，表的统计信息。

hm1989wz 2013-12-30

打赏
举报

各位大神们，单独使用sql2005里面用OPENQUERY连接Oracle也会很慢，300条数据大概需要7秒的样子，这个需要如何优化？

hm1989wz 2013-12-30

打赏
举报

引用 10 楼 ejason 的回复:

1，先確定是死鎖還是阻賽，sp_lock或sp_who_locK查看下 2，排除法先把事務語句去掉，看運行情況，因sQL和oracle事務處理有差異，orac|e每句查詢語句都可以有顯式事務提交；把查詢語句在orale上執行一次看返回時閽，遠程調用返回慢大多數情形是原服務查絢慢需優化

通过这几天的观察，有时候直接在sql2005里面用OPENQUERY连接Oracle也会很慢，这个应该怎么解决？

铁歌 2013-12-25

打赏
举报

1，先確定是死鎖還是阻賽，sp_lock或sp_who_locK查看下 2，排除法先把事務語句去掉，看運行情況，因sQL和oracle事務處理有差異，orac|e每句查詢語句都可以有顯式事務提交；把查詢語句在orale上執行一次看返回時閽，遠程調用返回慢大多數情形是原服務查絢慢需優化

铁歌 2013-12-25

打赏
举报

KevinLiu 2013-12-24

打赏
举报

引用楼主 hm1989wz 的回复:


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

你怎么看到的死锁啊？

cxmcxm 2013-12-24

打赏
举报

就你的语句，就简单的insert select语句，有死锁的可能性较少。是否记录太多，查询时间太长查询出来的数据是否存在主键重复，自己调试，查看数据，查找原因。

cxmcxm 2013-12-24

打赏
举报

这样的问题，可通过逐步排错的方法。 1.先在sql server的服务器上安装oracle的服务端，直接运行几个oracle中的select语句，看看执行的时间，能否正常查询。如无问题，可排除是oracle的问题 2.在sql server查询管理器中执行上面insert into后面的select .. from openquery(...)语句，看看有无问题，要多长时间。 3。。。如此一步步排查，找出问题所在，再寻找解决方案。

LongRui888 2013-12-24