Deadlocks and the adaptive workload manager

When applications open multiple cursors concurrently, it is possible that the applications might become deadlocked if the resource requirements for the cursors exceed the configured database resources.

Consider the simple scenario where an application opens a cursor that uses 75% of the configured sort memory. Note that as long as this cursor remains open, it still consumes both thread and memory resources. The application then opens a second cursor which requires 40% of the configured sort memory without closing the first cursor. This is a resource-based deadlock. In such a scenario, if the adaptive workload manager queues the second cursor to wait for available resources, the application would hang, because it is effectively blocked waiting for itself.

This can be generalized to multiple applications where a resource-based deadlock occurs whenever the resources held by the queued applications are great enough to prevent any further work from being admitted to the database. In such a scenario, if queuing an application would result in a resource-based deadlock (that is, if all resources are held by queued applications), the application that would normally cause the deadlock instead fails with SQL code -911 and reason code 76. This causes the application to roll back its cursors, and allow the database to continue to process additional queries.

For example, suppose applications A, B and C each open a cursor that uses 30% of the memory, thereby consuming 90% of the available memory. Now, each of A, B and C run a second statement in sequence that requires another 30% of the memory apiece. The statements issued by applications A and B each queue, because there is currently no available memory; however, they could proceed when the cursor opened by application C completes. When the second statement for C arrives, it fails with a SQL911 rc 76 error, because queuing this statement would result in a resource-based deadlock. In other words, if the statement from C queues, 90% of the available memory would be held by queued applications (A, B and C), and the queued applications each need more than 10% of the memory, so they are never able to make progress.