What are the different types of resources that can deadlock?
• Answer: Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. Here are the resources: • Locks – Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. • Worker threads – A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result • Memory – When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. • Parallel query execution-related resources – Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determ
