Lição 3: GerenciandoTransações (Parte 2)

abril 28, 2011

 

Para evitar acessos múltiplos ao mesmo recurso o banco de dados utiliza um recurso chamando LOCK. O controle de concorrência do Sql Server é classificado como otimista ou pessimista.

Pessimista – Permite acesso para leitura e atualização do mesmo dado em concorrência, bloqueios são utilizados para prevenir problemas devido aos múltiplos acessos ao dado.

Otimista – Não há bloqueio de recurso durante a leitura, no caso de atualização, o sistema verifica o dado foi alterado antes de lido.

 

Entendendo bloqueios (locks)

O SQL Server pode bloquear diferentes tipos de recursos ou granularidades, esses tipos podem ser RID (Row Identifier), página, índices, objetos e banco de dados.

Modos de bloqueios:

Shared (S) – Bloqueio utilizado em consultas, compatível com outros bloqueios compartilhados.

Update (U) – Bloqueio utilizado em recursos que podem ser atualizados, apenas uma transação por vez pode obter o Update lock no recurso.

Exclusive (X) – Bloqueio utilizado para modificação de dados, ex: INSERT, UPDATE, ou DELETE, incompatível com outros bloqueios

Intent (IS, IX, SIX) – Executa um bloqueio de objeto de alto nível (ex:table) antes de realizar um bloqueio exclusive (X) ou shared (S) no baixo nível (ex: página).

Schema (Sch-M, Sch-S) – Bloqueio utilizado quando uma operação dependente do esquema de uma tabela está em execução. Há dois bloqueios, o schema modification (Sch-M) e o schema stability (Sch-S), os dois são incompatíveis entre si.

Bulk Update (BU) – Bloqueio utilizado ao copiar massa de dados dentro de uma tabela e quando TABLOCK é especificado.

Key-Range – Bloqueio utilizado para evitar inserções/exclusões fantasmas ou exclusões em um conjunto de registros acessados ​​por uma transação. Esse bloqueio é utilizando transações que operam no nível de isolamento serializável.

 

Entendendo cenários de Deadlock e Blocking

As transações podem bloquear uma a outra, isso pode ocorrer de acordo com o nível de isolamento utilizado.

Uma situação de Deadlock ocorre quando uma transação “A” aguarda a liberação de um recurso bloqueado pela transação “B” que por sua vez aguarda uma liberação de um recurso bloqueado pela transação “A”. Para resolver esse impasse o Sql Server baseado no custo estimado de Rollback “derruba” uma das transações, essa transação é chamada de vítima.

Melhores práticas para evitar Deadlock:

– Faça transações curtas

– Obtenha e verifique os dados de entrada antes de iniciar uma transação.

– Mantenha as transações em um único batch

– Acesse a quantidade mínima possível de dados em uma transação.

 

Entendendo relatórios sobre o estado do bloqueio

Há diversas opções para visualizar o status de um bloqueio em um servidor de Sql Server. São eles Sql Profiler, System Monitor (perfom), Dmv’s e Activity Monitor.

 

Sql Server Profiler

Captura informações de locks e blocks. O Profiler disponibiliza um template já apropriado para detecção de bloqueios.

clip_image002

 

Activity Monitor

Utilize o Activity Monitor para visualizar dados de bloqueios, usuários, conexões e etc.

clip_image004

 

DMV’s

As DMV’s provém informações detalhadas sobre todos os bloqueios ativos.

Segue alguns exemplos.

 

SELECT *

FROM sys.dm_tran_active_transactions

 

clip_image006

 

SELECT Resource_type,Request_mode, Request_status,request_owner_type

FROM sys.dm_tran_locks

 

clip_image008

 

Tipos de recursos

 

RID (Row Identifier) – Linha física em um Heap.

KEY – Linha em um índice.

PAGE – Página em um arquivo de dados.

EXTENT – Uma extensão de arquivo de dados. Uma extensão é um grupo de oito páginas contíguas.

HoBT – Bloqueio em um Heap de uma página de dados ou na estrutura B-tree de um índice.

TABLE – Uma tabela.

FILE – Um arquivo de banco de dados.

APPLICATION – Um recurso de aplicativo especificado.

METADATA – Informações de metadados.

ALLOCATION_UNIT – Uma unidade de alocação.

DATABASE – Um banco de dados

 

Utilizando Sql Server Extended Events

Esse recurso está incluso no Sql Server 2008, assim como FindBlocker and lock_count que podem ser utilizados em conjunto com o Windows Event Logs, Sql Profiler ou System Monitor.

 

Usando DBCC LOG

Comando não documentado o qual retorna informações sobre o transaction log.

Sintaxe:

DBCC LOG (<databasename>,<output identifier>)

 

Níveis de output identifier

0: Retorno apenas o mínimo de informações para cada operação – incluindo operation, context, transactID, e log block generation.

1: Retorna toda as informações do nível acima, incluindo flags e record length information

2: Retorna toda as informações do nível acima, incluindo Object name, index name, page ID e slot ID.

3: Retorna todas as informações sobre a operação.

4: Retorna todas as informações sobre a operação, incluindo o hex dump da linha atual de log de transações.

 

DBCC LOG (’70_433′,1)

clip_image010

 

Definindo Níveis de Isolamento de Transações

Os seguintes níveis podem ser definidos através do comando SET TRANSACTIONS ISOLATION LEVEL:

 

READ UNCOMMITTED Nível mais baixo de isolamento, permite leitura de transações não confirmadas (leitura suja)

READ COMMITTED – Nível de baixo de isolamento, porém só permite leitura de transações confirmadas.

REPEATABLE READ- Não permite leitura de transações não confirmadas e realiza um bloqueio compartilhado em um recurso de dados.

SNAPSHOT – Requer ALLOW_SNAPSHOT_ISOLATION setada com ON Não permite leitura de transações não confirmadas, quando a transação é iniciada o banco de dados obtém um snapshot dos dados confirmados.

SERIALIZABLE – Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída.

 

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com


Lição 3: GerenciandoTransações (Parte 1)

abril 26, 2011

Transações podem ser definidas como um conjunto de ações e podem ser realizadas com sucesso ou não. São importantes para garantir a integridade e consistência dos dados em um banco de dados OLTP (Online Transaction Processing).

Entendendo Transações

As transações fornecem quanto principais funções (ACID):

Atomicidade – Uma transação deve ser uma unidade atômica de trabalho ou todas as suas modificações de dados são executadas ou nenhuma delas é executada.

Consistência – Quando concluída, uma transação deve deixar todos os dados em um estado consistente.

Isolamento – Modificações feitas por transações simultâneas devem ser isoladas das modificações feitas por qualquer outra transação simultânea.

Durabilidade – Depois que uma transação tiver sido concluída, seus efeitos ficam permanentemente no sistema. As modificações persistem até mesmo no caso de uma queda do sistema.

Definindo transações explicitas

Uma transação explicita inicia quando o comando BEGIN TRANSACTION é executado e finaliza quando o é executado um dos seguintes comandos COMMIT TRANSACTION ou ROLLBACK TRANSACTION.

Entendendo cenários especiais de ROLLBACK

Quando há transações aninhadas na mesma sessão, um comando ROLLBACK desfaz a transação aninhada. Isso acontece quando um comando COMMIT é executado antes do comando ROLLBACK. No exemplo a os dados são revertidos a primeira linha.

BEGIN TRANSACTION
INSERT
INTO TestTable
VALUES (1,‘a’,‘b
);

BEGIN TRANSACTION
UPDATE TestTable
SET Col2=‘c’
WHERE
Id = 1
    COMMIT TRANSACTION
ROLLBACK

Caso queira desfazer apenas uma parte da transação você pode definir savepoints usando o comando SAVE TRANSACTION nome_do_SavePoint, dessa forma a transação será desfeita até o último savepoint. Vale lembrar que você precisa definir o COMMIT.

Obtendo informação sobre transações

Quando trabalhamos com transações em stored procedures, tratamentos de erros devem ser realizados antes de fechar uma conexão.

Você pode utilizar o comando @@trancount para verificar a quantidade de transações ativas na sessão.

Para obter mais detalhes consulte as DMV’s abaixo:

sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store

image

sys.dm_tran_active_transactions

[]’s

Andressa Alves Martins

Twitter: Dre_Martins  Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com


Lição 2: Utilizando a cláusula OUTPUT e a instrução MERGE

abril 19, 2011

Olá pessoal!

Na lição de hoje estaremos estudando a cláusula OUTPUT e a instrução MERGE, e para entendermos bem como ambas funcionam vejamos o que cada uma delas faz.

OUTPUT: Permite que você retorne informações de registros afetadas por instruções como INSERT, UPDATE, DELETE ou pela instrução MERGE, podendo retornar à aplicação como mensagem de confirmação, variáveis, em tabelas de destino ou views. Uma vez recuperada, estas informações podemos manipulá-las da maneira que nos for mais conveniente.

Abaixo vamos comentar sobre 3 cenários possíveis para a utilização da cláusula OUTPUT, porém faremos o exemplo de apenas 1 as outras 2 fica a cargo de vocês e aguardo feedback! J

· Cenário 1: Muitos ambientes precisam salvar os dados inseridos em uma tabela de auditoria, como exemplo uma empresa pode querer tirar relatóriospara saber quantas linhas foram inseridas em diferentes períodos de tempo.

· Cenário 2: Outra situação é o arquivamento de dados. Por exemplo, suponha que uma empresa queira mover todos os registros de pedidos feitos antes 01 de dezembro de 1997, à partir de uma tabela de Pedidos para a tabela PedidosArchive. Você pode excluir as linhas da tabela Pedidos e movê-los para a tabela PedidosArchive em uma única etapa usando DELETE com a cláusula OUTPUT,

· Cenário 3: Neste último a idéia é de a empresa poder saber qual o status de antes e depois de uma alteração em uma determinada coluna. Vejamos o exemplo abaixo onde iremos alterar o nome de uma determinada categoria, para isso utilizaremos a base de dados Northwind, a tabela já existente Categories e uma segunda tabela que criamos chamada CategoriasModificadas que possui as colunas CategoryID, CategoryNameDeletado, CategoryNameNovo, DataCriacao e Usuario.

o Na tabela abaixo apresentamos os dados originais.

output00

o Com o código abaixo iremos alterar o dado da coluna CategoryName que possue o CategoryID 1 e popular nossa nova tabela CategoriasModificadas com os dados alterados e recupoerados.

1: UPDATE Categories

2: SET CategoryName = ‘Bebidas’

3: OUTPUT inserted.CategoryID, deleted.CategoryName

4: , inserted.CategoryName, getdate(), SUSER_SNAME()

5: INTO CategoriasModificadas

6: WHERE CategoryID = 1;

o Com isso teremos os seguintes resultados respectivamente nas tabelas Categories e CategoriasModificadas

output01

output02

MERGE: Nos permite executar comandos como INSERT, UPDATE ou DELETE baseando-se em duas tabelas sendo uma de origem e outra de destino.

No exemplo abaixo iremos comparar os dados de duas tabelas e quando um destes dados não estiverem na tabela de origem, o valor da coluna Cancelled na tabela de destino será alterado para True.

· Neste primeiro momento exibimos os dados iniciais da tabela de origem, observe que ainda temos os registros com o OrderID 10248.

merge00

· Neste instante eu excluí todos os registros que possuíam o OrderID = 10248 e executei a seguinte instrução:

1: MERGE INTO [Historico Order Details] AS ODH

2: USING [order details] AS OD

3: ON ODH.orderid = OD.orderid

4: AND ODH.ProductID = OD.ProductID

5: WHEN NOT MATCHED BY TARGET THEN

6: INSERT (OrderID, ProductID, UnitPrice, Quantity, Discount, ModifiedDate, Cancelled)

7: VALUES (OrderID, ProductID, UnitPrice, Quantity, Discount, ModifiedDate, ‘False’)

8: WHEN NOT MATCHED BY SOURCE THEN

9: UPDATE SET ODH.Cancelled = ‘True’;

· Sendo assim teremos os seguintes resultados em nossas tabelas:

o Na tabela de origem os registros com OrderID = 10248 não mais existem.

merge00a

o Já na tabela de destino os registros com OrderID = 10248 além de estarem presentes a coluna Cancelled foi alterada para True pois estes dados já existiam na tabela de destino e quando executamos a instrução acima atendeu nossa condição de que todos os dados que não estiverem na tabela de origem tivesse os dados da coluna Cancelled alterado para True.

merge01

Espero que tenham entendido, qualquer dúvida fico à disposição.

Grande abraço e sucesso a todos.

Lucas A. Romão
MSN:
laromao@oxygenhost.com.br
Blog: http://laromao.wordpress.com/ | Twitter: @LucasRomao
Líder da Comunidade Azure Services Brasil | @AzureServicesBR


Capítulo 2 – Modifying Data—The INSERT, UPDATE, DELETE, and MERGE Statements

novembro 23, 2010

Lição 1: Modifying Data by Using INSERT, UPDATE, and DELETE Statements

Olá pessoal, depois de um longo período dedicado a outras atividades eis que retorno às atividades, prometo que desta vez teremos novidades diárias (ou quase)! 😛

Mas vamos ao que interessa, nesta lição aprenderemos como inserir dados utilizando o INSERT, alterar dados utilizando o UPDATE, e excluir dados utilizando o DELETE dentro de uma determinada tabela.

Utilizando a instrução INSERT

Para inserir dados em uma tabela você pode inserir dados em determinadas colunas ou em todas, caso opte pela segunda opção vc não necessida indicar o nome das colunas.

Vamos dar uma olhada em ambas formas:

a) Inserindo dados em todas as colunas;

 1: Insert Into Customers
 2:     Values ('ASBR', 'Azure Services Br Corp', 'Lucas A. Romão', 'Manager',
 3:     'Gastão Vidigal, 267', 'Piracicaba', 'SP', '13418470', 'Brazil',
 4:     99999999, 888888888);

insertTodosCampos

b) Inserindo dados em algumas colunas, para isso basta adicionar o nome das colunas a que desejamos inserir valores e na sequencia seus valores propriamente ditos.

 1: Insert Into Customers
 2:     (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region,
 3:     PostalCode, Country)
 4:     Values ('EP', 'Estudando para 70433', 'Lucas A. Romão', 'Manager',
 5: 'Gastão Vidigal, 267', 'Piracicaba', 'SP', '13418470', 'Brazil');

insertAlgunsCampos

Uma outra forma de inserir dados em uma tabela é com base em uma pesquisa feita em uma outra tabela, veja o exemplo abaixo eu me baseei em uma consulta na tabela EMPLOYEES para inserir alguns dados na tabela CUSTOMERS.

 1: INSERT INTO Customers
 2: SELECT SUBSTRING(lastname,1,3), 'Northwind Traders', FirstName + ' ' + LastName
 3:        , 'Employee', Address, City, Region, PostalCode
 4:        , Country, HomePhone, NULL
 5: FROM Employees WHERE LastName='Fuller'

Veja o resultado após um SELECT na tabela CUSTOMERS.

3 - insertComSelect

Obs: Claro que este SELECT podia ser muito mais complexo utilizando-se , mais de uma tabela inclusive utilizando JOIN.

Que tal voces montarem um e postar nos comentários? Vale um brinde heim!!!!

Utilizando a instrução UPDATE

Nos próximos exemplos estarei demonstrando algumas maneiras de como atualizar os dados de uma tabela.

Na primeira forma estarei alterando todos os dados de uma coluna, onde aumentaremos em 1.25 o valor de todos os produtos da coluna UNITPRICE da tabela PRODUCTS e para isso utilizartemos a seguinte instrução.

 1: UPDATE Products
 2: SET UnitPrice = UnitPrice + 1.25;

Com apenas essas 2 linhas alteramos os preços de todos os produtos e se executarmos uma instrução SELECT teremos o seguinte resultado.

4 - update figura 1

Agora se quisermos alterar o valor de um único ou determinado grupo de registros, basta utilizarmos a cláusula WHERE, abaixo iremos alterar o valor do Produto CHANG (observe a linha 2 da imagem acima) de 20.25 para 19.99.

 1: UPDATE Products
 2: SET UnitPrice = 19.99
 3: WHERE ProductID = 2;

Veja o resultado final.

4 - update figura 2

Você também pode atualizar uma tabela baseando-se em um JOIN entre outras tabelas.

Utilizando a instrução DELETE

Para utilizarmos a instrução DELETE podemos utilizá-la das 3 formas mostradas anteriormente, vejamos as 3 formas.

a) Excluindo todas as informaçõe de uma única vez, para isso basta utilizarmos a seguinte sintaxe.

 1: DELETE FROM Customers;

PS: Cuidado ao executar esse comando pois ele definitivamente vai limpar sua tabela

b) Excluindo um determinado registro com base em uma cláusula WHERE, neste caso iremos excluir um registro que inserimos anteriormente.

Para isto basta utiliza a seguinte sintaxe.

 1: DELETE FROM Customers
 2: WHERE CustomerID = 'Ful';

5 - delete 2

c) E por último vamos ver como deletar determinadas informações baseadas em um resultado obtido por um JOIN feito em outras tabelas.

Neste exemplo iremos deletar todos os registros da tabela [Order Details] que possua o mesmo ORDER.ID entre ambas as tabelas e possuam o valor da coluna FREIGHT <= 1 e o valor da coluna SHIPVIA = 1

 1: DELETE FROM [Order Details]
 2: FROM ORDERS JOIN [Order Details]
 3: ON Orders.OrderID = [Order Details].OrderID
 4: WHERE Freight <= 1 AND ShipVia = 1

5 - delete join

Utilizando a instrução TRUNCATE

Para finalizar temos a instrução TRUNCATE, que possui a mesma função do DELETE para apagar todos os registros de uma tabela, porém com algumas diferenças significativas como por exemplo a velocidade em que os dados são apagados (muito mais rápido), requer menos recursos do servidor além de que se existe uma coluna com valor identity o TRUNCATE TABLE redefine este valor.

Veja sua sintaxe abaixo.

 1: TRUNCATE TABLE Customers;

PS: Cuidado ao executar esse comando pois ele definitivamente vai limpar sua tabela

E é isto pessoal espero que possam ter aproveitado e nos encontramos no próximo post.

Grande abraço e sucesso. Smiley piscando

Lucas A. Romão