Ao inserir uma linha, existe uma janela de oportunidade entre a geração de um novo valor de identidade e o bloqueio da chave de linha correspondente no índice clusterizado, onde um observador externo pode ver um valor de identidade mais recente inserido por uma transação simultânea?

Sim.

A alocação de valores de identidade é independente da transação do usuário recipiente . Esse é um dos motivos pelos quais os valores de identidade são consumidos mesmo se a transação for revertida. A própria operação de incremento é protegida por uma trava para evitar corrupção, mas essa é a extensão das proteções.

Nas circunstâncias específicas de sua implementação, a alocação de identidade (uma chamada para CMEDSeqGen::GenerateNewValue) é feita antes que a transação do usuário para a inserção seja ativada (e, portanto, antes que qualquer bloqueio seja feito).

Ao executar duas inserções simultaneamente com um depurador anexado para permitir o congelamento de um thread logo após o valor da identidade ser incrementado e alocado, consegui reproduzir um cenário em que:

1. A sessão 1 adquire um valor de identidade (3)
2. A sessão 2 adquire um valor de identidade (4)
3. A sessão 2 executa sua inserção e confirmação (portanto, a linha 4 fica totalmente visível)
4. A sessão 1 executa sua inserção e confirmação (linha 3)

Após a etapa 3, uma consulta usando row_number sob bloqueio de leitura confirmada retornou o seguinte:

Em sua implementação, isso resultaria no fato de o Checkpoint ID 3 ser ignorado incorretamente.

A janela de má oportunidade é relativamente pequena, mas existe. Para fornecer um cenário mais realista do que ter um depurador anexado: Um thread de consulta em execução pode gerar o agendador após a etapa 1 acima. Isso permite que um segundo thread aloque um valor de identidade, insira e confirme, antes que o thread original continue a executar sua inserção.

Para maior clareza, não há bloqueios ou outros objetos de sincronização protegendo o valor de identidade depois que ele é alocado e antes de ser usado. Por exemplo, após a etapa 1 acima, uma transação simultânea pode ver o novo valor de identidade usando funções T-SQL como IDENT_CURRENTantes de a linha existir na tabela (mesmo não confirmada).

Fundamentalmente, não há mais garantias em torno dos valores de identidade do que documentado :

• Cada novo valor é gerado com base na semente e incremento atuais.
• Cada novo valor para uma determinada transação é diferente de outras transações simultâneas na tabela.

Realmente é isso.

Se o processamento FIFO transacional estrito for necessário, você provavelmente não terá escolha a não ser serializar manualmente. Se o aplicativo tiver requisitos menos onerosos, você terá mais opções. A questão não está 100% clara a esse respeito. No entanto, você pode encontrar algumas informações úteis no artigo de Remus Rusanu Using Tables as Queues .

Como Paul White respondeu absolutamente correto, existe a possibilidade de linhas de identidade temporariamente "puladas". Aqui está apenas um pequeno pedaço de código para reproduzir este caso por conta própria.

Crie um banco de dados e uma tabela de teste:

create database IdentityTest
go
use IdentityTest
go
create table dbo.IdentityTest (ID int identity, c1 char(10))
create clustered index CI_dbo_IdentityTest_ID on dbo.IdentityTest(ID)

Execute inserções e seleções simultâneas nesta tabela em um programa de console C#:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading;

namespace IdentityTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var insertThreads = new List<Thread>();
            var selectThreads = new List<Thread>();

            //start threads for infinite inserts
            for (var i = 0; i < 100; i++)
            {
                insertThreads.Add(new Thread(InfiniteInsert));
                insertThreads[i].Start();
            }

            //start threads for infinite selects
            for (var i = 0; i < 10; i++)
            {
                selectThreads.Add(new Thread(InfiniteSelectAndCheck));
                selectThreads[i].Start();
            }
        }

        private static void InfiniteSelectAndCheck()
        {
            //infinite loop
            while (true)
            {
                //read top 2 IDs
                var cmd = new SqlCommand("select top(2) ID from dbo.IdentityTest order by ID desc")
                {
                    Connection = new SqlConnection("Server=localhost;Database=IdentityTest;Integrated Security=SSPI;Application Name=IdentityTest")
                };

                try
                {
                    cmd.Connection.Open();
                    var dr = cmd.ExecuteReader();

                    //read first row
                    dr.Read();
                    var row1 = int.Parse(dr["ID"].ToString());

                    //read second row
                    dr.Read();
                    var row2 = int.Parse(dr["ID"].ToString());

                    //write line if row1 and row are not consecutive
                    if (row1 - 1 != row2)
                    {
                        Console.WriteLine("row1=" + row1 + ", row2=" + row2);
                    }
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }

        private static void InfiniteInsert()
        {
            //infinite loop
            while (true)
            {
                var cmd = new SqlCommand("insert into dbo.IdentityTest (c1) values('a')")
                {
                    Connection = new SqlConnection("Server=localhost;Database=IdentityTest;Integrated Security=SSPI;Application Name=IdentityTest")
                };

                try
                {
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }
        }
    }
}

Este console imprime uma linha para cada caso quando um dos threads de leitura "perde" uma entrada.

É melhor não esperar que as identidades sejam consecutivas porque há muitos cenários que podem deixar lacunas. É melhor considerar a identidade como um número abstrato e não atribuir nenhum significado comercial a ela.

Basicamente, lacunas podem ocorrer se você reverter operações INSERT (ou excluir linhas explicitamente) e duplicatas podem ocorrer se você definir a propriedade da tabela IDENTITY_INSERT como ON.

As lacunas podem ocorrer quando:

1. Os registros são excluídos.
2. Ocorreu um erro ao tentar inserir um novo registro (revertido)
3. Uma atualização/inserção com valor explícito (opção identity_insert).
4. O valor incremental é maior que 1.
5. Uma transação reverte.

A propriedade de identidade em uma coluna nunca foi garantida:

• Singularidade

• Valores consecutivos dentro de uma transação. Se os valores devem ser consecutivos, a transação deve usar um bloqueio exclusivo na tabela ou usar o nível de isolamento SERIALIZABLE.

• Valores consecutivos após a reinicialização do servidor.

• Reaproveitamento de valores.

Se você não puder usar valores de identidade por causa disso, crie uma tabela separada contendo um valor atual e gerencie o acesso à tabela e a atribuição de número com seu aplicativo. Isso tem o potencial de afetar o desempenho.

https://msdn.microsoft.com/en-us/library/ms186775(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms186775(v=sql.110) .aspx

Eu suspeito que ocasionalmente pode levar a problemas, problemas que pioram quando o servidor está sob carga pesada. Considere duas transações:

1. T1: insira em T ... -- digamos 5 obtenha inserido
2. T2: insira em T ... -- digamos 6 obtenha inserido
3. T2: comprometer
4. O leitor vê 6, mas não 5
5. T1: confirmar

No cenário acima, seu LAST_READ_ID será 6, então 5 nunca será lido.

Executando este script:

BEGIN TRAN;
INSERT INTO dbo.Example DEFAULT VALUES;
COMMIT;

Abaixo estão os bloqueios que vejo adquiridos e liberados conforme capturados por uma sessão de Evento Estendido:

name            timestamp                   associated_object_id    mode    object_id   resource_type   session_id  resource_description
lock_acquired   2016-03-29 06:37:28.9968693 1585440722              IX      1585440722  OBJECT          51          
lock_acquired   2016-03-29 06:37:28.9969268 7205759890195415040     IX      0           PAGE            51          1:1235
lock_acquired   2016-03-29 06:37:28.9969306 7205759890195415040     RI_NL   0           KEY             51          (ffffffffffff)
lock_acquired   2016-03-29 06:37:28.9969330 7205759890195415040     X       0           KEY             51          (29cf3326f583)
lock_released   2016-03-29 06:37:28.9969579 7205759890195415040     X       0           KEY             51          (29cf3326f583)
lock_released   2016-03-29 06:37:28.9969598 7205759890195415040     IX      0           PAGE            51          1:1235
lock_released   2016-03-29 06:37:28.9969607 1585440722              IX      1585440722  OBJECT          51      

Observe o bloqueio RI_N KEY adquirido imediatamente antes do bloqueio da tecla X para a nova linha que está sendo criada. Esse bloqueio de intervalo de curta duração impedirá que uma inserção simultânea adquira outro bloqueio RI_N KEY, pois os bloqueios RI_N são incompatíveis. A janela que você mencionou entre as etapas 2 e 3 não é uma preocupação porque o bloqueio de intervalo é adquirido antes do bloqueio de linha na chave recém-gerada.

Contanto que você SELECT...ORDER BYcomece a verificação antes das linhas recém-inseridas desejadas, eu esperaria o comportamento que você deseja no READ COMMITTEDnível de isolamento padrão, desde que a READ_COMMITTED_SNAPSHOTopção de banco de dados esteja desativada.

Pelo que entendi do SQL Server, o comportamento padrão é que a segunda consulta não exiba nenhum resultado até que a primeira consulta seja confirmada. Se a primeira consulta fizer um ROLLBACK em vez de um COMMIT, você terá um ID ausente em sua coluna.

Configuração básica

Tabela de banco de dados

Criei uma tabela de banco de dados com a seguinte estrutura:

CREATE TABLE identity_rc_test (
    ID4VALUE INT IDENTITY (1,1), 
    TEXTVALUE NVARCHAR(20),
    CONSTRAINT PK_ID4_VALUE_CLUSTERED 
        PRIMARY KEY CLUSTERED (ID4VALUE, TEXTVALUE)
)

Nível de isolamento do banco de dados

Eu verifiquei o nível de isolamento do meu banco de dados com a seguinte declaração:

SELECT snapshot_isolation_state, 
       snapshot_isolation_state_desc, 
       is_read_committed_snapshot_on
FROM sys.databases WHERE NAME = 'mydatabase'

Que retornou o seguinte resultado para meu banco de dados:

snapshot_isolation_state    snapshot_isolation_state_desc   is_read_committed_snapshot_on
0                           OFF                             0

(Esta é a configuração padrão para um banco de dados no SQL Server 2012)

Scripts de teste

Os scripts a seguir foram executados usando as configurações padrão do cliente SQL Server SSMS e as configurações padrão do SQL Server.

Configurações de conexões do cliente

O cliente foi configurado para usar o nível READ COMMITTEDde isolamento de transação de acordo com as opções de consulta no SSMS.

Consulta 1

A consulta a seguir foi executada em uma janela Consulta com o SPID 57

SELECT * FROM dbo.identity_rc_test
BEGIN TRANSACTION [FIRST_QUERY]
INSERT INTO dbo.identity_rc_test (TEXTVALUE) VALUES ('Nine')
/* Commit is commented out to prevent the INSERT from being commited
--COMMIT TRANSACTION [FIRST_QUERY]
--ROLLBACK TRANSACTION [FIRST_QUERY]
*/

Consulta 2

A consulta a seguir foi executada em uma janela de consulta com o SPID 58

BEGIN TRANSACTION [SECOND_QUERY]
INSERT INTO dbo.identity_rc_test (TEXTVALUE) VALUES ('Ten')
COMMIT TRANSACTION [SECOND_QUERY]
SELECT * FROM dbo.identity_rc_test

A consulta não conclui e aguarda a liberação do bloqueio eXclusivo em uma PÁGINA.

Script para determinar o bloqueio

Este script exibe o bloqueio que ocorre nos objetos do banco de dados para as duas transações:

SELECT request_session_id, resource_type,
       resource_description, 
       resource_associated_entity_id,
       request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_session_id IN (57, 58)

E aqui estão os resultados:

58  DATABASE                    0                   S   GRANT
57  DATABASE                    0                   S   GRANT
58  PAGE            1:79        72057594040549300   IS  GRANT
57  PAGE            1:79        72057594040549300   IX  GRANT
57  KEY         (a0aba7857f1b)  72057594040549300   X   GRANT
58  KEY         (a0aba7857f1b)  72057594040549300   S   WAIT
58  OBJECT                      245575913           IS  GRANT
57  OBJECT                      245575913           IX  GRANT

Os resultados mostram que a janela de consulta um (SPID 57) possui um bloqueio Compartilhado (S) no BANCO DE DADOS, um bloqueio Intencionado eXlusivo (IX) no OBJETO, um bloqueio Intencionado eXlusivo (IX) na PÁGINA que deseja inserir e um bloqueio eXclusivo bloqueio (X) na CHAVE que foi inserido, mas ainda não confirmado.

Por causa dos dados não confirmados, a segunda consulta (SPID 58) tem um bloqueio compartilhado (S) no nível DATABASE, um bloqueio compartilhado intencional (IS) no OBJECT, um bloqueio compartilhado compartilhado (IS) na página a compartilhado (S ) bloqueie a CHAVE com um status de solicitação WAIT.

Resumo

A consulta na primeira janela de consulta é executada sem confirmação. Como a segunda consulta só pode fornecer READ COMMITTEDdados, ela espera até que o tempo limite ocorra ou até que a transação seja confirmada na primeira consulta.

Isso é do meu entendimento do comportamento padrão do Microsoft SQL Server.

Você deve observar que o ID está realmente em sequência para leituras subsequentes por instruções SELECT se a primeira instrução for COMMIT.

Se a primeira instrução fizer um ROLLBACK, você encontrará um ID ausente na sequência, mas ainda com o ID em ordem crescente (desde que você tenha criado o INDEX com a opção padrão ou ASC na coluna ID).

Atualizar:

(Sem rodeios) Sim, você pode confiar que a coluna de identidade está funcionando corretamente, até encontrar um problema. Existe apenas um HOTFIX em relação ao SQL Server 2000 e à coluna de identidade no site da Microsoft.

Se você não pudesse confiar na atualização correta da coluna de identidade, acho que haveria mais hotfixes ou patches no site da Microsoft.

Se você tiver um contrato de suporte da Microsoft, sempre poderá abrir um caso consultivo e solicitar informações adicionais.