beginnersbook.com

  • Home
  • All Tutorials
    • Learn Servlet
    • Learn JSP
    • Learn JSTL
    • Learn C
    • Learn C++
    • Learn MongoDB
    • Learn XML
    • Learn Python
    • Learn Perl
    • Learn Kotlin
  • Core Java
  • OOPs
  • Collections
  • Java I/O
  • JSON
  • DBMS

Concurrency Control in DBMS

By Chaitanya Singh | Filed Under: DBMS

When more than one transactions are running simultaneously there are chances of a conflict to occur which can leave database to an inconsistent state. To handle these conflicts we need concurrency control in DBMS, which allows transactions to run simultaneously but handles them in such a way so that the integrity of data remains intact.

Let’s take an example to understand what I’m talking here.

Conflict Example

You and your brother have a joint bank account, from which you both can withdraw money. Now let’s say you both go to different branches of the same bank at the same time and try to withdraw 5000 INR, your joint account has only 6000 balance. Now if we don’t have concurrency control in place you both can get 5000 INR at the same time but once both the transactions finish the account balance would be -4000 which is not possible and leaves the database in inconsistent state.
We need something that controls the transactions in such a way that allows the transaction to run concurrently but maintaining the consistency of data to avoid such issues.

Solution of Conflicts: Locks

A lock is kind of a mechanism that ensures that the integrity of data is maintained. There are two types of a lock that can be placed while accessing the data so that the concurrent transaction can not alter the data while we are processing it.

1. Shared Lock(S)
2. Exclusive Lock(X)

1. Shared Lock(S): Shared lock is placed when we are reading the data, multiple shared locks can be placed on the data but when a shared lock is placed no exclusive lock can be placed.

For example, when two transactions are reading Steve’s account balance, let them read by placing shared lock but at the same time if another transaction wants to update the Steve’s account balance by placing Exclusive lock, do not allow it until reading is finished.

2. Exclusive Lock(X): Exclusive lock is placed when we want to read and write the data. This lock allows both the read and write operation, Once this lock is placed on the data no other lock (shared or Exclusive) can be placed on the data until Exclusive lock is released.

For example, when a transaction wants to update the Steve’s account balance, let it do by placing X lock on it but if a second transaction wants to read the data(S lock) don’t allow it, if another transaction wants to write the data(X lock) don’t allow that either.

So based on this we can create a table like this:

Lock Compatibility Matrix

__________________________
|     |   S    |    X    |
|-------------------------
|  S  | True   |   False |
|-------------------------
|  X  | False  |   False |
--------------------------

How to read this matrix?:
There are two rows, first row says that when S lock is placed, another S lock can be acquired so it is marked true but no Exclusive locks can be acquired so marked False.
In second row, When X lock is acquired neither S nor X lock can be acquired so both marked false.

Enjoyed this post? Try these related posts

  1. Data models in DBMS
  2. Data Abstraction in DBMS
  3. Non trivial functional dependency in DBMS
  4. DBMS Relational Calculus
  5. Foreign key in DBMS
  6. DBMS languages

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

DBMS Tutorial

Basics

  • DBMS Tutorial
  • DBMS Introduction
  • Database Applications
  • DBMS vs File System
  • DBMS Architecture
  • Three-level DBMS architecture
  • View in DBMS
  • Abstraction
  • Instance & Schema
  • DBMS languages

Data Models

  • Data Models
  • ER Diagram
  • DBMS Generalization
  • DBMS Specialization
  • DBMS Aggregration
  • Relational Model
  • Hierarchical Model
  • Constraints
  • Cardinality

Relational Database

  • RDBMS concepts
  • Relational Algebra
  • Relational Calculus
  • Keys Index
  • Primary Key
  • Super Key
  • Candidate Key
  • Foreign Key
  • Composite Key
  • Alternate Key

Normalization

  • Normalization
  • Functional dependency

Transaction Management

  • Transaction Management
  • ACID properties
  • Transaction States
  • DBMS Schedules
  • Serializability
  • Conflict Serializability
  • View Serializability
  • Deadlock

Concurrency Control

  • Concurrency Control

Recently Added..

  • JSON Tutorial
  • Java Regular Expressions Tutorial
  • Java Enum Tutorial
  • Java Annotations Tutorial

Copyright © 2012 – 2021 BeginnersBook . Privacy Policy . Sitemap