SQL Server locks

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

SQL Server locks

Describe in brief SQL Server locking.

SQL server has a locking mechanism which locks the resources to be used by transactions. This is an automatic mechanism. Different modes are used for locking. Locking at a lower granularity would mean locking rows while locking at a higher granularity would mean locking tables.

What are the different types of lock modes in SQL Server 2000?

Different lock modes:

Shared (S): Mostly used for Read only operations like SELECT statements. It allows concurrent transactions to read data. No other transaction can modify the data until the lock is present. The lock is released as soon as the read is over.

Update locks (U): used to prevent dead locks. Used on resources that can be updated. Common forms of deadlocks occur when multiple sessions are reading, locking, and potentially updating resources later.

Exclusive (X): used for data modifications statements like INSERT, UPDATE or DELETE. This lock ensures multiple updates cant be made simultaneously.

Schema: Sch-M or Schema modification locks are used when an operation related to the table schema is being performed. Schema Stability or Sch-S locks are used when queries are being complied.

Bulk update locks: Used when bulk copy is being performed. BU allows processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

What is lock escalation? What is its purpose?

Lock escalation is when the system combines multiple locks into a higher level one. This is done to recover resources taken by the other finer granular locks. The system automatically does this. The threshold for this escalation is determined dynamically by the server.

Purpose:

• To reduce system over head by recovering locks
• Maximize the efficiency of queries
• Helps to minimize the required memory to keep track of locks

Identify the various types of concurrency problem.

Concurrency problem:

Lost updates: this occurs when two or more transactions are trying to update same row without being aware of each other. The last update overwrites updates made by the other transactions, which results in lost data.

Uncommitted dependency (Dirty read): This occurs when a second transaction selects a row that is being updated by another transaction. This second transaction is reading data that may not have been committed.

Inconsistent Analysis (Nonrepeatable Read): This occurs when a second transaction accesses the same row several times and reads different data each time. It is similar to dirty read. However, here it reads committed data, but different data each time.

Phantom Reads: This occurs when a range of rows which is being read by a transaction is deleted or updated. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read, as a result of a deletion by a different transaction.

Describe isolation levels that SQL server supports

The isolation level describes the degree to which the data being updated is visible to other transactions

Serializable: All transactions occur in a completely isolated fashion. This means all the transactions must execute serially.

Repeatable Read: All data records that are fetched by the Select statement cannot be changed unless a Phantom read occurs in case where clause is mentioned.

Read committed: Data records retrieved by a query are not prevented from modification by some other transaction. in this level, read locks are acquired on selected data but released immediately. Write locks are released at the end of the transaction

Read Uncommitted: Here, One transaction may see uncommitted changes made by some other transaction. Hence, dirty reads are allowed.

What are the difference between lost updates and uncommitted dependencies?

Lost updates results in loss of data. It is a write operation. Uncommitted dependency reads data that has not been committed yet.

What is a dead lock?

When two computer programs sharing the same resource prevent each other from accessing the resource results in a deadlock. This deadlock results in finishing the program.

Example:

P1 requests R1 and receives it.
P2 requests R2 and receives it.
P1 requests resource R2 and is queued up, pending the release of R2.
P2 requests resource R1 and is queued up, pending the release of R1

Here, P and R is Program and Resource respectively.

What is a live lock?

A live lock is similar to a deadlock that the states of the processes involved in the live lock constantly change with regard to one another, none progressing. This happens when where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.

What guidelines should you follow to help minimize deadlocks?

Prevent deadlocks:
Mutual exclusion: This means that no process will have exclusive rights on a resource. Processes should request all resources they will need before starting. This is to avoid “hold” and “Wait” situation.

No preemption: If a process that is holding a resource requests for more resources that cannot be immediately allocated to it, then all resources currently being held are released

Circular wait: imposes an ordering on the resources. The requests for ordering resources in an increasing order