22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb (
ID INT IDENTITY(1,1),
PK1 SMALLDATETIME,
PK2 CHAR(5),
PK3 CHAR(10),
DATA1 BIT,
DATA2 INT,
DATA3 VARCHAR(50) ,
CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED(ID)
)
GO
CREATE CLUSTERED INDEX CI_PK1_PK2_PK3 ON tb(PK1,PK2,PK3)
GO
INSERT INTO tb(PK1,PK2,PK3,DATA1,DATA2,DATA3)
SELECT '2009/08/15','A1','AAAA1',0,10,'Record 1'
UNION ALL
SELECT '2009/08/15','A1','AAAA2',0,10,'Record 2'
UNION ALL
SELECT '2009/08/15','A2','AAAA1',0,10,'Record 3'
UNION ALL
SELECT '2009/08/15','A2','AAAA2',0,10,'Record 4'
UNION ALL
SELECT '2009/08/15','A3','AAAA1',0,10,'Record 5'
UNION ALL
SELECT '2009/08/15','A3','AAAA2',0,10,'Record 6'
UNION ALL
SELECT '2009/08/14','B1','BBBBB1',0,10,'Record 7'
UNION ALL
SELECT '2009/08/14','B1','BBBBB2',0,10,'Record 8'
UNION ALL
SELECT '2009/08/14','B2','BBBBB1',0,10,'Record 9'
UNION ALL
SELECT '2009/08/14','B2','BBBBB2',0,10,'Record 10'
UNION ALL
SELECT '2009/08/14','B3','BBBBB1',0,10,'Record 11'
UNION ALL
SELECT '2009/08/14','B3','BBBBB2',0,10,'Record 12'
--Connection 1:
BEGIN TRAN
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB1'
--(1 行受影响)
--Connection 2:
BEGIN TRAN
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB2'
--(1 行受影响)
--返回connection 1:
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B3' AND PK3='BBBBB1' AND DATA3='Record 11'
--(1 行受影响)
--返回connection 2:
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B1' AND PK3='BBBBB1' AND DATA3='Record 7'
--(1 行受影响)
begin tran
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB2'
BEGIN TRAN
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB1'
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK3='BBBBB2' AND DATA3='Record 12'
BEGIN TRAN
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB2'
COMMIT TRAN
BEGIN TRAN
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB1'
COMMIT TRAN
--第一个connection
BEGIN TRAN
UPDATE tb1 with (rowlock) SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB1'
waitfor delay '00:00:10'
UPDATE tb1 with (rowlock) SET DATA1=1 WHERE PK1='2009/08/14' AND PK3='BBBBB2' AND DATA3='Record 12'
commit tran
--第二个connection
BEGIN TRAN
UPDATE tb1 with (rowlock) SET DATA1=1 WHERE PK1='2009/08/14' AND PK2='B2' AND PK3='BBBBB2'
waitfor delay '00:00:10'
UPDATE tb1 with (rowlock) SET DATA1=1 WHERE PK1='2009/08/14' AND PK3='BBBBB1' AND DATA3='Record 7'
commit tran
UPDATE tb SET DATA1=1 WHERE PK1='2009/08/14' AND PK3='BBBBB1' AND DATA3='Record 7'