Multiversion Concurrency Control Techniques (MVCC)

The aim of Multi-Version Concurrency is to avoid the problem of Writers blocking Readers and vice-versa, by making use of multiple versions of data. The problem of Writers blocking Readers can be avoided if Readers can obtain access to a previous version of the data that is locked by Writers for modification.

The problem of Readers blocking Writers can be avoided by ensuring that Readers do not obtain locks on data. Multi-Version Concurrency allows Readers to operate without acquiring any locks, by taking advantage of the fact that if a Writer has updated a particular record, its prior version can be used by the Reader without waiting for the Writer to Commit or Abort. In a Multi-version Concurrency solution, Readers do not block Writers, and vice versa. While Multi-version concurrency improves database concurrency, its impact on data consistency is more complex.

7.7.1 Requirements of Multi-Version Concurrency systems

As its name implies, multi-version concurrency relies upon multiple versions of data to achieve higher levels of concurrency. Typically, a DBMS offering multi-version concurrency (MVDB), needs to provide the following features:

1. The DBMS must be able to retrieve older versions of a row.

2. The DBMS must have a mechanism to determine which version of a row is valid in the context of a transaction.

Usually, the DBMS will only consider a version that was committed prior to the start of the transaction that is running the query. In order to determine this, the DBMS must know which transaction created a particular version of a row, and whether this transaction committed prior to the starting of the current transaction.

7.7.2 Approaches to Multi-Version Concurrency

There are essentially two approaches to multi-version concurrency. The first approach is to store multiple versions of records in the database, and garbage collect records when

they are no longer required. This is the approach adopted by PostgreSQL and Firebird/Interbase. The second approach is to keep only the latest version of data in the database, as in SVDB implementations, but reconstruct older versions of data dynamically as required by exploiting information within the Write Ahead Log. This is the approach taken by Oracle and MySQL/InnoDb.

7.8 Summary

• Concurrency in terms of databases means allowing multiple users to access the data contained within a database at the same time.

• A transaction is a sequence of read and write operations on data items that logically functions as one unit of work

• Concurrency control is needed to handle problems that can occur when transactions execute concurrently.

• To ensure data integrity the DBMS, should maintain the following transaction properties- atomicity, consistency, isolation and durability (ACID).

• Concurrency control in database management systems permits many users (assumed to be interactive) to access a database in a multi programmed environment while preserving the illusion that each user has sole access to the system.

• Serializability is a given set of interleaved transactions is said to be serial sable if and only if it produces the same results as the serial execution of the same

transactions Serializability is an important concept associated with locking. • Locking is necessary in a concurrent environment to assure that one process does

not retrieve or update a record that is being updated by another process. • A timestamp is a unique identifier created by the DBMS that indicates the

relative starting time of a transaction. 7.9 Key Words

Concurrency Control, Locking, Dead Lock, Time Stamp, ACID Properties, Transaction, Serializability

7.10 Self Assessment Questions

2. What is concurrency control?

3. Explain Transaction with live examples? 4. What are the ACID Properties of Transaction? 5. Why concurrency is needed?

6. What do you mean by locking? 7. What do you mean by deadlocks?

8. Explain the timestamp ordering protocol? 9. Explain the Timestamping control? 7.11 References/Suggested Readings

1. Date, C, J, Introduction to Database Systems, 7

th

edition 9 2. Silbershatz, Korth, Sudarshan, Database System Concepts 4

th

MCA 202/MS 11

Author: Abhishek Taneja Vetter: Dr. Pradeep Bhatia Lesson: Database RecoveryTechniques Lesson No. : 8

Structure 8.0 Objectives 8.1 Introduction

8.2 Recovery Concepts

8.3 Recovery Techniques Based On Deferred Update 8.4 Recovery Techniques Based On Immediate Update 8.5 Shadow Paging

8.6 Database Backup And Recovery From Catastrophic Failures 8.7 Summary

8.8 Key Words

8.9 Self Assessment Questions 8.10 References/Suggested Readings 8.0 Objectives

At the end of this chapter the reader will be able to:

• Describe some of the techniques that can be used for database recovery from failures.

• Describe several recovery concepts, including write ahead logging, in-place versus shadow updates, and the process of rolling back (undoing) the effect of an incomplete or failed transaction.

• Describe the technique known as shadowing or shadow paging • Describe techniques for recovery from catastrophic failure

8.1 Introduction

In this chapter we discuss some of the techniques that can be used for database recovery from failures. We have already discussed the different causes of failure, such as system crashes and transaction errors. We start Section 8.2 with an outline of a typical recovery procedures and a categorization of recovery algorithms, and then discuss several recovery concepts, including write ahead logging, in-place versus shadow updates, and the process of rolling back (undoing) the effect of an incomplete or failed transaction. In Section 8.3, we present recovery techniques based on deferred update, also known as the NO-UNDO/REDO technique. In Section 8.4, we discuss recovery techniques based on immediate update; these include the UNDO/REDO and UNDO/NO-REDO algorithms. We discuss the technique known as shadowing or shadow paging, which can be categorized as a NO-UNDO/NO-REDO algorithm in Section 8.5. Finally, techniques for recovery from catastrophic failure are discussed in Section 8.6. Our emphasis is on conceptually describing several different approaches to recovery.