READPAST & Furious:

Transactions, Locking and Isolation

Mark BroadbentPrincipal ConsultantSQLCloud

About

Mark Broadbent “Possibly the hardest drinking DBA on the planet”

Email: mark.broadbent@sqlcambs.org.uk

Twitter: retracement

Blog: http://tenbulls.co.uk

Event Lead to the UK’s first ever SQLSaturday (Cambridge) http://www.sqlsaturday.com/events.aspx

Cambridgeshire PASS Chapter UG Leader http://sqlcambs.org.uk

DBA-309

Our Sponsors:

Upcoming SQL Server events:

XXXIII Encontro da Comunidade SQLPort

Data Evento: 23 Abril 2013 - 18:30

Local do Evento: Auditório Microsoft, Parque das Nações, Lisboa

18:30 - Abertura e recepção.

19:10 - "Analyzing Twitter Data" - Niko Neugebauer (SQL Server MVP, Community Evangelist –PASS)

20:15 - Coffee break

20:30 - "First Approach to SQL Server Analysis Services" - João Fialho (Consultor BI Independente)

21:30 - Sorteio de prémios

XXXIV Encontro da Comunidade SQLPort

Data Evento: 7 Maio 2013 - 19:00

Local do Evento: Porto

18:30 - Abertura e recepção.

19:00 - «Apresentação para Developers» - para definir

20:15 - Coffee break

20:30 - «Apresentação para definir» - para definir

21:30 - Sorteio de prémios

Volunteers:

They spend their FREE time to give you this

event. (2 months per person)

Because they are crazy.

Because they want YOU

to learn from the BEST IN THE WORLD.

If you see a guy with “STAFF” on their back –

buy them a beer, they deserve it.

Paulo Matos:

Paulo Borges:

João Fialho:

Bruno Basto:

Niko Neugebauer:

Agenda

TRANSACTIONSStructure, Scope, Management, the weird and the wonderful!

LOCKINGCompatibility, Multi Granularity and Escalation

NOLOCK, READPAST and UPDLOCK

ISOLATIONIsolation Levels

Snapshot Isolation and Read Committed Snapshot

Rolling Database Snapshots

AlwaysOn Readable Secondaries

March 16, Lisbon, Portugal

TRANSACTIONSPart I

Transactions can be…

Batch Scope

Session Binding

BEGIN TRANUPDATE …

spid 115

BEGIN TRANINSERT …

spid 162

Distributed

MSDTC MSDTC

UPDATE …INSERT …DELETE …GO

Auto-Commit

INSERT …DELETE …

Explicit

BEGIN TRAN…

Implicit (evil muthas!)

INSERT …DELETE …

BEGIN TRAN

and provide Atomicity (all or nothing) …or do they?!

They effect the duration of locks. It can be really confusing!

Binding Token

BEGIN TRAN WITH MARKBEGIN TRAN

--do somethingCOMMIT TRANSAVE TRANBEGIN TRAN transaction_3

--do something elseCOMMITIF {something_wrong} THEN ROLLBACK TRAN savepoint

COMMITCOMMIT

Transactions are easy-peasy…

‘restorepoint’

Transaction Name Transaction Mark

Savepoint

Nested

Transaction

transaction_1

savepoint

BEGIN TRAN

March 16, Lisbon, Portugal

TRANSACTIONSDemo

March 16, Lisbon, Portugal

LOCKINGPart II

Locks are ONLY memory structures

• They can be converted or escalated

• Lock escalation will occur for performance and memory savings

• Are compatible OR incompatible with other locks

• Will wait if they are incompatible

• Are taken depending upon the Isolation level

• Can cause blocking or deadlocks

Lock Manager compares locks ONLY on same resource

AND lock partition

• So that’s why intent locks are needed (for granularity)

Locks, the Lock Manager and Locking

X

IS

IU

SIU

SIX

UIX

BU

RS-S

RS-U

RI-N

RI-S

RI-U

RI-X

RX-U

RX-X

IX

RX-S

U

S

SCH-M

SCH-S

NL

NL SCH-S SCH-M S U X IS IU SIU SIX UIX BU RS-S RS-U RI-N RI-S RI-U RI-X RX-S RX-U RX-XIX

No Conflict

Conflict

Illegal

NL

SCH-S

SCH-M

S

U

X

IS

IU

No Lock

Schema Stability Lock

Schema Modification Lock

Shared

Update

Exclusive

Intent Shared

Intent Update

IX

SIU

SIX

UIX

BU

RS-S

RS-U

RI-N

Intent Exclusive

Share with Intent Update

Share with Intent Exclusive

Update with Intent Exclusive

Bulk Update

Shared Range-Shared

Shared Range-Update

Insert Range-Null

RI-S

RI-U

RI-X

RX-S

RX-U

RX-X

Insert Range-Shared

Insert Range-Update

Insert Range-Exclusive

Exclusive Range-Shared

Exclusive Range-Update

Exclusive Range-Exclusive

SQL Server Lock Compatibility Chart

READPAST & Furious: Locking Blocking and Isolation ·

Mark Broadbent · sqlcloud.co.uk

X

IS

IU

SIU

SIX

UIX

BU

RS-S

RS-U

RI-N

RI-S

RI-U

RI-X

RX-U

RX-X

IX

RX-S

U

S

SCH-M

SCH-S

NL

NL SCH-S SCH-M S U X IS IU SIU SIX UIX BU RS-S RS-U RI-N RI-S RI-U RI-X RX-S RX-U RX-XIX

Compatible

Incompatible

Illegal

NL

SCH-S

SCH-M

S

U

X

IS

IU

No Lock

Schema Stability Lock

Schema Modification Lock

Shared

Update

Exclusive

Intent Shared

Intent Update

IX

SIU

SIX

UIX

BU

RS-S

RS-U

RI-N

Intent Exclusive

Share with Intent Update

Share with Intent Exclusive

Update with Intent Exclusive

Bulk Update

Shared Range-Shared

Shared Range-Update

Insert Range-Null

RI-S

RI-U

RI-X

RX-S

RX-U

RX-X

Insert Range-Shared

Insert Range-Update

Insert Range-Exclusive

Exclusive Range-Shared

Exclusive Range-Update

Exclusive Range-Exclusive

http://bit.ly/sc9f7l

SQL Server Lock Compatibility Chart

Free Space

Data Page

Intent Locks

orders

IX

IX

Table

X

X

Row

Free Space

Data Page

Intent Locks

orders

IX

Lock must wait!

S

IX

Table

X

X

Row

Free Space

Data Page

Escalation

IX

Partition

orders

IX Table

X

X

IX

Row

ALTER <table> SET

(LOCK_ESCALATION

= auto| table| disable)

Don’t escalate by TF1211

or take artificial IS on table

Ignore # locks by TF1224 X

NOLOCK (a Wolf in Sheep’s Clothing?)

Should use READUNCOMMITTED hint instead –but ONLY in very

specialist situations.

Doesn’t acquire Shared locks so is able to perform DIRTY READS,

NON-REPEATABLE READS, PHANTOMS

…it can also return DUPLICATE READS!

It DOES NOT give “Oracle style” concurrency

When can this ever be acceptable?

Well since you mention it…

Readonly Filegroup/s

Scalable Shared Database

Filegroup1

Filegroup2

Filegroup3

SQL ServerInstance BInstance C

ReadonlyDatabase

DatabaseSnapshot

SQL ServerInstance A

“There is NOLOCK”

Blog post: “When should you use NOLOCK?“ http://bit.ly/rdGzow

AlwaysOn Readable Secondaries

SQL ServerInstance B

SQL ServerInstance A

READPAST & (your boss) may be Furious!

Is an alternative to NOLOCK/ READUNCOMMITTED.

Skips over resources holding incompatible (to S) locks.

…does not therefore cause dirty or duplicate reads. (yay!)

DOES NOT give “Oracle style” concurrency

…and can return INCOMPLETE DATA SETS!

When can this ever be acceptable?

Using READPAST and Table Queues

orders

U

X

U

X

1

2

--Get the first record--possible and update

UPDATE u SET …FROM(SELECT TOP(1) *

FROM ordersWITH (READPAST

,UPDLOCK)WHERE …

) AS U

March 16, Lisbon, Portugal

LOCKINGDemo

March 16, Lisbon, Portugal

ISOLATIONPart III

REPEATABLE

READ

SNAPSHOT

SERIALIZABLE

Pessimistic

Optimistic

READ

COMMITTED

…WITH SNAPSHOT

BAD DEPENDENCIES

OK OK OK Warning!

OKOKOK OK

OK

OK

OK

Warning!

Warning!

Warning!

OK OK

OK

OK Warning!

Warning!

Dirty Read

Non-

Repeatable

Read

PhantomsUpdate

Conflict

READ

UNCOMMITTED

3

4

5

2

1

OKWarning!Warning!Warning!

Read Committed Snapshot vsSnapshot Isolation

Read Committed Snapshot Snapshot

Isolation at the statement level.

Becomes the new default

Isolation.

Requires Exclusive Transaction

Workspace lock (i.e. no other

connections to DB).

Not allowed on master, tempdb &

msdb.

Conflict detection not required.

Isolation at the transaction level.

Must be explicitly SET in each

connection.

Requires no active transactions in

order to transition.

Is allowed master, tempdb &

msdb.

Implements automatic update

conflict detection.

Optimistic Concurrency #FAIL

Snapshot Isolation introduces update conflicts!

TempDB overhead for version store.

Writers STILL block writers and their escalation can still cause a

concurrency problem!

RCSI (and SI) are not a silver bullet for Concurrency.

Database Snapshots

DBA-309

COMMIT or ROLLBACK

BEGIN TRANUPDATE

ordersSET …WHERE …

CREATE DATABASE ssdb…AS SNAPSHOT OF db

AutomaticCHECKPOINT

3

2

1

Blog post: “Lifting the lid on database snapshots“ http://bit.ly/NS64qA

COMMIT or ROLLBACK

5

Datafile Transaction Log

Buffer Cache

Dirty PagesRecovery

4

spid 115

spid 162

spid 115

Readable Secondaries

Great for reporting, but…

Cause a 14 byte pointer to be added to Primary database –since

Secondary uses SNAPSHOT isolation under the covers.

Beware sizing considerations and page splitting.

Data latency from redo thread can cause unexpected results if your

application does not expect it.

More administration overhead and knowledge.

You cannot write to them (name is the giveaway!).

DBA-309

March 16, Lisbon, Portugal

ISOLATIONDemo

Summary

Concurrency is a very complicated topic, ensure you understand the

effects of running different transactional modes and Isolation levels.

Understand the duration and compatibility of common locks and how

they are effected.

Isolation can be achieved through scalability mechanisms and

techniques, your ultimate aim is to separate writers from readers,

readers from writers and writers from writers. In short maximize lock

compatibility.

References and Thanks

Kalen Delaney, SQLPASS Summit 2011

DBA301P - Locking and Blocking

and Row Versions, Oh My! – DVD

Jose Barreto's Blog http://bit.ly/nPJGjv

Paul White http://sqlblog.com/blogs/paul_white

Benjamin Neverez http://www.benjaminnevarez.com/

PASS Resources

Free SQL Server and BI training Free 1-day Training Events Regional Event

Local and Virtual User Groups Free Online Technical Training

Learning Center

This is Community

March 16, Lisbon, Portugal

Thank youfor attending this session and

SQLSaturday #188 Lisbon

www.passbaconference.com

#passbac

I want to

upgrade

my skills

I want to take

the path from

DBA to Data

Analytics Guru

I want to give my career a

competitive edge

I want to expand

my network in the

business analytics

industry

What Are Your Professional Development

Goals?

Sound familiar? Get a head start and join us today at:

Enjoy $150 off registration: use code CHM2D