DeadLock in nested CTE

Question

Hi,
I created a view and in the view I created some CTE's and i also using nested CTE's but when i am selecting the view it is taking too much time and deadlock is coming can anyone guide me how to avoid deadlock in nested CTE's.

Regards,
Abhishek Sharma

Answer 1

Hi @Abhishek Sharma ,

Welcome to Microsoft Q&A!

Deadlock in SQL Server is a situation where two processes are waiting for one another to issue the locked resources.

If you face one deadlock, you will get below error.

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Did you face above error when you were selecting this view?

If no, this was not a deadlock and more like a performance issue.

If yes, you could enable the trace 1222 to track the details in the error log and find out the direct cause of this deadlock.

DBCC TRACEON(1222, -1)  

In additon,it is recommended for you to post your complete statement of your view, CREATE TABLE statements for your tables together with INSERT statements with sample data, and execution plan if possible, then we could proceed with finding out the cause.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A deadlock is when two processes block each other so that no one can continue. You don't seem to describe that condition, you only have a slow query.

There is nothing particular about CTEs as such - they are just building blocks.

I would suggest that you test your query on a small data set. It could be that your query is incorrect. For instance maybe there is an incomplete join condition which causes a row explosion.