When is Dead End not a dead end?

Question

I ask this question because from time to time I get a dead end that I do not understand.

This is the scenario:

Stored procedure that updates table A:

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

Stored procedure that is inserted into the temporary #temp table:

 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 might be a wait for a lock, but I don’t see how a dead end will happen, will I miss something obvious?

EDIT:

The SP that I typed here are obviously simplified versions, but I use the columns used. The structure of both tables will 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 because its locking reasons determine the table hint name and keyword for the tables:

 WITH(NOLOCK) 

So for example 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 are then.

You can find the table hint also for tsql, sql server to find out which one suits you. The one I specified NOLCOK will not cause locks, and it will also skip locked lines, as some other process uses them, so if you don't care, you can use it.

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