Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: Deadlock on a select query, possible?

    I have a simple select query that selects data from a view. I consistently get a deadlock exception when running this query:

    Server: Msg 1205, Level 13, State 2, Line 1
    Transaction (Process ID #) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    The view is a simple select statement that has WITH (NOLOCK) as a hint on all tables. I thought I understand how deadlocks worked, two threads are holding a lock and request the other's item. How does a read uncommitted select statement participate in this?

    If I look at the other processes in the current activity and run sql profiler, there is no other activity and no existing locks at the time the statement is run.

    Can anyone explain this? Or should I bounce my server and hope it never happens again?

    Thanks,

    Dave

  2. #2
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Although the (NOLOCK) hint is puzzling, it is possible to deadlock on a single SELECT. I replicated this behavior about 12 years ago, but can't remember the specific scenario other than it was happening on a wide table (I think there was one row to a page, 2K pages) and the deadlock happened on the index.

    To see where the deadlock is occuring, you may want to try:
    Connection 1:
    DBCC TraceOn(1204)
    go

    Begin Tran
    Select foo From v_bar WITH (NOLOCK)
    go

    Connection 2 (immediately after executing 1)
    Exec sp_lock
    go

    Check the errorlog to see the results of the trace and read up on Troubleshooting Deadlocks in SQL BOL.

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •