其实所有的死锁最深层的原因就是一个:资源竞争 表现一:
一个用户A 访问表A(锁住了表A),然后又访问表B
另一个用户B 访问表B(锁住了表B),然后企图访问表A
这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B,才能继续，好了他老人家就只好老老实实在这等了
同样用户B要等用户A释放表A才能继续这就死锁了
解决方法：
这种死锁是由于你的程序的BUG产生的，除了调整你的程序的逻辑别无他法
仔细分析你程序的逻辑，
1：尽量避免同时锁定两个资源
2: 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源.
表现二:
用户A读一条纪录，然后修改该条纪录
这是用户B修改该条纪录
这里用户A的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B里的独占锁由于A有共享锁存在所以必须等A释
放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。
这种死锁比较隐蔽，但其实在稍大点的项目中经常发生。
解决方法:
让用户A的事务（即先读后写类型的操作),在select 时就是用Update lock
语法如下：
select * from table1 with(updlock) where ....
==========================
在联机事务处理(OLTP)的数据库应用系统中，多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性，目前大部分RDBMS都采用加锁技术。然而由于现实环境的复杂性，使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术，最小化死锁是开发联机事务处理系统的关键。
死锁产生的原因
在联机事务处理系统中，造成死机主要有两方面原因。一方面，由于多用户、多任务的并发性和事务的完整性要求，当多个事务处理对多个资源同时访问时，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁。
另一方面，数据库本身加锁机制的实现方法不同，各数据库系统也会产生其特殊的死锁情况。如在Sybase SQL Server 11中，最小锁为2K一页的加锁方法，而非行级锁。如果某张表的记录数少且记录的长度较短(即记录密度高，如应用系统中的系统配置表或系统参数表就属于此类表)，被访问的频率高，就容易在该页上产生死锁。
几种死锁情况及解决方法
清算应用系统中，容易发生死锁的几种情况如下:
● 不同的存储过程、触发器、动态SQL语句段按照不同的顺序同时访问多张表;
● 在交换期间添加记录频繁的表，但在该表上使用了非群集索引(non-clustered);
● 表中的记录少，且单条记录较短，被访问的频率较高；
● 整张表被访问的频率高(如代码对照表的查询等)。
以上死锁情况的对应处理方法如下：
● 在系统实现时应规定所有存储过程、触发器、动态SQL语句段中，对多张表的操作总是使用同一顺序。如：有两个存储过程proc1、proc2，都需要访问三张表zltab、z2tab和z3tab，如果proc1按照zltab、z2tab和z3tab的顺序进行访问，那么，proc2也应该按照以上顺序访问这三张表。
● 对在交换期间添加记录频繁的表，使用群集索引(clustered)，以减少多个用户添加记录到该表的最后一页上，在表尾产生热点，造成死锁。这类表多为往来账的流水表，其特点是在交换期间需要在表尾追加大量的记录，并且对已添加的记录不做或较少做删除操作。
● 对单张表中记录数不太多，且在交换期间select或updata较频繁的表可使用设置每页最大行的办法，减少数据在表中存放的密度，模拟行级锁，减少在该表上死锁情况的发生。这类表多为信息繁杂且记录条数少的表。
如：系统配置表或系统参数表。在定义该表时添加如下语句：
with max_rows_per_page=1
● 在存储过程、触发器、动态SQL语句段中，若对某些整张表select操作较频繁，则可能在该表上与其他访问该表的用户产生死锁。对于检查账号是否存在，但被检查的字段在检查期间不会被更新等非关键语句，可以采用在select命令中使用at isolation read uncommitted子句的方法解决。该方法实际上降低了select语句对整张表的锁级别，提高了其他用户对该表操作的并发性。在系统高负荷运行时，该方法的效果尤为显著。
例如：
select*from titles at isolation read uncommitted
● 对流水号一类的顺序数生成器字段，可以先执行updata流水号字段+1，然后再执行select获取流水号的方法进行操作。
小结
笔者对同城清算系统进行压力测试时，分别对采用上述优化方法和不采用优化方法的两套系统进行测试。在其他条件相同的情况下，相同业务笔数、相同时间内，死锁发生的情况如下：
采用优化方法的系统: 0次/万笔业务;
不采用优化方法的系统：50～200次/万笔业务。
所以，使用上述优化方法后，特别是在系统高负荷运行时效果尤为显著。总之，在设计、开发数据库应用系统，尤其是OLTP系统时，应该根据应用系统的具体情况，依据上述原则对系统分别优化，为开发一套高效、可靠的应用系统打下良好的基础。
============
--转
/********************************************************
// 创建 :
// 日期 :
// 修改 :
//
// 说明 : 查看数据库里阻塞和死锁情况
********************************************************/
use master
go
CREATE procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
GO
==========================
呵呵，解决死锁，光查出来没有多大用处，我原来也是用这个存储过程来清理死锁的
我解决死锁的方式主要用了：
1 优化索引
2 对所有的报表，非事务性的select 语句 在from 后都加了 with (nolock) 语句
3 对所有的事务性更新尽量使用相同的更新顺序来执行
现在已解决了死锁的问题，希望能对你有帮助
with (nolock)的用法很灵活 可以说只要有 from的地方都可以加 with (nolock) 标记来取消产生意象锁，这里 可以用在 delete update,select 以及 inner join 后面的from里，对整个系统的性能提高都很有帮助
==========================
use master --必须在master数据库中创建
go
if exists (select * from dbo.sysobjects where id = object_id(N [dbo].[p_lockinfo] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
drop procedure [dbo].[p_lockinfo]
GO
/*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
--邹建 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
其实所有的死锁最深层的原因就是一个:资源竞争 表现一:
一个用户A 访问表A(锁住了表A),然后又访问表B
另一个用户B 访问表B(锁住了表B),然后企图访问表A
这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B,才能继续，好了他老人家就只好老老实实在这等了
同样用户B要等用户A释放表A才能继续这就死锁了
解决方法：
这种死锁是由于你的程序的BUG产生的，除了调整你的程序的逻辑别无他法
仔细分析你程序的逻辑，
1：尽量避免同时锁定两个资源
2: 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源.
表现二:
用户A读一条纪录，然后修改该条纪录
这是用户B修改该条纪录
这里用户A的事务里锁的性质由共享锁企图上升到独占锁(
for
update
),而用户B里的独占锁由于A有共享锁存在所以必须等A释
放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。
这种死锁比较隐蔽，但其实在稍大点的项目中经常发生。
解决方法:
让用户A的事务（即先读后写类型的操作),在
select
时就是用
Update
lock
语法如下：
select
*
from
table1
with
(updlock)
where
....
==========================
在联机事务处理(OLTP)的数据库应用系统中，多用户、多任务的并发性是系统最重要的技术指标之一。为了提高并发性，目前大部分RDBMS都采用加锁技术。然而由于现实环境的复杂性，使用加锁技术又不可避免地产生了死锁问题。因此如何合理有效地使用加锁技术，最小化死锁是开发联机事务处理系统的关键。
死锁产生的原因
在联机事务处理系统中，造成死机主要有两方面原因。一方面，由于多用户、多任务的并发性和事务的完整性要求，当多个事务处理对多个资源同时访问时，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁。
另一方面，数据库本身加锁机制的实现方法不同，各数据库系统也会产生其特殊的死锁情况。如在Sybase SQL Server 11中，最小锁为2K一页的加锁方法，而非行级锁。如果某张表的记录数少且记录的长度较短(即记录密度高，如应用系统中的系统配置表或系统参数表就属于此类表)，被访问的频率高，就容易在该页上产生死锁。
几种死锁情况及解决方法
清算应用系统中，容易发生死锁的几种情况如下:
● 不同的存储过程、触发器、动态SQL语句段按照不同的顺序同时访问多张表;
● 在交换期间添加记录频繁的表，但在该表上使用了非群集索引(non-clustered);
● 表中的记录少，且单条记录较短，被访问的频率较高；
● 整张表被访问的频率高(如代码对照表的查询等)。
以上死锁情况的对应处理方法如下：
● 在系统实现时应规定所有存储过程、触发器、动态SQL语句段中，对多张表的操作总是使用同一顺序。如：有两个存储过程proc1、proc2，都需要访问三张表zltab、z2tab和z3tab，如果proc1按照zltab、z2tab和z3tab的顺序进行访问，那么，proc2也应该按照以上顺序访问这三张表。
● 对在交换期间添加记录频繁的表，使用群集索引(clustered)，以减少多个用户添加记录到该表的最后一页上，在表尾产生热点，造成死锁。这类表多为往来账的流水表，其特点是在交换期间需要在表尾追加大量的记录，并且对已添加的记录不做或较少做删除操作。
● 对单张表中记录数不太多，且在交换期间
select
或updata较频繁的表可使用设置每页最大行的办法，减少数据在表中存放的密度，模拟行级锁，减少在该表上死锁情况的发生。这类表多为信息繁杂且记录条数少的表。
如：系统配置表或系统参数表。在定义该表时添加如下语句：
with
max_rows_per_page=1
● 在存储过程、触发器、动态SQL语句段中，若对某些整张表
select
操作较频繁，则可能在该表上与其他访问该表的用户产生死锁。对于检查账号是否存在，但被检查的字段在检查期间不会被更新等非关键语句，可以采用在
select
命令中使用
at
isolation
read
uncommitted
子句的方法解决。该方法实际上降低了
select
语句对整张表的锁级别，提高了其他用户对该表操作的并发性。在系统高负荷运行时，该方法的效果尤为显著。
例如：
select
*
from
titles
at
isolation
read
uncommitted
● 对流水号一类的顺序数生成器字段，可以先执行updata流水号字段+1，然后再执行
select
获取流水号的方法进行操作。
小结
笔者对同城清算系统进行压力测试时，分别对采用上述优化方法和不采用优化方法的两套系统进行测试。在其他条件相同的情况下，相同业务笔数、相同时间内，死锁发生的情况如下：
采用优化方法的系统: 0次/万笔业务;
不采用优化方法的系统：50～200次/万笔业务。
所以，使用上述优化方法后，特别是在系统高负荷运行时效果尤为显著。总之，在设计、开发数据库应用系统，尤其是OLTP系统时，应该根据应用系统的具体情况，依据上述原则对系统分别优化，为开发一套高效、可靠的应用系统打下良好的基础。
============
--转
/********************************************************
// 创建 :
// 日期 :
// 修改 :
//
// 说明 : 查看数据库里阻塞和死锁情况
********************************************************/
use master
go
CREATE
procedure
sp_who_lock
as
begin
declare
@spid
int
,@bl
int
,
@intTransactionCountOnEntry
int
,
@intRowcount
int
,
@intCountProperties
int
,
@intCounter
int
create
table
#tmp_lock_who (
id
int
identity(1,1),
spid
smallint
,
bl
smallint
)
IF @@ERROR<>0
RETURN
@@ERROR
insert
into
#tmp_lock_who(spid,bl)
select
0 ,blocked
from
(
select
*
from
sysprocesses
where
blocked>0 ) a
where
not
exists(
select
*
from
(
select
*
from
sysprocesses
where
blocked>0 ) b
where
a.blocked=spid)
union
select
spid,blocked
from
sysprocesses
where
blocked>0
IF @@ERROR<>0
RETURN
@@ERROR
-- 找到临时表的记录数
select
@intCountProperties =
Count
(*),@intCounter = 1
from
#tmp_lock_who
IF @@ERROR<>0
RETURN
@@ERROR
if @intCountProperties=0
select
'现在没有阻塞和死锁信息'
as
message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select
@spid = spid,@bl = bl
from
#tmp_lock_who
where
Id = @intCounter
begin
if @spid =0
select
'引起数据库死锁的是: '
+
CAST
(@bl
AS
VARCHAR
(10))
+
'进程号,其执行的SQL语法如下'
else
select
'进程号SPID：'
+
CAST
(@spid
AS
VARCHAR
(10))+
'被'
+
'进程号SPID：'
+
CAST
(@bl
AS
VARCHAR
(10)) +
'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set
@intCounter = @intCounter + 1
end
drop
table
#tmp_lock_who
return
0
end
GO
==========================
呵呵，解决死锁，光查出来没有多大用处，我原来也是用这个存储过程来清理死锁的
我解决死锁的方式主要用了：
1 优化索引
2 对所有的报表，非事务性的
select
语句 在
from
后都加了
with
(nolock) 语句
3 对所有的事务性更新尽量使用相同的更新顺序来执行
现在已解决了死锁的问题，希望能对你有帮助
with
(nolock)的用法很灵活 可以说只要有
from
的地方都可以加
with
(nolock) 标记来取消产生意象锁，这里 可以用在
delete
update
,
select
以及
inner
join
后面的
from
里，对整个系统的性能提高都很有帮助
==========================
use master
--必须在master数据库中创建
go
if exists (
select
*
from
dbo.sysobjects
where
id = object_id(N [dbo].[p_lockinfo] )
and
OBJECTPROPERTY(id, N IsProcedure ) = 1)
drop
procedure
[dbo].[p_lockinfo]
GO
/*
--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
--邹建 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
扫一扫
6979
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
