Top tag
Next privilege 200 Rep.
See reduced ads
~14k people reached

  • 0 posts edited
  • 0 helpful flags
  • 24 votes cast
comment Deadlocks From Locks on Same Temporary Tables In Different Processes
I can confirm that lock partitioning is involved here. These posts has some detail about analyzing deadlocks involving and due to lock partitions. But I don't know why both the sessions posted the same ObjectID.
comment ASPState database locking and growth problems
This is the latest supported version of the procedure. The latest version is supposed to work on individual rows based on the clustered index key and intended to avoid blocking active sessions. The older version was prone to lock escalation and severe blocking. Are you actually observing blocking on this procedure?
comment Why is this key lookup required?
@brain For lookups (and nested loops) you should compare the Actual Number of rows with (Estimated Number of Rows * Number of Executions). In this case it is 10259536 = (10259536 * 1). So there is no statistics mismatch on this Index. However, the Estimated Number of Executions (derived from the Estimated Number of Rows from the Outer side of the key lookup) 1216 is significantly lower than the actual number of executions, which may indicate a problem with the statistics on the NC Index.
comment SELECT/INSERT Deadlock
The locking behavior of OPTIMIZED Nested Loops is comparable to REPEATABLE READ ( keeps the locks till the end of the statement), but it does not explicity set the isolation level of the transaction to REPEATABLE READ. I think that answers your question one too. It is not that the parallel threads are requesting more than one page at a time, but one parallel thread is holding a lock on one page and another thread is waiting for a lock on another page