Summary
Database Blocks occur when a database transaction places an Update Lock or Exclusive Lock on a database resource while other database transactions are attempting to access that database resource.

Database Deadlocks occur when two database transactions require two separate database resources that the other database transaction has exclusively locked or locked for update. The deadlock ocurrs because neither database transaction can complete until the blocking database transaction releases the database resource. For example, Database Transaction #1 has an exclusive lock on the Customers table, but needs access to the Orders table to complete its transaction. However, Database Transaction #2 has an exclusive lock on the Orders table, and needs to access the Customers table in order to complete its transaction.

Mitigating Database Blocks and Deadlocks

1. Lock Type – Review the Lock Types used by your connection, recordset and in your SQL: Optimistic, Pessmistic, and Read Only.
2. Cursor Type – Review the Cursor Types used by your connection, recordset and in your SQL: Dynamic, Keyset, Static, and Forward-Only.
3. Lock Scope - Minimize the amount of data that is locked by the database transaction: Row, Page, Extent, and Table.
4. Lock Escalation – The lock escalation setting determines how many page locks are granted for a database transaction before the lock is escalated to a table lock. You can set lock escalation for a specific database transaction, a specific database session or for an entire database.
5. Lock Tuning – Database transactions can be optimized using optimizer hints. Microsoft's support documentation as well as Database Administrator (dba) websites are a good source of information about Query/Optimizer Hints.
6. Indexes – Review the indexes of the table(s) of the database transaction. Re-index the indexes as necessary. Remove and de-cluster indexes where possible.
7. Error Checking – Add error checking to database transactions in order to avoid transactions that have “died” due to an error.

Observing Locks and User Sessions

1. sp_lock – The sp_lock Stored Procedure is used to view the locking behavior of a database transaction.
2. sp_who – The sp_who Stored Procedure is used to view all the current system processes including blocked processes.