Avoid deadlock among data-read in SQL Server

Question

We have an issue with "deadlocks" in SQL Server, where there are no explicit locks involved and would like to know, how to get around them.

Some relevant background info: Our application is quite old and large. We recently began to remove some concurrency-hindering issues and doing so we stepped onto the deadlock-thing on SQL Server. We do not have the resources to tackle each select statement within the application but are looking for a more general approach on configuration level.

We can reduce one exemplary problem as follows: Basically, we have two entities, EntityA and EntityB. Both are mapped to individual tables in the SQL Server schema. Between both entities, there is a m:n relation, mapped within the database via a AToB table, which contains some additional context (i.e. there can be more than one entry within AToB regarding the same A and B.

During one business operation a new instance of A and B are inserted into the database and also multiple entries within the AToB table. Within the same transaction at a later point all this data is read again (without for update). When executing this operation in parallel deadlocks occur. These deadlocks are linked to the AToB table.

Say, we have A1 and B1, which are linked via A1B1_1 and A1B1_2 and A2 and B2, which are linked via A2B2_1 and A2B2_2. My guess is the following happens:

t1 -> INSERT A1
t1 -> INSERT B1
t1 -> INSERT A1B1_1 (PAGE1)
t2 -> INSERT A2
t2 -> INSERT B2
t2 -> INSERT A2B2_1 (PAGE1)
t1 -> INSERT A1B1_2 (PAGE2)
t2 -> INSERT A2B2_2 (PAGE2)
t1 -> SELECT * FROM AToB WHERE AToB.A=A1
t1 -> SELECT * FROM AToB WHERE AToB.A=A2

Now, during concurrent reads on the AToB table t1 obtains a lock on PAGE1 and t2 obtains a lock on PAGE2 resulting in a deadlock.

First question: Is this a plausible explanation for the deadlock to occur?

Second question: During research I was given the impression, an index on AToB.A might force SQL Server to lock less entries on the table (possibly even reducing it to a row- instead of a page lock). Is this right?

Third question: I further got the impression, this problem might be solved by snapshot-locking. Is that right?

We tried this approach, however it lead us into the next circle of hell: During the business-transaction at one point a business-identifier is assigned to A. This comes from a separate table and it must be unique among the As. There is no possibility to assign this via a database-sequence. Our solution is to assign this identifier via an select/update on a fourth table Identifier. This is done via a for update statement. When employing snapshot-locking this for update lock is ignored during acquisition and only leads to an optimistic locking exception during commit. This leads us to

Fourth question: When using snapshot-locking, is it possible to still have a special transactions, which still run on pessimistic locking or is it possible to tell SQL Server, some tables are excluded from optimistic locking?