Blocking Locks and Deadlocks

Applications use database locks to control data integrity in multiuser concurrency situations. Poor database design coupled with uncoordinated business processes can cause crippling database lock contention and destroy your application's performance.

Most databases have a number of locking capabilities. For example, Microsoft^® SQL Server™ provides row, page, and table locking. While normal processing in a multiuser environment can conveniently use locks, your application will run faster without locks. Considerable design effort should be applied to table design, indexing strategy, and query optimization to avoid locking situations.

The following topics discuss blocking locks, deadlocks, and present some design strategies for avoiding them.

Blocking Locks

A blocking lock occurs when one lock causes another process to wait in a holding queue until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common (rather than infrequent), there is probably some kind of design or query implementation problem.

Some of the common design problems that cause blocking locks are very wide tables, insufficient indexes, and tables that are not completely normalized. If your application is using SQL Server, you can monitor the total number of blocking locks by using the User Activity Monitor in Microsoft SQL Server's SQL Enterprise Manager.

Deadlocks

Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions.

Long simultaneous transactions which variously lock and unlock resources must be carefully designed to avoid deadlocks.

Lock-Avoiding Design Strategies

There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

• Use clustered indexes on high-usage tables.
• Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
• Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions.
• Make sure that UPDATE and DELETE statements use an existing index.
• If you use nested transactions, be sure there are no commit or rollback conflicts.

For More Information   For more information on how to use locks, see Managing Concurrency With Cursor Locks in Chapter 7 of this book. For more information on the types of locks available with SQL Server, see SQL Server Cursor Library in Chapter 7 of this book. For more information on using bound connections with SQL Server, search online for "Bound Connections" in MSDN Library Visual Studio 6.0.