Níveis de isolamento, Concorrência e análise de Locks com Spring @Transactional

Hoje, irei demonstrar a diferença prática entre os diferentes níveis de isolamento padrão, como eles afetam os tipos de lock gerados no banco de dados, e como escrever testes unitários que garantem o comportamento desejado de um método @Transactional específico. Também iremos explorar a influência de ter um índice criado para as buscas.

Vamos analisar 2 casos de estudo:

• Selecionar uma linha existente e atualizá-la mais tarde na mesma transação, baseado numa condição específica
• Selecionar uma linha, e inserir um novo registro caso a linha selecionada ainda não exista.

Para ambos os casos, usaremos a mesma tabela, que contém apenas 3 colunas. Um Id auto-gerado, um UUID, e um status numérico que representa o estado do registro.

Setup do Banco de Dados

Faremos os testes utilizando o banco de dados SQL Server. A criação da tabela tem o seguinte script DDL:

CREATE TABLE control_table (
 id int IDENTITY(1,1) NOT NULL,
 uuid varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 status int NOT NULL,
 CONSTRAINT PK__control___3213E83F4F29C19D PRIMARY KEY (id)
);

A tabela foi inicialmente populada com apenas 100 registros. Mais adiante, iremos inserir mais registro na tabela para mostrar o efeito de ter um índice no mecanismo de locks.

DECLARE @i INT = 1;
WHILE @i <= 100
BEGIN
    INSERT INTO control_table (uuid, status)
    VALUES (NEWID(), (ABS(CHECKSUM(NEWID())) % 5) + 1);
    SET @i = @i + 1;
END

Queries de Análise

Utilizaremos as seguintes queries para checar as tabelas de sistema do SQL Server e ver informações sobre os locks gerados e as sessões existentes. A query abaixo mostra informações sobre os locks existentes nas transações ativas. Por simplicidade, chamaremos esta tabela daqui em diante de “tabela de locks”.

SELECT 
 tl.resource_type, 
 tl.request_session_id,
 tl.resource_description, 
 tl.resource_associated_entity_id, 
 tl.request_mode, 
 tl.request_type, 
 tl.request_status,
 c.connection_id
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id;

A próxima query mostra informações sobre as sessões ativas, mostrando qual sessão bloqueia qual. Por simplicidade, chamaremos esta tabela de “tabela de sessões”.

SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.last_wait_type,
    r.status,
    r.command,
    t.resource_type,
    t.resource_description,
    t.request_mode,
    t.request_status
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_tran_locks t
    ON r.session_id = t.request_session_id
WHERE r.blocking_session_id <> 0
ORDER BY r.session_id;veling session.

As próximas duas sessões servem como nivelamento de conhecimento. Se você já é familiarizado com os termos apresentados, pode pular para a parte prática dos testes.

Níveis de Isolamento

• READ UNCOMMITTED: Leituras sujas são permitidas, ou seja, uma transação pode ver alterações ainda não comitadas por outras transações.
• READ COMMITTED: Proibe leitura suja. O banco de dados mantém locks de escrita em dados selecionados até o final da transação, mas locks de leitura duram somente durante a operação de leitura. Esse é o nível de isolamento padrão do SQL Server.
• REPEATABLE READ: O banco de dados mantém locks de leitura e escrita nos dados selecionados até o final da transação. Isso previne outras transações de modificarem dados selecionados, evitando leituras não repetíveis.
• SERIALIZABLE: O banco de dados mantém locks de leitura e escrita nos dados selecionados até o final da transação. Range-locks são adquiridos quando comandos SELECT utilizam cláusulas WHERE, evitando leituras fantasma. Isso previne outras transações de alterarem o result set, porque são gerados locks em cada chave que poderia dar match no predicado da consulta, evitando leituras fantasma.

Tipos de Lock do SQL Server

Iremos focar nos tipos de lock mais comuns que irão aparecer neste artigo.

• Shared (S): permite que transações leiam um recurso concorrentemente. Nenhuma outra transação pode modificar os dados enquanto os shared locks existirem no recurso. Shared locks são soltos em um recurso assim que a operação de leitura termina, exceto quando o nível de isolamento é REPEATABLE READ ou superior, ou uma hint é usada para reter o bloqueio até o final da duração da transação.
• Update (U): O banco de dados coloca bloqueios de update quando ele prepara para executar um update. U locks são compatíveis com S locks, mas apenas uma transação pode reter um lock U em um determinado recurso em um determinado momento. Esse é um fator chave, muitas transações concorrentes podem reter S locks, mas apenas uma pode reter um lock U em um recurso. Bloqueios de update são eventualmente evoluídos para bloqueios exclusivos para atualizar uma linha.
• Exclusive (X): Bloqueios exclusivos previnem o acesso ao recurso por transações concorrentes. Com um lock exclusivo, nenhuma outra transação podem modificar o recurso nem ler, a não ser que estejam no nível de isolamento READ UNCOMMITED ou utilizando a hint NOLOCK.
• Intent (I): São adquiridos antes de adquirir um lock em um nível mais baixo da hierarquia, portanto, sinalizam uma intenção de colocar um locks em níveis mais baixos da hierarquia.
• Key-Range Locks (Range): Protegem um range de linhas implicitamente incluídos no resultado de um comando SELECT quando é utilizado o nível de isolamento SERIALIZABLE. Previne leituras fantasmas através da proteção de ranges de chaves entre as linhas, e também previne inserções e deleções fantasmas.

Nessa sessão, vamos explorar os efeitos de mudar os níveis de isolamento para 2 casos de estudo.

Cenario 1: selecionar para atualizar

Iremos analisar os possíveis resultados de ter 2 threads chamando o método update() concorrentemente. O método primeiro seleciona um registro baseado no UUID, e então atualiza o registro apenas se o status que está chegando for maior que o status atual na tabela.

Objetivo: nosso objetivo é garantir que terminaremos com status 3 na tabela. Se a thread STATUS2 chegar primeiro, então o resultado esperado seria a evolução 1 → 2 → 3. Se a thread STATUS3 chegar primeiro, então seria apenas 1 → 3, sem regredir de 3 → 2.

@Transactional(isolation = Isolation.READ_COMMITED)
public void update(StatusDomain statusDomain) {
    log.info("Thread {} entered update method", currentThread().getName());

    StatusDomain current = repository.findByUuid(statusDomain.getUuid());

    log.info("Thread {} selected current status {}", currentThread().getName(), current.getStatus());

    ThreadUtils.sleep(3000);

    if (statusDomain.getStatus() > current.getStatus()) {
        repository.update(statusDomain);
        log.info("Thread {} updated from current status {} to new status {}",
                currentThread().getName(), current.getStatus(), statusDomain.getStatus());
    }

    log.info("Thread {} exited update method", currentThread().getName());
}

Dentro do método update(), há um sleep de 3 segundos após o SELECT e antes do UPDATE, para que ambas as threads possam performar o SELECT antes de uma delas performar o UPDATE. Os logs serão úteis para mostrar os valores selecionados e a ordem dos eventos.

Os testes de integração terão o seguinte formato:

@SpringBootTest
class StatusServiceTest {

    @Autowired
    public StatusService service;

    @MockitoSpyBean
    public StatusRepository repository;

    private final Integer BEFORE_EACH_UPDATE = 1;

    private final StatusDomain status1 = new StatusDomain(1L, "DBFB79A1-5E78-4EA8-9FD1-2B2F4217DACC", 1);
    private final StatusDomain status2 = new StatusDomain(1L, "DBFB79A1-5E78-4EA8-9FD1-2B2F4217DACC", 2);
    private final StatusDomain status3 = new StatusDomain(1L, "DBFB79A1-5E78-4EA8-9FD1-2B2F4217DACC", 3);

    @BeforeEach
    void setUp() {
        repository.update(status1);
    }

    @Test
    void testUpdate() {
        assertDoesNotThrow(() -> {
            Thread t1 = new Thread(() ->                service.update(status2),   "STATUS2");
            Thread t2 = new Thread(() -> { sleep(1000); service.update(status3);}, "STATUS3");
            t1.start(); t2.start();
            t1.join(); t2.join();
        });

        Mockito.verify(repository, times(2)).findByUuid(anyString());
        Mockito.verify(repository, times(2 + BEFORE_EACH_UPDATE)).update(any());
    }
}

O registro com UUID “DBFB79A1–5E78–4EA8–9FD1–2B2F4217DACC” será atualizado para status 1 antes de cada teste. Duas threads serão iniciadas ao mesmo tempo, mas a thread 2 terá um delay de 1 segundo. O único propósito deste delay é garantir qual thread entrará primeiro no método.

A primeira thread será chamada “STATUS2” e visa atualizar o registro para status 2, enquanto a segunda thread será chamada “STATUS3” e visa atualizar o registro para status 3.

A classe StatusRepository é anotada com @MockitoSpyBean para que possamos testar a quantidade de vezes que cada método foi acionado.

READ COMMITTED

O nível de isolamento READ_COMMITTED previne leitura suja, não permitindo a seleção de dados não confirmados. Se uma transação modificar um registro, mas não confirmar, os outros leitores não enxergarão o novo valor até que ele seja confirmado ou cancelado.

Executando este teste temos o seguinte output:

Execução do método update com nível de isolamento READ_COMMITTED.

Podemos ver que neste caso, ambas as threads entram no método e selecionam o registro com status 1. Do ponto de vista de cada thread, ambos são updates validos, já que 2 > 1 and 3 > 1. Ambas as threads performam o update e saem do método com sucesso. Num cenário real, isso significa que se ocorrer estes dois updates para status 2 e 3 concorrentemente, há uma chance de que a tabela termina com status 2 ao invés de 3, mesmo com a condição de não permitir atualização que diminuam o status, pois ambas as threads leram 1 como o status atual.

Se analisarmos os locks gerados no banco de dados ao longo do método, após SELECT não são encontrados locks, pois os locks Shared duram apenas durante o momento da leitura, e não se mantém após isso. Se colocarmos um break point após o UPDATE, mas antes de sair do método, capturamos os seguintes locks:

Locks gerados após atualizar um registro.

Isso mostra que quando um UPDATE é executado, o registro atualizado recebe um lock exclusivo (X). Então, após soltar o break point na segunda thread, mas ainda antes de confirmar a transação da primeira thread, vemos o seguinte:

Locks gerados após 2 transações tentarem atualizar a mesma linha.

A segunda thread (session_id = 61) gera 3 locks, e o lock para o recurso do tipo KEY fica com status “WAIT”, o que significa que essa thread precisa esperar até que a primeira finalize a transação. Isso também é visível na tabela de sessões:

Tabela de sessões com o UPDATE da segunda thread suspenso pela primeira thread.

Essa visão claramente demonstra que a segnuda thread (session_id = 61) está bloqueada pela primeira (session_id = 52), e que o comando bloqueado é um UPDATE. Após soltar o break point na primeira thread, o update é confirmado, permitindo a segunda thread a fazer o mesmo. Ambos os updates são executados sem exceções. Esse nível de isolamento não é suficiente para garantir que terminaremos com status 3, pois num cenário de concorrência, o update para status 2 pode ocorrer depois do update para status 3.

REPEATABLE READ

Agora, vejamos o que acontece quando usamos o isolamento REPEATABLE_READ. Como o nome sugere, ele previne leituras não repetíveis, significando que o resultado de um SELECT deve ser consistente dentro da mesma transação.

Vamos ver o resultado da execução do método com este nível de isolamento:

Execução do update com isolamento REPEATABLE_READ. Stack trace omitida.

Esse caso é interessante. Podemos ver que ambas as thread podem entrar no método e executar o SELECT. Então, a thread “STATUS3” recebeu uma SQLServerException com a mensagem: “Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

Vamos entender porque isso acontece, olhando mais de perto os locks gerados após o SELECT:

Locks gerados para ambas as transações após o SELECT com isolamento REPEATABLE_READ.

Diferente do READ_COMMITTED, o isolamento REPEATABLE_READ mantém os locks Shared gerados após executar o SELECT. Então, após soltar o break point na primeira thread (STATUS2), o update não é executado. A tabela de locks mostra que os shared locks são convertidos para update locks (U) e exclusive locks (X):

Generated locks after running the UPDATE statement, but before committing the transaction.

Repate que a coluna “request_status” no lock X está com valor CONVERT. Isso significa que a sessão já tem um lock em um modo (S), mas está requisitando um upgrade para outro modo. Se outra sessão impedir essa conversão (outra sessão estiver segurando locks S no mesmo recurso), o request permanece em CONVERT até que ele consiga realizar a conversão ou exceda o tempo limite.

Nesse momento, a tabela de sessões mostra o seguinte:

Tabela de sessões após uma thread tentar realizar o UPDATE depois de outra thread realizar o SELECT.

Podemos ver que a primeira thread (session_id = 52) está bloqueada por conta do SELECT da segunda thread (session_id = 61), suspendendo o comando de UPDATE, poirque a segunda sessão possui um shared (S) lock no mesmo recurso, impedindo a primeira sessão de converter seu lock para exclusive (X).

Isso significa que após ambas as transações adquirirem shared locks nos seus SELECTs, esses registros não podem ser atualizados até que as transações que detém os locks sejam finalizadas, pois caso contrário o nível de isolamento REPEATABLE_REDA não seria satisfeito.

Com a thread STATUS2 bloqueada aguardando recursos, podemos soltar o break point na thread STATUS3, que também ficará bloqueada, pelo mesmo motivo, causando um deadlock no banco de dados. Esse deadlock é identificado pelo banco de dados e uma das transações é morta aleatoriamente, gerando a exceção.

Se nós fizermos o SELECT na tabela de locks durante a execução deste teste, podemos capturar o momento que o deadlock ocorrer, logo antes do banco de dados matar uma das transações:

Locks gerados após 2 transações tentarem atualizar o mesmo registro.

Por um breve momento, podemos ver ambas as sessões tentando obter o lock exclusive na mesma chave. A tabela de sessões também evidencia a situação do deadlock:

Tabela de sessões mostrando o deadlock ocorrendo.

Podemos ver ambas as sessões bloqueadas uma pela outra, com seus comandos UPDATE correspondentes suspensos. Como vimos nos logs de execução do método, após uma das transações ser morta pelo banco de dados, a que resta procede para atualizar o registro com sucesso.

Em um cenário real, isso significa que se o método update processar concorrentemente estes status, um deadlock pode ocorrer, e apenas uma das atualizações terá sucesso, que pode não ser a correta, então novamente, este nível de isolamento não é suficiente para atingir o objetivo, pois não temos controle de qual transação sobreviveria.

SERIALIZABLE

Agora, o nível de isolamento mais restritivo, previne as anomalias acima e também leituras fantasma, que significa que o resultado de um SELECT não pode ser alterado. A exexcução do método update com este nível de isolamento é conforme:

Execução do método update com isolamento SERIALIZABLE . Stack trace omitida.

Novamente, o deadlock ocorre, e é resolvido automaticamente pelo banco de dados. A mensagem é a mesma de antes. Olhando mais de perto os locks gerados após o SELECT, vemos o seguinte:

Locks gerados para ambas as transações após o SELECT no isolamento SERIALIZABLE.

Centenas de locks RangeS-S são gerados. Para facilitar, iremos analisar os locks usando queries que agregam os resultados, para ver quantos locks de cada tipo são gerados para cada sessão e cada recurso, sem selecionar o id do recurso bloqueado em si (coluna resource_description).

Locks gerados para ambas as transações após o SELECT no isolamento SERIALIZABLE, agregadas.

Ao total, 101 locks do tipo RangeS-S são criados para cada sessão, basicamente gerando um lock para cada linha na tabela (lembrando que a tabela tem 100 registros nesse momento).

Soltando o break point após o SELECT na thread STATUS2, e pausando após o UPDATE mas antes de sair do método, alguns dos locks são promovidos:

Locks gerados para UPDATE e SELECT no isolamento SERIALIZABLE.

Um novo lock RangeS-U é gerado para o UPDATE. Além disso, um novo recurso KEY aparece com um X lock, com o mesmo status CONVERT visto na análise do REPEATABLE_READ. A diferença aqui é que a tabela inteira recebe locks RangeS-S, ao invés de apenas a linha selecionada.

Você consegue adivinhar porque isso ocorre? Lembrando, o lock RangeX-Y signifca um lock X em um range, e um lock Y em um recurso. Já que o SERIALIZABLE precisa garantir que não ocorra leitura fantasma, e nós não criamos nenhum índice para a coluna filtrada (UUID), o SQL Server não consegue predizer qual range dos dados precisa ser protegido, então a estratégia é proteger todos os possíveis pontos de inserção na tabela.

Inspecionando a tabela de sessões, podemos ver o mesmo comportamento do REPEATABLE_READ, o UPDATE não consegue ser executado por conta dos Range locks da segunda thread.

Tabela de sessões após o update da primeira thread.

Após soltar o break point da segunda thread, o deadlock ocorre e é automaticamente resolvido, resultando no mesmo output do teste com REPEATABLE_READ. Mesmo com o nível de isolamento mais restrito, ainda não conseguimos garantir que obteremos o status 3, devido à natureza não determinística da escolha da sessão que será a vítima do deadlock. Mais adiante no artigo, iremos ver como resolver este problema.

Scenario 2: selecionando uma linha não existente antes de uma inserção

Iremos estudar os possíveis resultados de ter 2 threads chamando o método save() concorrentemente. O método primeiro seleciona um registro baseado em um UUID, e então insere um novo registro com esse UUID caso ele ainda não existe.

Objetivo: nosso objetivo é garantir que apenas um registro seja inserido para cada UUID.

@Transactional(isolation = Isolation.READ_COMMITTED)
public void save(StatusDomain statusDomain) {
    log.info("Thread {} entered save method", currentThread().getName());

    StatusDomain current = repository.findByUuid(statusDomain.getUuid());

    log.info("Thread {} selected registry {}", currentThread().getName(), current);

    ThreadUtils.sleep(3000);
    if (current == null) {
        Long id = repository.save(statusDomain);
        log.info("Thread {} saved statusDomain and generated id {}", currentThread().getName(), id);
    }

    log.info("Thread {} exited save method", currentThread().getName());
}

Novamente, os 3 segundos de sleep garantem que ambas as threads tentarão performa o SELECT antes de uma delas realizar a inserção.

O teste de integração é bem simular ao do primeiro cenário:

@SpringBootTest
class StatusServiceTest {

    @Autowired
    public StatusService service;

    @MockitoSpyBean
    public StatusRepository repository;

    private final StatusDomain status4 = new StatusDomain(null, "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX", 4);

    @BeforeEach
    void setUp() {
        repository.deleteByUuid(status4.getUuid());
    }

    @Test
    void testSave() {
        assertDoesNotThrow(() -> {
            Thread t1 = new Thread(() ->                service.save(status4),   "THREAD1");
            Thread t2 = new Thread(() -> { sleep(1000); service.save(status4);}, "THREAD2");
            t1.start(); t2.start();
            t1.join();  t2.join();
        });

        Mockito.verify(repository, times(2)).findByUuid(anyString());
        Mockito.verify(repository, times(2)).save(any());
        assertEquals(2, repository.findAllByUuid(status4.getUuid()).size());
    }
}

Antes de cada teste, o registro com UUID “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX” será deletado. A primeira thread será chamada de “THREAD1” e a segunda de “THREAD2”.

READ COMMITTED

O teste com READ COMMITTED resulta no seguinte:

Execução do método save com isolamento READ COMMITTED.

Ambas as threads executam o SELECT concorrentemente, e nenhuma delas encontra o UUID, então ambas inserem o novo registro, criando um duplicado. Já que o READ COMMITED não mantém os locks gerados após o SELECT, e também já que o registro procurado não existe, esse é o comportamento esperado. Definitivamente este nível de isolamento não é suficiente para atingir o objetivo.

REPEATABLE READ

O teste com REPEATABLE READ resulta no seguinte:

Execução do método save com isolamento REPEATABLE READ.

Ambas as threads executam o SELECT concorrentemente, nenhuma encontra o UUID. Mas dessa vez, alguns locks são gerados no banco:

Locks gerados para o SELECT com isolamento REPEATABLE_READ.

Note que diferentemente do Cenário 1, a tabela de locks para o SELECT mostra locks de intenção (IS) apenas nos recursos OBJECT e PAGE, enquanto no Cenário 1, vimos os mesmos locks com a adição do recurso KEY recebendo um shared lock (S). Como neste cenário a linha procurada não existe, não há recurso KEY para bloquear.

Após soltar o break point em ambas as threads, a tabela de locks mostra o seguinte:

Tabela de locks após o UPDATE em ambas as transações, antes de committar.

Um novo lock exclusive (X) é gerado para cada sessão, representando a recem inserida linha. Nenhum bloqueio ocorre na tabela de sessões. Prosseguindo com a execução, ambos os registros são inseridos e nenhuma exceção é lançada.

Comparando este cenário com o Cenário 1, o primeiro recebeu uma SQLServerException, porque o dado lido por uma thread estava sendo modificado por outra transação, o que não é permitido. Mas aqui, por conta do registro inicial não existir, nenhuma exceção ocorre.

SERIALIZABLE

A execução com SERIALIZABLE resulta no seguinte:

Execução do método save com isolamento SERIALIZABLE.

Desta vez, recebemos a mesma SQLServerException de antes. Apenas um registro foi inserido, garantindo a não duplicidade, mas como podemos ver pelos logs acima, ambas as threads executaram o SELECT e não encontraram o registro. Vejamos a tabela de locks para essa execução:

Locks gerados para ambas as transações após o SELECT, no isolamento SERIALIZABLE, agregados.

Similar ao Cenário 1, um RangeS-S lock foi gerado para a tabela inteira, para cada thread. O restante deste cenário evoluirá da mesma maneira que o Cenário 1. Após soltar o breakpoint na thread 1 (session_id = 61), o comando INSERT fica suspenso, bloqueado pelos RangeS-S locks da thread 2 (session_id = 62).

Tabela de sessões após o UPDATE com isolamento SERIALIZABLE.

Após soltar o break point na thread 2, o deadlock ocorre. Após o banco de dados matar uma das sessões, a sessão sobrevivente consegue completar a transação, inserindo a nova linha.

Por um breve momento, o deadlock pode ser visto na tabela de sessões, tendo ambos os comandos INSERT suspensos:

Deadlock durante o método save com isolamento SERIALIZABLE.

Concluindo, este cenário ocorre literalmente da mesma forma que o Cenário 1. Já que estamos inserindo e não atualizando, a exceção acaba por prevenir a inserção duplicada como um efeito colateral. O isolamento SERIALIZABLE garante que somente um registro é inserido, atingindo o objetivo, mas ao custo de gerar centenas de locks e também produzir uam SQLServerException. Vamos ver como atingir nosso objetivo de maneira mais eficiente.

Locking Pessimista

Até agora, vimos o comportamento de utilizar diferentes níveis de isolamento em nossos métodos transacional, mas a query de SELECT sempre foi mantida igual. Em todos os testes, as seguintes linhas aparecem na mesma ordem:

2025-09-08 12:47:08.242 Thread THREAD1 entered save/update method
2025-09-08 12:47:08.278 Thread THREAD1 selected registry XXX
2025-09-08 12:47:09.246 Thread THREAD2 entered save/update method
2025-09-08 12:47:09.253 Thread THREAD2 selected registry XXX

Isso mostra que mesmo com o nível de isolamento mais restritivo, nós nunca estamos efetivando bloqueando os dados de serem lidos por outra transação após o primeiro SELECT.

Para tornar isso possível, podemos utilizar algumas Table Hints, que providenciam um mecanismo para nós indicarmos ao banco de dados algumas mudanças no plano de execução da query, e criar locks mais específicos para as linhas selecionadas. O uso de locks explícitos para previnir problemas de concorrência é chamado de locking pessimista.

Os testes seguintes serão executados com o nível de isolamento READ COMMITED.

Vamos voltar ao Cenário 1. Para forçar a thread 2 a aguardar até o final da transação da thread 1, podemos modificar o SELECT da seguinte forma:

SELECT * FROM control_table WITH(UPDLOCK) WHERE uuid = :uuid

Agora, re-executando o método update() do Cenário 1, vemos o seguinte:

Execução do método update com isolamento READ COMMITED e SELECT com UPDLOCK.

Dessa vez, podemos ver que após a thread STATUS3 entrar no método de update, ela não consegue executar o SELECT. Ela precisa esperar até a thread STATUS2 finalizar o update e confirmar a transação, para poder realizar a leitura.

A dica “UPDLOCK” marca a linha seleciona com um update (U) lock. Isso permite que outras transações leiam o registro, mas não permite que o atualizem. Já que a segunda thread execusa a mesma query de SELECT, com a mesma hint na mesma linha, a segunda thread precisa esperar a primeira thread completar a transação e assim liberar seu lock U, para que ela possa então adquirir o lock U no mesmo registro. A tabela de locks e de sessões demonstra esse comportamento:

Tabela de locks após ambos os SELECTs com UPDLOCK.

Tabela de sessões após ambos os SELECTs com UPDLOCK.

Dessa forma, podemos usar o nível de isolamento menos restritivo READ COMMITTED, enquanto ainda garantimos um update de cada vez, gerando menos locks no banco do que com os isolamentos REPEATABLE READ e SERIALIZABLE, e sem lançar exceções. Também é garantido que o status final será 3.

Para o Cenário 2, no entando, o mesmo método não irá funcionar. Já que a linha procurada não existe, é impossível bloqueá-la, certo? Errado.

É possível utilizar a seguinte query para o SELECT:

SELECT * FROM control_table WITH(UPDLOCK, HOLDLOCK) WHERE uuid = :uuid

A hint “HOLDLOCK” atua como um sinônimo para o isolamento SERIALIZABLE. Outras transações não podem inserir novas linham que iriam dar match com o predicado do SELECT da transação atual. Range locks são colocados no range de chaves que podem dar match com as condições da busca. Isso bloqueia outras transações de atualizar ou inserir linhas que qualificariam para serem selecionadas para transação atual.

O uso do UPDLOCK em conjunto faz com que os locks gerados sejam RangeS-U ao invés de RangeS-S, bloqueando a segunda thread de performar a leitura. Re-executando o método save() do cenário 2, obtemos o seguinte:

Execução do método save com SELECT com UPDLOCK e HOLDLOCK.

Dessa vez, podemos ver que o após a THREAD2 entrar no método de update, ela não consegue realizar o SELECT. Precisa esperar até que a THREAD1 finalize a transação para poder performar a leitura.

Dessa forma, podemos garantir que apenas um registro será inserido, sem lançar nenhuma exceção. Mas lembre-se que a hint HOLDLOCK é análoga ao nível de isolamento SERIALIZABLE, que pode ser custoso para a performance do banco de dados, porque ainda gerará um número considerável de locks.

O banco de dados precisa determinar o intervalo de chaves que podem dar match no resultado do SELECT. Então, para a coluna UUID, que não é sequencial nem indexada, isso pode significar o mesmo que lockar a tabela inteira. Vamos ver o comportamento dos locks após o SELECT:

Tabela de locks após o primeiro SELECT com UPDLOCK e HOLDLOCK

Como podemos ver, a primeira thread gerou 101 RangeS-U locks, enquanto a segunda thread aguarda para realizar a leitura. Essa é a diferença para o teste, que havia gerado 101 RangeS-S locks.

A tabela de sessões também evidencia o bloqueio:

Tabela de sessões mostrando a segunda thread sendo bloqueada pela primeira.

O comando SELECT da thread 2 (session_id = 62) é bloqueado pelo comando SELECT da primeira thread (session_id = 61). Após soltar o break point da primeira thread, o INSERT é realizado.

Tabela de locks após o primeiro comando INSERT.

Outro lock foi criado do tipo RangeX-X, que representa o novo registro que foi inserido mas ainda não foi commitado. Após soltar novamente o break point na primeira thread, o método finaliza com sucesso e o dado é commitado, e a segunda thread é capaz de realizar o SELECT e verificar que o registro não é nulo, portanto não inserindo novamente.

Concluindo, utilizando ambos UPDLOCK e HOLDLOCK em conjunto, nós conseguimos garantir nosso objetivo sem ter que se preocupar com nenhuma exceção lançada. Utilizar HOLDLOCK apenas é o mesmo que utilizar o nível de isolamento SERIALIZABLE, e por isso não previne o deadlock. O que previne o deadlock é o uso do UPDLOCK hint, que marca os registros lidos com lock do tipo U, prevenindo a leitura por outras threads, conforme explicado na documentação oficial.

Colunas Indexadas e comportamento de Locks

Até agora, todos os testes foram feitos em uma tabela com apenas 100 registros. Vamos inserir mais registros até obter 5,000,000. Leva um tempo, mas é importante para demonstrar a diferença entre ter e não ter um índice.

Agora, os testes ainda funcionam conforme o esperado, inserindo apenas uma vez. Mas, após o comando SELECT, a tabela de locks mostra o seguinte:

Locks gerados após o SELECT com UPDLOCK e HOLDLOCK, sem índice, numa tabela de 5M de linhas.

Como podemos ver, 43,860 PAGE locks foram gerados em modo update (U). Agora, vamos verificar o uso de espaço para essa tabela:

SELECT from sys.dm_db_partition_stats on OBJECT_ID(‘control_table’)

A coluna “in_row_data_pages” representa a quantidade de páginas que contém linhas de dados. Então, em outras palavras, bloqueamos 100% da tabela em modo U. Portanto, queries de leitura ainda podem executar, mas qualquer INSERT ou UPDATE ficaria bloqueado, porque nenhuma outra transação pode adquirir um lock U ou X dentro de nenhuma página.

Os Range locks foram substituídos por PAGE locks porque é mais barato para o banco de dados gerir milhares de PAGE locks do que milhões de Range locks. Quando a tabela tinha apenas 100 linhas, toda a tabela foi lockada também, apenas com um tipo diferente de lock (RangeS-U). Sem um índice, o mecanismo de banco de dados não consegue prevenir leituras fantasma sem lockar a tabela inteira, porque não é possível saber onde o novo registro seria inserido.

Agora, vamos criar um índice não-clusterizado:

CREATE NONCLUSTERED INDEX IControlTable_Uuid ON control_table (uuid);

Executando o teste novamente, a tabela de locks momostra o seguinte:

Locks gerados para o SELECT com UPDLOCK e HOLDLOCK, com um índice na coluna UUID, em uma tabela de 5M de linhas.

Agora há apenas um único RangeS-U lock para um recurso KEY, um único lock IU e IX para recursos PAGE e OBJECT, respectivamente. Lembre-se que um Range lock não significa um único lock, e que representa um modo de lock protegendo o intervalo entre duas entradas de índice consecutivas (RangeS), mais o modo de lock protegendo a linha do índice (U). No nosso caso, RangeS-U significa que o range entre duas entradas do índice possui Shared locks, e a KEY em si possui um U lock. Em resumo, o índice habilita que troquemos o lock na tabela inteira por um único lock RangeS-U, causando bem menos impacto na performance.

Quando criamos o índice, o mecanismo de banco de dados cria uma estrutura B-Tree ordenada para organizar os dados da coluna. Como o UUID é um campo textual, é ordenado de forma alfabética, o que possibilita que o banco de dados bloqueio apenas o intervalo onde seria inserido um novo registro. Imagine que fosse um campo numérico. Se a tabela possui os registros 1, 2, 5, 10, por exemplo, e a query está buscando pelo item 7 com HOLDLOCK, UPDLOCK, o banco de dados tem condições de saber que o item 7, mesmo não existindo na tabela, estaria localizado entre o item 5 e 10.

Perceba também que o intervalo que será bloqueado pode variar conforme os dados existentes. Em uma tabela que contenha os items 1, 4 e 80, o intervalo de locks gerado para um SELECT que filtra o item 3 será muito menor do que para um SELECT que filtre o item 70. Quanto mais uniforme for a distribuição dos dados, menor o impacto em performance. Caso os valores UUID sejam gerados de forma aleatória, a tendência é que os dados estejam bem distribuídos.

Conclusão

Ao longo deste artigo, vimos a influência que os diferentes níveis de isolamento, hints, e índices tem nos diferentes tipos de locks gerados pelo banco de dados. Também vimos como escrever um teste de integração que é capaz de assegurar o comportamento esperado para um determinado cenário envolvendo múltiplas threads, através da quantidade de vezes que um método é chamado.

Erros causados por concorrência podem ser difíceis de identificar, e as vezes podem levar um longo até para ocorrerem, sendo notados apenas após o sistema escalar o suficiente. Não é difícil escrever um trecho de código que não é thread-safe em um ambiente distribuído, e só reparar no problema quando algum sintoma aparecer.

O uso de table hints como UPDLOCK pode ajudar a prevenir deadlocks e condições de corrida e manter a integridade do banco de dados, enquanto o HOLDLOCK pode ser utilizado quando houver a necessidade de prevenir leituras fantasma. Nesses casos, um índice não-clusterizado deve ser utilizado sempre que possível para minimizar o impacto em performance.

O mecanismo de locks do SQL Server não é determinado apenas por níveis de isolamento, índices e hints. Também é determinado por fatores de sistema e carga, que desenvolvedores e DBAs devem estar atentos. Tamanho de tabela e contagem de linhas influenciam o comportamento do escalonamento de locks, uso de memória pode tornar os escalonamentos mais frequentes, enquanto alta concorrência e uso de CPU podem aumentar a duração dos locks e frequência de deadlocks. Escolhas do otimizador de queries, triggers, restrições em cascata, e particionamento podem expandir o escopo do mecanimos de locks de maneiras não óbvias a partir da query. Preste atenção não apenas às queries e índices, mas também ao volume de dados, recursos do sistema e configurações do banco de dados, pois todo o conjunto pode influenciar no comportamento dos locks quando o sistema estiver sobre grande carga.

Se você desejar executar estes testes por si mesmo (o que eu recomendo muito), o código fonte está disponível neste repositório Github.

Referências

[embed]Table Hints (Transact-SQL) — SQL Server Table hints override the default behavior of the query optimizer during the DML operation.learn.microsoft.com

[embed]Transaction locking and row versioning guide — SQL Server Transaction locking and row versioning guidelearn.microsoft.com