Concurrency Control: Optimism or Pessimism?

1
Concurrency Control Optimism or Pessimism?

• Zachary G. Ives
• University of Pennsylvania
• CIS 650 Implementing Data Management Systems
• September 25, 2008

2
Administrivia

• The next assignment
• Read the ARIES paper, Sections 1.1, 3-6
• Review Kung Robinson, especially considering
  the question of whether optimistic CC deserves to
  be reconsidered in todays distributed
  architectures

3
Switching Gears

• You now know how to build a query-only DBMS, from
  physical page layout through query optimization
• But what if we want to build an updatable DBMS?
• The major issues concurrency (today) and
  recovery (next time)

4
Recall the Fundamental Concepts of Updatable DBMSs

• ACID properties
• atomicity, consistency, isolation, durability
• Transactions as atomic units of operation
• always commit or abort
• can be terminated and restarted by the DBMS an
  essential property
• typically logged, as well discuss later
• Serializability of schedules

5
Serializability and Concurrent Deposits
Deposit 1 Deposit
2 read(X.bal)
read(X.bal) X.bal X.bal 50 X.bal
X.bal 10 write(X.bal)
write(X.bal)
6
Violations of Serializability

• Dirty data data written by an uncommitted
  transaction a dirty read is a read of dirty data
  (WR conflict)
• Unrepeatable read a transaction reads the same
  data item twice and gets different values (RW
  conflict)
• Phantom problem a transaction retrieves a
  collection of tuples twice and sees different
  results

7
Two Approaches to Serializability (or other
Consistency Models)

• Locking a pessimistic strategy
• First paper (Gray et al.) hierarchical locking,
  plus ways of compromising serializability for
  performance
• Optimistic concurrency control
• Second paper (Kung Robinson) allow writes by
  each session in parallel, then try to substitute
  them in (or reapply to merge)

8
Locking

• A lock manager that grants and releases locks
  on objects
• Two basic types of locks
• Shared locks (read locks) allow other shared
  locks to be granted on the same item
• Exclusive locks (write locks) do not coexist
  with any other locks
• Generally granted in two phase locking model
• Growing phase locks are granted
• Shrinking phase locks are released (no new
  locks granted)
• Well-formed, two-phase locking guarantees
  serializability
• (Note that deadlocks are possible in this model!)

9
Gray et al. Granularity of Locks

• For performance and concurrency, want different
  levels of lock granularity, i.e., a hierarchy of
  locks
• database
• extent
• table
• page
• row
• attribute
• But a problem arises
• What if T1 S-locks a row and T2 wants to X-lock a
  table?
• How do we easily check whether we should give a
  lock to T2?

10
Intention Locks

• Basic types
• Intention to Share (IS) a descendant item will
  be locked with a share lock
• Intention Exclusive lock a descendant item will
  be locked with an exclusive lock
• Locks are granted top-down, released bottom-up
• T1 grabs IS lock on table, page S lock on row
• T2 cant get X-lock on table until T1 is done
• But T3 can get an IS or S lock on the table
• SIX need to read, e.g., an table to find an
  item that we will get X access on

11
Lock Compatibility Matrix
IS IX S SIX X
IS Y Y Y Y N
IX Y Y N N N
S Y N Y N N
SIX Y N N N N
X N N N N N
12
Lock Implementation

• Maintain as a hash table based on items to lock
• Lock/unlock are atomic operations in critical
  sections
• First-come, first-served queue for each locked
  object
• All adjacent, compatible items are a compatible
  group
• The groups mode is the most restrictive of its
  members
• What if a transaction wants to convert (upgrade)
  its lock? Should we send it to the back of the
  queue?
• No will almost assuredly deadlock!
• Handle conversions immediately after the current
  group (unless the conversion is already
  compatible)

13
Degrees of Consistency

• Full locking, guaranteeing serializability, is
  generally very expensive
• So they propose several degrees of consistency as
  a compromise (these are roughly the SQL isolation
  levels)
• Degree 0 T doesnt overwrite dirty data of
  other transactions
• Degree 1 above, plus T does not commit writes
  before EOT
• Degree 2 above, plus T doesnt read dirty data
• Degree 3 above, plus other transactions dont
  dirty any data T read

14
Degrees and Locking

• Degree 0 short write locks on updated items
• well-formed wrt writes
• Degree 1 long write locks on updated items
• well formed wrt writes 2-phase wrt writes
• Degree 2 long write locks on updated items,
  short read locks on read items
• well formed and 2-phase wrt writes
• Degree 3 long write and read locks
• well formed and 2-phase
• Does Degree 3 prevent phantoms? If not, how do
  we fix this?

15
What If We Dont Want to Lock?

• Conflicts may be very uncommon so why incur the
  overhead of locking?
• Typically hundreds of instructions for every
  lock/unlock
• Examples read-mostly DBs large DB with few
  collisions append-mostly hierarchical data
• Proposition break lock into three phases
• Read and write to private copy of each page
  (i.e., copy-on-write)
• Validation make sure no conflicts between
  transactions
• Write swap the private copies in for the public
  ones

16
Validation

• Goal guarantee that only serializable schedules
  result in merging Ti and Tj writes
• Approach find an equivalent serializable
  schedule
• Assign each transaction a number
• Ensure equivalent serializable schedule as
  follows
• If TN(Ti) lt TN(Tj) then we must satisfy one of
• Ti finishes writing before Tj starts reading
  serial WR
• WS(Ti) disjoint from RS(Tj) and Ti finishes
  writing before Tj writes WW
• WS(Ti) disjoint from RS(Tj) and WS(Ti) disjoint
  from WS(Tj), and Ti finishes read phase before Tj
  completes its read phase RW

17
Why Does This Work?

• Condition 1 obvious since its serial
• Condition 2
• No W-R conflicts since disjoint
• In all R-W conflicts, Ti precedes Tj since Ti
  reads before it writes (and thats before Tj)
• In all W-W conflicts, Ti precedes Tj
• Condition 3
• No W-R conflicts since disjoint
• No W-W conflicts since disjoint
• In all R-W conflicts, Ti precedes Tj since Ti
  reads before it writes (and thats before Tj)

18
The Achilles Heel

• How do we assign TNs?
• Not optimistically they get assigned at the end
  of read phase
• Note that we need to maintain all of the read and
  write sets for transactions that are going on
  concurrently long-lived read phases cause
  difficulty here
• Solution bound buffer, abort and restart
  transactions when out of space
• Drawback starvation solved by locking the
  whole DB!

19
Serial Validation

• Simple writes wont be interleaved, so test
• Ti finishes writing before Tj starts reading
  (serial)
• WS(Ti) disjoint from RS(Tj) and Ti finishes
  writing before Tj writes
• Put in critical section
• Get TN
• Test 1 and 2 for everyone up to TN
• Write
• Long critical section limits parallelism of
  validation, so can optimize
• Outside critical section, get a TN and validate
  up to there
• Before write, in critical section, get new TN,
  validate up to that, write
• Reads no need for TN just validate up to
  highest TN at end of read phase (no critical
  section)

20
Parallel Validation

• For allowing interleaved writes
• Save active transactions (finished reading, not
  writing)
• Abort if intersect current read/write set
• Validate
• CRIT Get TN copy active set add self to
  active set
• Check (1), (2) against everything from start to
  finish
• Check (3) against all active set
• If OK, write
• CRIT Increment TN counter, remove self from
  active
• Drawback might conflict in condition (3) with
  someone who gets aborted

21
Whos the Top Dog?Optimistic vs. Non-Optimistic

• Drawbacks of the optimistic approach
• Generally requires some sort of global state,
  e.g., TN counter
• If theres a conflict, requires abort and full
  restart
• Study by Agrawal et al. comparing optimistic vs.
  locking
• Need load control with low resources
• Locking is better with moderate resources
• Optimistic is better with infinite or high
  resources

22
Reminder

• The next assignment
• Read the ARIES paper, Sections 1.1, 3-6
• Review Kung Robinson, especially considering
  the question of whether optimistic CC deserves to
  be reconsidered in todays distributed
  architectures