After the transaction is rolled back, the auto-increment ID still increases

After the rollback, the auto-increment ID still increases.

For example, if the current ID is 7, a data entry is inserted and then rolled back.
Then you insert another piece of data. At this time, the data is successfully inserted. At this time, your ID is not 8, but 9.
Because although you inserted rollback before, the ID is still auto-incrementing.

If you think that the auto-increment ID should not be transcoded, other transactions have to wait and check whether the auto-increment ID is used or rolled back, which leads to blocking. In the following example, table A uses an auto-incremental ID.
User 1
------------
Begin transaction
Insert into...
Insert into B...
Update C...
Insert into D...
Commit

User 2
-----------
Begin transaction
Insert into...
Insert into B...
Commit

Looking at the above example code, if the auto-increment ID is to be transcoded, assume that the transaction of user 2 is executed 1 millisecond after execution of user 1, then he has to wait until the end of the transaction of User 1 and check whether the First Auto-increment ID is used. This causes blocking.

Auto-increment IDs are designed to be non-transactional, not a bug. If you need a closely continuous auto-increment sequence, we recommend that you use other methods to generate it.

-- It doesn't matter if you don't need it. A column is generated when necessary.
If object_id (tb) is not null drop table tb
Go
Create table tb (ID int)
Insert tb select 1
Insert tb select 2
Insert tb select 5
Insert tb select 18
Insert tb select 13
Select id, [newid] = (select count (*) from tb where id <= t. id) from tb t order by [newid]
/* Id newid
----------------------
1 1
2 2
5 3
13 4
18 5

(5 Data columns)
*/