Transcript Document
Does the Optimistic Concurrency
resolve your blocking problems
Margarita Naumova, SQL Master Academy
About me
Working with SQL Server from v6.5
SQL Server Trainer and Consultant with over 60 projects in Bulgaria,
Finland, Germany, UK, Sweden…
5 years Senior Consultant in Microsoft, member of Microsoft
Worldwide Technical Leadership Team
BG SQL UG Founder and Leader
Now teaching and presenting SQL Server, running SQL Master
Academy Training Program - www.SQLMasterAcademy.com and
SQL Server consulting company - www.inspirit.bg
Approaching the blocking today
•
What’s the problem
– The blocking drama and how to isolate it
– The main reason, the common occurrence
•
How we are used to resolve the problems while working with pessimistic
concurrency levels
–
•
Some working and not so well working solutions
Optimistic Concurrency
–
–
how it works, when it could be a solution and in what cases it could generate problems
Side effects and thinks to consider
The drama of blocking
Locking and blocking are often confused!
Locking
•
•
The action of taking and potentially holding locks
Used to implement concurrency control between transactions
Blocking is a result of long time locking!
•
•
•
One process needs to wait for another process to release locked resources
In a multiuser environment, there is always blocking!
Only a problem if it lasts too long
DEMO
• Blocking and how to get info about it
The common reasons of
blocking
• Writers block readers and vise versa
•
•
•
•
•
Resource blocking - performance
Lock escalation
Lock duration
Default pessimistic isolation level – holding eXclusive lock too early
Increasing isolation level to RR because the need of consistent
reading
• Serializable specific cases – no index or using EF
• Schema blocking
• Writers block writers (concurrent writing – OLTP)
How we are used to deal with blocking in
pessimistic concurrency
• Controlling lock escalation with ALTER TABLE
– ALTER TABLE Person.Address SET (LOCK_ESCALATION = {AUTO | TABLE |
DISABLE)
– Trace flag 1211 – disables lock escalation on server level
– Trace flag 1224 – disables lock escalation on server level until 40% of the
memory used is consumed
• Controlling Lock duration with SET LOCK TIMEOUT n(sec)
– Error 1222!!!
• Reading dirty data with hints – NO_LOCK, READPAST
– Data consistency!!
How we are used to deal with blocking in
pessimistic concurrency
• Consider solutions design changes
– Partitioning
– Workload separation using 2012 AG or other technologies like
replication
– In Memory OLTP tables in SQL Server 2014
• OK, but it takes additional resources!
Optimistic Concurrency
•
Based on Row versioning
–
When updating a row, the previous version is stored in the version store
–
The new row contains a pointer to the old row in the version store
V1
Transaction 1
V2
Select
Transaction 2
•
Adds 14bytes to every row
•
Needs Version Store i.e. TempDB space
•
Readers do not block writers and writers do not block readers.
•
BUT writers can and will block writers, this can cause conflicts.
Implementation – RCSI and SI
•
RCSI
–
Statement-Level versioning, i.e. any query sees the last version of data as of the beginning of the statement
–
Requires ALTER ATABASE SET READ_COMMITTED_SNAPSHOT ON
V1
Transaction 1
Select
Transaction 2
•
V2
Select in RCSI
Select in SI
Snapshot Isolation Level
–
Session Level versioning, i.e. the most recent committed version of the rows as of the beginning of the transaction
–
Requires ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON and SET TRANSACTION ISOLATION
LEVEL SNAPSHOT
–
If used then better together with RCSI! Else, you have drawbacks of versioning only!
–
Conflicts occur!!
–
Some DDL statements are not allowed within SI transactions (no metadata versioning exists)
DEMO
• Optimistic Concurrency
Managing Version store
•
The Version Store
–
–
•
SQL Server starts generating versions in tempdb as soon as a database is enabled for one of the
snapshot-based isolation levels
Maintains link lists of rows, the end of the list is the oldest version of that particular row
Managing Version Store
–
–
–
SQL Server maintains a clean-up thread
If file cannot grow, a snapshot query fails (tempdb growth errors)
Monitoring counters
•
Free Space in tempdb
•
Version Store Size
•
Version Generation Rate and Version Cleanup Rate
•
Longest Transaction Running Time
•
Snapshot Transactions
[size of common version store] = 2 * [version store data generated per minute] * [longest running time
(minutes) of the transaction]
Choosing a Concurrency Model
•
RCSI
–
Consumes less tempdb space than SI
–
Works with distributed transactions; SI does not
–
Does not produce update conflicts
–
Does not require any change in your applications. Any of your applications written using the
default Read Committed isolation level automatically uses RCSI after making the change at
the database level
•
•
(you should always test first, check the complex Begin..commits)
Consider using SI when
–
The probability is low that any of your transactions have to be rolled back because of an
update conflict.
–
You have reports that need to be generated based on long-running, multistatement queries
that must have point-in-time consistency
Benefits of Optimistic
Concurrency
• SELECT operations do not acquire shared locks, so readers and
•
•
•
•
writers do not block each other.
All SELECT operations retrieve a consistent snapshot of the data.
The total number of locks needed is greatly reduced compared to
pessimistic concurrency, so less system overhead is used.
SQL Server needs to perform fewer lock escalations.
Deadlocks are less likely to occur
Costs of Optimistic Concurrency
•
•
•
•
•
•
SELECT performance can be affected negatively when long-version chains
must be scanned.
The older the snapshot, the more time it takes to access the required row in
an SI transaction.
Row versioning requires additional resources in tempdb. The space in
tempdb must be carefully managed
Row versioning information increases the size of every affected row by 14
bytes.
UPDATE performance might be slower due to the work involved in
maintaining the row versions
Applications must be programmed to deal with any update conflicts that
occur in SI
What optimistic concurrency doesn’t
resolve
• Writers block writers cases in high intensive OLTP
workloads
• Resource blockings (you need to perform resource
optimizations first)
• Lock escalation (especially X locks)
• Schema blocking
• In-Memory OLTP Memory-optimized Tables is a special
case!
Thank you!
www.maginaumova.com
www.sqlmasteracademy.com
http://bg.linkedin.com/pub/margaritanaumova/9/965/206