SQL存储过程多人同时操作时死锁问题(求大神)

andy21251002 2015-01-21 08:25:11

菜鸟求解。如题。调用存储过程代码如下：
CREATE PROCEDURE [dbo].[PRO_OM_ORDERS]
@ORDER_CODE VARCHAR(20) --订单单号
AS
--DECLARE
-- @ORDER_CODE VARCHAR(20) --订单单号
-- SET @ORDER_CODE = '%'

BEGIN
DECLARE
@ORDER_NEW VARCHAR(15),
@ORDER_ITEM VARCHAR(3),
@ORDER_ITEM_NEW VARCHAR(3),
@SQL VARCHAR(MAX),
@SIZE_NO VARCHAR(12),
@SIZE_NUM NUMERIC(20,0),
@LOSS_NUM NUMERIC(20,0),
@INT INT,
@NUM INT
SET @INT = 1

---定义临时表
CREATE TABLE [dbo].[#OM_ORDERS_ITEM](
[FTY_CODE] [varchar](15) NOT NULL,
[ORDER_CODE] [varchar](20) NOT NULL,
[ITEM_NO] [varchar](4) NOT NULL,
[CUSTOM_CODE] [varchar](15) NULL,
[PR_NO] [varchar](20) NOT NULL,
[PO_NO] [varchar](20) NULL,
[ARTICLE_CODE] [varchar](60) NULL,
[STYLE_CODE] [varchar](60) NULL,
[EDITION_NO] [varchar](5) NULL,
[CUSTOMER_STYLE] [varchar](60) NULL,
[COLOR_CODE] [varchar](60) NULL,
[PRODUCT_TYPE] [varchar](5) NULL,
[GOODS_TYPE] [varchar](5) NULL,
[GOODS_CODE] [varchar](40) NULL,
[GOODS_NAME] [varchar](300) NULL,
[UNIT] [varchar](5) NULL,
[QTY] [numeric](18, 6) NULL,
[LOSS_RATE] [numeric](18, 6) NULL,
[LOSS_NUM] [numeric](18, 6) NULL,
[PRICE_BILL] [varchar](15) NULL,
[PRICE] [numeric](18, 6) NULL,
[MONEY] [numeric](18, 6) NULL,
[ORDER_DATE] [datetime] NULL,
[ACCEPT_DATE] [datetime] NULL,
[REQUEST_DATE] [datetime] NULL,
[ETD_DATE] [datetime] NULL,
[SEND_LOCATION] [varchar](180) NULL,
[SEND_STORAGE] [varchar](20) NULL,
[SPRAY_COLOR] [varchar](30) NULL,
[HEADNESS] [varchar](30) NULL,
[REMARK] [varchar](60) NULL,
[size_no01] [varchar](12) NULL,
[size_num01] [numeric](20, 0) NULL,
[loss_num01] [numeric](20, 0) NULL,
[size_no02] [varchar](12) NULL,
[size_num02] [numeric](20, 0) NULL,
[loss_num02] [numeric](20, 0) NULL,
[size_no03] [varchar](12) NULL,
[size_num03] [numeric](20, 0) NULL,
[loss_num03] [numeric](20, 0) NULL,
[size_no04] [varchar](12) NULL,
[size_num04] [numeric](20, 0) NULL,
[loss_num04] [numeric](20, 0) NULL,
[size_no05] [varchar](12) NULL,
[size_num05] [numeric](20, 0) NULL,
[loss_num05] [numeric](20, 0) NULL,
[size_no06] [varchar](12) NULL,
[size_num06] [numeric](20, 0) NULL,
[loss_num06] [numeric](20, 0) NULL,
[size_no07] [varchar](12) NULL,
[size_num07] [numeric](20, 0) NULL,
[loss_num07] [numeric](20, 0) NULL,
[size_no08] [varchar](12) NULL,
[size_num08] [numeric](20, 0) NULL,
[loss_num08] [numeric](20, 0) NULL,
[size_no09] [varchar](12) NULL,
[size_num09] [numeric](20, 0) NULL,
[loss_num09] [numeric](20, 0) NULL,
[size_no10] [varchar](12) NULL,
[size_num10] [numeric](20, 0) NULL,
[loss_num10] [numeric](20, 0) NULL,
[size_no11] [varchar](12) NULL,
[size_num11] [numeric](20, 0) NULL,
[loss_num11] [numeric](20, 0) NULL,
[size_no12] [varchar](12) NULL,
[size_num12] [numeric](20, 0) NULL,
[loss_num12] [numeric](20, 0) NULL,
[size_no13] [varchar](12) NULL,
[size_num13] [numeric](20, 0) NULL,
[loss_num13] [numeric](20, 0) NULL,
[size_no14] [varchar](12) NULL,
[size_num14] [numeric](20, 0) NULL,
[loss_num14] [numeric](20, 0) NULL,
[size_no15] [varchar](12) NULL,
[size_num15] [numeric](20, 0) NULL,
[loss_num15] [numeric](20, 0) NULL,
[size_no16] [varchar](12) NULL,
[size_num16] [numeric](20, 0) NULL,
[loss_num16] [numeric](20, 0) NULL,
[size_no17] [varchar](12) NULL,
[size_num17] [numeric](20, 0) NULL,
[loss_num17] [numeric](20, 0) NULL,
[size_no18] [varchar](12) NULL,
[size_num18] [numeric](20, 0) NULL,
[loss_num18] [numeric](20, 0) NULL,
[CREATE_DATE] [datetime] NULL,
[CREATE_BY] [varchar](15) NULL,
[MODIFY_DATE] [datetime] NULL,
[MODIFY_BY] [varchar](15) NULL,
[LOCK_MK] [char](1) NULL,
[LOCK_DATE] [datetime] NULL,
[LOCK_BY] [varchar](15) NULL
[MAT_CODE] [varchar](50) NULL,
[STAPLE_CODE] [varchar](25) NULL,
[ABS_CODE] [varchar](100) NULL,
CONSTRAINT [PK_#OM_ORDERS_ITEM] PRIMARY KEY CLUSTERED
(
[FTY_CODE] ASC,
[ORDER_CODE] ASC,
[ITEM_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_NULLS OFF
SET NOCOUNT ON

--取订单明细

DECLARE AA CURSOR
FOR SELECT OM_ORDERS_ITEM.ORDER_CODE,OM_ORDERS_ITEM.ITEM_NO
FROM OM_ORDERS WITH(NOLOCK),OM_ORDERS_ITEM WITH(NOLOCK)
WHERE OM_ORDERS.FTY_CODE = OM_ORDERS_ITEM.FTY_CODE AND
OM_ORDERS.ORDER_CODE = OM_ORDERS_ITEM.ORDER_CODE AND
OM_ORDERS_ITEM.ORDER_CODE LIKE @ORDER_CODE
OPEN AA
FETCH NEXT FROM AA INTO @ORDER_NEW,@ORDER_ITEM;
WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @ORDER_NEW +'/'+ @ORDER_ITEM
---检索主档信息
INSERT INTO #OM_ORDERS_ITEM
( FTY_CODE ,ORDER_CODE ,ITEM_NO ,CUSTOM_CODE ,PR_NO ,PO_NO ,ARTICLE_CODE ,STYLE_CODE ,
EDITION_NO ,CUSTOMER_STYLE ,COLOR_CODE ,PRODUCT_TYPE ,GOODS_TYPE ,GOODS_CODE ,GOODS_NAME ,
UNIT ,QTY ,LOSS_RATE ,LOSS_NUM ,PRICE_BILL ,PRICE ,MONEY ,ORDER_DATE ,ACCEPT_DATE ,
REQUEST_DATE ,ETD_DATE ,SEND_LOCATION ,SEND_STORAGE ,SPRAY_COLOR ,HEADNESS ,REMARK ,CREATE_DATE ,
CREATE_BY ,MODIFY_DATE ,MODIFY_BY ,LOCK_MK ,LOCK_DATE ,LOCK_BY,MAT_CODE,STAPLE_CODE,ABS_CODE )
SELECT OM_ORDERS_ITEM.FTY_CODE,OM_ORDERS_ITEM.ORDER_CODE,
OM_ORDERS_ITEM.ITEM_NO ,SUBSTRING(OM_ORDERS.CUSTOM_CODE,2,LEN(OM_ORDERS.CUSTOM_CODE) - 1) ,
OM_ORDERS_ITEM.PR_NO ,OM_ORDERS.PO_NO ,
OM_ORDERS_ITEM.ARTICLE_CODE ,OM_ORDERS_ITEM.STYLE_CODE ,
OM_ORDERS_ITEM.EDITION_NO ,OM_ORDERS_ITEM.CUSTOMER_STYLE ,
OM_ORDERS_ITEM.COLOR_CODE ,OM_ORDERS_ITEM.PRODUCT_TYPE ,
OM_ORDERS_ITEM.GOODS_TYPE ,OM_ORDERS_ITEM.GOODS_CODE ,
OM_ORDERS_ITEM.GOODS_NAME ,OM_ORDERS_ITEM.UNIT ,
OM_ORDERS_ITEM.QTY ,OM_ORDERS_ITEM.LOSS_RATE ,
OM_ORDERS_ITEM.LOSS_NUM ,OM_ORDERS_ITEM.PRICE_BILL ,
OM_ORDERS_ITEM.PRICE ,OM_ORDERS_ITEM.MONEY ,
OM_ORDERS_ITEM.ORDER_DATE ,OM_ORDERS_ITEM.ACCEPT_DATE ,
OM_ORDERS_ITEM.REQUEST_DATE ,OM_ORDERS_ITEM.ETD_DATE ,
OM_ORDERS_ITEM.SEND_LOCATION ,OM_ORDERS_ITEM.SEND_STORAGE ,
OM_ORDERS_ITEM.SPRAY_COLOR ,OM_ORDERS_ITEM.HEADNESS ,
OM_ORDERS_ITEM.REMARK ,OM_ORDERS_ITEM.CREATE_DATE ,
OM_ORDERS_ITEM.CREATE_BY ,OM_ORDERS_ITEM.MODIFY_DATE ,
OM_ORDERS_ITEM.MODIFY_BY ,OM_ORDERS_ITEM.LOCK_MK ,
OM_ORDERS_ITEM.LOCK_DATE ,OM_ORDERS_ITEM.LOCK_BY,
OM_ORDERS_ITEM.MAT_CODE,OM_ORDERS_ITEM.STAPLE_CODE,
OM_ORDERS_ITEM.ABS_CODE
FROM OM_ORDERS WITH(NOLOCK),OM_ORDERS_ITEM WITH(NOLOCK)
WHERE OM_ORDERS.FTY_CODE = OM_ORDERS_ITEM.FTY_CODE AND
OM_ORDERS.ORDER_CODE = OM_ORDERS_ITEM.ORDER_CODE AND
OM_ORDERS_ITEM.ORDER_CODE = @ORDER_NEW AND
OM_ORDERS_ITEM.ITEM_NO = @ORDER_ITEM
--检索子档信息
SET @INT = 1;
DECLARE BB CURSOR
FOR SELECT ITEM_NO,WORK_SIZE,SIZE_NUM,LOSS_NUM
FROM OM_ORDERS_ITEM_SIZE WITH(NOLOCK)
WHERE ORDER_CODE LIKE @ORDER_NEW AND ITEM_NO = @ORDER_ITEM AND SIZE_NUM > 0
ORDER BY SORT_NUM
OPEN BB
FETCH NEXT FROM BB INTO @ORDER_ITEM_NEW,@SIZE_NO,@SIZE_NUM,@LOSS_NUM
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL = 'UPDATE #OM_ORDERS_ITEM SET size_no'+ REPLICATE('0',2 -LEN(@INT))+CONVERT(VARCHAR(2),@INT) + ' = ''' + @size_no + ''',size_num' + REPLICATE('0',2 -LEN(@INT))+CONVERT(VARCHAR(2),@INT) + ' = ' +CONVERT(VARCHAR(12),@SIZE_NUM)+',LOSS_NUM'+ REPLICATE('0',2 -LEN(@INT))+CONVERT(VARCHAR(2),@INT) + ' = ' + CONVERT(VARCHAR(12),@LOSS_NUM) + ' WHERE ORDER_CODE = ''' + @ORDER_NEW +'''' +' AND ITEM_NO = '''+@ORDER_ITEM_NEW + ''''
--PRINT @SQL
EXEC(@SQL)
SET @INT = @INT + 1

FETCH NEXT FROM BB INTO @ORDER_ITEM_NEW,@SIZE_NO,@SIZE_NUM,@LOSS_NUM
END
CLOSE BB
DEALLOCATE BB

FETCH NEXT FROM AA INTO @ORDER_NEW,@ORDER_ITEM;
END
CLOSE AA
DEALLOCATE AA

---反馈结果值
SELECT * FROM #OM_ORDERS_ITEM WITH(NOLOCK);
Drop TABLE #OM_ORDERS_ITEM;
END

...全文