Performance Issue

Deadlock in Database Operations

A deadlock is a situation where two or more competing actions are each waiting for the other to finish, resulting in a standstill where none can progress. To avoid deadlocks, it is crucial to ensure that operations are performed in the same order.

Database Index and Its Impact on Performance

A database index is a data structure that enhances the speed of data retrieval operations on a database table. However, this improvement comes at the cost of slower write operations and increased storage space.

Indexes can be defined as unique or non-unique. A unique index serves as a constraint on the table by preventing duplicate entries, ensuring data integrity.

Clustered vs. Non-Clustered Index

In a Non-Clustered Index (Default), data is stored in random order, but a logical ordering is defined by the index. On the other hand, clustering reorganizes the data blocks to match the index’s order, impacting both data storage blocks and the index itself. This reorganization leads to the creation of a Clustered Index.

Lock Types in Database Management

• Shared Locks: Allow multiple transactions to lock the same resource and persist until all transactions complete.
• Exclusive Locks: Held by a single transaction, preventing other transactions from locking the same resource.

• Read Locks: Typically shared, prevent other transactions from modifying the resource.
• Write Locks: Exclusive locks that prevent other transactions from modifying the resource. In some systems, they also block other transactions from reading the resource.