SQL Server vu par Christian Robert

SQL Server : Bug sur les colonnes auto incrémentées dans SQL Server 2005 et 2008

Bug qui m'a particulièrement surpris, et qui a mon sens est très sévère pour les bases de données. En effet celui-ci affecte la valeur renvoyée par @@IDENTITY et SCOPE_IDENTITY().

Voir ici pour leur rôle : http://blogs.codes-sources.com/christian/archive/2007/02/23/sql-server-champs-compteur-identity-ou-scope-identity.aspx

Le problème survient quand SQL Server utilise un plan d'exécution parallélisé pour une requête d'insertion. Ce cas n'est heureusement pas le plus fréquent, mais si une requête mets à jour une table utilisée par une vue indexées ou insère une grande quantité de données ce cas devient très probable. Dans ce cas la valeur du dernier incrément renvoyé pour une colonne de type IDENTITY est fausse !

Le bug et les scripts permettant de le reproduire : https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=328811

Ci-dessous un script permettant de reproduire le problème :

USE tempdb
GO

CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n
GO

CREATE TABLE alpha (ident int IDENTITY(1, 1) PRIMARY KEY, value int NOT NULL);
CREATE TABLE beta (Number int NOT NULL PRIMARY KEY);

INSERT beta(Number) SELECT n FROM fn_nums(200000);

DECLARE @var int;
SET @var = 4;

INSERT INTO alpha (value)
SELECT Number FROM beta
WHERE Number BETWEEN 1 AND @var

SELECT scope_identity() AS [scope_identity] ,
        @@identity AS [@@identity],
        @@rowcount AS [@@rowcount],
        ident_current('alpha') AS [ident_current]

SELECT * FROM alpha
GO

DROP TABLE alpha
DROP TABLE beta
DROP FUNCTION fn_nums
DROP FUNCTION fn_nums
GO

Dans mon cas j'obtiens :

Ou 256 à la place de NULL suivant les cas.

Ce problème ne se produit que quand un plan parallèle est utilisé par le moteur, donc ne peut se produire sur SQL Server édition Express. Une manière d'éviter ce bug est de limiter le nombre de processeur avec lesquels travaille SQL Server :

INSERT INTO alpha (value)
SELECT Number FROM beta
WHERE Number BETWEEN 1 AND @var
OPTION(MAXDOP 1)

Pour un correctif plus global sur le serveur :

Attention à cette méthode vous risquez d'avoir des requêtes s'executant en lecture qui prendront enormement plus de temps !

A vous de mesurer l'impact du bug sur vos applications, s'il y a un par rapport aux corrections qui pourraient éventuellement apportées pour y palier.

En attendant un correctif…

Bonne lecture…