Transcript t101 - sqlzaterdag.nl

1
SQL transactions
101
SQL zaterdag 2011-11-12
© [email protected]
Inhoud
•
•
•
•
•
•
•
Spreker BIO
Wat is een transaction?
Wat betekent het begrip ACID?
Wat is een “Transaction isolation level”?
De 3 gevolgen van een transaction level keuze
Transacties in transacties
Tips en links
2
The Dr. (Ebenhaëzer) House of SQL
• select bio.* from doopceel.spreker;
• Theo Ekelmans; 46 jaar, van oorsprong electronicus
• Technisch specialist bij Infotheek (hardware en netwerken)
• Master CNE bij Novell (hardware en netwerken)
• Gedetacheerd bij KPN voor opzetten 1200 server novell netwerk
• Bedenker Performance And Capacity MANagement systemen KPN
• VB, SNMP, SQL versus 14.000+ x Cisco, Nortel, UB, etc, etc.
• Tables tot 2G records & 12.000 inserts & deletes / sec
• Sizes 1,5 a 2 TB data
• MSCE+i, Cisco CCNA+ level, enz, enz…
• Nu: Sr. DBA voor de Ordina datacenters.
• Specialisatie: SQL kernel / performance / SQL stack tweaking
3
SQL transactions 101
Wat is een transaction?
Wat is een transaction?
• Een transaction is een methode om een database tijdens een bewerking gegarandeerd
van de ene “consistent state” naar een andere “consistent state” te brengen
• Indien dat niet lukt, dat biedt een transaction de mogelijkheid om terug te keren naar de
oorspronkelijke “consistent state”
5
SQL transactions 101
Transaction theory: ACID
Atomicity
Consistency
Isolation
Durability
Transaction: ACID
ACID is een set aan regels waarmee veel database systemen
werken.
Het doel van deze regels is om te garanderen dat de
afhandeling van transactions altijd op een consistente manier
gebeurt
Zonder deze regels kan het bijvoorbeeld makkelijk gebeuren
dat 2 transacties dezelfde (set) database object(en) kunnen
wijzigen
7
Transaction : Atomicity
Een transactie is een ondeelbaar geheel
Alles wordt na de start (begin transaction):
• Of volledig uitgevoerd (commit transaction)
• Of volledig teruggedraaid (rollback transaction)
8
Transaction : Consistency
Consistency houdt in dat:
• De server de situatie van VOOR de transactie bewaart
• Pas ‘gegijzelde’ data vrijgeeft geeft als alle onderdelen
van de transactie compleet zijn; de Commit
• Indien een onderdeel niet compleet (te maken) is:
• Wordt de transactie afgebroken
• De situatie van VOOR de transactie wordt hersteld;
de (Auto-)Rollback
• De client krijgt een foutmelding
9
Transaction : Isolation
Elke transaction staat volledig los van elke andere transaction
Dit houdt in dat:
• Data welke tijdens een transactie wordt geraakt
(normaliter) niet beschikbaar is voor andere transacties
• Indien dit toch geprobeerd wordt, dan grijpt de server in
om de Consistancy te bewaren (wait / terminate / dirty)
10
Transaction : Durability
Indien een transactie zijn definitieve staat heeft dient deze
wijziging permanent te zijn
Zelfs een server crash mag geen invloed meer hebben op
deze definitieve staat
11
SQL transactions 101
Transaction isolation levels
(ANSI – SQL92)
Read Uncommitted
Read Committed (default)
Repeatable Read
Snapshot
Serializable
Transaction : Standaarden
ANSI – SQL92 (en later) is een breed gedragen standaard,
en maakt een voorspelbare koppeling tussen diverse SQL
platformen mogelijk;
•
•
•
•
•
Interbase
Firebird
IBM
Microsoft
Sybase
•
•
•
•
Mimer SQL
MySQL
OraclePL
PostgreSQL
Transaction : Isolation Levels
Van de 4 ACID begrippen is transaction isolation de meest
complexe in de omgang en impact, en helaas de minst
begrepen!
Het isolation level bepaalt:
•
•
•
•
•
Hoe users elkaar beinvloeden
Of je stiekem toch in een andere transaction mag lezen
Hoeveel rows er worden gelocked
Of je bij een retry dezelfde data ziet
Of de data die je uitleest ook echt bestaat (^^)
Transaction : Isolation Levels
Dit zijn de 5 standaard isolation levels :
•
•
•
•
•
Read Uncommitted
Read Committed
Repeatable Read
Snapshot
Serializable (SQL 2005 en hoger)
Buiten beschouwing gelaten in deze presentatie (tijdgebrek):
• Row versioning & Read Committed Snapshot (DB option)
Read Uncommitted
• Maakt zelf geen locks aan
• Negeert locks van anderen en kan daarom uncommitted
data lezen (dirty read)
• Geeft voorrang aan ‘concurrent use’, maar ten koste van
‘data consistentie’
• Ideaal voor statische data & high volume / concurrency
Read Committed
• De SQL Server default setting
• Stopt met reads als deze uncommitted data raakt (open
transaction)
• Zet een shared lock voor de read en geeft deze na de
read weer vrij
• Concurrent use is goed mogelijk, omdat een shared lock
door meer connecties tegelijk gebruikt kan worden
Read Committed 2
• Maar er is geen harde garantie dat 2 x dezelfde read, ook
2 x dezelfde data oplevert (Non-repeatable read)
Voor meer info: http://blogs.msdn.com/b/craigfr/
Repeatable Read
• Houdt een lock op alle gelezen data, tot commit of rollback
• Dit transaction isolation level kan zowel de concurrency
als performance flink negatief beinvloeden.
• 2 x dezelfde read levert gegarandeerd 2 x dezelfde data
op (tenzij….)
Repeatable Read 2
• Een range query (DT between @A and @B) kan echter
nog steeds ‘phantom reads’ opleveren
• De nog niet gelezen records van een scan kunnen nog
veranderd worden, en initieel niet bestaande geinsert.
Voor meer info: http://blogs.msdn.com/b/craigfr/
Serializable
• Phantom reads komen niet meer voor als er 2 x dezelfde
query wordt uitgevoerd binnen dezelfde transactie
• De sever gebruikt table locks of key range locks om alle
rows te locken VOORDAT hij deze gaat inlezen
•
•
•
•
Dwingt daardoor data consistancy af op DB nivo
Is dus zeer veilig, maar….
Betekent vaak het einde van speed / concurrency …. 
Wordt vaak als default gebruikt door ‘statefull applications’
zoals: BizTalk, bank transactions, etc
Snapshot
• SQL Server 2005 en hoger !
• Is ontworpen om te voorkomen dat ‘data readers’ de ‘data
writers’ blokkeren
• Elke read maakt een snapshot van zijn versie van de rows
die hij leest, en bewaart deze in TempDB
• Omdat elke read van elke user writes naar de TempDB tot
gevolg heeft, kan deze setting grote impact hebben op de
performance
• Vereist goed doordachte performance tests bij toepassing
• Is in combinatie met updates bloedlink om te gebruiken
Snapshot 2
Lost marbles
DEMO
Snapshot 3: Het gevaar !
Voor meer info: Jim Gray’s blog van Microsoft eScience/
Read Committed Snapshot
• Dit is een database setting, geen connection setting!
• Indien actief, dan worden ‘Read Committed’ reads geupgrade naar een ‘Read Committed Snapshot’ read
Dit is een bijzondere variant die met veel zorg moet worden
ingezet vanwege de performance hit.
SQL transactions 101
De 3 gevolgen van een
isolation level keuze:
Dirty reads
Non-repeatable reads
Phantom reads
Dirty reads
• Een Dirty read onstaat als transaction 1, data leest die
transaction 2 mogelijk nog niet heeft gecommit
T1
Users table
Id
name
age
1
Joe
20
2
Jill
25
SELECT age
FROM users
WHERE id = 1;
T1 result
T2
20
UPDATE users
SET age = 21
WHERE id = 1;
SELECT age
FROM users
WHERE id = 1;
21
rollback;
Non-repeatable reads
• Een Non-repeatable read onstaat als transaction 1, data
2x leest en verschillende resultaten krijgt, doordat
transaction 2 tussentijds data wijzigt EN commit
T1
Users table
Id
name
age
1
Joe
20
2
Jill
25
SELECT age
FROM users
WHERE id = 1;
T1 result
T2
20
UPDATE users
SET age = 21
WHERE id = 1;
commit;
SELECT age
FROM users
WHERE id = 1;
21
Phantom reads
• Een Phantom read onstaat als transaction 1, data 2x leest
en verschillende resultaten krijgt, doordat transaction 2
tussentijds data wijzigt EN commit
T1
Users table
Id
name
age
1
Joe
20
2
Jill
25
SELECT age
FROM users
WHERE age between 10 and 30;
T1 result
T2
20
25
DELETE
FROM users
WHERE id = 2;
commit;
SELECT age
FROM users
WHERE age between 10 and 30;
20
Overzicht
Isolation level
Dirty reads
Non-repeatable
reads
Phantoms
Read Uncommitted
X
X
X
Read Committed
-
X
X
Repeatable Read
-
-
X
Serializable
-
-
-
Isolation level
Write Lock
Read Lock
Range Lock
Read Uncommitted
-
-
-
Read Committed
X
-
-
Repeatable Read
X
X
-
Serializable
X
X
X
Data integriteit
Performance
Welk isolation level moet ik gebruiken voor mijn applicatie?
Isolation level
Read uncommitted
Read committed
Repeatable read
Serializable
Snapshot
31
Indien:
• De performance / schaalbaarheid / parallellisme het belangrijkste design citerium
• De applicatie kan omgaan met Dirty, Non-repeatable en Phantom reads
• Applicaties zoals order entry systemen, die de te updaten rows blokkeren
• Er mogen tijdens het lezen van de query data records over de range nog updates worden gedaan, zolang het resultaat maar
transactioneel consistent is.
• Lockt singleton rows bij een write
• De applicatie kan omgaan met Non-repeatable en Phantom reads
• Applicaties die er vanuit moeten kunnen gaan dat data tijdens ‘long-running multistatement transactions’ een bepaalde
waarde heeft, en deze bij een latere update binnen dezelfde transactie nog steeds deze waarde heeft.
• Applicaties die meermaals door dezelfde set heen lezen ten behoeve van aggeregaties, en geen wijzigingen tolereren in
deze set.
• Is singleton row safe; Lockt de gelezen rows voor read en write
• De applicatie kan omgaan met Phantom reads
• Grote performance impact, met name in de concurrency
• Applicaties die er vanuit moeten kunnen gaan dat data tijdens ‘long-running multistatement transactions’ over ranges een
bepaalde waarde heeft, en deze bij een latere update binnen dezelfde transactie range nog steeds deze waarde heeft.
• Applicaties die meermaals door dezelfde set heen lezen ten behoeve van aggeregaties, en geen wijzigingen tolereren in
deze set.
• Is range safe; Lockt de gelezen range voor read en write
• Heeft geen last van Dirty, Non-repeatable en Phantom reads
• Is het isolation level die de server het zwaarst belast!
• Is het minst geschikt voor paralellisme
• Applicaties die er vanuit moeten kunnen gaan dat data tijdens ‘long-running multistatement transactions’ een bepaalde
waarde heeft, maar NIET van plan is in latere update binnen dezelfde transactie te wijzigen.
• Applicaties die meermaals door dezelfde set heen gaan ten behoeve van aggeregaties, en geen wijzigingen tolereren in
deze set.
• Er worden geen shared locks gezet op de oorpronkelijke records omdat de transactie werkt met een ‘kopie’
• Dit mechanisme leent zich uitstekend voor applicaties met multiple readers en een single writer
SQL transactions 101
Nested Transactions
Nested Transactions
33
Nested Transactions bestaan niet !
BEGIN TRANSACTION MAIN
BEGIN TRANSACTION INSIDE
INSERT INTO [LOGDATA] (LOGDATE, LOGTEXT)
VALUES (GETDATE(), 'XYZ')
COMMIT TRANSACTION INSIDE
COMMIT TRANSACTION MAIN
Inderdaad….. geen foutmeldingen!
Nested Transactions
34
Dezelfde code, maar dan met een inside rollback
BEGIN TRANSACTION MAIN
BEGIN TRANSACTION INSIDE
INSERT INTO [ERRLOGDATA] (LOGDATE, LOGTEXT)
VALUES (GETDATE(), 'XYZ')
ROLLBACK TRANSACTION INSIDE
COMMIT TRANSACTION MAIN
Msg 6401, Level 16, State 1, Line 9
Cannot roll back inside. No transaction
or savepoint of that name was found.
Nested Transactions
Bij dit soort vage fouten worden mensen creatief!
De “interessantste” oplossing die ik gezien heb?
Create procedure Blah() as
if @@trancount > 0
commit transaction
35
Nested Transactions
36
SAVEPOINT to the rescue !
BEGIN TRANSACTION MAIN
SAVE TRANSACTION INSIDE
INSERT INTO [ERRLOGDATA] (LOGDATE, LOGTEXT)
VALUES (GETDATE(), 'XYZ')
ROLLBACK TRANSACTION INSIDE
COMMIT TRANSACTION MAIN
Nested Transactions
37
De valkuil: SAVEPOINT kent geen COMMIT !
BEGIN TRANSACTION MAIN
SAVE TRANSACTION INSIDE
INSERT INTO [ERRLOGDATA] (LOGDATE, LOGTEXT)
VALUES (GETDATE(), 'XYZ')
COMMIT TRANSACTION INSIDE
COMMIT TRANSACTION MAIN
Msg 3902, Level 16, State 1, Line 14
The COMMIT TRANSACTION request has no
corresponding BEGIN TRANSACTION
Nested Transactions
BEGIN TRANSACTION MAIN
EXEC sp…
38
De correcte toepassing
-- IS er een transactie in de calling stack?
IF @@TRANCOUNT > 0
SET @ACTIVETRANSDETECTED = 1
ELSE
SET @ACTIVETRANSDETECTED = 0
IF @ACTIVETRANSDETECTED = 1
SAVE TRANSACTION INSIDE
ELSE
BEGIN TRANSACTION INSIDE
INSERT INTO [ERRLOGDATA] (LOGDATE, LOGTEXT)
VALUES (GETDATE(), 'XYZ')
IF @ACTIVETRANSDETECTED = 0
COMMIT TRANSACTION INSIDE
RETURN
-- De commit van outside zit bij calling SP
COMMIT TRANSACTION MAIN
Nested Transactions
39
Samenvattend:
• Er is geen volledige ondersteuning voor nested transactions
• DE commit is voorbehouden aan de root stored procedure
• Savepoints zijn op dieper liggende nivo’s de methode om bij
fouten een ‘partial rollback’ te doen
• Let bij grotere projecten met gestapelde stored procedures
op de @@trancount of stem heel goed af in de project
meetings
SQL transactions 101
Transaction tips
Transaction beperkingen
• Niet alle TSQL statements kunnen in een transaction (bvb):
• Alter database
• Backup log
• Create database
• Drop database !
• Reconfigure
• Restore database
• Restore Log
• Update statisctics
• ALLE overige DDL statements
41
Transaction / (dead)locking tips
(Dead)locks zijn lang niet altijd te voorkomen als je eenmaal een transaction isolation level
hebt gekozen voor je applicatie.
Best practices:
•
•
•
•
Niet alles hoeft in een transaction te worden afgehandeld
Kies het het laagste Isolation Level wat de SLA toestaat
Zorg dat je de data altijd in de zelfde volgorde bewerkt
Groepeer de transacties in logische brokken
• Moeten alle bestellingen van een klant in een enkele transaction, of mag er een
transaction per bestelling ook ?
• Vermijd koste wat het kost transacties waarbinnen user interactie nodig is!
42
Interessante links
• Row Versioning-based Isolation Levels:
http://msdn.microsoft.com/en-us/library/ms188277.aspx
• En in detail:
http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx
• De presentatie is vanaf nu ook te downloaden op
http://www.ekelmans.com/T101.pptx
43
44
Einde presentatiedeel
O frabjous day!
Callooh! Callay!
he chortled in his joy
SQL transactions 101
Transactions, Checkpoints & Crashes
Transactions, Checkpoints & Crashes
Wanneer doen we een Roll Back / Roll forward?
1
2
3
4
5
Checkpoint
Time
System crash
Marble query
create database marbles
go
use marbles
go
alter database marbles set allow_snapshot_isolation on
go
create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')
go
--Start transaction 1
set transaction isolation level snapshot
go
begin transaction one
update marbles set color = 'White' where color = 'Black'
select @@TRANCOUNT
--Run dit in 2e window
--Start transaction 2
use marbles
go
set transaction isolation level snapshot
go
begin transaction two
update marbles set color = 'Black' where color = 'White'
commit transaction two
--Nu committen we de 1e transaction
commit transaction one
--En de uitkomst is ???
select * from marbles
47
Snapshot 2: Het gevaar !
create database marbles;
alter database marbles set allow_snapshot_isolation on;
create table marbles (id int primary key, color char(5))
insert marbles values(1, 'Black')
insert marbles values(2, 'White')
Snapshot 3: Het gevaar !
Transaction 1
Transaction 2
set transaction isolation level snapshot;
begin transaction one;
update marbles set color = 'White'
where color = 'Black‘;
set transaction isolation level snapshot;
begin transaction two;
update marbles set color = 'Black'
where color = 'White‘;
commit transaction two;
commit transaction one;
select * from marbles;
id
1
2
color
White (was Black)
Black (was White)