Olá, bom dia, que bom te ver por aqui, neste sabadão, estamos entrando na semana que antecede a maior festa do Brasil.
Seja bem-vindo a mais um post da sessão Para que Serve, sendo este o de número 24, mais um dia de muito trabalhado, repleto de atividades e compromissos.
No post de hoje, vou compartilhar com você que esta acessando meu blog, um dos mais tradicionais recursos existentes no Microsoft SQL Server introduzido desde as versões iniciais do produto, mantendo-se sempre atualizada, úteis e principalmente apresentando um pouco perigoso ao ser utilizada quando não se conhece.
De uma forma bem diferente e mais direta, não vou realizar suspense ou criar qualquer clima de expectativa sobre este tema, na verdade estou me referindo ao uso das Table Hints (dicas de tabelas), com certeza você já deve ter ouvido falar ou já utilizou pelo menos uma delas, adianto que neste post não vou falar da mais conhecida a NoLock e sim de outras duas que também são importantes.
Sendo assim, chegou a hora de conhecer um pouco mais sobre o post de número 24 da sessão Para que serve. Mas uma vez, bem vindo ao #24 – Para que serve – Table Hint – UpdLock e TabLock.
Espero que você esteja animado para conhecer um pouco mais sobre estas funcionalidades, caso já conheça, continue lendo este post, sempre podemos aprender algo novo….
Introdução
As dicas de tabela tem um papel muito importante a partir do momento que decidimos fazer uso delas, alterar o comportamento padrão do otimizador de consulta durante a instrução DML (linguagem de manipulação de dados) ao especificar um método de bloqueio, um ou mais índices, uma operação de processamento de consulta, como uma verificação de tabela ou busca de índice, ou outras opções. As dicas da tabela são especificadas na cláusula FROM da instrução DML e afetam apenas a tabela ou exibição referenciada nessa cláusula.
Mesmo sabendo que o otimizador de consulta do SQL Server normalmente seleciona o melhor plano de execução para uma consulta, muitos desenvolvedores e administradores de banco de dados tomam a decisão de fazer uso deste recurso em diversos cenários de forma indiscriminada.
As dicas de tabelas podem ao longo de sua utilização impactar de forma direta na maneira que o otimizador de consultas existente no Microsoft SQL Server trabalha desde o momento da identificação da análise da query e criação do seu plano de execução, mas principalmente no momento da própria execução.
Vale ressaltar que as table hints serão ignoradas se a tabela não for acessada pelo plano de consulta, isso pode ser provocado porque o query optimizer opta por não acessar a tabela ou porque uma exibição indexada é acessada, mas este cenário também pode ser contornado, quem sabe em outro momento.
Vamos em frente, sabendo que estas funcionalidades precisam ser um pouco mais conhecidas e melhor entendidas, tomei a decisão de elaborar este post, destacando as duas dicas de tabela que atualmente acabei me deparando em cenários de consultoria, me refiro a:
· UpdLock; e
· Tablock.
Desta forma, vou apresentar uma breve descrição destas duas dicas de tabelas, e posteriormente utilizar um simples cenário prático para demonstrar como podemos fazer uso de ambas, começando pela UpdLock.
Table Hint – UpdLock
Sendo está uma das mais antigas dicas de tabela existente no Microsoft SQL Server, reconhecida como uma table hint exclusiva para seu uso no comando update, o que na verdade não se aplicada, mesmo sendo este o cenário mais comum de uso podemos em determinados momentos aplicar a instruções Select e Insert.
Seu papel é especificar que bloqueios de atualização serão usados e mantidos até que a transação seja concluída. UpdLock utiliza bloqueios de atualização apenas em operações de leitura no nível de linha ou de página.
Caso o UpdLock venha a combinado com TabLock, ou se um bloqueio em nível de tabela for usado por outro motivo, um bloqueio exclusivo reconhecido pela sigla (x) será usado.
Importante destacar que sempre que um UpdLock é especificado, as dicas em nível de isolamento ReadCommitted e ReadCommittedLock são ignoradas, sendo assim, podemos em alguns cenários se deparar com a ocorrência de dados fantasmas.
Agora que conhecemos um pouco mais sobre esta table hint, vamos avançar, conhecer seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 1, que realizará a criação do seguinte ambiente:
· Databases: TesteDatabaseTableHintUpdLock;
· Tables: UpdLockTable; e
· Transações: TUPD e TUPDII.
— Bloco de Código 1 —
— Criando o Banco de Dados – TesteDatabaseTableHintUpdLock —
Create Database TesteDatabaseTableHintUpdLock
Go
— Acessando o Banco de Dados – TesteDatabaseTableHintUpdLock —
Use TesteDatabaseTableHintUpdLock
Go
— Criando a Tabela – UpdLockTable —
Create Table UpdLockTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(20))
Go
Até aqui nada de especial, nosso ambiente já está criado e pronto para receber alguns dados, para tal, utilizaremos o Bloco de Código 2, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:
— Bloco de Código 2 —
— Inserindo uma pequena porção de dados —
Insert Into UpdLockTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint UpdLock’)
Go— Consultando os dados —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go
A partir deste momento temos uma porção de dados armazenados em nossa table UpdLockTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como a ela realiza o bloqueio em nossa query.
Vamos então realizar a execução do Bloco de Código 3, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TUPD (conforme já destacado anteriormente).
— Bloco de Código 3 —
— Iniciando um novo Bloco de Transações denominada TUPD —
Begin Transaction TUPD
— Realizando o Update —
Update UpdLockTable
Set Valores = ‘BD’
Where Codigo = 10
Go
Neste instante o Microsoft SQL Server em conjunto com alguns de seus mais importantes componentes: Database Engine e Storage Engine, estabelecem uma nova transação que deverá ser processada dentro da sessão que estamos trabalhando mais que a mesma não recebeu até o presente momento nenhuma instrução Commit ou Rollback.
No próximo passo a ser realizado aparentemente pode parecer bastante simples, mas após sua execução você vai entender que nem sempre é assim que as coisas podem ser interpretadas.
Seguindo em frente, vamos executar o Bloco de Código 4, o qual vai nos exigir abrir uma nova query dentro da ferramenta que você se encontra acessando o Microsoft SQL Server no momento, eu como de costume estou utilizando o bom e velho Management Studio.
— Bloco de Código 4 —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go
Acredito que você deva ter feito a execução deste simples bloco de código e até o presente momento o Microsoft SQL Server ainda não te retornou nenhum dado. Não é verdade?
A Figura 1 pode te ajudar a comprovar e responder este meu questionamento:
Figura 1 – Execução do bloco de código 4 sendo realizada e nenhum dado retornado em tela.
Viu, nem tudo que parece ser fácil realmente é! Mas o que pode ter acontecido?
Na verdade, o SQL Server está trabalhando da forma correta, realizamos o processamento de um comando Update dentro de um bloco de transações que ainda não foi encerrado e internamente o bloqueio da tabela foi aplicada de forma geral o que impede que quaisquer outras transações que venha a acessar a tabela como um todo consiga.
Mas se você observou em nosso Bloco de Código 3 não fizemos uso da table hint UpdLock, é justamente este o ponto chave, a partir do momento em que fazemos uso desta dica de tabela teremos a capacidade de acessar nossos dados mesmo que estes se encontrem dentro de outra transação ou bloqueio.
Para confirmar esta mudança de comportamento, vamos executar agora o Bloco de Código 5 e logo na sequência abrir uma nova query e observar o resultado apresentado na Figura 2:
— Bloco de Código 5 —
RollBack Transaction TUPD — Encerrando a transação anterior
Go
— Realizar novamente o Update agora com UpdLock e Abrir nova Sessão —
Begin Transaction TUPDII
Update UpdLockTable With (UpdLock)
Set Valores = ‘Forçando UpdLock’
Where Codigo = 11
Go
Figura 2 – Select realizado em outra query mesmo com a transação TUPDII ainda não encerrada.
Show, não é verdade, neste momento o Microsoft SQL Server acaba de retornar nossos dados manipulados na tabela UpdLock a qual se encontra pertencente a um bloqueio de atualizações de dados que pode estar sendo aplicado no nível de linha ou página de dados (mas este comportamento e estudo vou deixar para outro momento).
Com isso, conhecemos a table hint UpdLock, ainda não terminamos nossa caminhada, podemos evoluir mais um pouco nesta jornada, conhecendo agora a dica de tabela TabLock na sequência.
Table Hint – TabLock
Também uma das mais tradicionais dicas de tabela, e durante minha pesquisa e entendimento sobre esta table hint, cheguei a conclusão que a ela é muito misteriosa, devido ao seu comportamento.
Aonde a mesma tem a função de especificar que o bloqueio adquirido seja aplicado no nível de tabela, o tipo de bloqueio que é adquirido depende da instrução que está sendo executada.
Por exemplo:
1. Uma instrução Select pode adquirir um bloqueio compartilhado. Ao especificar TABLOCK, o bloqueio compartilhado é aplicado à tabela inteira, e não no nível de linha ou página. Se HoldLock também for especificado, o bloqueio de tabela será mantido até o final da transação; e
2. Ou então quando usado com o provedor de conjuntos de linhas em massa através do uso de uma instrução OPENROWSET para importar dados em uma tabela, TabLock permite que vários clientes carreguem dados simultaneamente na tabela de destino com o registro em log e o bloqueio otimizados.
Estamos preparados para seguir em frente, conhecemos um pouco mais sobre esta table hint, chegou a hora de conhecer o seu funcionamento de forma prática, para tal, utilizaremos como base o Bloco de Código 6, que realizará a criação do seguinte ambiente:
· Databases: TesteDatabaseTableHintTabLock;
· Tables: TabLockTable; e
· Transações: TTBL e TTBLII.
— Bloco de Código 6 —
— Criando o Banco de Dados – TesteDatabaseTableHintTabLock —
Create Database TesteDatabaseTableHintTabLock
Go
— Acessando o Banco de Dados – TesteDatabaseTableHintTabLock —
Use TesteDatabaseTableHintTabLock
Go
— Criando a Tabela – TabLockTable —
Create Table TabLockTable
(Codigo Int Identity(1,1) Not Null Primary Key Clustered,
Valores Varchar(20))
Go
Até aqui nada de especial, nosso ambiente foi criado, preparado para receber alguns dados, para tal, utilizaremos o Bloco de Código 7, responsável em realizar o insert e consulta dos dados, conforme apresento a seguir:
— Bloco de Código 7 —
— Inserindo uma pequena porção de dados —
Insert Into UpdLockTable
Values (‘Pedro’), (‘Antonio’), (‘Galvão’), (‘Junior’),
(‘MVP’), (‘MCC’), (‘MSTC’), (‘MIE’), (‘SQL Server’),
(‘Banco de Dados’),(‘Table Hint TabLock’)
Go
— Consultando os dados —
Select Codigo, Valores From UpdLockTable
Order By Valores Desc
Go
Nossa porção de dados encontra-se armazenados em nossa table TabLockTable, o que nos permite realizar uma pequena simulação de seu comportamento e entender como ela realiza o bloqueio em nossa query.
Vamos então realizar a execução do Bloco de Código 8, sendo este o ponto chave que vai nortear nosso entendimento, observe que estamos abrindo um bloco transacional denominado TTBL (conforme já destacado anteriormente).
— Bloco de Código 8 —
— Iniciando um novo Bloco de Transações denominada TTBL —
Begin Transaction TTBL
— Utilizando o TabLock —
Begin Transaction TTBL
Declare @Contador TinyInt = 1
Select ‘Início…’
While @Contador < 255
BeginUpdate TabLockTable
Set Valores = @Contador
Where Codigo = 10
Select Valores From TabLockTable
Where Codigo = 10
Set @Contador += 1
End
Select ‘Fim…’
Go
Observação: Você deve ter notado um pouco de lentidão na execução de bloco de código, bem como, o uso de alguns comandos e técnicas que podem ser melhoradas ou até mesmo substituídas, ressalto que o mesmo não possui o objetivo de ser executado no menor tempo possível ou até mesmo ser entendimento como uma técnica para inserção de dados. Este bloco de código foi criado e elaborado somente para esta prática, sem qualquer finalidade de uso em cenários reais.
Dando continuidade, enquanto nosso Bloco de Código 8 é processado, vamos abrir uma nossa query e executar o Bloco de Código 9 e observar se algo de errado ou diferente acontece:
— Bloco de Código 9 —
— Inserindo uma nova linha de registró lógico na TableTableLock durante o processamento do bloco de código 8 —
Insert Into TabLockTable Values (‘Teste TabLock’)
Go
Tenho a certeza que você imaginava que o SQL Server iria demorar para processar nossa query ou simplesmente não iria retornar nada em tela, da mesma forma que a UpdLock.
Não!
Ele mais uma vez está trabalhando de forma certa, da mesma maneira que fizemos anteriormente não utilizamos de forma proposital a table hint TabLock em nosso bloco de código, algo que vamos realizar agora utilizando os blocos de código 10 e 11, declarados a seguir:
— Bloco de Código 10 —
Rollback Transaction TTBL — Encerrando a transação anterior —
Go
— Adicionar TabLock —
Begin Transaction TTBLII
Declare @Contador TinyInt = 1
Select ‘Início…’
While @Contador < 255
BeginUpdate TabLockTable With (TabLock)
Set Valores = @Contador
Where Codigo = 10
Select Valores From TabLockTable
Where Codigo = 10
Set @Contador += 1
End
Select ‘Fim…’
Go
— Bloco de Código 11 —
— Abrir nova query e realizar Insert vai ocorrer bloqueio —
Insert Into TabLockTable Values (‘Teste TabLock II’)
Go
Para nossa surpresa, o Bloco de Código 10 já deve ter sido processado, mas o 11 ainda se encontra em processamento, a Figura 3 apresentada abaixo, comprova este comportamento:
Figura 3 – Bloco de código 11 ainda em execução, aguardando algum retorno do bloco de código 10, mesmo após sua execução.
Você pode estar questionamento a si mesmo, o porquê deste comportamento por parte do nosso Microsoft SQL Server. Não precisa ficar preocupado, por padrão o Database Engine em conjunto com o Storage Engine estão aplicando em tempo de execução um bloqueio em nossa tabela TabLockTable, ou seja, a mesma ficou bloqueada pela operação de Update que nos disparamos no bloco de código 10 em conjunto table hint TabLock.
Para que possamos concluir a execução do Bloco de Código 11, precisamos encerrar nossa transação aberta anteriormente denominada TTBLII, para tal vamos executar o Bloco de Código 12 dentro da mesma query utilizada para executar o Bloco de Código 10:
— Bloco de Código 12 —
— Confirmando e encerrando a transação TTBLII dentro da mesma sessão do bloco de código 10 —
Commit Transaction TTBLII
Go
E como um passe de mágica, o Bloco de Código 11 foi encerrado simultaneamente após o processamento do Bloco de Código 12.
Vou deixar para que você mesmo comprove este resultado.
Não é algo realmente surpreendente, como a mesma funcionalidade utilizada de formas similares ou parecidas possa ser comportar de maneiras tão diferentes.
Eu sou suspeito a dizer, mas não podemos dizer que isso é um passe de mágicas ou truque, ao contrário, existe um conjunto de propriedades relacionadas ao controle de transacional e bloqueios que os Sistemas Gerenciadores de Bancos de Dados utilizam para garantir que tudo funcione da melhor forma possível chamado ACID:
-
Atomicidade;
-
Consistência;
-
Isolamento; e
-
Durabilidade.
Quem sabe em um outro post eu aborde este tema, por hoje, vou ficando por aqui.
Com isso, e sem mais delongas, chegamos ao final. Ufa deu um pouco de trabalho este post, como de costume, mesmo assim sempre vale a pena poder compartilhar um pouco do conhecimento e experiências adquiridas ao longo dos anos de trabalho como DBA e Professor.
Espero que você tenha gostado, eu posso dizer que sim, mas sua opinião é muito importante.
Referências
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table
http://sqlissuessa.blogspot.com/2016/07/table-hints-transact-sql.html#!
https://sqlhelp.wordpress.com/2009/01/13/table-hints-transact-sql/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d2e78faa-9c79-4faf-8232-5ae08a5dd23e/sql-table-hints-not-working-updlock-holdlock
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7536fc4-ed1e-44aa-a16b-d4dc9ca6cd70/what-is-the-difference-between-holdlock-and-updlock-in-sql-server
https://www.sqlservercentral.com/Forums/Topic1227171-392-1.aspx
http://www.sql-server-performance.com/2004/2000-table-hints/
https://pedrogalvaojunior.wordpress.com/2018/03/01/script-challenge-13-a-resposta/
https://pedrogalvaojunior.wordpress.com/tag/trace-flag/
Links
Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:
https://pedrogalvaojunior.wordpress.com/2018/12/21/23-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2018/08/22/22-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2018/05/28/21-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2018/04/12/20-para-que-serve/
https://pedrogalvaojunior.wordpress.com/2018/01/02/19-para-que-serve/
Conclusão
Mais uma vez podemos observar como o Microsoft SQL Server apresenta um conjunto grandioso de funcionalidades, aplicando formas diferentes e especificas para cenários simulares em suas estruturas de código, mas que durante sua execução pode ser entendida de uma maneira muito particular.
Neste post podemos conhecer, aprender e visualizar como as table hints UpdLock e TabLock são utilizadas, seus comportamentos, e principalmente como os bloqueios realizados por ambas podem ser aplicar para um mesmo cenário no caso o uso do comando Update dentro de um bloco de transações.
Este é o fantástico Microsoft SQL Server, que desde suas primeiras versões nos apresenta inúmeras possibilidades de se aprender, possibilitando usar o passado como fonte de inspiração para construção de um futuro melhor, por isso que a cada dia eu me apaixono ainda mais por este produto…
Vai SQL Server, Vai SQL Server….
Agradecimentos
Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.
Um forte abraço, até o próximo post da sessão Para que serve a ser publicado no mês maio de 2019.
Um grande abraço e ótimo final de semana.
Valeu.
Figura 3 – Posição atual de dados armazenados na tabela TemporalTableTemperatura.
Figura 4 – Todas as manipulações realizadas na tabela TemporalTableTemperatura armazenadas de forma espealhada na tabela histórico TemporalTableTemperaturaHistorico.
Figura 5 – Resultados obtidos após a execução de cada comando select declarado no Bloco de Código 5.
Figura 2 – Resultado apresentado pelo comando DBCC ShowFileStats.
Junior Galvão - MVP - Data Platform 