-
Why is Locking important in SQL server?
-
What are the different types of locks in SQL server?
-
Describe isolation levels in SQL server.
SQL Server locks - October 24, 2008 at 18:10 pm by Rajmeet Ghai
Explain the various types of concurrency problem. I.e. Lost or buried updates,
uncommitted dependency, inconsistent analysis, phantom read.
Types of concurrency problem:-
Lost or buried updates: - When the same row is selected for
updates by two or more transactions and updates the row based on the value
originally selected. Here, each transaction is unaware of the other
transactions. The last update overwrites updates made by the other
transactions, which results in lost data.
Uncommited dependency: - here, a transaction reads data of
another transaction which has not been committed yet. The value may be changed
by the other transaction.
Inconsistent analysis: - here, the transaction reads the data
inconsistently. This means that every time the data is read; different values
are read. This is because another transaction is continuously updating the
data.
Phantom Read: - Here, an insert or update is done on a row that
belongs to some other transaction. Hence the transaction may read a row that
may be deleted by some other transaction.
Describe optimistic and pessimistic concurrency.
Optimistic concurrency: - Assumes that a resource is likely to
be available at all times. This means that resource locking is very unlikely.
If a conflict occurs, the application must read the data and attempt the change
again.
Pessimistic concurrency: - this locks the resources as and when
required. A transaction can be assured to be completed unless a deadlock
occurs.
What are the differences between lost updates and uncommitted dependencies?
In lost update, the data is lost. Here, the last update overwrites updates made
by other transactions. On the other hand, in uncommitted dependency data is
changed by other transactions. So, a transaction reads data of another
transaction which has not been committed yet
Explain the isolation level does SQL Server support.
Isolation levels:-
-
READ UNCOMMITTED: - Reads data that has been modified but not committed yet.
-
READ UNCOMMITTED: - Cannot Read data that has been modified but not committed
yet
-
REPEATABLE READ: - cannot read data that has been modified but not yet
committed by other transactions and that no other transactions can modify data
that has been read by the current transaction until the current transaction
completes
-
SNAPSHOT: - Data read by any statement in a transaction will be a consistent
version of the data that existed at the start of the transaction. Here, the
transaction can only identify the data modification that was committed before
the start of the transaction.
-
SERIALIZABLE:- Cannot Read data that has been modified but not committed yet,
until the current transaction completes, no other transaction can modify the
data and no other transactions will insert new rows whose keys falls in the
range of current transaction
What guidelines should be followed to help minimize deadlocks?
Guidelines to minimize deadlocks:-
-
Avoid user interaction in the transactions. The transaction must not rely on
any inputs from the user.
-
The concurrent transactions must access data in the same order. There should be
consistency in which the operations occur
-
Transactions must be short and simple to avoid deadlocks. Long transactions may
block other necessary activities
-
A lower isolation level like read committed must be used. Using lower isolation
levels reduces the possibility of holding shared locks for a longer time
-
Bound connections should be used. Here, two or more connections opened by the
same application can assist each other
The answers to following questions will be made available soon. Keep
visiting.
Describe in brief SQL Server locking.
What are the different types of lock modes in SQL Server 2000?
What is lock escalation? What is its purpose?
Identify the various types of concurrency problem.
Describe isolation levels that SQL server supports
What are the difference between lost updates and uncommitted dependencies?
What is a dead lock?
What is a live lock?
What guidelines should you follow to help minimize deadlocks?
|