The nightmare of locking, blocking and isolation levels!

50 %
50 %
Information about The nightmare of locking, blocking and isolation levels!
Technology

Published on March 7, 2014

Author: brshristov

Source: slideshare.net

Description

I am sure you all know that troubleshooting problems related to locking and blocking (hey, sometimes there are deadlocks too) can be a real nightmare! In this session, you will be able to see and understand why and how locking actually works, what problems it causes and how can we use isolation levels and various other techniques to resolve them!

Welcome to the nightmare of locking, blocking and isolation levels!

Our Sponsors

So who am I? @BorisHristov

Agenda… Locks. What is there for us? Troubleshooting locking problems Transaction Isolation Levels

Locks. What is there for us?

Methods of Concurrency Control 1. Pessimistic – SQL Server uses locks, causes blocks and who said deadlocks? 2. Optimistic – SQL Server generates versions for everyone, but the updates…

What Are Locks and what is locking? Lock – internal memory structure that “tells” us what we all do with the resources inside the system Locking – mechanism to protect the resources and guarantee consistent data

Common lock types Shared  (S)   Update  (U)   Used  for:  Reading   Dura-on:  Released  almost  immediately   (depends  on  the  isola-on  level) Used  for:  Preparing  to  modify   Dura-on:  End  of  the  transac-on  or  un-l   converted  to  exclusive  (X) Exclusive  (X)   Intent Used  for:  Modifying   Dura-on:  End  of  the  transac-on Used  for:    Preven-ng  incompa-ble  locks   Dura-on:  End  of  the  transac-on        

Lock Compatibility Not all locks are compatible with other locks. Lock Shared Update Exclusive Shared (S) P P X Update (U) P X X Exclusive (X) X X X

Lock Hierarchy Database Table Page Row

Let’s update a row! What do we need? S IX USE AdventureWorks2012 GO UPDATE [Person].[Address] SET AddressLine1=’Vienna, Austria' WHERE AddressID=2 IX Header Row Row Row Row Row X

Methods to View Locking Information Dynamic   Management   Views   SQL  Server   Profiler  or   Extended  Events   Performance   monitor  or   Ac-vity  Monitor  

Troubleshooting locking problems

Locking and blocking Locking and blocking are often confused! Locking •  The action of taking and potentially holding locks •  Used to implement concurrency control Blocking is result of locking! •  One process needs to wait for another process to release locked resources •  In a multiuser environment, there is always, always blocking! •  Only a problem if it lasts too long

Lock escalation S S IX X Header Row Row Row IX X X Row X Row X >= 5000

Controlling Lock escalation 1. Switch the escalation level (per table) SELECT lock_escalation_desc FROM sys.tables WHERE name = 'Person.Address' ALTER  TABLE  Person.Address  SET  (LOCK_ESCALATION  =  {AUTO  |  TABLE  |  DISABLE} AUTO – Partition-level escalation if the table is partitioned TABLE – Always table-level escalation DISABLE – Do not escalate until absolutely necessary 2. Just disable it (that’s not Nike’s “Just do it!”) •  Trace flag 1211 – disables lock escalation on server level •  Trace flag 1224 – disables lock escalation if 40% of the memory used is consumed

What Are Deadlocks? Who is victim? •  Cost for Rollback •  Deadlock priority – SET DEADLOCK_PRIOIRTY Task A Task B Resource 1 Resource 2

Resolve blocking a.k.a live locking 1.  Keep the transactions as short as possible 2.  No user interactions required in the middle of the transaction 3.  Use indexes (proper ones) 4.  Consider a server to offload some of the workloads 5.  Choose isolation level

DEMO Monitor for locks with xEvents Lock escalation – both to table and partition Deadlock and the SET DEADLOCK_PRIORITY option

Transaction isolation levels

Read Uncommitted (pessimistic concurrency control) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (NOLOCK?) Transaction 1 eXclusive lock Update Select Transaction 2 Dirty read   Suggestion: Better offload the reads or go with optimistic level concurrency!

Repeatable Read (pessimistic concurrency control) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ Transaction 1 S(hared) lock Transaction 2 select Update   No non-repeatable reads possible (updates during Transaction 1)   Phantom records still possible (inserts during Transaction 1)

Serializable (pessimistic concurrency control) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE Transaction 1 S(hared) lock select Transaction 2 Insert   Even phantom records are not possible!   Highest pessimistic level of isolation, lowest level of concurrency

Optimistic Concurrency Based on Row versioning (stored inside tempdb’s version store area) •  No dirty, non-repeatable reads or phantom records •  Every single modification is versioned even if not used •  Adds 14 bytes per row Readers do not block writers and writers do not block readers Writers can and will block writers, this can cause conflicts

RCSI and SI (optimistic concurrency control) V1 Transaction 1 Select Transaction 2 V2 Select in RCSI Select in SI RCSI – Read Committed Snapshot Isolation Level •  Statement level versioning •  Requires ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON Snapshot Isolation Level •  Transaction level versioning •  Requires ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON •  Requires SET TRANSACTION ISOLATION LEVEL SNAPSHOT

DEMO Playing around with the Isolation levels

Summary 1. Blocking is something normal when it’s not for long 2. There are numerous of ways to monitor locking and blocking 3. Be extremely careful for lock escalations 4. Choosing the Isolation level is also a business decision!

Resources MCM Readiness videos on locking lecture and demo MCM Readiness video on Snapshot Isolation Level http://blogs.msdn.com/b/bartd/archive/tags/sql+locking http://www.sqlskills.com/blogs/paul/category/locking/ Lock hints http://www.techrepublic.com/article/control-sql-serverlocking-with-hints/5181472

THANK YOU!

Add a comment

Related presentations

Related pages

The nightmare of locking, blocking and isolation levels ...

×Close Share The nightmare of locking, blocking and isolation levels! Embed ...
Read more

SQLSaturday #384 - Varna 2015 > Sessions > Details

The nightmare of locking, blocking and isolation levels! ... can be a real nightmare! ... what problems it causes and how can we use isolation levels and ...
Read more

Locking Archives - Boris Hristov

The Nightmare of Locking, Blocking and Isolation Levels! from Boris Hristov. ... The Nightmare of Locking, Blocking and Isolation Levels! from Boris Hristov.
Read more

SQLSaturday #310 - Dublin 2014 > Sessions > Details

The nightmare of locking, blocking and isolation levels! Speaker(s): Boris Hristov ... can be a real nightmare! In this session, ...
Read more

Blocking Archives - Boris Hristov

The Nightmare of Locking, Blocking and Isolation Levels! from Boris Hristov. ... Blocking and Isolation Levels! from Boris Hristov. Filed Under: ...
Read more

SQLSaturday #278 - Budapest 2014 > Sessions > Details

The nightmare of locking, blocking and isolation levels! Speaker(s): Boris Hristov ... can be a real nightmare! In this session, ...
Read more

Isolation (database systems) - Wikipedia, the free ...

Isolation levels Of the four ACID ... Most DBMSs offer a number of transaction isolation levels, which control the degree of locking that ... thus blocking ...
Read more

SQLSaturday #313 - Rheinland 2014 > Event Home

The nightmare of locking, blocking and isolation levels! Boris Hristov. Always ON 2014. Frank Geisler.
Read more

The nightmare of locking, blocking and deadlocking ...

Transcript Header: The nightmare of locking, blocking and deadlocking. SQLSaturday #257, Verona Transcript Body: 1. Welcome to the nightmare of ...
Read more