身份密钥计数器递增 1，尽管它在 TRY Catch 中并且事务已回滚?SSMS 2008 [英] Identity key counter increment by one although it is in TRY Catch and Transaction is roll-backed ? SSMS 2008

问题描述

身份计数器加一，尽管它在 TRY Catch 中并且事务被回滚?SSMS 2008 有什么办法可以停止 +1 或回滚它.

Identity counter increment by one although it is in TRY Catch and Transaction is roll-backed ? SSMS 2008 is there any way i can stop it +1 or rollback it too.

推荐答案

为了理解为什么会发生这种情况，让我们先执行下面的示例代码-

In order to understand why this happened, Let's execute below sample code first-

USE tempdb

CREATE  TABLE dbo.Sales
(ID INT IDENTITY(1,1), Address VARCHAR(200))
GO

BEGIN TRANSACTION
    INSERT DBO.Sales
            ( Address )
    VALUES  ( 'Dwarka, Delhi' );

ROLLBACK TRANSACTION

现在，上述查询的执行计划是-

Now, Execution plan for above query is-

右起倒数第二个运算符 Compute Scalar 是 [Expr1003]=getidentity((629577281),(2),NULL) 的计算值，即 IDENTITY 值标识栏.所以这清楚地表明 IDENTITY 值被获取 &在插入之前增加(INSERT Operator).所以本质上，即使是在后期创建 IDENTITY 值的事务回滚也是存在的.

The second last operator from right Compute Scalar is computing value for [Expr1003]=getidentity((629577281),(2),NULL) which is IDENTITY value for ID column. So this clearly indicates that IDENTITY values are fetched & Incremented prior to Insertion (INSERT Operator). So its by nature that even transaction rollback at later stage once created IDENTITY value is there.

现在，为了将 IDENTITY 值重新设定为表 + 1 中存在的最大标识值，您需要 sysadmin 权限才能在 DBCC 命令下执行 -

Now, in order to reseed the IDENTITY value to Maximum Identity Value present in table + 1, you need sysadmin permission to execute below DBCC command -

DBCC CHECKIDENT   
 (   
    table_name  
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  
)  
[ WITH NO_INFOMSGS ]

因此最终查询应在回滚语句之前包含以下代码:-

So the final query should include below piece of code prior to rollback statement:-

--  Code to check max ID value, and verify it again IDENTITY SEED
DECLARE @MaxValue INT = (SELECT ISNULL(MAX(ID),1) FROM dbo.Sales)
IF @MaxValue IS NOT NULL AND @MaxValue <> IDENT_CURRENT('dbo.Sales')
    DBCC CHECKIDENT ( 'dbo.Sales', RESEED, @MaxValue ) 

--ROLLBACK TRANSACTION

所以建议保留在 SQL Server 上.

So it is recommended to leave it on SQL Server.

这篇关于身份密钥计数器递增 1，尽管它在 TRY Catch 中并且事务已回滚?SSMS 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！