In a previous post, I presented a solution for monitoring and logging long running queries in SQL Server. In this post I’ll show an extended version of that solution that also sends an email when a request has been blocked for more than 20 seconds.
Deadlocks in SQL Server can be difficult to avoid completely, but there are some basic rules that can help in avoiding them.
This blog post will cover the basics of deadlocking in SQL Server.
To avoid the blocking of other users, rowlocks are sometimes used to create locks on the most granular locking level in a table. In theory, when user A makes changes to a row, user B can read, change or delete any other row than that. But in practice, user B can still become blocked by user A altough they are not accessing the same row. It all depends on how data is accessed.