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.