Transcript where AnimalID = 1

Sofia, Bulgaria | 9-10 October
Concurrency Management –
ADO.NET 2.0
Presented By: Sahil Malik
http://www.winsmarts.com
About Me
● Microsoft MVP (Visual C#), INETA
speaker, telerik Technical Evangelist
● Author three books.
● Reviewer on several.
● Available for technical consulting or
training for your organization.
● Contact me at www.winsmarts.com
(that’s smart”S” with an “S”)
Sofia, Bulgaria | 9-10 October
What is a concurrency conflict?
Sofia, Bulgaria | 9-10 October
What is a concurrency conflict?
Sofia, Bulgaria | 9-10 October
What is a concurrency conflict?
Sofia, Bulgaria | 9-10 October
Database Example
PersonID Expenditure
1
$100.00
Spend $20 - Twice
PersonID
Expenditure
1
$120.00
PersonID
Expenditure
1
$120.00
Sofia, Bulgaria | 9-10 October
Solution
● Send in only the change – let the
database do the addition?
● Good approach – but might not solve
every situation.
Sofia, Bulgaria | 9-10 October
Concurrency Resolution
● Design the database right.
● Staging Area
● Journaling database
● Always unique primary keys
● Request a number of keys in advance
● Use GUIDs
● Use a Seed Generator Table
● This prevents conflicts, but does not
eliminate them.
Sofia, Bulgaria | 9-10 October
Conflict happens !!
● Pessimistic Locking
● Optimistic Concurrency Control
Sofia, Bulgaria | 9-10 October
Pessimistic Locking
Sofia, Bulgaria | 9-10 October
Pessimistic Locking - Database
Select
Finger
From
GirlFriends HOLDLOCK
Where
GirlfriendName = ‘Jane’
Sofia, Bulgaria | 9-10 October
Pessimistic Locking – ADO.NET
● Isolation Level = RepeatableRead or
● Isolation Level = Serializable
● Or just execute the HOLDLOCK
command.
Sofia, Bulgaria | 9-10 October
Pessimistic Locking - Advantages
● You can be sure nobody touched your
record.
● Apparently simple programming model
● Intuitive approach – but don’t do it !!!
● SERIOUSLY !! DON’T DO IT !!
● But Why?
Sofia, Bulgaria | 9-10 October
Pessimistic Locking - Downsides
● The girl can’t date anybody else –
nobody else can access that database
row.
● Erick could have died in a war – the
program user could have locked a row
and left for lunch, who unlocks the
row?
● Deadlocks
● A serious hit on performance.
Sofia, Bulgaria | 9-10 October
Pessimistic Locking - Alternatives
● If you must use pessimistic locking, instead
try and use –
● Server side cursors. Position the cursor
on the row you wish to update, and thus
always work with the latest possible
value.
● Create an IsLocked column, and have
application logic read that column and act
accordingly. Similar to checkin/checkout.
● You are technically disconnected this way,
but you still have to worry about timeouts,
and unexpected application crashes, but at
least the performance is better.
Sofia, Bulgaria | 9-10 October
Optimistic Concurrency
● Various flavors of optimistic
concurrency exist.
● Primary Keys a.k.a. Last In Wins !!
● Primary Keys + Changed Columns
● Primary Keys + All Columns
● Primary Keys + Timestamp/rowversion
● Show me some code man !!
Sofia, Bulgaria | 9-10 October
Optimistic Concurrency
● Let us examine each concurrency
model one by one.
● First we need a database structure.
● Three tables, hierarchically arranged
● Animals
● Pets
● PetBelonging
● Just run Sql\database setup script.sql
Sofia, Bulgaria | 9-10 October
Database Diagram
Sofia, Bulgaria | 9-10 October
Code Example #1
● Create an application using Drag Drop that
works with the Pets table.
● Run two instances, execute parallel inserts
● You will see that the primary key is
automatically resolved.
● Next run two instances, and execute parallel
updates.
● You should get a concurrency violation.
● Demonstrate the various commands being
generated by the TableAdapter
Sofia, Bulgaria | 9-10 October
PetsTableAdapter.InitAdapter
● Demonstrate the DeleteCommand,
InsertCommand, UpdateCommand
texts in PetsTableAdapter.InitAdapter
● These commands check for the
primary key and all columns involved.
● The most database portable
concurrency check.
● First in wins !!
● Not too efficient.
Sofia, Bulgaria | 9-10 October
Optimistic Concurrency – Only PK
● Check only for primary keys.
● Effectively, don’t check for
concurrency.
Sofia, Bulgaria | 9-10 October
Database
AnimalID
AnimalType AnimalWeight
1
Puppy
3 lbs
AnimalID
AnimalType
AnimalWeight
1
Dog
50 lbs
AnimalID
AnimalType AnimalWeight
1
Mutt
50 lbs
Sofia, Bulgaria | 9-10 October
Database
AnimalID
AnimalType
AnimalWeight
1
Dog
50 lbs
AnimalID
AnimalType
AnimalWeight
1
Mutt
50 lbs
Sofia, Bulgaria | 9-10 October
Optimistic Concurrency – Only PK
-- Erick’s Select query
Select AnimalID, AnimalType, AnimalWeight
from Animals
-- Frans’ Select query
Select AnimalID, AnimalType, AnimalWeight
from Animals
-- Erick’s update query
Update Animals Set AnimalType = 'Dog',
AnimalWeight = '50 lbs' where AnimalID = 1
-- Frans’ update query
Update Animals Set AnimalType = 'Mutt',
AnimalWeight = '50 lbs' where AnimalID = 1
Sofia, Bulgaria | 9-10 October
Optimistic Concurrency – Only PK
● Database portable, but doesn’t do
anything to prevent data corruption.
● Good performance (because it doesn’t
do anything).
Sofia, Bulgaria | 9-10 October
OC – PK + Changed Columns
● This approach checks the PK and all
changed columns.
● It is database portable.
● But it does not guarantee reliability in
all situations.
Sofia, Bulgaria | 9-10 October
Database
AnimalID
AnimalType AnimalWeight
1
Puppy
3 lbs
AnimalID
AnimalType
AnimalWeight
1
Dog
50 lbs
1
Puppy
3 lbs
AnimalID
AnimalType
AnimalWeight
1
Puppy
60 lbs
1
Puppy
3 lbs
Sofia, Bulgaria | 9-10 October
Database
AnimalID
AnimalType
AnimalWeight
1
Dog
50 lbs
1
Puppy
3 lbs
AnimalID
AnimalType
AnimalWeight
1
Puppy
60 lbs
1
Puppy
3 Sofia,
lbs Bulgaria | 9-10 October
OC –PK + Changed Columns
-- Erick’s Select query
Select AnimalID, AnimalType, AnimalWeight from Animals
-- Frans’ Select query
Select AnimalID, AnimalType, AnimalWeight from Animals
-- Erick’s update query
Update Animals
Set AnimalType = 'Dog', AnimalWeight = '50 lbs'
where
AnimalID = 1 and AnimalWeight = '3 lbs' and AnimalType
= 'Puppy'
-- Frans’ update query, this will now fail.
Update Animals
Set AnimalWeight = '60 lbs'
where
AnimalID = 1 and AnimalWeight = '3 lbs'
Sofia, Bulgaria | 9-10 October
OC – PK + Changed Columns
● Better performance than checking all
columns.
● Database portable.
● But this approach has a sinister hole !!!
Sofia, Bulgaria | 9-10 October
-- Erick’s Select query
Select AnimalID, AnimalType, AnimalWeight from
Animals
-- Frans’ Select query
Select AnimalID, AnimalType, AnimalWeight from
Animals
-- Erick’s update query
Update Animals
Set AnimalType = 'Dog', AnimalWeight = '50 lbs'
where
AnimalID = 1 and AnimalWeight = '3 lbs' and
AnimalType = 'Puppy'
-- Frans’ update query, this will now fail.
Update Animals
Set AnimalWeight = '60 lbs'
where
AnimalID
= 1 do
and
AnimalWeight = '3 lbs'
So what
does Frans
next?
Sofia, Bulgaria | 9-10 October
-- Frans’ Select query (Second pass)
Select AnimalID, AnimalType, AnimalWeight from Animals
-- Sushil’s Select query
Select AnimalID, AnimalType, AnimalWeight from Animals
-- Sushil’s Update query
Update Animals
Set AnimalType = 'Monkey'
where
AnimalID = 1 and AnimalType = 'Dog'
-- Frans’ update query
Update Animals
AnimalWeight = '60 lbs'
where
AnimalID = 1 and AnimalWeight = '50 lbs'
-- Pablo’s Select query (Second pass)
Select AnimalID, AnimalType, AnimalWeight from Animals
Sofia, Bulgaria | 9-10 October
Final Results
Erick – Dog
50 lbs
Sushil – Monkey
50 lbs
Frans – Dog
60 lbs
Sofia, Bulgaria | 9-10 October
Final Actual Database Results
Pablo – Monkey – 60 lbs
Sofia, Bulgaria | 9-10 October
Optimistic Concurrency Timestamps
● Sql Server specific feature.
● Sql server supports a column type
called timestamp.
● Timestamp is actually rowversion
● T-SQL Timestamp = SQL-92 standard’s
datetime.
● One day in the future microsoft may
decide to deprecate timestamp and ask
you to use rowversion instead.
Sofia, Bulgaria | 9-10 October
Code demonstration
● Show how the following work
● UpdateCommand
● DeleteCommand (Beta2 bug)
● InsertCommand
● Show the significance of
● AcceptChangesDuringUpdate
● ContinueUpdateOnError
Sofia, Bulgaria | 9-10 October
Code Demonstration
● Hierarchical Updates and Hierarchical
update concerns.
● Insert Order
● Update Order
● Delete Order
● Concurrency in Hierarchical updates.
Sofia, Bulgaria | 9-10 October
End Notes
● Thank you !!
● Questions?
● Contact me –
● http://www.winsmarts.com/contact.aspx
Sofia, Bulgaria | 9-10 October