How do I avoid deadlock problems in my application?

An unavoidable feature of any lock-based concurrency system is that the system may block under certain conditions. When a connection occupies a lock and another connection attempts to change the state of the lock, the second connection either waits for the first connection to release the lock or is thus blocked.
To achieve the best scalability, performance, and concurrency, you should be careful when designing applications and query statements to minimize the length of the transaction and reduce the time it takes to hold the lock. Most concurrency problems often occur in the design phase of the application and database, so it is critical that the design phase take this factor into full consideration.
Otherwise, the application is potentially flawed and will typically be discovered when it comes to application extensibility testing.