lock, block & two smoking barrels

Copyright © 2014, SQLCloud Limited. Please do not redistribute, republish in whole or in part without prior permission of content owner. www.sqlcloud.co.uk

Mark Broadbent

Principal Consultant

SQLCloud

SQLCLOUD.CO.UK

lock, lockb& two smoking barrels


About

Mark Broadbent.“30 billion times more intelligent than a live mattress”Microsoft Certified Master/ Certified Solutions Master: SQL Server

Owner/ Principal at SQLCloud

Email: [email protected]

Twitter: retracement

Blog: http://tenbulls.co.uk

Event Lead to the UK’s first ever SQLSaturday (Cambridge) and

Cambridgeshire SQL Usergroup

Event #3 on 10/ 11/ 12th Sept 2015 (pre-cons & Community day)

http://sqlcambs.org.uk

http://tenbulls.co.uk/

http://sqlcambs.org.uk/


Agenda

Versioning

Theory

1

Isolation

2

5

X X

Locking

X

No Locking

The best part of the presentation… Gin O‘Clock

4

3

6


ACI

D


Concurrency-versus-Correctness

“ I s o l a t i o n i s a b a l a n c e b e tw e e n c o n c u r r e n c y a n d c o r r e c t n e s s ” – Me *1

1st law of Concurrency Control:

Concurrent execution should not cause

application programs to malfunction.

2nd law of Concurrency Control:

Concurrent execution should not have

lower throughput or much higher

response times than serial execution.

– Jim Gray and Andreas Reuter

*1 derived from the writings of Gray, Reuter, Bernstein et.al


Serial Processing

Read R1Read R2Write R3

T1

Read R1Read R2Write R3

T2

Read R4Write R4

T3

Read R4Write R4

T4


Transaction Interleaving

T1 Read R1T1 Read R2T1 Write R3

T2 Read R1T2 Read R2T2 Write R3T3 Read R4T3 Write R4T4 Read R4T4 Write R3

T1 Read R1T1 Read R2T2 Read R1T2 Read R2T1 Write R3T2 Write R3T3 Read R4T3 Write R4T4 Read R4T4 Write R3

T2 Read R1T2 Read R2T2 Write R3

T1 Read R1T1 Read R2T1 Write R3T4 Read R4T4 Write R3T3 Read R4T3 Write R4

T1 Read R1T1 Read R2T2 Read R1T2 Read R2T2 Write R3T1 Write R3T3 Read R4T3 Write R4T4 Read R4T4 Write R4

T1 Read R1T1 Read R2T2 Read R1T2 Read R2T1 Write R3T2 Write R3T3 Read R4T4 Read R4T3 Write R4T4 Write R4


Transactional Histories

T1 Read R1T1 Read R2T2 Read R1T2 Read R2T1 Write R3T2 Write R3T3 Read R4T4 Read R4T3 Write R4T4 Write R4

T1 SLOCK R1T1 Read R1T1 UNLOCK R1T1 SLOCK R2

T2 SLOCK R1T2 Read R1T1 Read R2T2 UNLOCK R1T2 SLOCK R2T2 Read R2T1 UNLOCK R2T2 UNLOCK R2T1 XLOCK R3T1 Write R3T1 UNLOCK R3

T2 XLOCK R3T2 Write R3T2 UNLOCK R3

A transaction is

considered Two-

Phased if all LOCK

actions precede an

UNLOCK action

A transaction is

considered to be

Well-Formed if all

READ, WRITE and

UNLOCK operations

are covered by a

preceding LOCK.

Isolation mechanism has “serialized these transactional writes to same resource


SERIALIZABLE4

NoneWRITE WRITEWRITE READREAD WRITE

LOWEST: read and write locks held to EOT

Isolation Levels

READ UNCOMMITTED1

“Bad Dependencies”Isolation Level

Dirty Read,Non-Repeatable Read, Phantoms,

Lost Updates

X Dependencies

WRITE WRITE

Concurrency

GOOD: only wait on write-write locks

READ COMMITTED2 Non-Repeatable Read,

Phantoms , Lost Updates

WRITE WRITEREAD WRITEWRITE READ

OK: wait on both but only writes held to EOT

REPEATABLE READ3

PhantomsWRITE WRITEREAD WRITEWRITE READ

LOW: read and write locks held to EOT

READCOMMITTED (with Snapshot

Isolation)

2 Non-Repeatable Read,Phantoms , Lost Updates

WRITE WRITEGOOD: only wait on writes held to EOT

SNAPSHOT5 None (though Causal consistency

concerns, lost update prevention and other behaviours)

WRITE WRITEGOOD: only wait on writes held to EOT


Isolation Levels…

• Attempt to solve reduce interleaving dependency problems

• 4 Levels are defined standard by ANSI, SQL 2005+ introduces 5th

• Pessimistic concurrency readers block writers, writers block readers

• Optimistic concurrency isolates the writes from the reads but not writes from writes

• Not all Isolation Levels can be used everywhere – FILESTREAM enabled databases support only RC, RCS and SSI

• Set at session level, transaction level and statement

• Sometimes used behind the covers (such as Readable Secondaries)


Read Committed Lost Updates

DECLARE @basketcount INT = 1DECLARE @newquantity INTBEGIN TRANSACTION

SELECT @newquantity = quantity FROM GunInventory WITH (NOLOCK) WHERE id = 1SET @newquantity = @newquantity - @basketcountUPDATE GunInventory SET quantity = @newquantity WHEREid = 1

COMMIT


DemoPessimistic isolation


Locks

• Are compatible OR incompatible with each other

• Are ONLY memory structures (lock blocks) and long chains can consume a lot of memory

• Compared by lock manager across same resource or lock partition

• Isolation determines their existence, granularity and duration

• They can be converted or escalated

• Poor queries cause less granular locks or escalation of locks• For performance and memory savings

• Increases chances of blocking and deadlocks which cause perceived OR actual poor performance!

• Is why developers love NOLOCK

DBA-309

Mark Broadbent · https://twitter.com/retracement · [email protected] · http://tenbulls.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

SQL Server Lock Compatibility Chart


Granularity and Escalation

Free Space

Data Page

IX

Partition

orders

Table

X

X

Row

IX


Granularity and Escalation

Free Space

Data Page

Partition

orders

Table

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


orders

id status

1 0

2 2

3 1

4 0

(U)pdate lock …a special kind of lock

UPDATE ordersSET status = 1 WHERE id > 1AND id < 4

spid 115


spid 162

Lock will be blocked!

SEARCHPHASE

X

U

S

S U X


orders

id status

1 0

2 2

3 1

4 0

(U)pdate lock …a special kind of lock


Lock Conversion

spid 115


spid 162

Lock will be blocked!

UPDATEPHASE

X

U

S

S U X

X

X


Why don’t you LOCK OFF!

Its obvious why developers (wrongly) use NOLOCK for performance but…

• No Isolation guarantees since resource locks not placed meaning dirty reads (and the other bad dependencies)

• Allocation order scan can result in• Duplicate reads

• Unstable reads (error 601: “Could not continue scan with NOLOCK due to data movement”

• READPAST hint is a better alternative but returns potentially incomplete data sets

• What about Optimistic Concurrency?


Row Versioning

1 2

1 4 B 9910

1 6 A 9925

UPDATE… x=6

UPDATE… x=4COMMIT

UPDATE… x=2COMMIT

Version created on

statement execution not

transaction commit.

Pointer XSN

14 Bytes

Row

Version

Version

Row data

T1

T2

T3


• SI provides isolation at the transaction level, RCS provides isolation at the statement level

• SI must be explicitly SET in each connection, for RCS it becomes the new default

• Enabling SI level requires no active transactions in order to transition. Enabling RCS requires Exclusive Transaction Workspace lock (and therefore no other connections to DB)

• RCS not allowed on master, tempdb and msdb, SI is allowed

• SI implements automatic update conflict detection

Read Committed Snapshot vs Snapshot Isolation

READPAST & Furious: In defence of being Pessimistic · Mark

Broadbent · sqlcloud.co.uk


Optimistic Concurrency #FAIL

• Snapshot Isolation introduces • Update conflicts! (not quite true)• Causal*1 isolation behaviours and no serializable guarantees

• TempDB version-store overhead• Increased IOPS for version creation• Tree Traversal• Long running transactions cause large version chains

• Writers use e(X)clusive locks• Still block writers • Could still escalate

• Page Fragmentation and 14 byte version pointer

• Readers should be short lived (in default isolation)• Locks taken row-by-row, read and released• OLTP loads are not read based (if you are doing it right!)

*1 At least that is what I believe it is called (i.e. causal consistency).


In-Memory Isolation

• Supports only SNAPSHOT, REPEATABLE READ and SERIALIZABLE

• Long running transactions consume more memory for in-memory versions

• Cross disk/ in-memory table transactions are supported but only for:• READCOMMITTED + in-memory SNAPSHOT

• READCOMMITTED + in-memory REPEATABLEREAD/ SERIALIZABLE

• REPEATABLEREAD/ SERIALIZABLE + in-memory SNAPSHOT

• Are synchronization issues for:• SNAPSHOT + in-memory any isolation

• REPEATABLEREAD/ SERIALIZABLE


DemoOptimistic isolation


Are you Pessimistic about being Optimistic?

• Being pessimistic means that “I expect lots of contention, I must prevent concurrent access.”

• Being optimistic means: “I do not expect resource contention, so lets not overburden the system with concurrency controls.”

Consider:

• Proper use of indexing strategy

• Keep Transactions short lived

• Avoid escalation (in the wrong situation)

• Avoid higher ISOLATION LEVELS

• Make an intelligent use of SQL Server Scalability


In Summary…

• Interleaving and concurrent execution is why isolation is required• Static read-only data does not require isolation

• Isolation and bad dependencies determined by the ISOLATION Level

• NOLOCK is ALMOST never a good idea… EVER

• RCSI is perhaps the best optimistic compromise but has overheads

• Snapshot Isolation is conceptually a perfect Isolation level but practically dangerous because It DOES NOT offer SERIALIZABLE protection

• Optimistic isolation is not a silver bullet for Concurrency


Thank you for listening!

Email: [email protected]

Twitter: retracement

Blog: http://tenbulls.co.uk

http://tenbulls.co.uk/

lock, block & two smoking barrels

Software

sqlcloud limited

transaction interleaving

transactional histories

sqlcloud email

n d c o r r e c t n

t i o n i s

sql server owner principal

concurrent execution