USE test GO CREATE TABLE [dbo].[TRANTEST]( [id] [int] IDENTITY(1,1) NOT NULL, [Value] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL, [InDate] [datetime] NULL ) ON [PRIMARY]INSERT INTO [dbo].[TRANTEST]([Value],[InDate]) VALUES ('325',GETDATE()) INSERT INTO [dbo].[TRANTEST]([Value],[InDate]) VALUES ('35465',GETDATE()) INSERT INTO [dbo].[TRANTEST]([Value],[InDate]) VALUES ('32435',GETDATE()) SELECT * FROM DBO.TRANTEST WITH (NOLOCK) BEGIN TRAN --事务回滚测试 TRUNCATE TABLE TRUNCATE TABLE DBO.TRANTEST SELECT * FROM DBO.TRANTEST WITH (NOLOCK) ROLLBACK TRAN SELECT * FROM DBO.TRANTEST WITH (NOLOCK) --回滚成功 BEGIN TRAN--事务回滚测试 DROP TABLE DROP TABLE DBO.TRANTEST ROLLBACK TRAN SELECT * FROM DBO.TRANTEST WITH(NOLOCK)--回滚成功
USE test go CREATE TABLE [dbo].[TRANTEST2]( [id] [int] IDENTITY(1,1) NOT NULL, [Value] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL, [InDate] [datetime] NULL ) ON [PRIMARY] /* 事务中嵌套存储过程的回滚测试 */ --EXEC DBO.UP_TRANTEST2 CREATE PROC DBO.UP_TRANTEST2 AS SET NOCOUNT ON SELECT * FROM [dbo].[TRANTEST2]WITH(NOLOCK) TRUNCATE TABLE DBO.TRANTEST2 BEGIN TRANINSERT INTO [dbo].[TRANTEST2]([Value],[InDate]) VALUES ('TEST1',GETDATE())EXEC DBO.UP_TRANTEST3 EXEC DBO.UP_TRANTEST4SELECT * FROM [dbo].[TRANTEST2]WITH(NOLOCK)ROLLBACK TRAN SELECT * FROM [dbo].[TRANTEST2]WITH(NOLOCK)--存储过程1--
CREATE PROC DBO.UP_TRANTEST3 ASSET NOCOUNT ONINSERT INTO [dbo].[TRANTEST2]([Value],[InDate]) VALUES ('TEST2',GETDATE())--存储过程2--
GO
CREATE TABLE [dbo].[TRANTEST](
[id] [int] IDENTITY(1,1) NOT NULL,
[Value] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
[InDate] [datetime] NULL
) ON [PRIMARY]INSERT INTO [dbo].[TRANTEST]([Value],[InDate]) VALUES ('325',GETDATE())
INSERT INTO [dbo].[TRANTEST]([Value],[InDate]) VALUES ('35465',GETDATE())
INSERT INTO [dbo].[TRANTEST]([Value],[InDate]) VALUES ('32435',GETDATE())
SELECT * FROM DBO.TRANTEST WITH (NOLOCK) BEGIN TRAN --事务回滚测试 TRUNCATE TABLE
TRUNCATE TABLE DBO.TRANTEST
SELECT * FROM DBO.TRANTEST WITH (NOLOCK)
ROLLBACK TRAN
SELECT * FROM DBO.TRANTEST WITH (NOLOCK) --回滚成功
BEGIN TRAN--事务回滚测试 DROP TABLE
DROP TABLE DBO.TRANTEST
ROLLBACK TRAN
SELECT * FROM DBO.TRANTEST WITH(NOLOCK)--回滚成功
go
CREATE TABLE [dbo].[TRANTEST2](
[id] [int] IDENTITY(1,1) NOT NULL,
[Value] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
[InDate] [datetime] NULL
) ON [PRIMARY]
/*
事务中嵌套存储过程的回滚测试
*/
--EXEC DBO.UP_TRANTEST2
CREATE PROC DBO.UP_TRANTEST2
AS
SET NOCOUNT ON
SELECT * FROM [dbo].[TRANTEST2]WITH(NOLOCK)
TRUNCATE TABLE DBO.TRANTEST2
BEGIN TRANINSERT INTO [dbo].[TRANTEST2]([Value],[InDate]) VALUES ('TEST1',GETDATE())EXEC DBO.UP_TRANTEST3 EXEC DBO.UP_TRANTEST4SELECT * FROM [dbo].[TRANTEST2]WITH(NOLOCK)ROLLBACK TRAN SELECT * FROM [dbo].[TRANTEST2]WITH(NOLOCK)--存储过程1--
ASSET NOCOUNT ONINSERT INTO [dbo].[TRANTEST2]([Value],[InDate]) VALUES ('TEST2',GETDATE())--存储过程2--