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
hint
HOLDLOCK
NOLOCK
PAGLOCK
READCOMMITTED
Description
Hold a shared lock until completion of the transaction instead of releasing the lock as
soon as the required table, row, or data page is no longer required. HOLDLOCK is
equivalent to SERIALIZABLE.
Do not issue shared locks and do not honor exclusive locks. When this option is in
effect, it is possible to read an uncommitted transaction or a set of pages that are rolled
back in the middle of a read. Dirty reads are possible. Only applies to the SELECT
statement.
Use page locks where a single table lock would usually be taken.
Perform a scan with the same locking semantics as a transaction running at the READ
COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
READPAST
Skip locked rows. This option causes a transaction to skip rows locked by other
transactions that would ordinarily appear in the result set, rather than block the
transaction waiting for the other transactions to release their locks on these rows. The
READPAST lock hint applies only to transactions operating at READ COMMITTED
isolation and will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED
REPEATABLEREAD
Equivalent to NOLOCK.
Perform a scan with the same locking semantics as a transaction running at the
REPEATABLE READ isolation level.
Use row-level locks instead of the coarser-grained page- and table-level locks.
ROWLOCK
SERIALIZABLE
TABLOCK
TABLOCKX
UPDLOCK
XLOCK
Perform a scan with the same locking semantics as a transaction running at the
SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds
this lock until the end of the statement. However, if you also specify HOLDLOCK, the
lock is held until the end of the transaction.
Use an exclusive lock on a table. This lock prevents others from reading or updating the
table and is held until the end of the statement or transaction.
Use update locks instead of shared locks while reading a table, and hold locks until the
end of the statement or transaction. UPDLOCK has the advantage of allowing you to
read data (without blocking other readers) and update it later with the assurance that
the data has not changed since you last read it.
Use an exclusive lock that will be held until the end of the transaction on all data
processed by the statement. This lock can be specified with either PAGLOCK or
TABLOCK, in which case the exclusive lock applies to the appropriate level of
granularity.
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