Performance Issue

• A deadlock is a situation where in two or more competing actions is each waiting for the other to finish, and thus neither ever does. Do operations in same order.
• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes may be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing duplicate entries in the index and thus the backing table.
• In Non-Clustered Index (Default) the data is present in random order, but the logical ordering is specified by the index. Clustering alters the data block into a certain distinct order to match the index; hence it is also an operation on the data storage blocks as well as on the index. Thus, Clustered Index is created.

Lock Types:

• Shared locks allow multiple transactions to lock the same resource. The lock persists until all such transactions complete. Exclusive locks are held by a single transaction and prevent other transactions from locking the same resource.
• Read locks are usually shared, and prevent other transactions from modifying the resource. Write locks are exclusive, and prevent other transactions from modifying the resource. On some systems, write locks also prevent other transactions from reading the resource.