UNIT III TRANSACTIONS

-

 3.6 NEED FOR CONCURRENCY CONTROL


Need for Concurrency  

Following are the purposes of concurrency control -

• To ensure isolation

• To resolve read-write or write-write conflicts

• To preserve consistency of database

• Concurrent execution of transactions over shared database creates several data integrity and consistency problems - these are

(1) Lost update problem: This problem occurs when two transactions that access the same database items have their operations interleaved in a way that makes the value of some database item incorrect.

For example - Consider following transactions

(1) Salary of Employee is read during transaction T1.

(2) Salary of Employee is read by another transaction T2.

(3) During transaction T1, the salary is incremented by 200

(4) During transaction T2, the salary is incremented by 500

The result of the above sequence is that the update made by transaction T1 is completely lost. Therefor this problem is called as lost update problem.

(2) Dirty read or Uncommited read problem: The dirty read is a situation in which one transaction reads the data immediately after the write operation of previous transaction

For example - Consider following transactions -

Assume initially salary is = 1000

(1) At the time t1, the transaction T2 updates the salary to 1200

(2) This salary is read at time t2 by transaction T1. Obviously it is 1200

(3) But at the time t3, the transaction T2 performs Rollback by undoing the changes made by T1 and T2 at time t1 and t2.

(4) Thus the salary again becomes = 1000. This situation leads to Dirty Read or Uncommited Read because here the read made at time t2(immediately after roid update of another transaction) becomes a dirty read.

(3) Non-repeatable read problem

This problem is also known as inconsistent analysis problem. This problem occurs when a particular transaction sees two different values for the same row within its lifetime. For example-

(1) At time t1, the transaction T1 reads the salary as 1000

(2) At time t2 the transaction T2 reads the same salary as 1000 and updates it to 1200

(3) Then at time t3, the transaction T2 gets committed.

(4) Now when the transaction T1 reads the same salary at time t4, it gets different value than what it had read at time t1. Now, transaction T1, cannot repeat its reading operation. Thus inconsistent values are obtained.

Hence the name of this problem is non-repeatable read or inconsistent analysis problem.

(4) Phantom read problem

The phantom read problem is a special case of non repeatable read problem.

This is a problem in which one of the transaction makes the changes in the database system and due to these changes another transaction can not read the data item which it has read just recently. For example -

(1) At time t1, the transaction T1 reads the value of salary as 1000

(2) At time t2, the transaction T2 reads the value of the same salary as 1000

(3) At time t3, the transaction T1 deletes the variable salary.

(4) Now at time t4, when T2 again reads the salary it gets error. Now transaction T2 can not identify the reason why it is not getting the salary value which is read just few time back.

This problem occurs due to changes in the database and is called phantom read problem.



3.7 Concurrency Control


• One of the fundamental properties of a transaction is isolation.

• When several transactions execute concurrently in the database, however, the isolation property may no longer be preserved.

• A database can have multiple transactions running at the same time. This is called concurrency.

• To preserve the isolation property, the system must control the interaction among the concurrent transactions; this control is achieved through one of a variety of mechanisms called concurrency control schemes.

• Definition of concurrency control: A mechanism which ensures that simultaneous execution of more than one transactions does not lead to any database inconsistencies is called concurrency control mechanism.

• The concurrency control can be achieved with the help of various protocols such as - lock based protocol, Deadlock handling, Multiple Granularity, Timestamp based protocol, and validation based protocols.

Comments

Post a Comment

Popular posts from this blog

CS3492 Database Management Systems Syllabus

-
UNIT I RELATIONAL DATABASES  Purpose of Database System – Views of data – Data Models – Database System Architecture – Introduction to relational databases – Relational Model – Keys – Relational Algebra – SQL fundamentals – Advanced SQL features – Embedded SQL– Dynamic SQL UNIT II DATABASE DESIGN Entity-Relationship model – E-R Diagrams – Enhanced-ER Model – ER-to-Relational Mapping – Functional Dependencies – Non-loss Decomposition – First, Second, Third Normal Forms, Dependency Preservation – Boyce/Codd Normal Form – Multi-valued Dependencies and Fourth Normal Form – Join Dependencies and Fifth Normal Form UNIT III TRANSACTIONS Transaction Concepts – ACID Properties – Schedules – Serializability – Transaction support in SQL – Need for Concurrency – Concurrency control – Two Phase Locking - Timestamp – Multiversion – Validation and Snapshot isolation – Multiple Granularity locking – Deadlock Handling – Recovery Concepts – Recovery based on deferred and immed...
Read more

UNIT I RELATIONAL DATABASES

-
Image
 1.7 Keys In the context of a relational database, keys are one of the basic requirements of a relational database model. Keys are fundamental components that ensure data integrity, uniqueness and efficient access. It is widely used to identify the tuples(rows) uniquely in the table. We also use keys to set up relations amongst various columns and tables of a relational database. Keys in DBMS Why do we require Keys in a DBMS? Keys are important in a Database Management System (DBMS) for several reasons: Uniqueness: Keys ensure that each record in a table is unique and can be identified distinctly. Data Integrity: Keys prevent data duplication and maintain the consistency of the data. Efficient Data Retrieval: By defining relationships between tables, keys enable faster querying and better data organization. Without keys, it would be extremely difficult to manage large datasets and queries would become inefficient and prone to errors. Types of Database Keys 1. Super Key The set of o...
Read more

UNIT I WEBSITE BASICS, HTML 5, CSS 3, WEB 2.0

-
1.11 Colors   ,Shadows,Text, Transformations,Transitions,Animations,Bootstrap Framework The following explains how to use  colors, shadows, text utilities, transformations, transitions, animations , and the  Bootstrap framework  in web technologies, with examples primarily using CSS and Bootstrap.  CSS Concepts Cascading Style Sheets (CSS) provides core functionalities for styling web elements.   Concept   Description Example (CSS) Colors Used for foreground (text) and background colors. color: blue; background-color: #fff; Shadows Adds visual depth to text or box elements. box-shadow: 2px 2px 5px rgba(0,0,0,0.3); Text Controls typography, alignment, capitalization, etc.. text-align: center; text-transform: uppercase; font-family: sans-serif; Transformations Modifies the position, rotation, scale, or skew of an element in 2D or 3D space. transform: rotate(45deg);  or  transform: scale(1.1); Transitions Creates smooth, animated changes to CSS ...
Read more