DBMS Concepts

DBMS Tutorial Components of DBMS. Applications of DBMS The difference between file system and DBMS. Types of DBMS DBMS Architecture DBMS Schema Three Schema Architecture. DBMS Languages.

DBMS ER Model

ER model: Entity Relationship Diagram (ERD) Components of ER Model. DBMS Generalization, Specialization and Aggregation.

DBMS Relational Model

Codd’s rule of DBMS Relational DBMS concepts Relational Integrity Constraints DBMS keys Convert ER model into Relational model Difference between DBMS and RDBMS Relational Algebra DBMS Joins

DBMS Normalization

Functional Dependency Inference Rules Multivalued Dependency Normalization in DBMS: 1NF, 2NF, 3NF, BCNF and 4NF

DBMS Transaction

What is Transaction? States of transaction ACID Properties in DBMS Concurrent execution and its problems DBMS schedule DBMS Serializability Conflict Serializability View Serializability Deadlock in DBMS Concurrency control Protocols

Difference

Difference between DFD and ERD

Misc

Advantages of DBMS Disadvantages of DBMS Data Models in DBMS Relational Algebra in DBMS Cardinality in DBMS Entity in DBMS Attributes in DBMS Data Independence in DBMS Primary Key in DBMS Foreign Key in DBMS Candidate Key in DBMS Super Key in DBMS Aggregation in DBMS Hashing in DBMS Generalization in DBMS Specialization in DBMS View in DBMS File Organization in DBMS What Is A Cloud Database What Is A Database Levels Of Locking In DBMS What is RDBMS Fragmentation in Distributed DBMS What is Advanced Database Management System Data Abstraction in DBMS Checkpoint In DBMS B Tree in DBMS BCNF in DBMS Advantages of Threaded Binary Tree in DBMS Advantages of Database Management System in DBMS Enforcing Integrity Constraints in DBMS B-Tree Insertion in DBMS B+ Tree in DBMS Advantages of B-Tree in DBMS Types of Data Abstraction in DBMS Levels of Abstraction in DBMS 3- Tier Architecture in DBMS Anomalies in Database Management System Atomicity in Database Management System Characteristics of DBMS DBMS Examples Difference between Relational and Non-Relational Databases Domain Constraints in DBMS Entity and Entity set in DBMS ER Diagram for Banking System in DBMS ER Diagram for Company Database in DBMS ER Diagram for School Management System in DBMS ER Diagram for Student Management System in DBMS ER Diagram for University Database in DBMS ER Diagram of Company Database in DBMS Er Diagram Symbols and Notations in DBMS How to draw ER-Diagram in DBMS Integrity Constraints in DBMS Red-Black Tree Deletion in DBMS Red-Black Tree Properties in DBMS Red-Black Tree Visualization in DBMS Redundancy in Database Management System Secondary Key in DBMS Structure of DBMS 2-Tier Architecture in DBMS Advantages and Disadvantages of Binary Search Tree Closure of Functional Dependency in DBMS Consistency in Database Management System Durability in Database Management System ER Diagram for Bank Management System in DBMS ER Diagram for College Management System in DBMS ER Diagram for Hotel Management System in DBMS ER Diagram for Online Shopping ER Diagram for Railway Reservation System ER Diagram for Student Management System in DBMS Isolation in DBMS Lossless Join and Dependency Preserving Decomposition in DBMS Non-Key Attributes in DBMS Data Security Requirements in DBMS DBMS functions and Components What is Homogeneous Database? DBMS Functions and Components Advantages and Disadvantages of Distributed Database

Levels of Locking in DBMS

There are several levels of locks that can control the access to the elements stored at various levels in the Database. There can be multiple levels of the locks. These locks make the transaction process quite efficient and allow concurrent access between various users. Using various locks, we can lock the information at the particular level or database object that has requested the lock without disturbing the entire Database.

These levels of the lock are:

  • The row of any table or Database
  • A page from the Database
  • An entire table of the Database.
  • On the complete Database

The level here represents the scope of the lock and the proportion of the Database that is affected by it. Implementation of a lock in the rows, page or table can be done directly by the user, but implementing any lock on the entire Database can only be done by using SQL commands or utilities.

Let us discuss each level of lock individually:

Row

This is the most basic level where the user can implement a lock on the information stored. Implementing a lock on any row provides the user with complete control of the row. But the locks can only be acquired level-wise, which means to implement the lock on the row, the user needs to make sure that the page on which the row is present is an intent lock.

Any transactions that aim to use the lock on the Database row should hold intent locks of the table and page in which the row is present. Additionally, it should have shared or exclusive locks of the Database too. The user can implement the row lock only when the page size of the Database is greater than 2KB. We cannot implement row locking when working with servers that do not support fast commits or when the Distributed Multi-Cache Management (DMCM) is turned on or VMS Cluster is being installed.

It is important to understand the term Multi-version Concurrency Control to implement the row locks without using the intent lock on the data pages.
Multi-version Concurrency Control (MVCC).

It is a technique used in various database management systems to implement locks on the data. It allows implementing the lock without the requirement of intent locking the page. It is not possible to lock the row of the table in a read-only state, nor is it to exclusively lock the row while updating the contents of the row. The reader cannot implement a lock to block the writer, and similarly, the writer cannot block the reader either. If we perform the transaction on the Database using the MVCC, we can implement a lock on the row at MVCC that will hold intent table locks and exclusive row locks on the modified table.

Page

As the name suggests, it will lock the content displayed on the entire Database page. For any transaction to implement the lock on the page, it requires holding the intent lock of the tables of that page and shared or exclusive page locks.

Table

This lock is used to implement a lock on an entire relationship in the Database. Any transaction that requires the implementation of the lock at the table level. It must hold shared or exclusive table locks.

Database

It is not an actual lock level as locking the entire Database does not make any real sense in the real world. But when connecting an application to the database management system using the CONNECT system. This is done by the application to prevent concurrent updates by other applications on the Database while executing the query on the Database.

Purpose of implementing Locks In DBMS

As discussed above, the database management system allows multiple users to modify the data stored; implementing locks on the data ensures the ACID properties are maintained throughout. Each transaction performed must ensure they fulfil all the ACID requirements.

The ACID properties include:

  • Atomicity: It makes sure that any transaction that is started must also be terminated successfully, or it should not start at all.
  • Consistency: Any change made should be written in the memory. Before any other transactions take place. 
  • Isolation: One transaction should not interfere with another transaction. Each transaction should be independent of the other.
  • Durability: The committed results should then be modified in the database too for future use.

Lock Modes

The lock implemented at various levels can be applied, allowing different actions on the locked items. There are six modes of lock:

  1. Exclusive Lock: These locks are represented by X. They are implemented when the user performs data manipulation. This lock can only be implemented on a row of the table or the database page. It prevents access to these resources; no more than one transaction can be performed to an item holding this lock.
  2. Share Lock: It is represented by S; it allows all the transactions to read the values from the resource. The user is introduced as a reader to the resource. It prevents the users from implementing any data definition command on the resource. All action that tries to modify the value of the resource is blocked.
  3. Update Lock: It is represented by U and is similar to an exclusive lock. This is more flexible than the exclusive lock. A resource must have a shared lock to upgrade it to update the lock. It can also be implemented on the page or row. This lock is implemented to prevent the system from going into a deadlock condition. The resource can switch between update and exclusive lock when the resource attempt to modify the data.
  4. Intent Lock: It is represented by I; it notifies other transactions about the transaction's intention to gain a lock. This lock ensures that all the locks are acquired sequentially. The aim is to ensure that the data modification is performed. All locks must follow the lock hierarchy. The intent lock can be classified into three types:
    • Intent Shared: It is represented by IS. A page or row can acquire this lock. It is acquired on a resource if the transaction aims to acquire a shared lock. Once an IS lock is intended, no other transaction can acquire the shared lock on it.
    • Intent Exclusive: This lock is sent by the transaction when it wants to acquire an exclusive lock enabling the transaction to modify the value of the resource.
    • Intent Update: This lock can only be implemented on the page of the database; it increases the flexibility of the exclusive lock whenever an update operation is performed.
  5. Schema Lock: It is acquired if the transaction wants to perform any operation on the table's schema. Usually, we can perform two operations on the schema. These are modifying the schema or ensuring the stability of the schema. Depending upon these functions, there are two modes of schema lock.
    • Schema modification: This lock is acquired when the transaction attempts to perform a data definition lock on the schema.
    • Schema stability: It is acquired when the transactions attempt to perform schema dependent.
  6. Bulk Update Lock: This lock is acquired when the modification is performed on the table. This allows the user to add a large amount of data to the database.

Placement of Locks

Database Management System allows users to access various elements of the database. The user can request the lock at the following resources:

ResourcesDefinition
Row IDAllows locking an entire row or tuple in the table.
TableThe whole relationship can be locked at once.
KeyThe index of the table can be locked in the table.
PageIt constitutes 8KB of data on the current page.
ExtentIt locks about 64KB of data which is equivalent to 8 pages of the database. The pages should be consecutive.
DatabaseThe database administrator can only implement this lock, it ensures that the database is read-only and no manipulation can be performed on it.
FileThe lock is implemented on the file.
ApplicationThis locks the entire resources that are being used by a particular application.
MetadataThis lock is different from others as it is implemented on the metadata and not on the database object.
ObjectThis lock can be used on any element that is entered as a database object.

Locking Hierarchy

To acquire a lock on any resources the locks are required to be acquired in a sequential top to bottom order. The hierarchy of locking is as follows:

Levels Of Locking In DBMS

Similarly, the various lock modes can only be acquired if the lock at the lower lock is compatible with the upper lock. If not, the lock at the lower level will not be executed. The compatibility can be studied using the following matrix:

Levels Of Locking In DBMS