Lock
Administrao de Banco de
Dados
1
Tpicos
o
o
o
o
o
o
Lock
Tipos de Lock
Recursos que podem ser bloqueados
Dica de Lock sobre uma Tabela
Dica de Lock em uma Sesso
Compatibilidade entre Locks
2
Lock
o Os Locks servem para impedir que os dados de
suas tabelas sejam atualizadas por mais de um
usurio ao mesmo tempo, o que poderia provocar
inconsistncia no seu database.
o Bancos de Dados Relacionais como o SQL Server
implementam Locks para que um determinado
dado esteja travado enquanto alguem faz uma
modificao naquele dado, nenhum usurio
poder modificar os dados que esto sendo
modificados por outro usurio
3
Tipos de Lock
o
o
o
o
o
Lock Compartilhado (SHARED LOCK X)
Lock Exclusivo (EXCLUSIVE LOCK X)
Lock de Update (UPDATE LOCK U)
Lock de Inteno (INTENT LOCK I)
Lock de Alterao em Volume (BULK UPDATE
BU)
o Lock de Schema (SCHEMA SCH)
SHARED LOCK - X
o o tipo de Lock usado durante o processamento de
comandos que no modificam dados, como o
SELECT.
o O SQL Server permite que um dado possa ser lido
por mais de uma cenxo ao mesmo tempo.
Enquanto esses dados so recuperados, o SQL
Server coloca um lock do tipo compartilhado o que
impede que outros atualizem estes dados enquanto
esto sendo lidos
5
SHARED LOCK - SH
TRANSAO 1
Momento 1
Select na tabela Tab_x
O SQL Server coloca
SHARED
NOS REGISTROS
LIDOS
TRANSAO 2
Momento 1
Select na tabela Tab_x
O SQL Server coloca
SHARED
NOS REGISTROS
LIDOS
EXCLUSIVE LOCK - X
o Bloqueia qualquer acesso inclusive a leitura de um
determinado dado que se encontra bloqueado, este
utilizado durante operaes de
insero/deleo/atualizao de dados;
o So grandes causadores de bloqueio do sistema;
o Operaes que coloquem um Lock exclusivo
devem demorar o mnimo possvel;
EXCLUSIVE LOCK - X
Transao
Transao11
Transao
Transao22
W
Momento 1
UPDATE na Tabela
Tab_X
EXCLUSIVE
LOCK
Momento 1
Select na Tabela Tab_X
I
T
Momento 2
Executa UPDATE
Momento 3
Commit / Rollback
Momento 2
Wait
Momento 3
Executa o Select
UPDATE LOCK - X
o Preveno de DeadLocks;
o Um Update consiste em uma transao que l os
dados (portanto coloca nesses dados um Shared
Lock(S)) e em seguida modifica-os (colocando
sobre ele o Exclusive Lock (X)). Voc pode notar
que ocorre uma converso de lock de Shared em
Exclusive.
o O SQl Server resolve problema de DeadLock,
desfazendo uma das transaes para que a outra
prossiga, envia uma mensagem para a transao
desfeita, informando do deadlock, que ela foi
desfeita e avisa o usurio para reexecutar seu 9
processamento.
UPDATE LOCK - U
o Apenas Uma tabela pode lanar um UPDATE Lock
sobre determinada coluna;
o UPDATE Lock seguido de um EXCLUSIVE
Lock (Caso as modificaes precisem ser feitas) os
dados so atualizados, e o Lock desfeito, assim, se
duas transaes tentam fazer ambas uma atualizao a
primeira que lanar o UPDATE Lock em seguida
lana um Exclusive Lock, a segunda espera a operao
de Exclusive Lock e s depois pode entrar em
execuo
10
INTENT LOCK - I
o Indica a inteno de futuramente o SQL Server
colocar Locks em determinado conjunto de dados
para uma determinada transao.
o Os Locks exclusivos so enfileirados, O lock de
inteno impede que outras transaes coloquem
exclusive lock nesses dados.
o Existem trs tipos
Shared Intent Lock (IS)
Exclusive Intent Lock (ix)
Shared com Intent Exclusive Lock (SIX)
11
INTENT LOCK - I
Shared Intent Lock(IS)
O Lock IS indica que a transao ir ler alguns(mas no todos)
dados na tabela Colocando Shared Locks
Intent Exclusive Lock(IX)
O Lock IX indica que a transao deve modificar alguns (Mas no
todos) os dados na tabela colocando Exclusive Locks.
Shared With Intent Exclusive Lock(SIX)
O Lock SIX indica que a transao deve ler todas as fontes, e
modificar algumas (Mas no todas). Isto executado colocando
shared locks nas fontes lidas e exclusive locks nas colunas
modificadas.
12
BULK UPDATE - BU
o Lock de alterao de volume;
o Usado durante uma cpia de grande volume de
dados de uma tabela para outra, ou quando o usurio
utilizar a opo TABLOCK;
o Melhora a performance enquanto uma cpia do tipo
bulk feita;
o De fato ele diminue a concorrencia a tabela de
destino, desabilitando o acesso tanto de leitura quanto
de escrita a tabela.;
13
SCHEMA LOCK - SCH
Existem dois tipos de lock de esquema, que so
colocados quando se fazem alteraes nos esquemas das
tabelas:
1. SCH_M Locks Schema Modification so colocados no
esquema de uma tabela quando sua estrutura estiver sendo
alterada, quando comando de DDL (Definio de dados) so
executados.
2. SCH_S Lock Schema Stability indica que a query que est
acessando a tabela est sendo compilada. Esse lock no impede
exclusive lock (X), mais impede que outras sesses alterem o
esquema da tabela bloqueada.
14
Recursos que podem ser bloqueados
Nome
Nvel do Lock
Quem coloca o Lock nos
dados
RID
Row Identifier. Usado para bloquear
uma nica linha de dado dentro de uma
tabela.
a.
O SQL Server
bloqueia
automaticamente as
linhas individuais.
b.
O usurio pode
colocar lock na linha
com a opo
ROWLOCK.
KEY
um lock de linha dentro de um ndice. O SQL Server bloqueia as
Usado para proteger ranges de chaves
linhas dos ndices
em transaes em srie.
automaticamente.
PAGE
Bloquear pginas inteiras (8kb).
a.
O SQL Server.
b.
O usurio
(PAGLOCK)
15
Recursos que podem ser bloqueados
Nome do nvel do
Lock
Nvel do Lock
Quem coloca o Lock nos dados
ENTENT
um nome que se d para um conjunto
contguo de oito pginas (de 8KB cada
uma).
O SQL Server bloqueia entents
automaticamente.
TABLE
Uma tabela incluindo todos os dados e
ndices.
a.
O SQL Server
b.
O usurio pode com as
opes TABLOCK e
TABLOCKX.
ESQUEMA
Todos os objetos de um usurio.
Colocada automaticamente pelo
SQL Server quando a
estrutura de uma tabela
estiver sendo modificada.
DB
Database.
SQL Server quando um database
estiver sendo restaurado.
16
Otimizao com Locks
Opo de Lock
Granularidade
Durao do Lock
ROWLOCK
Lock de linha
Por default, o SQL Server mantm
este lock at o final do comando.
PAGLOCK
Lock de pgina
Por default, o SQL Server mantm
este lock at o final do comando.
TABLOCK
Lock de tabela
Por default, o SQL Server mantm
este lock at o final do comando.
TABLOCKX
Lock exclusivo de tabela.
Por default, o SQL Server mantm
este lock at o final da transao.
17
Otimizao com Locks
Opo de Lock
Granularidade
Tempo de durao
HOLDLOCK
Lock de Update
UPDLOCK
Lock de Update
Mantm este lock at o final da
transao. equivalente a
SERIALIZABLE.
Mantm este lock at o final do
comando.
NOLOCK
No bloqueia os dados.
Apenas consegue ler dados
que esto bloqueados de
forma exclusiva em
processo de alterao.
READPAST
S pode ser aplicado no comando
SELECT. chamado lock de leitura
suja, pois mostra alterao ainda no
confirmada que, portanto, pode ser
desfeita. Mantm o lock at o final
do comando.
Salta linhas bloqueadas. Esta opo
faz com que uma transao salte
18
sobre linhas bloqueadas por outras
transao.
Otimizao com locks
Opo do Lock
Tipo de Lock
Tempo de durao
READ COMMITED
Exibe apenas dados de tabelas
sem lock.
Existe enquanto outro
lock for configurado na
sesso. o lock default.
READ UNCOMMITED
Equivale ao NOLOCK.(leitura
suja).
Existe enquanto outro
lock for configurado na
sesso
REPEATABLEREAD
Permite que a 2 transao leia e Dura at o final da
insira dados, mas impede que
transao.
ela altere dados lidos pela 1
transao.
SERIALIZABLE
Permite que a 2 transao
apenas leia dados existentes na
faixa de dados atingidos pelo
lock. (HOLDLOCK)
Dura at o final da
transao.
19
UPDLOCK
o Se voc quiser, pode colocar, com o comando SELECT, o
lock de update em uma tabela, para garantir que outras
transaes possam ler os dados, mas s a sua transao possa
alter-los. Observe o exemplo a seguir:
20
UPDLOCK
Transao 1
Transao 2
Momento 1
Momento 2
BEGIN TRANSACTION
BEGIN TRANSACTION
SELECT * FROM Funcionario
SELECT * FROM Funcionario --OK
WITH (UPDLOCK)
WHERE Cod_Func = 1
WHERE Cod_Func = 1
Hint
UPDATE Funcionario
--Wait
SET Sal_Func = Sal_Func * 1.1
WHERE Cod_Func = 1
Momento 3
UPDATE Funcionario
SET Sal_Func = Sal_Func * 1.1
WHERE Cod_Func = 1
Momento 4
SELECT *
--Wait
FROM Funcionario
WHERE Cod_Func = 1
21
NOLOCK
Permite que o SELECT leia dados que estejam em
processo de alterao pela primeira transao. Veja o
exemplo seguinte:
Transao 1
Transao 2
Momento 1
Momento 2
BEGIN TRANSACTION
BEGIN TRANSACTION
UPDATE Funcionario
SELECT * FROM Funcionario --OK
SET Sal_Func = Sal_Func * 1.1
WITH (NOLOCK)
WHERE Cod_Func = 1
WHERE Cod_Func = 1
UPDATE Funcionario
--Wait
SET Sal_Func = Sal_Func * 1.1
WHERE Cod_Func = 1
22
READPAST
Le os dados da tabela pulando os dados
bloqueados, melhor que deixar a transao bloqueada
enquanto aguarda a primeira terminar seu processamento
Veja o exemplo a seguir:
Transao 1
Transao 2
Momento 1
Momento 2
BEGIN TRANSACTION
BEGIN TRANSACTION
UPDATE Funcionario
SELECT * FROM Funcionario --OK
SET Sal_Func = Sal_Func * 1.1
WITH (READPAST)
WHERE Cod_Func = 1
WHERE Cod_Func = 1
UPDATE Funcionario
--Wait
SET Sal_Func = Sal_Func * 1.123
WHERE Cod_Func = 1
Lock de sesso
(Nvel de Isolamento de Transaes)
o Dependendo da situao, voc pode optar pelo lock de
sesso para as suas transaes.
READ COMMITED DEFAULT
READ UNCOMMITED NOLOCK(table)
SERIALIZABLE HOLDLOCK(table)
REPEATABLE READ
24
Sem lock
Transao 1
Transao 2
Momento 1
Momento 2
BEGIN TRANSACTION
SELECT * FROM Funcionario
Este comando UPDATE altera os dados
da tabela funcionario, fazendo com que
o segundo SELECT da primeira
transao no devolva os mesmos
dados da primeira.
UPDATE Funcionario
SET Sal_Func = Sal_Func * 1.1
WHERE Cod_Func = 1
SELECT * FROM Funcionario
25
Lock REPEATABLE READ
Transao 1
Transao 2
Momento 1
Momento 2
Para garantir que outras transaes no
interferiro nesta primeira voc poderia
aplicar o lock de sesso.
SET TRANSACTION ISOLATION
LEVEL REPEATABLE READ
Este comando UPDATE fica
aguardando at que a primeira
transao termine.
UPDATE Funcionario
--Wait
SET Sal_Func = Sal_Func * 1.1
WHERE Cod_Func = 1
BEGIN TRANSACTION
SELECT * FROM Funcionario
SELECT * FROM Funcionario
26
Sem lock
Transao 1
Transao 2
Momento 1
Momento 2
BEGIN TRANSACTION
UPDATE Funcionario
SET Sal_Func = Sal_Func * 1.1
Apesar de o Update da primeira
transao bloquear a tabela toda, ainda
possvel inserir dados nessa tabela:
INSERT Funcionario
VALUES (....,5000)
Este select apresenta todos os dados
afetados pelo update e uma linha a mais
inserida pela segunda transao essa
linha a mais chamada linha
fantasma.
SELECT * FROM Funcionario
27
Lock SERIALIZABLE
Transao 1
Transao 2
Momento 1
Momento 2
Para evitar os FANTASMAS na sua
aplicao, configure este tipo de lock de
sesso:
Este Insert ficar aguardando at que a
primeira transao termine.
INSERT Funcionario VALUES (...,5000)
SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE
/*WAIT */
BEGIN TRANSACTION
UPDATE Funcionario
SET Sal_Func = Sal_Func * 1.1
SELECT * FROM Funcionario
/* SELECT sem fantasma */
28
DEAD LOCK
o Um Dead Lock provocado pelo prprio SQL Server quando
ocorrer uma situao de inanio. Esta situao acontece quando
duas ou mais transaes aguardam infinitamente por um recurso.
Observe:
29
DEAD LOCK
Transao 1
Transao 2
Momento 1
Momento 1
BEGIN TRANSACTION
BEGIN TRANSACTION
SELECT *
SELECT *
FROM Funcionario WITH
FROM Funcionario WITH
(HOLDLOCK)
(HOLDLOCK)
MOMENTO 2
Este Update ficaria aguardando a
segunda transao terminar.
MOMENTO 2
Este Update ficaria aguardando a
segunda transao terminar.
UPDATE Funcionario SET Sal_Func = UPDATE Funcionario SET Sal_Func =
1000
5000
30
Escolher ou no o Lock a ser utilizado?
o Sempre que possvel, deixe que o SQL Server implemente o lock
de acordo com as escolhas do otimizador, porque voc pode
provocar problemas de conteno, ou seja, voc pode bloquear uma
tabela por muito tempo e outras transaes podero ficar muito
tempo aguardando.
31