2 of 4 deleted 3 characters in body

Is it necessary to force row locks to avoid deadlock in this scenario?

Suppose I want to atomically reserve inventory for N objects for M users.

I have two inventory tables, one for global inventory and one for personal inventory, because I have to enforce maximum inventory restrictions for an object globally, as well as per-user (i.e. max of 1000 of object A reserved overall, and max of 10 of object A reserved per user).

The global inventory table is keyed on {ObjectID} and the personal inventory table is keyed on {UserID, ObjectID}. The primary key is the only index on each table. (I think that's important for exclusive row-level locks to be correctly functioning). The personal inventory table may also be sharded onto another database, so the transaction may be distributed with the global inventory table on one database and the personal inventory table on another database.

Global Inventory Table: {ObjectID}, Count
Personal Inventory Table: {UserID, ObjectID}, Count

Suppose a request arrives to reserve a quantity of 1 for objects B, A, and C. This will be a two-step transaction where all relevant records in the global inventory table are updated first, and all relevant records in the personal inventory table are updated afterwards.

First, I begin a transaction.

Next, I sort the object IDs to ensure row-level locks are taken out in the same order by concurrent operations, so deadlock cannot occur.

Step 1: Lock/update desired rows in the global inventory table. For each ordered object ID, I'm locking the corresponding record in the global inventory table by running an update statement to increment its count by the desired quantity to reserve. Because a transaction is active, an exclusive lock is taken on those rows and held through the end of the transaction, so other concurrent transactions will block waiting for the first transaction to commit the updated rows before they can obtain a lock to update the rows.

Assuming that's all correct, at this point in the transaction, exclusive row locks should be held for records A, B, and C in the global inventory table.

Step 2: I want to update records in the personal inventory table. The hope here is that because no other transaction should be holding locks to global records A, B, and C, and all global locks are taken out before any personal locks are taken in this transaction, we can infer that no other transaction should hold locks to the personal inventory records of any user for objects A, B, and C. We infer that's the case, because they would have first had to take out the locks for the global records, which we just established cannot be the case for any other transaction.

My question is, in order for that last paragraph to hold true, must I force the database engine to take out row-level locks to ensure its correctness and avoid potential deadlock?

To avoid deadlock, I have to be sure no other concurrent transaction has any of the personal inventory rows locked that I intend to update, and in order to guarantee that, I have to ensure that no other transaction has inadvertently locked those records. I think the only way to guarantee that is to ensure only row-level locks are taken out, because a page-level lock in another transaction could inadvertently lock records I intend to update.

For example, suppose a concurrent transaction locks global record D, so it seems totally unrelated to the first transaction working with A, B, and C. None of the global or personal inventory records should overlap, so there should be no lock contention. This second transaction enters the phase where it updates personal inventory records for 1000 different users for records associated with object D. However, if it takes out page-level locks in the personal inventory table, and some of those pages for D happen to contain records for object B, transaction D could inadvertently hold page locks for records belonging to both D and B. Likewise, the first transaction may also hold page-level locks that contain records for B and D, and neither transaction can proceed because each one has locked pages that the other is waiting for.

My main concern are whether forcing such row-level locks is even possible, especially at scale if I'm locking thousands of rows (e.g. reserving quantities for 4 objects for each of 1000 users), which would hold 4000 distinct row-level locks. I'm concerned more about correctness than performance here. Using database row locks is going to be many orders of magnitude faster than and other locking solution that involves multiple round trips to the server. By using database locks, multiple concurrent transactions can complete quickly, one after another, with minimal lock contention. This will result in transactional, high-throughput reservations, without having to worry about managing transactions at the application-level, which ultimately would be more complex and less reliable.

A follow up question might be, if forcing row-level locks will not perform well, is there a way to force an index to put different parts of a composite key on different data pages. For example, if I instead keyed the personal table on (ObjectID, UserID), is there any way to force SQL Server to ensure pages are fragmented so each page contains data for at most a single ObjectID (and many users)?