Consultando apenas os registros "sujos" em uma tabela com Microsoft SQL Server

Lidando com bancos de dados podemos controlar as transações e a maneira como podemos acessar os dados em transações concorrentes.

Os níveis de isolamento de transação e os comandos para tais serão discutidos em um próximo post, porém, resumidamente, temos (Serializável, snapshot, repeatable read, read commited, read uncommited).

Dirty read (read uncommited ou nolock) nos permitem visualizar os dados de uma tabela mesmo sendo sujos (alterados em outras transações). Isso garante, com certo nível de inconsistência se as transações forem desprezadas e o  rollback usado, uma leitura o mais rápido possível dos dados no seu estado atual quase que em tempo real.

No entanto, como separar os dados "sujos" daqueles que já estavam gravados e commitados?
Imagine que você tem um ambiente onde um sistema legado permite que a aplicação seja fechada, após uma exception ou queda de rede, sem dar commit ou rollback nas transações correntes. Você rapidamente ficaria com várias transações em aberto, utilizando recursos do servidor e dando table locks ou situações de deadlock. Como saber quais são os dados não commitados?

Em um cenário real, como saber quais dados podem não ser consistentes porque estão sendo alterados neste momento, em uma transação ainda não finalizada?

O comando / table hint nolock depois do nome da tabela nos permite fazer uma dirty read e visualizar tanto os dados antigos (commitados) como os não commitados.  Já o comando readpast faz uma leitura sem lock (somente leitura) da tabela trazendo apenas os dados commitados. Então, para sabermos os dados não comitados basta usar not exist ou except.

Dado o seguinte cenário:

create table teste
(
 valor varchar(50)
)

insert into teste values ('comitado')
select * from teste



begin transaction  --rollback
insert into teste values ('nao comitado')

Not Exist

select * from teste(nolock) where valor not in (select valor from teste (READPAST ))

Except

O Except funciona como not in ou not exists, mas sem especificar uma coluna ou pk: você simplesmente especifica a query com os resultados que você NÃO quer que apareça.

SELECT * FROM teste WITH (nolock)
EXCEPT
SELECT * FROM teste WITH (READPAST)

Com esses comandos você pode verificar quais são os comandos "sujos" de transações ainda não "commitadas" do seu banco de dados. O except ainda te ajudará com relatórios mais elaborados onde você deverá especificar com um filtro complexo um conjunto de resultados que você não quer trazer na sua consulta mas não tem uma chave primária ou não tem exatamente um campo único para comparar.

Níveis de Isolamento
Transações em Bancos de Dados
nolock vs readpast

photo credit: tec_estromberg via photopin cc