What is deadlock in SQL Server?

Asked

Viewed 12,225 times

22

I am trying to solve a problem with an error in SQL Server and seeing a question in the OS, in the reply is quoted a possible 'deadlock' in SQL Server. What is this?

sql-server

3 answers

30

This illustrates well what a deadlock: inserir a descrição da imagem aqui

There are four conditions for the occurrence of deadlock:

  • Mutual exclusion condition: At a given time, each resource is in one of two situations: either associated with a single process or available.
  • Condition of can and wait: Processes that, in a given instant, retain resources previously granted can request new resources.
  • No preemption condition: Resources previously granted to a process cannot be taken to force this process, they must be explicitly released by the process that retains them.
  • Circular hold condition: There must be a circular chaining of two or more processes; each of them is waiting for a resource being used by the next member of that chain.

All these conditions must occur simultaneously so that a deadlock.

Regarding your problem, make sure some transaction is not retaining some resource that another transaction needs.

Reference: Introduction to Deadlock - Devmedia

  • 6

    This one deserves to be the answer accepted only because of the photo. + 1.

  • 4

    I leave here an experiment: Recently we had a problem with this here at the company. One user was making some change to a pre-sale, while another tried to turn it into a sale. Devs have chosen to create a 'lock' field and when/while a user is changing some data in the pre-sale, it cannot be transformed into a sale.

  • 2

    @Ciganomorrisonmendez - I remember when I was presenting a paper on Operating Systems, I used this image to explain what deadlock was. I think 100% of the class understood. kkkk

  • 2

    I was admin of a page called Computer Depression. I remember taking this image and putting it into a demotivated one. The result was this

  • Great representation (photo)!

23


A deadlock happens when two or more tasks block each other permanently, with each one blocking a feature, which the other task is trying to block. For example:

  • Transaction A acquires a shared block of line 1.
  • Transaction B acquires a shared block of line 2.
  • Transaction A now requests an exclusive lock on line 2 and is blocked until transaction B ends and releases the lock shared that you have in line 2.
  • Transaction B now requests an exclusive lock on line 1 and is blocked until transaction A ends and releases the lock shared that you have in line 1.

inserir a descrição da imagem aqui

Transaction A cannot end until transaction B ends, but transaction B is blocked by transaction A. This condition is also called a cyclical dependency: transaction A has a dependency on transaction B, and transaction B closes the circle having a dependency on transaction A.

In this post on the Technet forum talks more about deadlock solution processes.

16

Deadlock is not a unique database concept, but competing applications. In a nutshell:

It is a situation where two or more competing actions are each waiting for the other to end, and therefore this never occurs.

For the case of a database, a deadlock is usually triggered by transactions, and involving exclusive locks. Unique locks usually appear in modification operations.

Suppose two transactions, A and B. A need to modify the same tables as B, but B has already asked for a unique lock on some of the tables that A needs to modify. The result is that A can ask for a unique lock on other tables that B will also use, resulting in deadlock.

The database itself has some internal mechanisms to avoid deadlock, but the mechanism is not infallible and usually demands user actions.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.