This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the ACID Properties in the Database System?
ACID Properties in Database System (Atomicity, Consistency, Isolation, Durability)
What is a Phantom read?
A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.
This can occur when range locks are not acquired on performing a SELECT … WHERE operation.
What is a Dirty read?
Dirty read—Dirty reads occur when one transaction reads data that has been written but not yet committed by another transaction. If the changes are later rolled back, the data obtained by the first transaction will be invalid.
What is Nonrepeatable read?
Nonrepeatable reads happen when a transaction performs the same query two or more times and each time the data is different. This is usually due to another concurrent transaction updating the data between the queries.
What is a lost update problem?
Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.
What is Timeout?
Database Theory: What is Timeout, Lock, Block, and Deadlock in a Database System?
What is a Lock?
When one process is accessing an object, it can request for a lock for that particular object so that other processes cannot perform read or update on that object.
There are different types of lock like: Exclusive Lock, Non-exclusive Lock.
What is Blocking?
If two processes are accessing same resource so at that time one process has to wait until the other process release the occupied resource.
This is called blocking because one transaction is blocked by another transaction and all blocked processes kept in the waiting queue.
If the blocked transaction is waiting for a long time, it may generate a Database Timeout. If the long running blocked transaction requires a lock, it may generate a Database Deadlock.
What is a Deadlock?
One X blocked process is waiting to complete the transaction of the Y process and the same time, Y process is blocked because it is also waiting to complete the transaction of the X process.
This is called as Deadlock situation.
Deadlock is a next stage of the blocking situation so if you want to avoid Deadlock situation, you should first find the blocked transaction and tried to remove internal dependency of the transactions.
What is a Live lock?
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
What is Optimistic Locking and Pessimistic Locking?
Database Theory: What is Optimistic Locking and Pessimistic Locking?
What are the different ways to detect a deadlock in the SQL Server?
SQL Server: 8 different ways to Detect a DeadLock in a Database
What happens after the Deadlock arise in the SQL Server?
What is your ultimate strategies to avoid a Deadlock?
What is the Database level Locking?
With database level locks, the entire database is locked – which means that only one database session can apply any updates to the database. This type of lock is not often used, because it obviously prevents all users except one from updating anything in the database.
What is the File level Locking?
With a file lock level, an entire database file is locked. What exactly is a file in a database? Well, a file can have a wide variety of data – inside a file there could be an entire table, a part of a table, or even parts of different tables. Because of the variety of data stored inside a file, this type of lock level is less favored.
What is the Column level Locking?
A column level lock just means that some columns within a given row in a given table are locked. This form of locking is not commonly used because it requires a lot of resources to enable and release locks at this level. Also, there is very little support for column level locking in most database vendors.
