How MERGE on two different rows can still deadlock you

I recently ran into a curious deadlock issue. I have a process that performs a lot of updates in a “state” table using multiple, concurrent connections. The business logic in the application guarantees that two connections won’t try to update the same item, so we shouldn’t ever run into any locking issues. And yet, we keep getting deadlocks.

What’s going on here? Hint: it has to do with isolation levels and range locks.

Continue reading

Isolation levels

Isolation levels affect how aggressively SQL Server places and holds locks on tables and schemas. Get too lazy and you’ll end up with phantom data and dirty reads. Be too zealous, and you’ll end up troubleshooting deadlocks. Here’s an overview of the different types of isolation levels available, to help you choose which one is best for you.

Continue reading