使用ASP.NET 2.0 成员资格/角色模型需要在SQL SERVER数据库中创建若干个以"aspnet_"开头的数据表,在处理这些数据表的时候也用到了不少的存储过程,在这些存储过程中,我发现有好几个存储过程都用到了以下的代码:IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
通过查询MSDN我知道, @@TRANCOUNT是一个全局变量, 存储着当前连接活动的事务数.但问题是, 为什么当前存在活动的事务的时候就要BEGIN TRANSACTION
而不存在活动事务的时候就不需要 设置一个显式事物的起点呢?有大虾知道这是为什么吗?以下是aspnet_Membership_CreateUser存储过程的定义:CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL DECLARE @IsLockedOut bit
SET @IsLockedOut = 0 DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 ) DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0 DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0 DECLARE @ErrorCode int
SET @ErrorCode = 0 DECLARE @TranStarted bit
SET @TranStarted = 0 --就是这里, 鄙人感到很困惑...
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0 EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END SET @CreateDate = @CurrentTimeUtc SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END SET @UserId = @NewUserId IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart ) IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END RETURN 0Cleanup: IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END RETURN @ErrorCodeEND
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
通过查询MSDN我知道, @@TRANCOUNT是一个全局变量, 存储着当前连接活动的事务数.但问题是, 为什么当前存在活动的事务的时候就要BEGIN TRANSACTION
而不存在活动事务的时候就不需要 设置一个显式事物的起点呢?有大虾知道这是为什么吗?以下是aspnet_Membership_CreateUser存储过程的定义:CREATE PROCEDURE [dbo].[aspnet_Membership_CreateUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL DECLARE @IsLockedOut bit
SET @IsLockedOut = 0 DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 ) DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0 DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0 DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 ) DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0 DECLARE @ErrorCode int
SET @ErrorCode = 0 DECLARE @TranStarted bit
SET @TranStarted = 0 --就是这里, 鄙人感到很困惑...
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0 EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END SET @CreateDate = @CurrentTimeUtc SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END SET @UserId = @NewUserId IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart ) IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END RETURN 0Cleanup: IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END RETURN @ErrorCodeEND
根据全局变量@@TRANCOUNT ,如果为0,表示外部没有事务,那么本存储过程中的事务可以运行了,BEGIN TRANSACTION
SET @TranStarted = 1
运行后,设置内部变量@TranStarted = 1最后如果跳到Cleanup:时,判断@TranStarted 是否为1,为1则表示事务开始后未结束,
回滚事务。
存储过程本身的SQL语句会创建事务.如果访问量不是很大,一般不会影响.
其实,这段程序根本没必要去判断,直接加一个事务就行了,写成这样,看起来也别扭.
我覺得那段代碼的作用是,當在執行那個存儲過程之前也有其他事務正在進行,沒有結束。
如果執行了完那個存儲過程之後,其他事務進行回滾操作,那麼就會連帶着那個存儲過程的所有修改都會進行回滾操作。看看下面的代碼就會明白了:-- 如果在執行那個存儲過程之前,设置一个显式事物的起点
BEGIN TRANSACTIONDECLARE @return_value int,
@UserId uniqueidentifier,
@CurrentTime datetime
SET @CurrentTime = GETDATE()EXEC @return_value = [dbo].[aspnet_Membership_CreateUser]
@ApplicationName = 'Teaching',
@UserName = 'hang',
@Password = 'asDF12#$',
@PasswordSalt = 'qw#45&!t',
@Email = '[email protected]',
@PasswordQuestion = 'who are you?',
@PasswordAnswer = 'hang',
@IsApproved = 1,
@CurrentTimeUtc = @CurrentTime,
@CreateDate = @CurrentTime,
@UniqueEmail = 0,
@PasswordFormat = 1,
@UserId = @UserId OUTPUTSELECT @UserId as N'@UserId'SELECT 'Return Value' = @return_valueGO-- 執行存儲過程完畢-- 假如執行完那個存儲過程之後執行ROLLBACK操作
-- 那麼之前執行的存儲過程的所有修改都會得到回滾
ROLLBACK TRANSACTION