Concurrency Control

1
Concurrency Control

• Presented by
• YOGESWARI JAYARAMAN (WGA060017)
• SHARMALADEVI VELOO (WGA060025)
• SUNTHARI SOLAIMALAI (WGA060020)
• THAMARAI SUBRAMANIAM (WGA060009)
• VIMALA SUBRAMANIAM (WGA060044)

2
Topics

• Introduction
• Concurrency Control Techniques
• Current Implementation in DBMS
• Future Trend Considerations
• Proposed Solution

3
What is Concurrency Control?

• Concurrency control refers to the process of
  managing simultaneous operations on the database
  without having them interfere with one another.
• Permits users to access a database in a
  multi-programmed fashion while preserving the
  illusion that each user is executing alone on a
  dedicated system.

4
Why Concurrency Control?

• When two or more users are accessing the database
  simultaneously and at least one is updating data,
  there may be interference that can result in
  inconsistencies.
• Problems caused by concurrency access
• Lost update problem
• Uncommitted dependency problem
• Inconsistent analysis problem

5
Concurrency Control

• The main objective of Concurrency Control is to
  schedule transactions in such a way to avoid any
  interference between them and hence prevent the
  above problems.
• Concurrency control is based on the following
  properties
• Serializability
• Non-serializability
• Serializability concurreny control traditionally
  can be classified
• Pessimistic Concurrency Control (PCC)
• Optimistic Concurrency Control (OCC)

6
Pessimistic Concurrency Control (PCC)

• Assumes that conflicts will happen, detects
  conflict as soon as they occur and resolve them
  using blocking.
• Pessimistic concurrency control uses locking
  techniques, when one transaction is accessing the
  database, a lock may deny access to other
  transaction to prevent incorrect result.

7
Pessimistic Concurrency Control (PCC)

• Advantages
• Simple to implement
• Database servers support and enforce locking
  mechanisms.
• Very secure
• Locking ensures nothing will be able to ignore
  the lock and change data in any way.
• Disadvantages
• Not very scalable
• Locking requires an open connection to database.
• Prone to deadlocks
• Occurs when two users decide to wait until the
  data is available.
• Livelock or starvation
• No one else can make changes until the first
  changes have been committed.

8
Optimistic Concurrency Control (OCC)

• Assumes that conflicts between transactions are
  rare, only detects and resolves conflicts when
  writing to the database.
• Optimistic concurrency control does not lock any
  data and transaction executed without
  restrictions and check for conflicts just before
  commit.

9
Optimistic Concurrency Control (OCC)

• Advantages
• Scalable
• Application no need to keep a connection open to
  the database.
• Simple to implement
• Especially using row version field.
• Little risk of deadlocks
• No need to keep locks
• Disadvantages
• Not very secure
• - Application may ignore a row version and update
  the data.
• Single-row oriented

10
Concurrency Control Techniques

• Three main techniques are
• Lock-Based Protocol
• Timestamp-Based Protocol
• Validation-Based Protocol

11
Lock-Based Protocol

• Locks deny access to other transactions to
  prevent incorrect updates
• Two-phase locking each transaction issues lock
  unlock requests in 2 phases
• Growing Phase
• New locks acquired, but none released
• Shrinking Phase
• Existing locks can be released, but no new ones
  acquired

12
Two-Phase Locking
Note Concurrency control techniques may
introduce cascading rollback deadlock
problem
13
Cascading Rollback
14
Deadlock
15
Timestamp-Based Protocol

• Transactions are ordered in a manner whereby
  older transactions are prioritized when conflict
  arises.
• Conflict is resolved by rolling back and
  restarting transaction.
• Timestamp is generated by
• Using system clock at time transaction started,
  OR
• Using logical counter every time new transaction
  starts

16
Timestamp-Based Protocol
17
Validation-Based Protocol

• Based on optimistic approach since check is only
  made at commit.
• If conflict occurs, transaction will be rolled
  back restarted.
• Each transaction, Ti has 3 phases in its
  lifetime
• Read phase
• Reads values in data items stores in temporary
  local variables
• Validation phase
• Performs validation test to ensure local
  variables can be written without causing
  violation of serializability
• Write phase
• If validation confirms Ti is successful, system
  applies updates. Otherwise, rolls back Ti.
• So, each transaction has 3 timestamps
  Start(Ti), Validation(Ti), and Finish(Ti)

18
Current Implementation in DBMS

• Oracle
• Multiversion with 2PL and Timestamp
• IBM DB2
• 2PL
• Microsoft SQL Server
• By default locking and so offers OCC
• PostgreSQL
• Multiversion (DML) and 2PL (DDL)

19
Oracle - An Example
User 1 issues select command delete
20
Oracle - An Example
User 2 issues select command
21
Oracle - An Example
User 1 issues commit
22
Oracle - An Example
User 2 issues select command
23
Oracle - An Example
User 1 User 2 issuing select command
24
Future Trend Considerations

• Mixed Concurrency Control
• Pessimistic Concurrency Control
• Performance evaluation of two shadow speculative
  concurrency control
• Distributed high priority two-phase locking
• Multiversion concurrency control for large scale
  service directory

25
Analysis

• PCC and OCC techniques
• Speculative concurrency control combines their
  advantages while avoiding their disadvantages.
• SCC able to detect conflict early and avoid
  unnecessary delays that may jeopardize their
  timely commitment.
• SCC-2S allows maximum of 2 shadows (primary
  shadow and stand-by shadow) per-uncommitted
  transaction to exist in system at any point of
  time

26
Analysis of SCC-2S
Schedule with a standby shadow promotion
27
Proposed Solution

• Value-cognizant SCC
• It is an improved version of SCC-2S whereby a
  transaction value is added.
• Make use of deadline priority information in
  resolving data conflicts or in making other
  scheduling.

28
Value-Cognizant SCC

• In real-time applications
• Different transaction will be assigned different
  value.
• Cashes on full value of a transaction if it is
  committed on time. Otherwise, a penalty is
  accessed.
• Maximizes the value-added to the system by the
  transactions commitment minimizes delay or the
  number of missed deadlines.
• Each transaction, Tu is associated with a value
  function Vu(t) which represents the value of Tu
  as a function of its completion (commit) time.

29
Penalty gradient
Penalty gradient is the rate at which a
transaction loses its value when it commits past
the deadline.
30
Value-Cognizant Applications

• SCC with Deferred Commit (SCC-DC)
• introduces delays by giving primary shadows more
  time to execute and commit instead of being
  aborted in favour of validating transactions of
  lesser worth.
• SCC with Voted Waiting (SCC-VW)
• allow uncommitted transactions to vote for or
  against the commitment of a finished transaction
  based on the expected value-added to the system.

31
Evaluation - Simulation Results

• Performance Objective 1
• to minimize the Missed Ratio
• Percentage of transactions that missed their
  deadlines

32
Evaluation - Simulation Results

• Performance Objective 2
• to minimize the Average Tardiness
• Average time by which late transactions missed
  their deadlines

33
Conclusion

• Extensive research throughout this project shows
  that each concurrency control technique has its
  strengths and weaknesses. Therefore, ability to
  identify pros and cons of the technique,
  manipulating it and continuous improvement are
  the critical success factors towards an ideal
  concurrency control.

34
The End
Thank You!