寻求可行性！

[wumylove1234]

举个例子：
表1记录的是某个货物应该的发货数量
表2记录的是某个货物实际的发货数量

我要控制表二不能出现表2的实发数量不能大于表1的应货数量。

实际过程中，计算表1的应发数量逻辑要复杂一些，可能要经过计算才能确定，而且有并发的可能性，即：能过计算得到表1的应发数量后，确定可以将表2的实发数量加1，但有可能在我计算表1的时候有并发的请求也在在计算表1，而取表2的数量符合要求，所以决定将表2数量加1，二者都决定可以将表2的实发数量加1，数据可能发生错误而超过表1的数量。

我考虑两种可能性：
1：使用更新触发器，将计算表2的逻辑写在触发器里。
2：使用锁定表的方式。

问题1：如果使用触发器，触发器是可以并发进行的，还是要顺序进行的？
问题2：如何能过事务锁定表？表1的数量是不会变化的，如何锁定我要更新的表2？是不是先开启务，然后Select得到我要更新的记录，这样就把它锁定了？把其它并发请求排队后才能看到更新后的表2数量？

能不能综合使用触发器和事务，来满足我的业务要求？

[wumylove1234]

我顶！

[wumylove1234]

在另个帖另加100分.
http://community.csdn.net/Expert/TopicView3.asp?id=4908572

[zjcxc]

表1和表2的记录对应关系是什么?
1对1
1对多
多对1
多对多?

[zjcxc]

一般来说, 对于更新我可能会这样做

BEGIN TRAN
update B set 可发货标记 = 1
from 表 2 B
WHERE (
SELECT SUM(实发数量) FROM B
WHERE 货物编号 = B.货物编号)
<=(
SELECT SUM(可发数量) FROM A
WHERE 货物编号 = B.货物编号)
IF @@ROWCOUNT = 0
BEGIN
PRINT '不可发货'
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT '可发货'
COMMIT TRAN
END

[zjcxc]

上面是多对多的情况

实现的原理是, 在发货时, 直接按正常条件更新, 如果更新不成功(意味着失败或者是条件不对), 就不发货就行了.

这样只需要一句update, 自动加锁, 其他的你不用考虑了.

[昵称被占用了]

lz提的问题很多

问题1：如果使用触发器，触发器是可以并发进行的，还是要顺序进行的？
并发进行，触发器相当于触发触发器的语句和触发器语句一起组成一个事务。

问题2：如何能过事务锁定表？表1的数量是不会变化的，如何锁定我要更新的表2？是不是先开启务，然后Select得到我要更新的记录，这样就把它锁定了？把其它并发请求排队后才能看到更新后的表2数量？
zjcxc(邹建) 回复过，“只需要一句update, 自动加锁, 其他的你不用考虑了”

3
那么能不能出现我还没有插入完记录呢，另外一个请求也发现没有记录决定新增，结果造成一方增加失败？（失败是由于主键约束）想要避免这种情况怎么办？
这种错误不能完全避免，只能捕捉错误，做出出错处理。

4
关于锁，除非特殊，不要去指定，可以用触发器来防止数量超出。

[wumylove1234]

CREATE PROCEDURE P_W_RevAndsendGoods
@revBillNO varchar(20),
@cartonNO varchar(15),
@UPC varchar(30),
@CustomerName varchar(30) output
as
DECLARE @error varchar(200)
DECLARE @sendBillNo varchar(30)
DECLARE @OldQty int
DECLARE @count int
BEGIN TRANSACTION

SELECT * FROM I_SendGoodsD (tablockx) WHERE 1=0 --锁定发货表
SELECT @sendBillNo=SendBillNO,@OldQty=RQTY,@CustomerName=Customer_na FROM
(SELECT TOP 1 I_SendTaskGoodsD.SendBillNO,I_SendTaskGoodsD.UPC,I_SendTaskGoodsD.QTY AS PQTY,ISNULL(I_SendGoodsD.QTY,0) AS RQTY,
V_W_CustomerAll.Customer_na
FROM
I_SendTaskGoodsM INNER JOIN I_SendTaskGoodsD ON I_SendTaskGoodsM.SendBillNO=I_SendTaskGoodsD.SendBillNO
LEFT JOIN I_SendGoodsD ON I_SendTaskGoodsD.SendBillNO=I_SendGoodsD.SendBillNO AND I_SendTaskGoodsD.UPC=I_SendGoodsD.UPC
INNER JOIN V_W_CustomerAll ON I_SendTaskGoodsM.CustomerID=V_W_CustomerAll.Customer_id
WHERE I_SendTaskGoodsM.RevBillNO=@revBillNO AND I_SendTaskGoodsD.UPC=@UPC AND I_SendTaskGoodsD.QTY>ISNULL(I_SendGoodsD.QTY,0)
ORDER BY V_W_CustomerAll.Customer_id ASC) A
IF @sendBillNo IS NULL
BEGIN
SET @error='UPC条码为:'+@UPC+'的数据异常,请联系数据部工作人员进行检查.'
RAISERROR (@error, 16, 1)
ROLLBACK TRANSACTION
RETURN @@error
END
--1.处理收货记录
SELECT * FROM I_RevGoodsD (tablockx) WHERE 1=0 --锁定收货表
SELECT @count=COUNT(*) FROM I_RevGoodsD WHERE BillNO=@revBillNO AND CartonNO=@cartonNO AND UPC=@UPC
IF @count=0
BEGIN
INSERT INTO I_RevGoodsD(BillNO,CartonNO,UPC,QTY) VALUES(@revBillNO,@cartonNO,@UPC,1)
END
ELSE
BEGIN
UPDATE I_RevGoodsD SET QTY=QTY+1 WHERE BillNO=@revBillNO AND CartonNO=@cartonNO AND UPC=@UPC
END
--2.处理发货记录
SELECT @count=COUNT(*) FROM I_SendGoodsD WHERE SendBillNo=@sendBillNo AND UPC=@UPC
IF @count=0
BEGIN
INSERT INTO I_SendGoodsD(SendBillNo,UPC,QTY) VALUES(@sendBillNo,@UPC,1)
END
ELSE
BEGIN
UPDATE I_SendGoodsD SET QTY=QTY+1 WHERE SendBillNo=@sendBillNo AND UPC=@UPC
END
COMMIT TRANSACTION
GO

我写了一个存储过程.
但是确要锁定两个表,这是因为我实际的业务中的"QTY"在两个表里存放,且另个表的数据不存在时要进行增加操作.数据结构可能不太合理,但是客户要求用这样的结构.
但是我感觉这种方法实在不好,而且我还担心我的这个存储过程会造成死锁,能不能不用锁整个表的方式来满足我的要求?

[wumylove1234]

表1和表2的记录对应关系是什么?
1对1
1对多
多对1
多对多?

收货表对发货表是1对多,
计划发货与实际发货是1对1,计划收货与实际收货是1对1.

邹大侠,我看了你上面那个Update的思路,果真精妙.买了你写的书,不过还没有抽出时间看.但是我的问题是在表B没有数据的时候还要新增呢.不锁定表就可能造成同时新增而引发一方错误的情况(由主键引起).

发货表倒是可以先让发货表与计划发货表记录一致,只是让数量成为0(然后就可以只用Update来更新数量).但是收货表不能这样,因为有可能要收的东西不在收货计划之内.

[wumylove1234]

邹大侠,你上面的那个例子好像是没有经过验证的吧,我照写了一下发现是错误的.我改成这样了.
UPDATE I_SendGoodsD SET QTY=QTY+1
WHERE (SELECT QTY FROM I_SendGoodsD WHERE SendBillNo='111' AND UPC='884802624769')<
(SELECT QTY FROM I_SendTaskGoodsD WHERE SendBillNo='111' AND UPC='884802624769')
AND SendBillNo='111' AND UPC='884802624769'

用的是我的表里的结构,您看一下在是否可以控制并发的情况了.

[奋斗一生的程序员]

^

[昵称被占用了]

锁表的思路有问题，代价太大

[wumylove1234]

我又写了一个不加锁的.
大侠帮我看一下:

/*
此过程为目前所知道的并发性能最好的,因为没有显示的指定锁定表,全由系统自动控制.
要求:
1.导发货计划明细表时,自动将实际表也导入,只不过数量置为0,这样对实发明细表将来只作更新不做新增
*/
CREATE PROCEDURE P_W_RevAndsendGoods_Optimizing
@revBillNO varchar(20),
@cartonNO varchar(15),
@UPC varchar(30),
@CustomerName varchar(30) output
as
DECLARE @error varchar(200)
DECLARE @sendBillNo varchar(30)
DECLARE @OldQty int
DECLARE @count int

SELECT @sendBillNo=SendBillNO,@OldQty=RQTY,@CustomerName=Customer_na FROM
(SELECT TOP 1 I_SendTaskGoodsD.SendBillNO,I_SendTaskGoodsD.UPC,I_SendTaskGoodsD.QTY AS PQTY,ISNULL(I_SendGoodsD.QTY,0) AS RQTY,
V_W_CustomerAll.Customer_na
FROM
I_SendTaskGoodsM INNER JOIN I_SendTaskGoodsD ON I_SendTaskGoodsM.SendBillNO=I_SendTaskGoodsD.SendBillNO
LEFT JOIN I_SendGoodsD ON I_SendTaskGoodsD.SendBillNO=I_SendGoodsD.SendBillNO AND I_SendTaskGoodsD.UPC=I_SendGoodsD.UPC
INNER JOIN V_W_CustomerAll ON I_SendTaskGoodsM.CustomerID=V_W_CustomerAll.Customer_id
WHERE I_SendTaskGoodsM.RevBillNO=@revBillNO AND I_SendTaskGoodsD.UPC=@UPC AND I_SendTaskGoodsD.QTY>ISNULL(I_SendGoodsD.QTY,0)
ORDER BY V_W_CustomerAll.Customer_id ASC) A
IF @sendBillNo IS NULL
BEGIN
SET @error='UPC条码为:'+@UPC+'的数据异常,请联系数据部工作人员进行检查.'
RAISERROR (@error, 16, 1)
RETURN @@error
END
BEGIN TRANSACTION
--1.处理发货记录
UPDATE I_SendGoodsD SET QTY=QTY+1
WHERE (SELECT QTY FROM I_SendGoodsD WHERE SendBillNo=@sendBillNo AND UPC=@UPC)<
(SELECT QTY FROM I_SendTaskGoodsD WHERE SendBillNo=@sendBillNo AND UPC=@UPC)
AND SendBillNO=@sendBillNo AND UPC=@UPC
IF @@RowCount=0
BEGIN
SELECT @count=COUNT(*) FROM master.dbo.sysmessages WHERE error=500001
IF @count=0
BEGIN
EXEC sp_addmessage 50001, 16, N'发生并发冲突:未能成功更新实际发货表中的数量.'
END
RAISERROR (50001, 16, 1)
GOTO EXIT_PROC
END
--2.处理收货记录
SELECT @count=COUNT(*) FROM I_RevGoodsD WHERE BillNO=@revBillNO AND CartonNO=@cartonNO AND UPC=@UPC
IF @count=0
BEGIN
INSERT INTO I_RevGoodsD(BillNO,CartonNO,UPC,QTY) VALUES(@revBillNO,@cartonNO,@UPC,1)
END
ELSE
BEGIN
UPDATE I_RevGoodsD SET QTY=QTY+1 WHERE BillNO=@revBillNO AND CartonNO=@cartonNO AND UPC=@UPC
END
EXIT_PROC:
IF @@error=0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
RETURN @@error

GO

[wumylove1234]

上面的又有一个问题:
如果对于主键冲突这种错误,我在存储过程内部能捕获吗?

[wumylove1234]

ALTER proc test
as
declare @a int
begin tran
insert into i_sendpacketm values('1','1','2005-01-01')
insert into i_sendpacketm values('1','1','2005-01-01')

set @a=@@error
if @a<>0
begin
PRINT '重复'
end

commit tran

这样的错误是:
服务器: 消息 3604，级别 16，状态 1，过程 test，行 12
已忽略重复的键。
服务器: 消息 3604，级别 16，状态 1，过程 test，行 12
已忽略重复的键。
存储过程: PDSortDB.dbo.test
返回代码 = 0

不知道为什么@@error居然还是0.......急啊.这个搞定就确定用这个方法了.

[zjcxc]

1. 你的写法可以
2. 对于insert , 你可以用与update 一样的思路, insert 的时候加条件, 判断你insert 后的数量是否超过计划, 类似于
DECLARE @qty int
SET @qty = 10 --要发出的数量
INSERT I_SendGoodsD(qty, ...)
SELECT @qty, .....
WHERE @qty + ISNULL((SELECT QTY FROM I_SendGoodsD WHERE SendBillNo='111' AND UPC='884802624769'), 0)<
(SELECT QTY FROM I_SendTaskGoodsD WHERE SendBillNo='111' AND UPC='884802624769')

[zjcxc]

--2000里面的错误处理比较弱, 要在每条语句后判断@@error, 所以楼主的存储过程应该改为

ALTER proc test
as
declare @a int
begin tran
insert into i_sendpacketm values('1','1','2005-01-01')
if @@error<>0
goto lb_err
insert into i_sendpacketm values('1','1','2005-01-01')
if @@error<>0
goto lb_err

commit tran
return

lb_err:
rollback tran
PRINT '重复'

[zjcxc]

另外, 要注意, 在sql中,严重的错误会导致处理终止, 所以可能会导致你的错误处理语句得不到执行.

所以一般要配合调用程序的错误处理来保障所有的错误都得到控制.

[wumylove1234]

谢谢邹大侠.

我现在又有一个问题.
在Insert的时候,并发时会导致Insert失败,我修改了存储过程,在Insert失败后递归调用当前存储过程,使其在第二次调用时正确的处理Update.

但是Insert失败后会引起前端程序的异常,而我想屏掉这个异常,因为我已经在存储过程中处理了它.

[zjcxc]

2000中没有什么好办法

[wumylove1234]

/*
未实现:自动置收与发货单状态的功能.

功能:
分拣业务处理.
使用错误描述+返回值+前台捕获错误的方式

前台:
1.正常情况下检查返回值与ERROR,返回值为0则正常进行了执行,否则检查ERROR的错误描述..
2.异常情况下:
(1)如果返回值非0则是系统已经捕获了该错误,不做处理.
(2)如果返回值为0则说明是系统未捕获的错误,需要做错误处理.

此过程为目前所知道的并发性能最好的,因为没有显示的指定锁定表,全由系统自动控制.
要求:
1.导发货计划明细表时,自动将实际表也导入,只不过数量置为0,这样对实发明细表将来只作更新不做新增
2.前台程序通过返回值确定处理方式:
(1).50000:没有匹配的发货数据.
3.前台如果得到返回值为0,而又有错误的话,就是未知错误
*/
ALTER PROCEDURE P_W_RevAndsendGoods_Return
@revBillNO varchar(20), --收货单号
@cartonNO varchar(15), --箱号
@UPC varchar(30), --UPC
@CustomerName varchar(30) output, --返回的客户名称
@Error varchar(8000) output, --返回的错误描述
@debug int =0, --0:不调试;1:调试发货;2:调试收货;3:全调
@UserID varchar(20)='' --调用此方法的操作员ID
as
DECLARE @saveError int;
DECLARE @sendBillNo varchar(30)
DECLARE @OldQty int
DECLARE @count int

------------------------------------------当常量使用--------------------------------------------------
DECLARE @DATAERROR int
DECLARE @SENDCONFLICT int
DECLARE @REVCONFLICT int

SET @DATAERROR=50000
SET @SENDCONFLICT=50001
SET @REVCONFLICT=50002
-----------------------------------------------------------------------------------------------------
SET NOCOUNT ON

SET @saveError=0
SELECT @sendBillNo=SendBillNO,@OldQty=RQTY,@CustomerName=Customer_na FROM
(SELECT TOP 1 I_SendTaskGoodsD.SendBillNO,I_SendTaskGoodsD.UPC,I_SendTaskGoodsD.QTY AS PQTY,ISNULL(I_SendGoodsD.QTY,0) AS RQTY,
V_W_CustomerAll.Customer_na
FROM
I_RevTaskGoodsM INNER JOIN I_SendTaskGoodsM ON I_RevTaskGoodsM.BillNO=I_SendTaskGoodsM.RevBillNO
INNER JOIN I_SendTaskGoodsD ON I_SendTaskGoodsM.SendBillNO=I_SendTaskGoodsD.SendBillNO
LEFT JOIN I_SendGoodsD ON I_SendTaskGoodsD.SendBillNO=I_SendGoodsD.SendBillNO AND I_SendTaskGoodsD.UPC=I_SendGoodsD.UPC
INNER JOIN V_W_CustomerAll ON I_SendTaskGoodsM.CustomerID=V_W_CustomerAll.Customer_id
WHERE I_RevTaskGoodsM.BillNO=@revBillNO AND I_SendTaskGoodsD.UPC=@UPC AND I_SendTaskGoodsD.QTY>ISNULL(I_SendGoodsD.QTY,0) AND I_RevTaskGoodsM.BillStatus=0 AND I_SendTaskGoodsM.BillStatus=0
ORDER BY V_W_CustomerAll.Customer_id ASC) A
BEGIN TRANSACTION
IF @sendBillNo IS NULL
BEGIN
SET @saveError=@DATAERROR
SET @Error='系统数据发生错误,请联系数据部工作人员.出错的原因可能是:
\n系统中无此收货单;
\n货物不能进行分货(已全部分完或不存在对应的客户)';
GOTO EXIT_PROC
END
--1.处理发货记录
IF @debug=1 OR @debug=3
BEGIN
waitfor delay '00:00:20'
END
UPDATE I_SendGoodsD SET QTY=QTY+1
WHERE (SELECT QTY FROM I_SendGoodsD WHERE SendBillNo=@sendBillNo AND UPC=@UPC)<
(SELECT QTY FROM I_SendTaskGoodsD WHERE SendBillNo=@sendBillNo AND UPC=@UPC)
AND SendBillNO=@sendBillNo AND UPC=@UPC
IF @@RowCount=0
BEGIN
SET @saveError=@SENDCONFLICT
GOTO EXIT_PROC
END
--2.处理收货记录
SELECT @count=COUNT(*) FROM I_RevGoodsD WHERE BillNO=@revBillNO AND CartonNO=@cartonNO AND UPC=@UPC
IF @count=0
BEGIN
IF @debug=2 OR @debug=3
BEGIN
waitfor delay '00:00:20'
END
INSERT INTO I_RevGoodsD(BillNO,CartonNO,UPC,QTY) VALUES(@revBillNO,@cartonNO,@UPC,1)
SET @saveError=@@error
IF @saveError<>0
BEGIN
SET @saveError=@REVCONFLICT
GOTO EXIT_PROC
END
END
ELSE
BEGIN
UPDATE I_RevGoodsD SET QTY=QTY+1 WHERE BillNO=@revBillNO AND CartonNO=@cartonNO AND UPC=@UPC
END
EXIT_PROC:
IF @saveError=0
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
SET @CustomerName=NULL
ROLLBACK TRANSACTION
--如果错误是由于并发冲突造成的,则递归调用此过程
IF @saveError=@SENDCONFLICT OR @saveError=@REVCONFLICT
BEGIN
EXECUTE @saveError=P_W_RevAndsendGoods_Return @revBillNO,@cartonNO,@UPC,@CustomerName output,@debug
END
END
RETURN @saveError
SET NOCOUNT OFF

最终基本上就做这个样了.结帖.