SQL Server deadlock and Data Pages

Question

1. If a row in a table (without primary key) is locked when some modification(update query) is taking place for that row, I assume that the intent locks are first acquired on table, then page before the exclusive lock is acquired on the row.

   Now let's say that some other thread wishes to make modification(update query) for some other row in the same table at the very same time, then SQL Server throws the following error:

   Msg 1205, Level 13, State 45, Line 1
   Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

   Now is this error might be the reason that the other row of the same table was amongst the data locked in the same data page of the first query?

2. I know data page also selects additional data that we do not request for. So if we have a primary key in a table, then will Data Page still select additional data or only that row with the primary key?

Answer 1

1. might be the reason that the other row of the same table was amongst the data locked in the same data page of the first query?

No. If both queries use the same granularity (eg. row) then they will obtain compatible intent locks at the high levels (table(partition), page) and different locks at the low (row) level. Even if they use incompatible granularities, no deadlock will occur. Blocking may occur, but no deadlock.

I know data page also selects additional data that we do not request for. So if we have a primary key in a table, then will Data Page still select additional data or only that row with the primary key?

This just doesn't make any sense. You are mixing logical (primary key) with physical (data page) and the data page also selects additional data that we do not request for is quite literally impossible to parse for me. What I can only speculate you're trying to say is the following:

In a table organized as a heap (no clustered index) all scans have to inspect every row to test a predicate. This will result in lock conflicts.

When concurrent updates occur on a heap (a table without a clustered index) if there are no nonclustered indexes to consider then no updates can deadlock. All updates will scan the table in the same order (the heap physical allocation order) and all locks will be acquired in the same order. Updates can block, but not deadlock.

When concurrent updates occur on a heap or a table organized as a clustered index, but there are non-clustered indexes then each update can use one (or more) non-clustered index to locate the candidate rows for the update. Updates that do not use the same predicate (ie. different WHERE clauses) can use different NC indexes, in different order. These can deadlock, as the order of acquiring the locks will differ.

When concurrent updates occur on a table organized as a clustered index then deadlocks can occur because the application can requests updates explicitly in an order that results in deadlock (Tran 1 updates keys A then B while Tran 2 updates keys B then A).

There are many more ways deadlocks can occur, but these are the basic ways UPDATE vs UPDATE deadlocks can occur.

If you want an answer about a deadlock, any deadlock, in SQL Server always start by capturing the deadlock graph. Without the deadlock info everything is speculation.

PS. Please don't refer to clustered organized tables as 'primary key'. Primary key is a logical concept.