sql死锁问题

wenhaocs 2010-10-12 07:14:52

最的SQL2000 SP4 数据库不知道为什么老会出现死锁问题,以及活动进程错误提示 1222.

在网上用了 p-lockinfo 查询的结果如下;
1 |_牺牲品_> 51 sleeping DECLARE @P022 varchar(16) SELECT @P022 = '' execute PROC_EXEC_curorder_ADDDETAIL @CURORDER_ID = 'D201010120198', @tablename = '1631', @waiter = '姜霞', @dish_id = '01001', @ps_flag = 'N', @quantity = 1, @discount_rate = 1.0, @price = 98, @kill_id = '', @dis
2 |_牺牲品_> 57 DECLARE @P022 varchar(16) SELECT @P022 = '' execute PROC_EXEC_curorder_ADDDETAIL @CURORDER_ID = 'D201010120185', @tablename = '1606', @waiter = '高剑', @dish_id = '08139', @ps_flag = 'N', @quantity = 1, @discount_rate = 1, @price = 10, @kill_id = '', @dish_
3 |_牺牲品_> 60 DECLARE @P022 varchar(16) SELECT @P022 = '' execute PROC_EXEC_curorder_ADDDETAIL @CURORDER_ID = 'D201010120199', @tablename = '1103', @waiter = '姜霞', @dish_id = '01001', @ps_flag = 'N', @quantity = 1, @discount_rate = 1.0, @price = 98, @kill_id = '', @dis
4 死锁的进程 55 DECLARE @P001 int, @P002 int, @P003 int, @P004 int, @P005 int, @P006 int SELECT @P001 = 0, @P002 = 0, @P003 = 0, @P004 = 0, @P005 = 0, @P006 = 0 execute PROC_EXEC_kftj @zfjs = @P001 output , @usefjs = @P002 output , @freefjs = @P003 output , @zffjs = @P00
5 |_牺牲品_> 54 update tables set room_id ='' , bed_no ='' , room_flag ='' , begin_time =null , time_long =0 , warn_flag =0 , end_time =null , if_adv ='n' where tablename ='1863'
6 |_牺牲品_> 61 DECLARE @P002 int SELECT @P002 = 0 execute PROC_sauna_zzcdj @sp_id = '89-5000078306ac-16', @my_xh = @P002 output SELECT @P002
7 |_牺牲品_> 53 DECLARE @P022 varchar(16) SELECT @P022 = '' execute PROC_EXEC_curorder_ADDDETAIL @CURORDER_ID = 'D201010120132', @tablename = '2220', @waiter = '蔡婷', @dish_id = '02001', @ps_flag = 'N', @quantity = 1, @discount_rate = 1, @price = 98, @kill_id = '', @dish_

更奇怪的问题,我已经录入到计算机的数据信息,当我重启动slq数据库时会丢失.

希望朋友们能支持.分析分析在下感谢了.

...全文

55 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

wenhaocs 2010-10-21

1 死锁的进程 53 0 0 8 Sn20106171437 1 sa 4454 2010-10-21 01:21:59.250 2 sleeping 3004 406186256F1D begin tran
2 |_牺牲品_> 56 2640 53 8 Sn20106171437 0 sa 15421 2010-10-20 22:27:42.937 1 sleeping 116 40618625704D SELECT a.posname, (select count(*) from tables c where c.posname=a.posname ) as sp_sl, (select count(*) from tables c where c.posname=a.posname and statusname='空闲') as sp_freesl, (select count(*) from tables c whe
3 |_牺牲品_> 57 2372 56 8 Sn20106171437 0 sa 0 2010-10-21 03:17:20.140 1 sleeping 2904 40618625A520 SELECT a.posname, (select count(*) from tables c where c.posname=a.posname ) as sp_sl, (select count(*) from tables c where c.posname=a.posname and statusname='空闲') as sp_freesl, (select count(*) from tables c whe
4 |_牺牲品_> 58 3764 56 8 Sn20106171437 0 sa 0 2010-10-21 03:21:24.263 1 sleeping 1940 406186256D6B SELECT a.posname, (select count(*) from tables c where c.posname=a.posname ) as sp_sl, (select count(*) from tables c where c.posname=a.posname and statusname='空闲') as sp_freesl, (select count(*) from tables c whe
5 |_牺牲品_> 59 3408 56 8 Sn20106171437 0 sa 0 2010-10-21 03:20:58.123 1 sleeping 3192 406186257041 SELECT a.posname, (select count(*) from tables c where c.posname=a.posname ) as sp_sl, (select count(*) from tables c where c.posname=a.posname and statusname='空闲') as sp_freesl, (select count(*) from tables c whe
6 |_牺牲品_> 54 640 56 8 Sn20106171437 0 sa 19284 2010-10-20 22:34:25.437 1 sleeping 3608 40618625A519 SELECT a.posname, (select count(*) from tables c where c.posname=a.posname ) as sp_sl, (select count(*) from tables c where c.posname=a.posname and statusname='空闲') as sp_freesl, (select count(*) from tables c whe
7 |_牺牲品_> 55 3636 56 8 Sn20106171437 0 sa 16458 2010-10-20 22:23:23.607 1 sleeping 3192 40618625A51D SELECT a.posname, (select count(*) from tables c where c.posname=a.posname ) as sp_sl, (select count(*) from tables c where c.posname=a.posname and statusname='空闲') as sp_freesl, (select count(*) from tables c whe

回复赞

yuhuiwqvb 2010-10-12

不懂。。。。

回复赞

billpu 2010-10-12

重启是因为被回滚了,慢慢用事件探查器分析一下吧自己能解决可以学到很多东西

回复赞

hao1hao2hao3 2010-10-12

检查PROC_EXEC_curorder_ADDDETAIL ，PROC_sauna_zzcdj ，PROC_EXEC_kftj 等存储过程看看是不是有什么逻辑上的问题，或者其他可能导致死锁的情况，优化一下代码。

回复赞

SQLCenter 2010-10-12

检查这两个存储过程：

PROC_EXEC_kftj
PROC_EXEC_curorder_ADDDETAIL

回复赞

dawugui 2010-10-12

/*--处理死锁

查看当前进程,或死锁进程,并能自动杀掉死进程

因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

--邹建 2004.4--*/

/*--调用示例

exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into #t from(
select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end

if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go

回复赞