事务回滚锁表有关问题

事务回滚锁表问题

SQL code

create proc usp_ChangeHander                    @UserGUID VARCHAR(40),             @EventGUID VARCHAR(40),             @WorkL money,             @Ru VARCHAR(40), --更改的人           @remarks VARCHAR(4000)                      AS             DECLARE @count int               DECLARE @strDepartment VARCHAR(40)             DECLARE @strUserName VARCHAR(10)             DECLARE @strGW VARCHAR(20)       DECLARE @strDepartmentSelf VARCHAR(40)             DECLARE @strUserNameSelf VARCHAR(10)             DECLARE @strGWSelf VARCHAR(20)                       DECLARE @date VARCHAR(24)              begin               SET @date = getdate()             SET @count =0              BEGIN TRANSACTION              --SET @count = 0                        --SELECT  @strUserName =UserName, @strDepartment=CONVERT(varchar(40),DepartmentGUID),@strGW =JobTitle FROM myuser WHERE USERGUID = @UserGUID              SELECT   @strUserName =UserName,@strDepartment=RoleOrgName,@strGW =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @UserGUID  --接受的人                  SELECT   @strUserNameSelf =UserName,@strDepartmentSelf=RoleOrgName,@strGWSelf =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @Ru --更改的人                         BEGIN                  ---插入数据到处理过程                INSERT INTO Itsm_ProcessReport (ProcessGUID,EventGUID,ProcessTime,ProcessContent,Hander,Receiver,Remarks) VALUES (newid(),@EventGUID,@date,'更改处理人',@strUserNameSelf,@strUserName,@remarks)                  SET @count = @count + @@ERROR               END               BEGIN                  ---插入数据到工作量表                  INSERT INTO Itsm_WorkLoad (WorkLoadGUID,EventGUID,UserName,Department,WorkQuatity,WorkGw,IsZf,Remarks) VALUES (newid(),@EventGUID,@strUserNameSelf,@strDepartmentSelf,@WorkL,@strGWSelf,0,'更改处理人')                  SET @count = @count + @@ERROR               END               BEGIN                  ---更新事件单信息                UPDATE Itsm_EventInfo SET NowHandlerGUID=@UserGUID WHERE EventGUID=@EventGUID                SET @count = @count + @@ERROR               END                 print @count              IF @count<>0               begin                ROLLBACK TRANSACTION                SELECT 'FAILED'               end              ELSE               begin                COMMIT TRANSACTION                SELECT 'SUCCESS'               end               end   

我想问下，我的这个事务如果第一个insert 出错， 就会报 事务无 回滚 或者 提交，然后就把表锁死了。。。
难道不能这样写？

[解决办法]
将 BEGIN TRANSACTION 更改为SET XACT_ABORT ON
[解决办法]
try this,

SQL code

create proc usp_ChangeHander                    @UserGUID VARCHAR(40),             @EventGUID VARCHAR(40),             @WorkL money,             @Ru VARCHAR(40), --更改的人           @remarks VARCHAR(4000)                      AS             DECLARE @count int               DECLARE @strDepartment VARCHAR(40)             DECLARE @strUserName VARCHAR(10)             DECLARE @strGW VARCHAR(20)       DECLARE @strDepartmentSelf VARCHAR(40)             DECLARE @strUserNameSelf VARCHAR(10)             DECLARE @strGWSelf VARCHAR(20)                       DECLARE @date VARCHAR(24)              begin               SET @date = getdate()             SET @count =0 set xact_abort on              BEGIN TRANSACTION              --SET @count = 0                        --SELECT  @strUserName =UserName, @strDepartment=CONVERT(varchar(40),DepartmentGUID),@strGW =JobTitle FROM myuser WHERE USERGUID = @UserGUID              SELECT   @strUserName =UserName,@strDepartment=RoleOrgName,@strGW =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @UserGUID  --接受的人                  SELECT   @strUserNameSelf =UserName,@strDepartmentSelf=RoleOrgName,@strGWSelf =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @Ru --更改的人                         BEGIN                  ---插入数据到处理过程                INSERT INTO Itsm_ProcessReport (ProcessGUID,EventGUID,ProcessTime,ProcessContent,Hander,Receiver,Remarks) VALUES (newid(),@EventGUID,@date,'更改处理人',@strUserNameSelf,@strUserName,@remarks)                  SET @count = @count + @@ERROR               END               BEGIN                  ---插入数据到工作量表                  INSERT INTO Itsm_WorkLoad (WorkLoadGUID,EventGUID,UserName,Department,WorkQuatity,WorkGw,IsZf,Remarks) VALUES (newid(),@EventGUID,@strUserNameSelf,@strDepartmentSelf,@WorkL,@strGWSelf,0,'更改处理人')                  SET @count = @count + @@ERROR               END               BEGIN                  ---更新事件单信息                UPDATE Itsm_EventInfo SET NowHandlerGUID=@UserGUID WHERE EventGUID=@EventGUID                SET @count = @count + @@ERROR               END                 print @count              IF @count<>0               begin                ROLLBACK TRANSACTION                SELECT 'FAILED'               end              ELSE               begin                COMMIT TRANSACTION                SELECT 'SUCCESS'               end               end 
 
[解决办法]
每个语句处理下，跳转一下就可以了
SQL code
create proc usp_ChangeHander                    @UserGUID VARCHAR(40),             @EventGUID VARCHAR(40),             @WorkL money,             @Ru VARCHAR(40), --更改的人           @remarks VARCHAR(4000)                      AS             DECLARE @count int               DECLARE @strDepartment VARCHAR(40)             DECLARE @strUserName VARCHAR(10)             DECLARE @strGW VARCHAR(20)       DECLARE @strDepartmentSelf VARCHAR(40)             DECLARE @strUserNameSelf VARCHAR(10)             DECLARE @strGWSelf VARCHAR(20)                       DECLARE @date VARCHAR(24)              begin               SET @date = getdate()             SET @count =0 set xact_abort on              BEGIN TRANSACTION              --SET @count = 0                        --SELECT  @strUserName =UserName, @strDepartment=CONVERT(varchar(40),DepartmentGUID),@strGW =JobTitle FROM myuser WHERE USERGUID = @UserGUID              SELECT   @strUserName =UserName,@strDepartment=RoleOrgName,@strGW =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @UserGUID  --接受的人                  SELECT   @strUserNameSelf =UserName,@strDepartmentSelf=RoleOrgName,@strGWSelf =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @Ru --更改的人                         BEGIN                  ---插入数据到处理过程                INSERT INTO Itsm_ProcessReport (ProcessGUID,EventGUID,ProcessTime,ProcessContent,Hander,Receiver,Remarks) VALUES (newid(),@EventGUID,@date,'更改处理人',@strUserNameSelf,@strUserName,@remarks)                  SET @count = @@ERROR       IF @count <> 0      BEGIN        SELECT '插入数据到处理过程失败！'        GOTO ERRORHANDLE      END              END               BEGIN                  ---插入数据到工作量表                  INSERT INTO Itsm_WorkLoad (WorkLoadGUID,EventGUID,UserName,Department,WorkQuatity,WorkGw,IsZf,Remarks) VALUES (newid(),@EventGUID,@strUserNameSelf,@strDepartmentSelf,@WorkL,@strGWSelf,0,'更改处理人')                  SET @count = @@ERROR       IF @count <> 0      BEGIN        SELECT '插入数据到工作表失败！'        GOTO ERRORHANDLE      END               END               BEGIN                  ---更新事件单信息                UPDATE Itsm_EventInfo SET NowHandlerGUID=@UserGUID WHERE EventGUID=@EventGUID                SET @count = @@ERROR    IF @count <> 0    BEGIN        SELECT '更新事件单信息失败！'        GOTO ERRORHANDLE    END                END                 print @count             COMMIT TRANSACTIONSELECT 'SUCCESSED'RETURN 100ERRORHANDLE:ROLLBACK TRANSACTIONSELECT 'FAILED'RETURN -100 

 

查看更多 下一篇