Transcript The_Family_Affairxx

LOCKS, BLOCKS, AND DEADLOCKS; TAME
THE SIBLING RIVALRY
SQL SERVER FAMILY MANAGEMENT
~ Wolf ~
About Wolf
• DBA for nearly 17 years
• At RDX for over 6 years.
“Manager – SQL Server
Performance Tuning”
• Works with a variety of
clients and challenges
Resources to use:
#sqlsatNash
#sqlhelp
#sqlserver
http://1.bp.blogspot.com/ai6uXFZl9ck/UpOdtP9kRwI/AAAAAAAAJ64/D9ZbgZL0SFg/s1600/charliebrownthanks2.jpg
What we will focus on
1
• ACID
2
• Isolation Levels
3
• Locking Explained
4
• Deadlock Defined
A.C.I.D
ACID
A
• Atomicity – All or None
C
• Consistency – All intended to be
affected, is affected
I
• Isolation – Clean your own room
D
• Durability – Your daughter spilled
coke onto database, data still there
ISOLATION LEVELS
• In SQL Server, and other RDBMS, the engine
implements the configured isolation level which will
affect the ACIDity when reading data. The isolation
level will isolate the process and protect it from
other transactions. To do this, locking is required.
The locking is determined by the isolation level. Low
levels of isolation will give high levels of concurrency
and shared use of the data, but can result in lower
levels of consistency with dirty or phantom reads.
• SQL Server will use isolation levels to command the
read behavior of the database/transaction.
Read Uncommitted
• Read data from
other transactions
yet completed
•Dirty Reads
•Uncommitted
updates
http://www.peanuts.com/wp-content/themes/desktop-themepeanuts/images/characters/round/pigpen.png
Read Committed
•SQL Server Default
•Prevents reading
Data from
uncommitted
transactions
•Can result in
Phantom
Reads/Repeatable
Reads
http://www.peanuts.com/wp-content/themes/desktop-themepeanuts/images/characters/round/charliebrown.png
Repeatable Read
• This will ensure that if data is
reread within a transaction,
that the data does not change
• No transaction can modify the
data until the transaction
reading the data is complete
• This comes at the cost that all
shared locks in the read will hold
until the transaction is
completed
• This will eliminate Dirty and
Non-Repeatable reads, but
Phantom reads may still occur.
http://www.peanuts.com/wp-content/themes/desktop-themepeanuts/images/characters/round/woodstock.png
Serializable
• This is putting the
transactions in a single
line.
• This is the most consistent
and best way to ensure for
ACIDity.
• A read/write lock will be
issued for all of the records
affected by the transaction
• This includes Repeatable
Read isolation and will also
eliminate Phantom reads.
http://images.fanpop.com/images/image_uploads/Linus-peanuts-239722_366_360.gif
Snapshot
• This will read the data as it is
at the beginning of the
transaction.
• This will come at a high cost
to tempdb as the data for the
snapshot is stored in tempdb.
• This will eliminate Dirty,
Phantom, Non-Repeatable
Read and Lost updates
• Introduces a 14 byte addition
to each row on the page.
Could cause increased page
splits, causing fragmentation.
http://www.peanuts.com/wp-content/themes/desktop-themepeanuts/images/characters/round/snoopy.png
CONCURRENCY ISSUES
• Lost Updates
• Dirty Reads
• Non-Repeatable Reads
• Phantom Reads
http://biglizards.net/Graphics/ForegroundPix/CharlieBrownFootball.jpg
LOCKING EXPLAINED
• Locking is a mechanism used by the SQL Server Database
Engine to synchronize access by multiple users to the
same piece of data at the same time. Locks are managed
internally by a part of the Database Engine called the lock
manager.
• Each transaction will lock levels of resources such as
rows, pages or tables for which the transaction is
dependent. The transaction will release the lock when it
either commits or rolls back. The amount of time the
lock is held is dependent on the isolation level.
• The two basic types of locks are read locks and write
locks.
Shared Read Lock
• Shared -While a shared
lock is being held other
transactions can read but
cannot modify locked
data. The lock is released
after the locked data has
been read unless the
isolation level is at or
higher than Repeatable
Read or a locking hint such
as READCOMMITED or
READCOMMITTEDLOCK is
used.
https://s-media-cacheak0.pinimg.com/originals/bb/e1/73/bbe1738a6187d68058f02ffeb6160113.gif
Concurrent Read Lock
•Concurrent - This is
when you read data
using read data held
by exclusive lock
only by specifying a
NOLOCK hint or
using a read
uncommitted
isolation level
http://vignette1.wikia.nocookie.net/peanuts/images/a/a2/Pigpen_peanuts.png/revision/latest?cb=20090301044242
Update Write Lock
• Update - Update locks are a mix
of shared and exclusive locks.
When an update statement is
executed, SQL Server has to find
the data it wants to modify first,
so to avoid lock conversion
deadlocks an update lock is
used. Only one update lock can
be held on the data at one time,
similar to an exclusive lock.
The update lock itself can't
modify the underlying data,
when the data is modified, it is
transferred to an exclusive lock.
http://www.peanuts.com/wp-content/themes/desktop-themepeanuts/images/characters/round/schroeder.png
Exclusive Write Lock
• Exclusive - Exclusive locks
are used to lock data being
modified by one
transaction thus
preventing modifications
by other concurrent
transactions. A transaction
always gets an exclusive
lock on any data it
modifies, and holds that
lock until the transaction
completes, regardless of
the isolation level set for
that transaction.
http://www.peanuts.com/wp-content/themes/desktop-themepeanuts/images/characters/round/lucy.png
LOCKING EXAMPLE
LOCK GRANULARITY/ESCALATION
•
•
•
Lock Escalations are an optimization technique
used by SQL Server to control the amount of locks
that are held within the Lock Manager of SQL
Server.
Lock granularity consists of DATABASE, TABLE,
PAGE and ROW locks. As soon as you connect to a
SQL Server database, a shared lock is issued on the
database level itself. This prevents another user
from performing an action such as "DROP
DATABASE" while there is a connection to it.
SQL Server will perform it's lock granularity from
top down. First, it will check if a table lock is
necessary, then page then row. An Intent(Shared
or Exclusive), dependent if the operation is read or
write, is issued on the table and page and a shared
lock on the row. If the amount of data necessary is
more than a row and on a page. Once SQL Server
needs more than 5,000 row locks, it will
escalate(by default) to a table lock.
http://p-fst1.pixstatic.com/5069de44dbd0cb3071000bba._w.1500_s.fit_.jpg
• You can alter these defaults by either using query
hints such as ROWLOCK, PAGLOCK or TABLOCK. You
can also alter the lock escalation of each table by
using the following:
• ALTER TABLE MyTable SET LOCK_ESCALATION =
<AUTO, TABLE, DISABLE>
• This is generally not recommended as it is best to
allow the Database Engine to escalate locks
accordingly. Lower level locks increase concurrency,
but consume more memory
DEADLOCKING
http://vignette1.wikia.nocookie.net/peanuts/images/4/40/SnoopyVsTheRe
dBaron.jpg/revision/latest?cb=20111221012356
DEADLOCKING
http://3.bp.blogspot.com/dkwFt59JX1o/VDPG3B3YR6I/AAAAAAAAAE4/l9IPRK6NeEk/s1600/mo
des-of-deadlock.png
Which child is easiest to deal with
when they don’t get what they
want?
• The Database Engine will check for
deadlocks in intervals of 5 seconds as a
default. The transaction that is chosen as
the victim is the one that either has the
lower DEADLOCK_PRIORITY which may be
set on the session explicitly or the
transaction that is least expensive to
rollback.
REPORTING ON DEADLOCKS
https://leduc998.files.wordpress.com/2011/04/snoopy-typing-away-1cvv14j0d95-1024x7685.jpg
Trace Flag
• There are various ways to report on
deadlocks. The simplest is the using the trace
flag 1222. This will record the deadlock in the
SQL Server Error Log in XML format. To turn the
trace on or off, use the following.
• DBCC TRACEON(1222,-1); DBCC
TRACEOFF(1222,-1);
• Once the trace flag is set, when a deadlock
occurs, you can read from the SQL Server Error
Log
Default Extended Events
• Another way to report on deadlocks is to
use the default extended event from the
ring buffer. I tend to avoid the ring buffer as
I find it less than dependable.
@SQLWAREWOLF Custom Reporting
• I have written a full deadlock reporting structure for which I am going
to have here to share. To be clear, these scripts and procedures are
written by myself and may be shared with that copyright
knowledge. They are AS IS and no warranty or support is included. I
am NOT liable for ANY damages using these scripts may cause. Use at
your own risk and always test THOROUGHLY on a development system
before implementing in production.
• This process will use a defined extended event to record deadlocks to a
configurable disk location. A job will read the events and parse the
XML into a reporting structure. A final stored procedure is then ran to
see the results of the deadlock report.
• The deadlock analysis also has the capability to obfuscate the
results. What that means is that if the same query that is the victim 5
times has only a string difference, the report should show the result
only once.
DEADLOCKING EXAMPLE
AVOIDING DEADLOCKS
http://vignette2.wikia.nocookie.net/peanuts/images/6/61/Charlie-brownchristmas3.jpg/revision/latest?cb=20101204081339
References:
• https://technet.microsoft.com/enus/library/ms178104(v=sql.105).aspx
• http://www.sqlteam.com/article/introductionto-locking-in-sql-server#sthash.ujDqLUPK.dpuf
• http://blogs.msdn.com/b/sqlcat/archive/2011/
02/20/concurrency-series-basics-of-transactionisolation-levels.aspx
• https://technet.microsoft.com/enus/library/jj856598(v=sql.110).aspx
QUESTIONS?