Understanding Locks and transaction process

  • Can anyone tell me how to read and understand the Locks/Process ID and Locks/Object in SQL Server Enterprise Manager under Management. How does SQL transactions process?

     

    thanks


    Kindest Regards,

    Chaz

  • current activity is a snapshot of the state of various processes across databases collected at the specified timestamp. You need to refresh the current activity to get the latest information.

    Process info: This shows the status for each process running in SQL Server. Each logged on user is a process. In addition there are internal processes which are used by SQL Server itself.

     

    Locks by Process ID: This view shows the Process ID and all locks held by the process. Process means user. Therefore, this view is useful for troubleshooting a particular user related problem.

     

    Locks by Object: This lists all locks (from different users and processes) on a given object. This view is useful to find out contentions on a given object.

    In the process info,There are two columns. ‘Blocking’ and ‘Blocked By’. The Blocked By column shows the PID of the process that is blocking the current process. The blocking columns indicate the number of PIDs that are currently blocked by this process.

    Now, if you find a particular transaction that is blocking others, what would you want to do? You would like to know which workstation and user is involved. You would also like to know whether this transaction is generally in hung state or if it is taking a long time to complete. You may want to see the command that was last issued. All this can be viewed by simply double-clicking the item in the processes listing

     

  • Thanks you for your reply, it was very helpful.

     

    Thanks


    Kindest Regards,

    Chaz

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply