Lets say we select from TableA that joins to TableB

TableB also gets data inserted into into it via another query.

So I guess SQL server places a shared lock on Table A and Table B.
and the insert will try and put an exclusive lock on the table.

We then get a deadlock. What I am trying to get my head around is why?
I would have thought that the Insert query would wait until the lock
was released on table B. Ok the select query does take a long time,
but I would have thought the insert would time out.

I know there is something I am missing in my logic. I am just trying
to figure out how the tables become deadlocked in this situation.

TIA

bozzzza (bozzzza@googlemail.com) writes:
> Lets say we select from TableA that joins to TableB
>
> TableB also gets data inserted into into it via another query.
>
> So I guess SQL server places a shared lock on Table A and Table B.
> and the insert will try and put an exclusive lock on the table.
>
> We then get a deadlock. What I am trying to get my head around is why?
> I would have thought that the Insert query would wait until the lock
> was released on table B. Ok the select query does take a long time,
> but I would have thought the insert would time out.

Without seeing the queries and the query plans, the field is open for
wild speculations.

But to start with, locks are far from always on table level. Most commonly
locks are on row level, or key level in case of an index.

I would guess the problem has to do with index and data pages being
access in different order.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx