-1

I have a windows application that is used by multiple users, often there are deadlocks in the database related to lock resources.

Looking at the deadlock graph I see 2 processes and 2 resources, arrows representing the own/request, and that 1 process is killed to allow the other process to continue. Seems logical so far.

I am trying to understand what exactly does a process in the deadlock graph represents because the query shown when hover over the Process is not the same as the query that holds the lock on the resource. So assuming there is a long transaction- begin transaction, followed by 100 lines of sql and commit. Now will each sql code run in single process?

Improve this question

2 Answers 2

Reset to default
2

You will need to read up on the SQL Server scheduler to get the exact idea on how this works.

The simple explanination is that she SQL Server OS (SQLOS) is a non-preemptive scheduler where all queries are queued internally and then the SQLOS will schedule the tasks on it's worker threads, which are reserved when the service starts.

So there is not a one-to-one relationship beween connections and worker threads (processes).

There is plenty of information about how to read deadlock graphs, I like this series.

Improve this answer
1
  • Does 1 long transaction run in its entirety inside 1 process?
    – variable
    Commented Sep 10, 2021 at 14:36
2

The process ID in the deadlock graph is an internal ID that represents a particular Unit Of Work. It is not related to the operating system's process IDs. As far as the OS is concerned there is only one process. It is purely internal to SQLOS, the internal scheduler which manages worker threads and UOW.

As far as parallelism is concerned: that is correct, it's unclear what the actual question is. The process is attached to the original query.

Improve this answer
7
  • Does 1 process run the entire transaction? And then once its done with this transaction, then the same processes runs a different transaction?
    – variable
    Commented Sep 9, 2021 at 13:16
  • 1
    No, I thought I was clear: a process represents some work to be done, and is queued against a worker thread. The threads are long-lived and execute each process in turn
    – Charlieface
    Commented Sep 9, 2021 at 13:18
  • So 1 transaction (assuming begin transaction followed by 100 lines of code and commit) can run across multiple processes?
    – variable
    Commented Sep 10, 2021 at 7:59
  • Only if it goes parallel, otherwise there is only one UOW at a time
    – Charlieface
    Commented Sep 10, 2021 at 8:36
  • Ok and the sql scheduler will handle the transactions concept? The process will only execute what scheduler assigns to the process?
    – variable
    Commented Sep 10, 2021 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.