SQL Server 2019: Difficulty updating a table with a join on a subquery result causing deadlocks
I've spent hours debugging this and I've tried everything I can think of but I've looked through the documentation and I'm still confused about I'm working with deadlock issues when trying to update a table using a join with a subquery in SQL Server 2019... My update statement looks like this: ```sql UPDATE t1 SET t1.columnA = t2.newValue FROM Table1 t1 JOIN ( SELECT id, MAX(value) as newValue FROM Table2 WHERE condition = 'some_condition' GROUP BY id ) t2 ON t1.id = t2.id; ``` I've tried using `WITH (NOLOCK)` in the subquery to avoid reading rows locked by other transactions, but the scenario continues. The behavior log indicates deadlocks happen frequently, especially during peak hours. I'm also using a non-clustered index on `Table1.id`, but I'm not sure if it's being used efficiently. Additionally, I attempted to implement a retry logic in my application when a deadlock occurs, but this doesn't solve the underlying scenario. Any suggestions on how to optimize this query or restructure it to reduce contention and avoid deadlocks? Is there a better approach to executing this update? My database is running in Compatibility Level 150. I would appreciate any insights or best practices that could help manage this situation better. I'm working on a API that needs to handle this. This issue appeared after updating to Sql stable. This is my first time working with Sql 3.10. Has anyone dealt with something similar?