为何调用$dbh->disconnect会导致DB2自增列产生间隙？

现象复现 #

调用$dbh->disconnect的代码示例：

use DBI;

my $db = 'MYDB2';
my $table = 'SCHEMA.TABLE';
my $user = 'user';
my $pass = 'passwd';
my $dsn = "dbi:DB2:$db";

my $dbh = DBI->connect( $dsn, $user, $pass );
$dbh->do( "DROP TABLE IF EXISTS $table" );
$dbh->do( "CREATE TABLE $table (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, NAME CHAR(3))" );
my $sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'aaa' );
$sth->execute( 'bbb' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;
$sth->finish;
$dbh->disconnect;

$dbh = DBI->connect( $dsn, $user, $pass );
$sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'ccc' );
$sth->execute( 'ddd' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;

输出：

'1', 'aaa'
'2', 'bbb'
2 rows
'1', 'aaa'
'2', 'bbb'
'21', 'ccc'
'22', 'ddd'
4 rows

不调用$dbh->disconnect的代码示例：

use DBI;

my $db = 'MYDB2';
my $table = 'SCHEMA.TABLE';
my $user = 'user';
my $pass = 'passwd';
my $dsn = "dbi:DB2:$db";

my $dbh = DBI->connect( $dsn, $user, $pass );
$dbh->do( "DROP TABLE IF EXISTS $table" );
$dbh->do( "CREATE TABLE $table (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, NAME CHAR(3))" );
my $sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'aaa' );
$sth->execute( 'bbb' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;
$sth->finish;
#$dbh->disconnect;

$dbh = DBI->connect( $dsn, $user, $pass );
$sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'ccc' );
$sth->execute( 'ddd' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;

输出：

'1', 'aaa'
'2', 'bbb'
2 rows
'1', 'aaa'
'2', 'bbb'
'3', 'ccc'
'4', 'ddd'
4 rows

原因解析 #

这是DB2针对IDENTITY自增列的预分配缓存机制导致的：

• DB2为了提升插入性能，会给每个数据库连接预分配一段连续的自增值（默认缓存大小是20），这段值属于当前连接私有。
• 当你调用$dbh->disconnect主动断开连接时，该连接预分配但未使用的自增值（比如示例里的3-20）会直接被丢弃，不会被回收或分配给其他连接。新连接会从下一个缓存段（21-40）开始获取值，因此出现了从2跳到21的间隙。
• 不调用$dbh->disconnect时，DBI内部可能复用了之前的数据库连接（连接池机制），原连接的预分配缓存还在，后续插入会继续使用剩下的预分配值（3-4），所以没有间隙。

补充说明 #

• 若要减少间隙，可以在创建表时调整自增列的缓存大小，例如：

  CREATE TABLE $table (
      ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (CACHE 1) PRIMARY KEY, 
      NAME CHAR(3)
  )
  

  但设置CACHE 1会降低插入性能，因为每次插入都需要向数据库申请新值。
• 需要注意：IDENTITY列的设计目标是保证值的唯一性，而非连续性。即使调整缓存大小，在事务回滚、连接异常断开等场景下，仍可能出现间隙，这是正常现象。

内容的提问来源于stack exchange，提问作者sid_com