Transcript SQL Server Lock Model

1
Lock Model, o que é ?
 Como o SQL Server gere toda a problemática dos locks
2
Lock Model, porquê ?
 Os locks, em conjunto com os índices, são as 2 coisas
que mais contribuem para escalabilidade do SQL
Server, ou para a falta dela.
 Conhecer o lock model é uma grande ajuda, nos
despiste de problemas “estranhos”, como por exemplo
a aplicação estar lenta e o CPU, IO não ter utilização
significativa ou ainda um determinado job às vezes não
terminar.
3
Locks
4
Problemática dos locks
Problemas “estranhos”
tps
Escalabilidade
“Non repeatable Reads”
Integridade
carga
Concurrência
Duração
“Phantoms”
“Dirty Read”
5
Propriedades das transacções
ACID
 A)tomicity (tudo ou nada)
 C)onsistency
 I)solation (grau de independência)
 D)urability
6
Uma transação
BEGIN TRY
BEGIN TRANSACTION T1
INSERT INTO dbo.t1
SELECT p.ProductID
FROM Production.Product AS p
INSERT INTO dbo.t1
VALUES (1)
COMMIT TRANSACTION T1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
PRINT 'An error occurred'
RETURN
END CATCH
7
Granularidade dos Locks
 Row (RID)
 Key (KEY)
 Page (PAG)
 Extent (EXT)
 Table (TAB)
 Database (DB)
8
Background information
 P:Qual a dimensão máxima de uma row ?
 R:8060 bytes
 P:Qual é a dimensão de uma página ?
 R:8Kb
 P:Qual é a dimensão de um extent ?
 R:8 Páginas, 64 Kb
9
Row : até 8Kb (8060 Bytes)
Page : 8Kb (8192 Bytes)
Extent (8 pages) 8 x 8Kb =64Kb
Mixed exents (8 objectos)
10
Tipos de Lock









Shared (S)
Update (U)
Exclusive (X)
Intent Shared (IS)
Intent Exclusive (IX)
Shema Modification (Sch-M)
Shema Stability (Sch-S)
RangeS-S
Etc..
11
Gestão da concorrência:
a) Níveis de Isolamento
 Read Uncomited
 Read Committed
 Read Commited
Snapshot
 Repeatable Read
 Serialzable
 Snapshot
12
“Sistema Solar” do SQL
13
Comportamentos Permitidos
Transaction Level
Comportamento
Outros Locks
X Lock
Read Uncommited
Dirty Reads
Non repeatable Reads
Phantoms
-
-
Read Commited
(Locking)
Non repeatable Reads
Phantoms
S
Process.
Fim
Read Commited
(Snapshot)
Non repeatable Reads
Phantoms
-
-
Repeatable Read
Phantoms
C:KEY Lock
NC:S,IS : index
e table pag.
Fim
Fim
Serializable
None
RangeS-S fim
Fim
Snapshot
None
-
14
Lock Hints
h in t
H O LD LO C K
D e s c rip tio n
H o ld a s h a re d lo c k u n til c o m p le tio n o f th e tra n s a c tio n in s te a d o f re le a s in g th e lo c k a s
s o o n a s th e re q u ire d ta b le , ro w , o r d a ta p a g e is n o lo n g e r re q u ire d . H O L D L O C K is
N O LO C K
e q u iv a le n t to S E R IA L IZ A B L E .
D o n o t is s u e s h a re d lo c k s a n d d o n o t h o n o r e x c lu s iv e lo c k s . W h e n th is o p tio n is in
e ffe c t, it is p o s s ib le to re a d a n u n c o m m itte d tra n s a c tio n o r a s e t o f p a g e s th a t a re ro lle d
b a c k in th e m id d le o f a re a d . D irty re a d s a re p o s s ib le . O n ly a p p lie s to th e S E L E C T
PA G LO C K
s ta te m e n t.
U s e p a g e lo c k s w h e re a s in g le ta b le lo c k w o u ld u s u a lly b e ta k e n .
R E A D C O M M IT T E D
P e rfo rm a s c a n w ith th e s a m e lo c k in g s e m a n tic s a s a tra n s a c tio n ru n n in g a t th e R E A D
C O M M IT T E D is o la tio n le v e l. B y d e fa u lt, S Q L S e rv e r 2 0 0 0 o p e ra te s a t th is is o la tio n le v e l.
R EAD PAST
S k ip lo c k e d ro w s . T h is o p tio n c a u s e s a tra n s a c tio n to s k ip ro w s lo c k e d b y o th e r
tra n s a c tio n s th a t w o u ld o rd in a rily a p p e a r in th e re s u lt s e t, ra th e r th a n b lo c k th e
tra n s a c tio n w a itin g fo r th e o th e r tra n s a c tio n s to re le a s e th e ir lo c k s o n th e s e ro w s . T h e
R E A D P A S T lo c k h in t a p p lie s o n ly to tra n s a c tio n s o p e ra tin g a t R E A D C O M M IT T E D
is o la tio n a n d w ill re a d o n ly p a s t ro w -le v e l lo c k s . A p p lie s o n ly to th e S E L E C T s ta te m e n t.
R E A D U N C O M M IT T E D
E q u iv a le n t to N O L O C K .
R EPEA TA B LER EA D
P e rfo rm a s c a n w ith th e s a m e lo c k in g s e m a n tic s a s a tra n s a c tio n ru n n in g a t th e
R O W LO C K
R E P E A T A B L E R E A D is o la tio n le v e l.
U s e ro w -le v e l lo c k s in s te a d o f th e c o a rs e r-g ra in e d p a g e - a n d ta b le -le v e l lo c k s .
S E R IA L IZ A B L E
P e rfo rm a s c a n w ith th e s a m e lo c k in g s e m a n tic s a s a tra n s a c tio n ru n n in g a t th e
TA B LO C K
S E R IA L IZ A B L E is o la tio n le v e l. E q u iv a le n t to H O L D L O C K .
U s e a ta b le lo c k in s te a d o f th e fin e r-g ra in e d ro w - o r p a g e -le v e l lo c k s . S Q L S e rv e r h o ld s
th is lo c k u n til th e e n d o f th e s ta te m e n t. H o w e v e r, if y o u a ls o s p e c ify H O L D L O C K , th e
TA B LO C KX
lo c k is h e ld u n til th e e n d o f th e tra n s a c tio n .
U s e a n e x c lu s iv e lo c k o n a ta b le . T h is lo c k p re v e n ts o th e rs fro m re a d in g o r u p d a tin g th e
U PD LO C K
ta b le a n d is h e ld u n til th e e n d o f th e s ta te m e n t o r tra n s a c tio n .
U s e u p d a te lo c k s in s te a d o f s h a re d lo c k s w h ile re a d in g a ta b le , a n d h o ld lo c k s u n til th e
e n d o f th e s ta te m e n t o r tra n s a c tio n . U P D L O C K h a s th e a d v a n ta g e o f a llo w in g y o u to
re a d d a ta (w ith o u t b lo c k in g o th e r re a d e rs ) a n d u p d a te it la te r w ith th e a s s u ra n c e th a t
th e d a ta h a s n o t c h a n g e d s in c e y o u la s t re a d it.
X LO C K
U s e a n e x c lu s iv e lo c k th a t w ill b e h e ld u n til th e e n d o f th e tra n s a c tio n o n a ll d a ta
p ro c e s s e d b y th e s ta te m e n t. T h is lo c k c a n b e s p e c ifie d w ith e ith e r P A G L O C K o r
T A B L O C K , in w h ic h c a s e th e e x c lu s iv e lo c k a p p lie s to th e a p p ro p ria te le v e l o f
g ra n u la rity .
15
Qual estratégia esclher ?
Isolation Level Lock Hints
Hibrido
Controlo
Standard
Mais fino
Mais fino
Previsibilidade
Previsível
Imprevisível
Imprevisível
Escalation
Sim
Não
Não
16
P: Como definir o isolation level ?
R: SET TRANSACTION ISOLATION LEVEL
R: DBCC USEROPTIONS
17
SET TRANSACTION ISOLATION LEVEL









SET TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED |
READ COMMITTED |
REPEATABLE READ |
SNAPSHOT |
SERIALIZABLE
}
Nota : Se READ_COMMITTED_SNAPSHOT está ON,
O SQL Server usa “row versioning” (tempdb).
18
Modo de compatibilidade
Lock
IS
S
U
IX
Sim
Intent Shared
IS
Sim
Sim
Sim
Shared
S
Sim
Sim
Sim
Update
U
Sim
Sim
Intent Exclusive
IX
Sim
Exclusive
X
Schema Stability
Sck-S
Schema Modification
Sch-M
X
Sck-S
Sch-M
Sim
Sim
Sim
Sim
Sim
Sim
Sim
Sim
Sim
Sim
Grant
Não
Wait
Sim
Sim
Sim
Compatível ?
S > S > Grant
X > S > Wait
SET LOCK_TIMEOUT timeout_period
@@LOCK_TIMEOUT
19
Modo de compatibilidade (full)
20
Quais os locks na alteração e porquê
 P: S ⇒ X ou S ⇒ U ⇒ X ?
 R: S ⇒ U ⇒ X
0
1
2
3
4
S
U
X
-
-
U
X
S
21
Deadlock
22
Tipos de Locks especiais
 Intent Lock
 Latches
23
Ferramentas
 sp_lock
 SQL Server Management Studio
 Profiler
 Performance counter
24
sp_lock
Spid
Dbid
Objid
Type
Resource
Mode
Status
The SQL server process ID Number
The Database Id Number
The oject Id number of object beeing locked
Lock Type
DB
Database
FIL
File
IDX
Index
PAG
Page
KEY
Key
TAB
Table
EXT
Extent
RID
Row Identifier
Page Lock
PAG
file#:page#pair
RID
file#:page#slot#triple
EXT
file#:page#pair
Key
Hashed value
Mode S,X,U,etc
GRANT, WAIT, CNVRT
25
Recomendações
 Manter transacções pequenas evitando operações “caras”.
 Optimizar queries usando índices.
 Evitar perder controlo no âmbito da transacção.
 Monitorizar “long running processes”.
 Investir no tratamento de erros ou usar SET XACT_ABORT
ON para evitar uma transacção ficar aberta no surgimento
de uma condição de erro.
 Usar o nível de isolamento o mais baixo possível,
26
Problemática dos locks
Problemas “estranhos”
sp_lock
tps
Escalabilidade
Baixar isolation
level
“Non repeatable Reads”
Repeatable read
Integridade
carga
Concurrência
Baixar isolation level
Duração
Lock timout
Controlo erros
SET XACT_ABORT
ON
“Phantoms”
Serializable
“Dirty Read”
Read commited
Repetable reads
Serializable
27
Bibliografia
 Hand on SQL 2000 Troubleshooting locking and
blocking (net impress)
 Inside Microsoft SQL Server 2005. The Storage Engine
(microsoft)
 Microsoft SQL Server 2008 Internals (microsoft)
 SQL Server 2008 Query Performance Tuning Distilled
(apress)
28
Q&A
29
30