求教数据库并发控制的解决方案，欢迎讨论,再开１００分
原问题见
http://community.csdn.net/Expert/topic/3224/3224926.xml?temp=.8728449
现在在执行２时
用３个查询分析器，调用一个ＳＰ
会出现３个都长时间不返回
最终只能终止
有企业管理器打开锁管理时
提示超过锁超时
请问怎么回事？
下午要去客户调试，很急，谢谢
解决以上结贴
问题点数：100、回复次数：31Top
1 楼sunshareforever（阳光）回复于 2004-08-01 12:18:21 得分 10
检查一下你的事务，是不是事务用得太多，
应急办法：去掉一定的事务
回头再检查你的spTop
2 楼guxing（孤星）回复于 2004-08-01 12:24:07 得分 0
我现在是在查询分析器中调用ＳＰ的
是调用同一个ＳＰ
不管是用updlock
还是系统默认的都会长时间不返回结果
但昨天还是
２个正确返回，１个出１２０５错误
真是不解了Top
3 楼guxing（孤星）回复于 2004-08-01 13:41:59 得分 0
上面说的错误，删除表重新建就好了，怪
谢谢关注和帮助 过的朋友
经过调试已经基本搞定,下午再去客户处调试就知道了
解决方法如下：
１，对所有Select 允许脏读的强制加 with(nolock)
２，对所有
insert update,delete 等强制加with(updlock)
这样锁表的时间缩短了
3，主键用自增长型ＩＤ
我用6个查询分析器同时执行，也没出错了
在ＳＰ中延时5S
希望能对大家有所启发
进来有分Top
4 楼guxing（孤星）回复于 2004-08-01 14:50:02 得分 0
现在还有个问题：
第二个ＳP中
有一个地方用
select ... 读取一个要更新的表的数据,不能脏读的
如果用 with(updlock) 时会出现同时调用时,只有2个成功
其它几个调用返回1205错误
说是在
select * from 时死锁
请问怎样才能让SQL等待,而不是死锁牺牲Top
5 楼zjcxc（邹建）回复于 2004-08-01 15:42:53 得分 30
--试试将锁超时时间设置长一些.
SET LOCK_TIMEOUTTop
6 楼guxing（孤星）回复于 2004-08-01 16:04:23 得分 0
可能不是这个吧
初始是 -1 也就是不会超时返回
我设置了
SET LOCK_TIMEOUT 218000 还是几秒就返回出错了
请再指教Top
7 楼zjcxc（邹建）回复于 2004-08-01 16:07:25 得分 0
去看了你原来帖子中帖出的存储过程,提出2点.
1.既然你可以把 SYS_INF_FQ、SYS_INF_COMBINE这几个存储过程合并在第一个存储过程中
那么,你把你的处理方法改一下,你原来的插入处理中,涉及到循环插入的,这些都是放在
事务中的,那必须增加数据处理的时间,而事务会锁表,也就增加了发生死锁的可能性
所以,建议你先把这些在事务外处理好,并将处理结果放入临时表中,然后开启事务,一次
性的从临时表中插入到正式表中,这样就减小了事务处理的开销(也提高了处理效率)
2.尽量节省语句,将 set 这类的赋值,有可能的,都合并到一个 select 中赋值Top
8 楼zjcxc（邹建）回复于 2004-08-01 16:08:50 得分 0
原则上,尽量做到,事务处理中的语句最小,这样可以大大减小死锁的机率.Top
9 楼guxing（孤星）回复于 2004-08-01 16:10:43 得分 0
发现
每超过2个进程同时调用 SP时
会在SP中调用的另一个SP的
如下语句时死锁,并只会有2个进程成功,其它返回1205错误
INSERT INTO #DT_FF ( AUTH , Custid , Margin )
select '3' , code , Margin = ((case when @Qbz > '1' then limit else wlimit end) - ISNULL(
(Select Sum(Isnull(amt , 0)) From DT_FDET_OUT With(updlock) where stanzaid = @stanzaid AND Custid = code And (Fdet = @Fdet OR Fdet = @RevFdet)
Group by stanzaid, Custid
) , 0 )
) from bm_customer with(nolock) where cate = 1 order by limit
请帮我分析怎么不让它返回错误,而是继续等待锁释放Top
10 楼guxing（孤星）回复于 2004-08-01 16:19:37 得分 0
因为这个SP要循环调用,没法放到一个SP中的
谢谢
zjcxc(邹建) 老大
我会按你说的改进一下看看Top
11 楼zonelive（peter）回复于 2004-08-01 19:26:08 得分 10
同意zjcxc的方案
我只想到了这点Top
12 楼guxing（孤星）回复于 2004-08-01 20:13:38 得分 0
完了
今天上客户处测试和我的本机测试差别很大,问题还是存在.
用updlock
会导致死锁(并长时间无响应,死机)
用系统默认的锁
会出错,也就是死锁牺牲
zjcxc的方案
第一点,不好改
在\因为
这个SP计算很复杂,必须在计算时锁住
否则另一个进程调用会有脏读
我现在要的是,能锁住,且不会出错返回
如果用
SET LOCK_TIMEOUT 6000
会在1-2秒时出错,提示
锁超时,不解Top
13 楼zjcxc（邹建）回复于 2004-08-01 20:37:15 得分 0
用事件探察器了吗? 查出来是那句SQL产生锁,那句SQL是锁的牺牲品了吗?Top
14 楼guxing（孤星）回复于 2004-08-01 22:14:45 得分 0
是这句
INSERT INTO #DT_FF ( AUTH , Custid , Margin )
select '3' , code , Margin = ((case when @Qbz > '1' then limit else wlimit end) - ISNULL(
(Select Sum(Isnull(amt , 0)) From DT_FDET_OUT where stanzaid = @stanzaid AND Custid = code And (Fdet = @Fdet OR Fdet = @RevFdet)
Group by stanzaid, Custid
) , 0 )
) from bm_customer with(nolock) where cate = 1 order by limit
是这个表
DT_FDET_OUT
这里是读取当前已经分配的数量,再进行分配
所以不能脏读
另外我设置
锁超时根本没用
同时执行3个SP,第二个会牺牲
严重时出现死锁进程
Top
15 楼guxing（孤星）回复于 2004-08-01 22:17:18 得分 0
按你说的第二点改了一下,好象速度有所改观
另外,正在升级服务器,加个CPU
好象看到将tempdb的磁盘文件放到RAID的另一个区会
提高性能是否?
因为用了大量SPTop
16 楼zjcxc（邹建）回复于 2004-08-02 08:23:47 得分 0
tempdb文件放到高性能的磁盘分区,并且与正常数据库的文件分属不同的分区,对提高性能有改善Top
17 楼tx1icenhe（冒牌马可 V0.4）回复于 2004-08-02 08:28:41 得分 0
改过隔离级别没有？
Top
18 楼tx1icenhe（冒牌马可 V0.4）回复于 2004-08-02 08:30:46 得分 30
索引建了没有
Top
19 楼tx1icenhe（冒牌马可 V0.4）回复于 2004-08-02 08:49:37 得分 0
建议：
画一个处理逻辑图，按步骤标明涉及的表及对表的操作类型。
如果一个事务先对一个表做修改，如果记录不多，形成的是行锁，可能就没问题，如果记录较多，升级成页锁或者表锁，在这个事务里的以后语句，哪怕只是对这个表查询，都可能形成死锁。
还有，你的事务太大，涉及的表很多，如果一个地方先操作a表后操作b表，另一个地方先操作b表后操作a表，并发情况就可能形成死锁。
画处理逻辑图，就是为了找到死锁的原因，可能画起来比较困难，但是做还是值得的。
Top
20 楼guxing（孤星）回复于 2004-08-02 11:42:55 得分 0
TO tx1icenhe(冒牌马可 V0.1) (
索引建了--有了
改过隔离级别没有---没有?
能否详细说一下作用.
可能 会有锁升级,怎么处理?
还有，你的事务太大，涉及的表很多，如果一个地方先操作a表后操作b表，另一个地方先操作b表后操作a表，并发情况就可能形成死锁。
这个已经检查过了.
画处理逻辑图,真不会:_
Top
21 楼guxing（孤星）回复于 2004-08-02 12:09:54 得分 0
另外,
我是打开3个查询分析器
在SP中延时5S
然后差不多同时执行3个对同一SP的调用
但每次只会有2个成功,其它的返回1205,牺牲
如果是3个以上也是这样
对同一SP事务是一致的
是不是数据库当死锁超过2个就自动将其它的牺牲了
如果是这样,我想让其它的继续等待怎么做?
设置锁超时好象没用
设置了6000 ,结果1-2秒就提示锁超时返回
谢谢Top
22 楼tx1icenhe（冒牌马可 V0.4）回复于 2004-08-02 13:30:25 得分 0
没延时没死锁的时候，执行一次要多少时间？
Top
23 楼tx1icenhe（冒牌马可 V0.4）回复于 2004-08-02 13:31:51 得分 0
其实不一定要画图，只要是理清楚一个事务涉及哪些表的那些操作，只有这才能查清楚死锁的原因
Top
24 楼guxing（孤星）回复于 2004-08-02 14:08:24 得分 0
100-400ms
吧Top
25 楼seth99（seth）回复于 2004-08-02 14:40:18 得分 20
试一下下面:
1.如果可以，用表变量代替临时表
=================================================================================
CREATE TABLE #DT_FDET ( pid int Not null , Qbz char(1) not null, Fdet varchar (20) NOT NULL , RFdet varchar (20) NULL , Amt INT NULL , PAmt INT NULL default 0)
=================================================================================
改为
=================================================================================
DECLARE @DT_FDET Table(
pid int Not null ,
Qbz char(1) not null,
Fdet varchar (20) NOT NULL ,
RFdet varchar (20) NULL ,
Amt INT NULL ,
PAmt INT NULL default 0)
=================================================================================
2.在每个存储过程的开头SET NOCOUNT ON ,在结尾SET NOCOUNT OFF
3.去掉所有的强制加的with(rowlock)，然后看看那个表被lock，然后将SQL贴上来
Top
26 楼seth99（seth）回复于 2004-08-02 14:41:25 得分 0
表结构和相关的index也贴上来看看Top
27 楼guxing（孤星）回复于 2004-08-02 14:54:05 得分 0
主要是这2个表
CREATE TABLE dbo.DT_FDET_OUT (
id int IDENTITY (1, 1) NOT NULL ,
Inid int Not null,
pid int null,
stanzaid char (2) NOT NULL ,
Auth char(1) not null,
Custid varchar (2) NOT NULL ,
Qbz char(1) NOT NULL ,/*0--W , 1 -- WP , 2 ,Q*/
Fdet varchar (20) NOT NULL ,
Amt int NOT NULL ,/*每F金额*/
PAmt int NULL default 0,/*每FWP的P金额*/
Accept char(1) NOT NULL ,/*类型，'0'－－－不要，'1'－－－要*/
DisFdet varchar (50) NOT NULL ,
PRIMARY Key (id)
)
CREATE INDEX IX1_DT_FDET_OUT ON dbo.DT_FDET_OUT(Inid )
CREATE INDEX IX_DT_FDET_OUT ON dbo.DT_FDET_OUT(pid ,Custid)
GO
CREATE TABLE dbo.DT_OUTF (
pid int IDENTITY (1, 1) NOT NULL ,
Inid int Not null,
stanzaid char (2) NOT NULL ,
Auth char(1) not null,
Custid char (2) NOT NULL ,
Qbz char (1) NOT NULL ,
Fdet varchar (20) NOT NULL ,
Amt int NOT NULL ,/*每F金额*/
PAmt int NULL default 0,/*每FWP的P金额*/
Accept char(1) NOT NULL ,/*类型，'0'－－－不要，'1'－－－要*/
DisFdet varchar (50) NOT NULL ,
PRIMARY Key (pid)
)
CREATE INDEX IX1_DT_OUTF ON dbo.DT_OUTF(Inid )
CREATE INDEX IX_DT_OUTF ON dbo.DT_OUTF(stanzaid,Auth)
GO
w锁表是这里
是这句
INSERT INTO #DT_FF ( AUTH , Custid , Margin )
select '3' , code , Margin = ((case when @Qbz > '1' then limit else wlimit end) - ISNULL(
(Select Sum(Isnull(amt , 0)) From DT_FDET_OUT where stanzaid = @stanzaid AND Custid = code And (Fdet = @Fdet OR Fdet = @RevFdet)
Group by stanzaid, Custid
) , 0 )
) from bm_customer with(nolock) where cate = 1 order by limit
是这个表
DT_FDET_OUT
Top
28 楼seth99（seth）回复于 2004-08-02 23:21:45 得分 0
有点问题，单从上面的来看不会有lock，看了一下你前面的帖子，将SYS_INF_FQ贴出来看看，如果你确定是DT_FDET_OUT被lock.Top
29 楼guxing（孤星）回复于 2004-08-02 23:27:55 得分 0
CREATE PROCEDURE DBO.SYS_OUTF_FQ
@stanzaid char (2) , /*场次ID */
@Custid char(2),
@Pid int , /*PID */
@Qbz char(1) , /*Q单标志 */
@Fdet varchar (20) , /*F单内容 */
@Amt int , /*F金额 */
@PAmt int , /*F单P金额 */
@DisFdet varchar (50) , /*显示F单内容 */
@Inid int /*Inid */
/*WITH ENCRYPTION*/
AS
DECLARE @iTotal decimal(12,0) , @iMargin int , @iAmt int , @iPAmt int , @iScale int , @iPScale int, @iExeceed int, @iPExeceed int , @iWexec int ;
DECLARE @PiWexec int, @iLexec int , @iPLexec int, @iId int , @iCount int;
DECLARE @iSlimit int /*最小限额*/ , @iDjamt int, @iPDjamt int /*对机金额*/
DECLARE @iMod int /*余数*/
DECLARE @iTotalAmt int , @iPTotalAmt int /*当前分配总数*/
DECLARE @rBs decimal(4,2);
DECLARE @RevFdet varchar(50) , @cCustid char(2);
DECLARE @InAmt int , @InPAmt int
BEGIN
SELECT @InAmt = @Amt , @InPAmt = @PAmt , @RevFdet = RIGHT(@Fdet , 1) + '-' + LEFT(@Fdet , 1)
/*Amt --- 实际分配金额 Margin---为差多少到限额 Scale----按比例分配的金额 Execeed----超出部份 */
CREATE TABLE #DT_FF (id int IDENTITY (1, 1) NOT NULL , AUTH CHAR(1) NULL ,
Custid CHAR(2) NULL , Amt INT NULL , Margin decimal(12,0) NULL , Scale INT NULL , Execeed INT NULL , PAmt INT NULL DEFAULT 0, PExeceed INT NULL )
INSERT INTO #DT_FF ( AUTH , Custid , Margin )
select '3' , code , Margin = ((case when @Qbz > '1' then limit else wlimit end) - ISNULL(
(Select Sum(Isnull(amt , 0)) From DT_FDET_OUT where stanzaid = @stanzaid AND Custid = code And (Fdet = @Fdet OR Fdet = @RevFdet)
Group by stanzaid, Custid
) , 0 )
) from bm_customer with(nolock) where cate = 1 and code<>@Custid order by limit
SELECT @iSlimit = Cast(mc as int) FROM BM_BM with(nolock) where lb='10' and bh = '1'/*得到最小限额*/
DELETE FROM #DT_FF WHERE Margin < @iSlimit
SELECT @iTotal = ISNULL(SUM(Margin) , 0 ) FROM #DT_FF WHERE auth = '3'
IF @iTotal > 0
BEGIN
IF @Qbz ='1'
SET @rBs =Cast( @PAmt as decimal(12,2)) / Cast( @Amt as decimal(12,2))
SELECT @iTotalAmt = 0 , @iPTotalAmt = 0 , @iLexec = 0 , @iPLexec = 0
UPDATE #DT_FF SET Scale = (Margin / @iTotal) * @Amt
DECLARE My_cursor CURSOR FOR SELECT Id , Margin , Scale , Isnull(Execeed , 0 ) , Isnull(PExeceed , 0 ) FROM #DT_FF WHERE auth = '3' ORDER BY Margin ASC
OPEN My_cursor
FETCH NEXT FROM My_cursor INTO @iId , @iMargin , @iScale , @iExeceed, @iPExeceed
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @iAmt = @iScale + ISNULL( @iLexec , 0)
IF @iAmt < @iSlimit /*不足最小限额算最小限额*/
SET @iAmt = @iSlimit
ELSE
BEGIN
SET @iMod = @iAmt % @iSlimit
IF @iMod > 0
BEGIN
SET @iAmt = @iAmt - @iMod
IF @iMod >= (@iSlimit / 2)
SET @iAmt = @iAmt + @iSlimit
END
END
IF @iAmt > @iMargin
SET @iAmt = @iMargin
SET @iTotalAmt = @iTotalAmt + @iAmt
IF @iTotalAmt >= @Amt
SET @iAmt = @iAmt - (@iTotalAmt - @Amt) /*@iSlimit */
ELSE IF @iTotalAmt+@iSlimit > @Amt
SELECT @iAmt = @iAmt + (@Amt - @iTotalAmt) , @iTotalAmt = @Amt
IF @Qbz = '1'
BEGIN
SELECT @iPScale = @rBs * @iAmt , @iPAmt = @iPScale + ISNULL( @iPLexec , 0)
IF @iPAmt < @iSlimit /*不足最小限额算最小限额*/
SET @iPAmt = @iSlimit
ELSE
BEGIN
SET @iMod = @iPAmt % @iSlimit
IF @iMod > 0
BEGIN
SET @iPAmt = @iPAmt - @iMod
IF @iMod >= (@iSlimit / 2)
SET @iPAmt = @iPAmt + @iSlimit
END
END
SET @iPTotalAmt = @iPTotalAmt + @iPAmt
IF @iPTotalAmt >= @PAmt
SET @iPAmt = @iPAmt - (@iPTotalAmt - @PAmt) /*@iSlimit */
ELSE IF @iPTotalAmt+@iSlimit > @PAmt
SELECT @iPAmt = @iPAmt + (@PAmt - @iPTotalAmt) , @iPTotalAmt = @PAmt
SELECT @PiWexec = @iPScale - @iPAmt , @iPLexec = @iPLexec + @PiWexec
UPDATE #DT_FF SET PExeceed = @PiWexec , PAmt = @iPAmt WHERE ID = @iId
END
SELECT @iWexec = @iScale - @iAmt , @iLexec = @iLexec + @iWexec /*@iExeceed*/
UPDATE #DT_FF SET Execeed = @iWexec , Amt = @iAmt WHERE ID = @iId
IF @iTotalAmt >= @Amt
BREAK
END
FETCH NEXT FROM My_cursor INTO @iId , @iMargin , @iScale , @iExeceed , @iPExeceed
END
CLOSE My_cursor
DEALLOCATE My_cursor
SELECT @iDjamt = @Amt - SUM(Amt), @iPDjamt =@PAmt - SUM(PAmt) FROM #DT_FF WHERE auth = '3'
DELETE FROM #DT_FF WHERE Amt IS NULL
END
ELSE
SELECT @iDjamt = @Amt , @iPDjamt = @PAmt
IF @Qbz ='1' AND exists(select 1 From #DT_FF where pamt <= 0 ) /*检查WP是否分给客户P0*/
BEGIN
SELECT @iTotalAmt = SUM(AMT) FROM #DT_FF where pamt <= 0
DECLARE My_cursor1 CURSOR FOR SELECT Id FROM #DT_FF
WHERE auth = '3' and pamt > 0 and (Margin - amt) > @iSlimit ORDER BY (Margin - amt) DESC
OPEN My_cursor1
FETCH NEXT FROM My_cursor1 INTO @iId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
IF @iTotalAmt < @iSlimit
BEGIN
IF @iTotalAmt < = 0
Goto label
SET @iAmt = @iTotalAmt
END
Else
SET @iAmt = @iSlimit
UPDATE #DT_FF SET AMT = AMT + @iAmt WHERE ID = @iId
SET @iTotalAmt = @iTotalAmt - @iAmt
END
FETCH NEXT FROM My_cursor1 INTO @iId
END
IF @iTotalAmt > 0
BEGIN --打对机
SELECT @iId =min( Id ) FROM #DT_FF WHERE auth = '3' and pamt = (select max(pamt) from #DT_FF WHERE auth = '3' )
SELECT @iPAmt = pamt FROM #DT_FF WHERE id = @iId
IF @iPAmt < = @iSlimit
UPDATE #DT_FF SET amt = amt + @iTotalAmt WHERE id = @iId
ELSE
BEGIN
SET @iDjamt = @iDjamt + @iTotalAmt ; SET @iPDjamt = @iPDjamt + @iSlimit
UPDATE #DT_FF SET pamt = pamt - @iSlimit WHERE id = @iId
END
END
label:
CLOSE My_cursor1
DEALLOCATE My_cursor1
DELETE FROM #DT_FF where pamt < = 0
END
If @iDjamt > 0
INSERT INTO #DT_FF ( AUTH , Custid , Amt, Pamt )
Values ( '4' , '99' , @iDjamt , @iPDjamt )
SELECT @iTotalAmt = SUM(Amt) , @iPTotalAmt = SUM(PAmt) FROM #DT_FF WHERE Amt > 0 AND Amt IS NOT NULL
IF @iTotalAmt <> @InAmt OR @iPTotalAmt <> @InPAmt/*分F金额不对*/
RETURN - 1
SELECT inid = @Inid , Pid = @Pid , @stanzaid , AUTH , Custid , Qbz = @Qbz , Fdet = @Fdet , Amt , Isnull(PAmt , 0) as PAmt , Accept = '0' , DisFdet = @DisFdet FROM #DT_FF
WHERE Amt > 0 AND Amt IS NOT NULL
/* Accept = (CASE AUTH WHEN '3' THEN 0 ELSE 1 END)*/
END
RETURN 0
GO
确定是那句SQL的dt_fdet_out表锁住Top
30 楼guxing（孤星）回复于 2004-08-02 23:29:26 得分 0
因为这里不能脏读,必须等待其它进程提交事务才能继续
但超过2个连接时,其它的就会被系统牺牲返回1205,而不是继续等待?Top
31 楼guxing（孤星）回复于 2004-08-03 12:10:34 得分 0
我觉得应该可以
我只是在一个存储过程 中
对2-3上表更新(都有自增长ID,所以可以只要行锁)
但还有一个SELECT,必须得知某客户当前已经分配的数量
不能脏读,所以其它进程在更新表时
它要等待
现在我要的是等待但不出错返回
SQL就是自动把2个以上的进程全部死锁牺牲了
唉,正在安装oracle看看它怎么样Top
