Concurrency Control Using NonLocking Methods

1
Concurrency Control Using Non-Locking Methods

• -by
• Rajesh Mulampaka.

2
INTRODUCTION

• A major objective in developing a database is to
  allow
• concurrent access to the shared data for
  all the users who are accessing the database.
• Concurrency control in database management
  systems (DBMS) must ensure that all the
  transactions are performed without the integrity
  of the database getting violated.
• All the transactions must ensure that they follow
  the ACID rules.
• The DBMS must guarantee that only serializable
  schedules are generated and also no committed
  transactions must be lost while we are using
  these methods.
• Most of the concurrency control algorithms are
  divided into two main categories locking and
  non-locking.

3
Problems in locking methods.

• A transaction must hold exclusive locks on all
  data items that it may update at some time during
  the execution of the transaction, thereby
  potentially reducing the level of concurrency in
  the system.
• Deadlock A situation that may result when two
  (or more) transactions are each waiting for locks
  to be released that are held by each other.
• Indefinite delays will cause the transactions to
  run slow, when the transactions involving more
  dependencies are executed, especially this
  creates a problem if it is online transaction
  processing where many users are trying to hit the
  database.

4
Non-locking Concurrency Control

• A non-locking concurrency control is a mechanism
  which manages the concurrency without using locks
  on the data items.
• There are several non-lock concurrency control
  methods
• Optimistic concurrency control
• Timestamp-based concurrency control
• Multiversion concurrency control

5
Optimistic Concurrency Control

• The OCC technique is based on the assumption that
  conflicts are rare in transactions.
• This helps to avoid the additional processing
  required to ensure serializability.
• When a transaction wishes to commit it just
  checks whether there is any conflicts or not, if
  there is any then the transaction must be rolled
  back and restarted.
• This method could be expensive if the
  transactions have conflicts because the cost of
  rolling back and restarting the transaction in
  this method is very high.
• So most of the transactions are assumed to be
  conflict free and the method is followed. These
  kind of techniques allow us to achieve greater
  concurrency provided with their limitations.

6

• Read Phase The transactions read values from the
  database, storing them in local variables and
  they edit these values. These values are mirrored
  to the local copy and not the database.
• Validate Phase When the transaction has
  completed updating the values in its local
  variables, it ensures that serializability in not
  violated, when the changes are made back to the
  database. If there is any kind of conflict exists
  between the local variables and the items which
  are already in the database then the transaction
  must be rolled back and restarted and this should
  be done so that we minimize the number of changes
  made by the user or as a last resort, the entire
  transaction can be aborted (resulting in the loss
  of all changes made by the user).
• Write Phase If we found that there are no
  conflicts, the transaction commits and the
  changes are applied to the database.

7
Timestamp Based Concurrency Control.

• Though the use of locks guarantees
  serialaizibility of schedules, but when a
  transaction requires access to an item which is
  already locked by some other transaction, it is
  force wait until the other transaction releases
  locks.
• Timestamp is a unique identifier assigned by the
  DBMS to a transaction, to indicate which
  transaction started early.
• In timestamping if a transaction wants to read or
  write data, this will be allowed only if last
  update done on that item is by an older
  transaction. If the update is done by any younger
  transaction then the transaction requested will
  be rolled back and restarted with a new time
  stamp.

8

• Transaction issues read(x)
• When a transaction T is trying to read a data
  item (x), then it checks whether the item has
  already been updated by any younger transaction
  or not, that is ts(T) lt write_stamp(x). If this
  condition holds it means some other younger
  transaction has updated the value of item(x), and
  this value is not useful to for reading and these
  kinds of values leaves the database in
  inconsistent state, therefore this transaction
  must be aborted and assigned a new timestamp.
• Suppose if ts(T) gt write_stamp, then the read
  operation can proceed and after reading the we
  must set its value to read_timestamp(x)
  max(ts(T), read_timestamp(x)).

9

• Transaction issues write(x)
• the time stamp of the item will be set to
  write_stamp(x) ts(T). When a transaction T is
  trying to write a data item (x), then it checks
  whether the item has already been read and
  started by any younger transaction or not, that
  is ts(T) lt read_stamp(x). If this condition holds
  it means that some other younger transaction
  already started using the value and it would
  leave the database inconsistent if we update it
  now.
• There is other kind of situation when the
  transaction T is trying to write an item (x)
  which has already been by written by other
  younger transaction that is ts(T) lt
  write_stamp(x), since our transaction is trying
  to write outdated value which will leave the
  database in inconsistent state, so we must roll
  back our transaction and must restart all over
  again with a new time stamp.
• Suppose if ts(T) gt read_stamp(x) then the
  transaction will proceed without any conflicts
  and the time stamp of the item will be set to
  write_stamp(x) ts(T).

10
Example for Timestamp Ordering
11
Thomass Write Rule

• Suppose when the transaction wants to write data
  item (x) and it checks whether any other younger
  transaction has already updated or not, that is
  ts(T) lt write_stamp(x). If this condition holds
  then unlike in basic timestamping it never gets
  aborted and restarted but instead just ignores
  because whatever the transaction takes place
  finally younger transaction must write the value
  to the data item so if this transaction finds
  that already an younger has updated then it is ok
  if we just ignore it. In this way we can increase
  some amount of concurrency without restarting the
  transaction.

12
Example for Thomass Rule
13
Multiversion Concurrency Control

• We can also increase concurrency by maintaining
  multiple versions of the same data, since
  different users may work concurrently on
  different versions of the same object instead of
  having to wait for each others transactions to
  complete.
• When we see the basic timestamping we know that
  only one transaction can access the data item at
  time, but here we can maintain a different copies
  of the same data and allowed to be accessed by
  many users.

14
Implementation

• Version number of item xi
• Read_stamp(xi), which has the timestamp of last
  transaction which read its value.
• Write_stamp(xi), which has the timestamp of last
  transaction which has updated its value.
• Transaction issues a read(x) If a transaction T
  wants to read a data item (x), then we must
  return a version xi which has the latest write
  timestamp and it is younger or equal to
  transaction ts(T). We can say this as write_stamp
  (xi) lt ts(T). Set the value of read_timestamp
  (xi) with the greater timestamps of either ts(T)
  or read_stamp(xi). The main advantage of this
  method is read operation never fails here.

15

• Transaction issues a write(x) If a transaction
  wishes to write a data item x, we must make sure
  that the data item was not read by some other
  younger transaction such that ts(T) lt ts (Tj).
  In order for the transactions to appear as
  serializable transactions Tj must not be able to
  view the changes that are taking place since it
  has already read the value. Therefore if we have
  a version xj has the largest writestamp of the
  item (x) is smaller or equal to ts(T) that is
  write_timestamp(xj) lt ts(T) and
  read_timestamp(xj) gt ts(T), in this case
  transaction must be aborted and restarted again.
  In any other case it will create a new version xj
  of x and set read and write stamps to ts(t).

16
Databases with MVCC

• Berkeley DB
• Firebird (database server)
• FLAIM
• H2 Database Engine (experimental since Version
  1.0.57 (2007-08-25))
• InterBase (all versions)
• Microsoft SQL Server (only in SQL Server 2005)
• MySQL when used with InnoDB or Falcon storage
  engines.
• Oracle database all versions since Oracle 7
• PostgreSQL and PostgreSQL derivatives such as
  Netezza and Greenplum
• ThinkSQL
• Zope Object Database

17
Conclusion

• We can conclude that by using concurrency control
  without using locks we can increase the
  performance level up to some extent provided each
  method is having its own assumptions and flaws.
• In optimistic method we are assuming that
  conflicts between transactions are very rare, by
  this we dont have any overhead to check the
  serilizability of the transactions.
• In timestamping we are avoiding locks on the data
  items, and thus we are providing a non deadlock
  situation for the trasactions which can resolve
  the conflicts by means of timestamps.
• In multiversion method we are using versioning
  of data to increase the concurrency so that
  different users can work on the same data
  objects.

18
ANY QUESTIONS ?
19
QUIZ-1

• 1) Which of the following does not put locks on
  the data items?
• A) Optimistic Concurrency Control D)
  A, B, and C
• B) Timestamping Method E) A and B
• C) Multiversion Concurrency Control
• 2) Which of the following methods is best
  suitable when conflicts are very rare?
• A) Multiversion Concurrency Control D)
  Strict Two Phase Locking
• B) Optimistic Concurrency Control E)
  None of the above
• C) Timestamping Concurrency Control
• 3) Which of the following technique maintains
  different versions of the same object, all of
  which can be accessed by the user?
• A) Optimistic Concurrency Control
• B) Multiple Granularity Locking
• C) Optimistic Concurrency Control
• D) Timestamping Concurrency Control
• E) None of the above

20
QUIZ-1

• 1) Which of the following does not put locks on
  the data items?
• A) Optimistic Concurrency Control D)
  A, B, and C
• B) Timestamping Method E) A and B
• C) Multiversion Concurrency Control
• 2) Which of the following methods is best
  suitable when conflicts are very rare?
• A) Multiversion Concurrency Control D)
  Strict Two Phase Locking.
• B) Optimistic Concurrency Control E) None
  of the above
• C) Timestamping Concurrency Control
• 3) Which of the following technique maintains
  different versions of the same object, all of
  which can be accessed by the user?
• A) Optimistic Concurrency Control
• B) Multiple Granularity Locking
• C) Optimistic Concurrency Control
• D) Timestamping Concurrency Control
• E) None of the above