- 论坛徽章:
- 0
|
deadlock问题,请进=>
好的,谢谢版主 & 2BeSybPro!
Insert的两个表的结构:
1、RCD_CKTD:出库单头表,存放出库单表头的信息
create table dbo.RCD_CKTD (
CKDH char(10) not null,
SYGSBM char(10) not null,
CKRQ datetime not null,
ZDRQ datetime not null,
THDW char(20) not null,
FYR char(13) null,
THDJ char(1) not null,
DJH char(10) not null,
CH char( null,
THR char( null,
ZDR char(14) null,
ZFBZ char(1) DEFAULT '0' not null,
ZFSJ datetime null,
DYZPH char(10) null,
BZ varchar(100) null,
KQDBKRKDBZ char(1) DEFAULT '0' null,
constraint PCD_CKTD_12055793331 PRIMARY KEY CLUSTERED ( CKDH)
)
on 'default'
go
print 'idx_cdtd_ckrq'
create nonclustered index idx_cdtd_ckrq
on dbo.RCD_CKTD (CKRQ)
on 'default'
另外,该表的唯一一致性的列为CKDH。
2、RCD_CKDZB:出库单子表,存放出库单明细信息
create table dbo.RCD_CKDZB (
CKDH char(10) not null,
XH int not null,
YL char(5) not null,
YPBM char(16) not null,
LCBM char(10) not null,
PPBM char(10) null,
GGXHBM char(6) null,
JLDWBM char(6) not null,
GJHSXS numeric(16, 10) not null,
SL numeric(16, 6) not null,
SLD numeric(16, 6) not null,
constraint RCD_CKDZB_4501006441 PRIMARY KEY CLUSTERED ( CKDH,XH)
)
on 'default'
print 'idx_cdzb_yplcppggdw'
create nonclustered index idx_cdzb_yplcppggdw
on dbo.RCD_CKDZB (YPBM, LCBM, PPBM, GGXHBM, JLDWBM)
on 'default'
另外,该表的唯一一致性的列为CKDH、XH。
Update的表的结构:
1、RCD_THDTB:提货单头表,存放提货单表头的信息
create table dbo.RCD_THDTB (
THDH char(10) not null,
SYGSBM char(10) not null,
THRQ datetime not null,
ZDRQ datetime not null,
CYYK char(10) null,
DJLX char(2) not null,
XSXZ char(1) null,
ZDR char(14) null,
YWY char(14) null,
THQX int null,
BZ varchar(100) null,
PSBZ char(1) DEFAULT '0' not null,
KHBM char(20) null,
TWBZ char(1) DEFAULT '0' not null,
ZFBZ char(1) DEFAULT '0' not null,
ZFSJ datetime null,
DYZPH char(10) null,
FPH char(12) null,
YKBZ char(1) DEFAULT '0' null,
CFBZ char(1) DEFAULT '0' null,
HTBH char(10) null,
constraint RCD_THDTB_10935789341 PRIMARY KEY CLUSTERED ( THDH)
)
on 'default'
go
print 'idx_thdtb_thrq'
create nonclustered index idx_thdtb_thrq
on dbo.RCD_THDTB (THRQ)
on 'default'
另外,该表的唯一一致性的列为THDH。
2、RCD_THDZB:提货单子表,存放提货单明细信息
create table dbo.RCD_THDZB (
THDH char(10) not null,
XH int not null,
YL char(5) not null,
YPBM char(16) not null,
LCBM char(10) not null,
PPBM char(10) null,
GGXHBM char(6) null,
JLDWBM char(6) not null,
GJHSXS numeric(16, 10) not null,
SL numeric(16, 6) not null,
DJ numeric(16, 10) null,
JE numeric(16, 2) null,
SLD numeric(16, 6) not null,
YTSL numeric(16, 6) null,
JSBZ char(1) DEFAULT '0' null,
HKJE numeric(10, 2) null,
YKSL numeric(16, 6) DEFAULT 0 null,
constraint RCD_THDZB_3617683461 PRIMARY KEY CLUSTERED ( THDH,XH)
)
on 'default'
go
print 'idx_thdzb_yplcppggdw'
create nonclustered index idx_thdzb_yplcppggdw
on dbo.RCD_THDZB (YPBM, LCBM, PPBM, GGXHBM, JLDWBM)
on 'default'
另外,该表的唯一一致性的列为THDH、XH。
Insert表的顺序及SQL:
INSERT INTO RCD_CKTD(CKDH,SYGSBM,CKRQ,ZDRQ,THDW,FYR,THDJ,DJH,CH,THR,ZDR,ZFBZ,ZFSJ,DYZPH,BZ)
VALUES('0500005330','0103101001','2005-05-10','2005-05-10 9:38:37','01031010060000000001','','2','0500006213','','','01031010010001','0',null,'','')
INSERT INTO RCD_CKDZB( CKDH, XH,YL,YPBM,LCBM,PPBM,GGXHBM,JLDWBM,GJHSXS,SL,SLD)
VALUES('0500005330',1,'04001','04001010101','0000000002','0000000002','000038','000002',3.5,1,0.003500)
INSERT INTO RCD_CKDZB( CKDH, XH,YL,YPBM,LCBM,PPBM,GGXHBM,JLDWBM,GJHSXS,SL,SLD)
VALUES('0500005330',2,'04001','04001010102','0000000002','0000000002','000038','000002',3.5,3,1.05)
插入RCD_CKDZB的类似语句最多有5个
Update标的顺序及SQL:
UPDATE RCD_THDZB SET YTSL=ISNULL(YTSL,0)+1 WHERE THDH='0500006213' AND XH=1
UPDATE RCD_THDTB SET TWBZ='1' WHERE THDH='0500006213' AND (SELECT SUM(ISNULL(YTSL,0))-SUM(ISNULL(SL,0)) FROM RCD_THDZB WHERE THDH='0500006213' )=0 |
|