When is a Deadlock not a Deadlock?

Question

I'm asking this question because I'm getting a deadlock from time to time that I don't understand.

This is the scenario:

Stored Procedure that updates table A:

UPDATE A
SET    A.Column = @SomeValue
WHERE  A.ID     = @ID

Stored Procedure that inserts into a temp table #temp:

INSERT INTO #temp (Column1,Column2)

SELECT B.Column1, A.Column2

FROM B

INNER JOIN A
  ON A.ID = B.ID

WHERE  B.Code IN ('Something','SomethingElse')

I see that there could possibly be a lock wait but I fail to see how a deadlock would occur, am I missing something obvious?

EDIT:

The SPs that I typed here are obviously simplified versions but I'm using the columns involved. The structure of both tables would be:

CREATE TABLE A (ID IDENTITY
                   CONSTRAINT PRIMARY KEY,
                Column VARCHAR (100))


CREATE TABLE B (ID IDENTITY
                   CONSTRAINT PRIMARY KEY,
                Code VARCHAR (100))

Answer 1

Try this since its causeing locks specify for the tables name the table hint and keyword:

WITH(NOLOCK)

So some thing like this for your scenario:

    INSERT INTO #temp (Column1,Column2)

    SELECT B.Column1, A.Column2

FROM B WITH(NOLCOK)

INNER JOIN A WITH(NOLOCK)
  ON A.ID = B.ID

WHERE  B.Code IN ('Something','SomethingElse')

See how you go then.

You can lookup table hint also for tsql, sql server to see which one suits you best. The one I specified NOLCOK will not cause locks and also it will skip locked rows as some other process is using them, so if you dont care you can use it.

I am not sure with temp tables but you can also use table hints with INSERT, INSERT INTO WITH(TABLE_HINT).