January 2010 - Posts

• Correlated subqueries do not execute once per row.

  Correlated subqueries do not have to execute once per row - on the contrary, they are equivalent to outer joins, and they may have the same execution plans and the same real execution costs (if we retrieve only one column via a correlated subquery, of Read More... Posted Wednesday, January 20, 2010 6:08 PM by Alexander Kuznetsov | 13 Comments Filed under: Transact SQL

• When acquiring locks in the same order is not possible or not feasible.

  To avoid deadlocks, one of the most common recommendations is "to acquire locks in the same order" or "access objects in the same order". Clearly this makes perfect sense, but is it always feasible? Is it always possible? I keep encountering cases when Read More... Posted Friday, January 15, 2010 4:18 PM by Alexander Kuznetsov | 6 Comments Filed under: Transact SQL, Defensive programming, Deadlocks

• T-SQL Tuesday #002: patterns that do not work as expected.

  Neither UPDATE … IF (@@ROWCOUNT = 0) INSERT nor IF EXISTS(...) UPDATE ELSE INSERT patterns work as expected under high concurrency. Both may fail. Both may fail very frequently. MERGE is the king - it holds up much better.Let us do some stress testing Read More... Posted Tuesday, January 12, 2010 9:38 AM by Alexander Kuznetsov | 14 Comments Filed under: Transact SQL, Defensive programming, Stress Testing

• Retrying after deadlocks leads to lost updates

  When we retry after deadlocks, we are very likely to overwrite other processes' changes. We need to be aware that very likely someone else modified the data we intended to modify. Especially if all the readers run under snapshot isolation, then readers Read More... Posted Friday, January 08, 2010 10:15 AM by Alexander Kuznetsov | 0 Comments