In deadlock resolution, SQL Server will choose a victim at random (more on this later) and kill the process. Thankfully, SQL Server has a deadlock resolution mechanism to prevent processes from keeping the database busy waiting for something that will never happen. However, the Update User Skill Count is currently waiting for Update PersonSkill to complete and release its lock on PersonSkills so we now have a scenario where two queries each have something the other needs and will not release their locks until they complete. Ordinarily, the Update User Skill Count query would complete, release its lock and then the Update PersonSkill query could acquire its lock on People and complete its task. Unfortunately, the row in question in the People table is already exclusively locked by the Update User Skill Count query, creating a deadlock. In order to do this, the query needs a lock on the People table and a lock on a range of data in the PersonSkill table, which the query already has a lock for. In this scenario, we have a query that needs to update a People entry as well as a PersonSkill entry associated with that person and a skill.
SQL SERVER DEADLOCK COUNT HOW TO
The error is then propagated to the executing code which can determine how to proceed. When this occurs, SQL Server must terminate one of the two processes, resulting in the query failing to execute and the transaction failing. This article discusses what deadlocks are, how to interpret deadlock graphs, and some options for handling deadlocks.Ī deadlock occurs when two processes are competing for multiple resources in a way that does not resolve itself. When you work with SQL Server long enough on a database with enough traffic, you're eventually going to encounter deadlocks. This is a continuation of a series of articles I've written on SQL Server concepts.
0 kommentar(er)
