Concurrency Control

The concurrency control concept comes under the Transaction in the database management system (DBMS). It is a procedure in DBMS which helps us control two simultaneous processes to execute without conflicts among every other, these conflicts occur in multi-user systems.

Concurrency explains executing multiple transactions at a time. It is required to increase time efficiency. If many transactions try to access the same data, then irregularity arises. Concurrency control is required to maintain consistent data.

For example, if we take ATMs and do not use concurrency, multiple persons cannot draw money at a time in different places. Hence, we need concurrency.

Advantages

The advantages of concurrency control are: –

• Waiting time will be decreased.
• Response time will gradually decrease.
• It increases Resource Utilization.
• System accuracy & Efficiency is increased.

Control Concurrency

The simultaneous execution of transactions over shared databases can create several data integrity and consistency problems.

For example, if too many people are logging in to the ATMs, serial updates and synchronization in the bank servers should manifest whenever the transaction is done, if not it gives wrong information and wrong data in the database.

Main problems with using Concurrency

The problems which arise while using concurrency are as follows −

• Updates will be lost – If a single transaction does some changes, and another transaction removes that change. One transaction empties the updates of another transaction.

For example:

Consider the below diagram in which two transactions T_X and T_Y, are performed on the same account A where the balance of account A is $300.

• At time t1, transaction T_X reads the value of account A, i.e., $300 (only read).
• At time t2, transaction T_X deducts $50 from account A which becomes $250 (only deducted and not updated/written).
• Alternately, at time t3, transaction T_Y reads the value of account A that will be $300 only because T_X didn’t update the value yet.
• At time t4, transaction T_Y adds $100 to account A which becomes $400 (only added but not updated/written).
• At time t6, transaction T_X writes the value of account A that will be updated as $250 only, as T_Y didn’t update the value yet.
• Similarly, at time t7, transaction T_Y writes the values of account A, so it will write as done at time t4 which will be $400. Then the value written by T_X is lost, i.e., $250 is lost.

As a result, data becomes incorrect, and database sets to inconsistent.

•  Dirty read problem – The variable which updated in one transaction, at the same time another transaction has started and deleted the value of the variable there the variable is not getting updated or committed that has been done on the first transaction this gives us false values or the previous values of the variables this is a major problem.

For example:

Consider two transactions T_X and T_Y in the below diagram executing read/write operations on account A where the available balance in account A is $300:

At time t1, transaction T_X reads the value of account A, i.e., $300.

At time t2, transaction T_X adds $50 to account A which becomes $350.

At time t3, transaction T_X writes the newly refurbished value in account A, i.e., $350.

Then at time t4, transaction T_Y reads account A which will be read as $350.

Then at time t5, transaction T_X rollbacks because of a server problem, and the value changes back to $300 (as initially).

But the value for account A remains $350 for transaction T_Y as committed, which is the dirty read and therefore known as the Dirty Read Problem.

Inconsistent retrievals − One transaction is updating multiple different variables, another transaction is in the process to update those variables, and the problem that occurs is the inconsistency of the same variable in various instances.

Concurrency control techniques

The concurrency control techniques are as follows –

Locking

Lock guarantees exclusive use of data items to a current transaction. It first gains the data items by acquiring a lock, after completion of the transaction it releases the lock.

Types of Locks

The variety of locks is as follows: –

• Shared Lock [Transaction can read only the data item values]
• Exclusive Lock [Used for both reading and writing data item values]

Time Stamping

The timestamp is a unique identifier created by DBMS that indicates the relative starting at the time of a transaction. Whatever transaction we are doing stores the starting time of the transaction and denotes a specific time.

This can be created using a system clock or logical counter. This can be implemented whenever a transaction is started. Here, the logical counter in addition after a new timestamp has been assigned.

Optimistic

It is based on the belief that conflict is rare, and it is more efficient to allow transactions to proceed without implementing delays to ensure serializability.

Summary

You are now familiar with concepts of transaction management and concurrency. The concurrency control management of database employs various techniques that ensures that database access smooth and efficient. The concurrency control also makes sure that the database is always in a consistent state.

post

DBMS – Basics

DBMS stands for Database management system is software than help users to access database efficiently. In this lesson you will learn – DBMS Basics

Before DBMS, data was stored in operating system files and each file had its own rules and constraints that required specific applications to access the data.

If the data organization were to change with in the files, then application logic must also change. Sometimes you must write a new application to access the modified data files.

Storing and retrieving of data was very difficult with operating system files. The major disadvantages of these data files are as follows.

Redundancy and Inconsistency

Redundancy means duplicate information. Programmers insert new data or update the data files which leads to redundancy for two reasons.

1. Same information in different files in different format can cause redundancy.
2. Multiple copies of same file is also redundant.

The second problem with the data file is inconsistency. The files are stored in different locations in a different format for different applications, this leads to inconsistency. Updating files concurrently is another reason for the inconsistency.

The cost of accessing the data is also high because applications are written in many programming languages.

Difficulty accessing data

If a user wants some information extracted from data files, we cannot query the flat files directly. As a result, the information must be gathered manually or we have to write an application for that.

There is always a delay associated with the process or retrieving valuable information from data files.

Constraints

Data stored in files must follow some constraints so that it is consistent everywhere in the system.

Suppose, you are working with customer bank accounts and the constraint is to keep the account above $1000. The application has a piece of code that understand this constraint.

If the constraints change then entire application logic must change because the piece of code with the constraint is part of a bigger software program.

Atomicity Problem

If the system fails to record certain data information due to application failure, the database will be inconsistent. Then it is necessary to – Do complete transaction or no transaction at all. This is principle of Atomicity.

If the atomicity is not maintained, then the database will be in an inconsistent state and atomicity is not possible in file-processing systems.

Concurrent Access

There is no system in file-based data storage to maintain concurrent access. If two application users access the same data file – then the update from each concurrent user will not be recorded properly, if both of them write data file at the same time. There is no concurrency control mechanism.

Security Problem

There is no way to access only the relevant part of database files. The application will access all the information it is allowed to access regardless of what it is going to process.

The DBMS on the other hand, load relevant part of the database only.

DBMS Basics – Database system

We know that the DBMS does some amazing things and it is a software product. The core functions of a DBMS can be generalized into following.

1. Database Design
2. Data Analysis
3. Concurrency Control

Complex data structures are hidden and an abstract view of data is presented to users to simplify communication with the database. This is because the technical expertise of users are different.

There are 3 level of data abstraction

1. Physical – At this level the system administrator is user who decide and maintain the physical storage for DBMS.
2. Conceptual – The Database administrator is user at logical level and design, maintain the organization database.
3. External or View – The user at this level at normal users who use the system for other purposes.

At each level, the user does not need to know the complexity at the level below and this is known as data independence.

Database Schema

Overall design of database is called the database schema. The database schema is very important during the design process. There is a relation between the level of abstraction and database schema.

For example,

• Physical Schema at physical level.
• Logical schema at Logical level
• Schema at view level is sub-schema.

The information stored in database at a particular moment is called instance of database. The instance of database contains collections of records.

Data Model

A data model describe a way to describe the database at physical, logical and view levels. It helps user to store data in terms of data model. These are the main type of data model used in DBMS.

1. Relational Model
2. E-R Model
3. Object-Based data Model
4. Semi-structured Data model

Database Languages

Data-Manipulation Language or DML

DML is the query language and performs some data manipulations. You can divide the DML into two part – Procedural and Non-procedural DML.

The common tasks performed by DML are

1. Insert
2. Delete
3. Update
4. Query

Procedural DML you can define a procedure or function. It defines what to query and how to query. In case of procedural DML, you many require a procedural language. They are hard to write because you need expertise in two languages.

Non-procedural DML also known as Declarative DMLs only define what to query.

e.g,

select * from department;

Data-Definition Language or DDL

Storage structure and access methods in DDL and stored in data dictionary contains metadata information – (data about data.)

e.g. create table, alter table, change a field name or type, etc.

create table department (deptid number (2), deptname varchar2 (15));

DDL for constraints

• Domain constraints – integer, float, etc.
• Referential integrity – attribute in a table must appear in another table (referential integrity). Any change that break this constraint is denied.
• Assertion – a condition that the database must satisfy all the time.

Relational Database

The relational database is made of tables and each table has rows and columns. The tables are also known as relations. The rows are called the tuples and the columns are attributes or fields.

Department Relation

DeptID	DeptName	Location
12	Finance	London
34	Operations	Tokyo

In the DBMS Basics – example above, the columns – DeptID, DeptName and Location are fields or attributes of a Department.

The row with DeptID 12 and 34 are tuples or records of Department table.

References

Avi Silberschatz, Henry F. Korth, and S. Sudarshan. 27-Jan-2010. Database System Concepts. McGraw-Hill Education.

Ramakrishnan, Johannes Gehrke, and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).

post