STUDENTS 3K

  • Home
  • Students+
    • Engineering Students
    • Education Loan
    • Study Abroad Guide
    • Projects Download
  • Freshers
    • Aptitude Q & A
    • Placement Papers
    • Verbal Ability
    • Interview Questions
    • IT Company Details
    • Job Updates
  • Study Resources
    • Career Guidance
    • LAB Programs
      • C Programs
      • CPP Programs [C++]
      • Java Programs
    • Question Papers
    • Learn English
    • Notice Board
  • More –>>
    • Love calculator
You are here: Home / Interview / Concurrent Update Problem

Concurrent Update Problem

Concurrent update problem in a database can be defined as the occur when multiple sessions are allowed to update their data present in a single database. Concurrent update problem may lead to data integrity loss and data corruption.

  • Read: More Interview Questions

Example for Concurrent Update Problem

Imagine that we have an application which manages the database of a store. The store will have several retail salesman and products. In the normal process, the number of product in stock will be reduced by one as soon as the product is billed by the customer. This scenario won’t reveal any problem in our application but the scenario is different when two salesmen are selling the same object at the same time. The concurrent update problem in our store will follow the sequence given below,

  • The salesman will send a query to the database about the stock availability in the store. Let us consider there are 10 products in the store and 10 will be returned to the salesman as a query result.
  • After few seconds later, let us consider that a second salesman who requests the same query from database. He will also receive the same result.
  • After selling the product to the customer; the first salesman will update the stock on the product as nine similar update will be done by the second salesman after selling the product to second customer.
  • Now the database will list the stock remaining as nine while only 8 products are available in the store. This process is called as concurrent update problem.

How to Prevent Concurrent Update Problem?

Concurrent update problem can be prevented by restricting the database access to a single user. However this method is not feasible for vast databases which require regular and immediate updates. The database locking is the only feasible method to prevent concurrent update problem.

  • Do you Know: SQL Clustered & Non-clustered Index

What is Database Locking?

Database locking is the process of restricting the access to certain data stored in the database until particular user/update session is complete. Database locking is used to prevent data loss and concurrent update problem. The period during which a user waits to gain access to a database is called as lock/wait period. There are several types of database locking which are classified according to the restrictions placed on the database.

Types of Database Locking

  • Database Level Locking
  • File Level Locking
  • Table Level Locking
  • Page or Block Level Locking
  • Column Level Locking
  • Row Level Locking

Database Level Locking

This locking process is used to restrict the access to the entire database. Database level locking allow access to only one user/session. This locking is not commonly used unless a restriction is needed for entire database.

File Level Locking

Using this locking method, files present in a database can be locked out from access. This locking method is not used unless an update is required for the whole file.

  • Related: SQL & .NET Interview questions and Answers

Table Level Locking

Table level locking is the most commonly used database locking method. This locking process is used to restrict the access to a particular table. Rows and columns present in a table are altered or updated using table level locking.

Page or Block level locking is used when updates are required for page or block present in a database. Row and column level locking are used to update certain rows and columns present in a database. If row and column locking is applied to all rows and columns present in a table then it will be promoted to table level locking.

When two users try to lock a database at the same time, lock contention problem arises. Lock contention problem can be eradicated by giving priority to the user who requested the lock first.

Filed Under: Interview Tagged With: SQL, SQL Interview questions





Random Materials :

  • Data Mining and Data Warehousing
  • Difference Between Class and Primitive Types
  • Database Roles in SQL
  • CASE Expression in SQL
  • Concurrent Update Problem

Follow Us on Facebook

Advertisement

Company Profile

  • About Students3k
  • Contact Us
  • Earn Money Online
  • Website Archive Page
  • Privacy Policy
  • Disclaimer

Categories

  • Important Programs
  • Study Abroad
  • IT Companies
  • Career Guidance for Students
  • Teachers
  • Verbal Analogies

We Are Social

  • Facebook Fans
  • Twitter Follow
  • Google Plus
  • Pinterest Page

© Copyright 2012 - 2016 Students3k.com · All Rights Reserved · Designed By Benefits

Show