How to prevent table locking with multiple users writing and reading from table at the same time?

I have a program I wrote for a group that is constantly writing, reading and deleting per session per user to our SQL server. I do not need to worry about what is being written or deleted as all the data being written/deleted by an individual will never be needed by another. Each users writes are separated by a unique ID and all queries are based on that unique ID.

I want to be able to write/delete rows from the same table by multiple users at the same time. I know I can set up the session to be able to read while data is being written using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

The question:

That said can I have multiple users write/delete from the same table at the same time?

Currently my only idea if this is not possible is to set up the tool to write to temp tables per user session. But I don’t think that is an efficient option to constantly create and delete temp tables hundreds of times a day.

Answer

Yes you can make this multi tenant approach work fine.

1. Ensure leading column of all indexes is UserId so a query for one user never needs to scan rows belonging to a different user.
2. Ensure all queries have an equality predicate on UserId and verify execution plans to ensure that they are seeking on it.
3. Ensure no use of serializable isolation level as this can take range locks affecting adjacent users.
4. Ensure that row locking is not disabled on all indexes and restrict DML operations to <= 5,000 rows (to prevent lock escalation)
5. Consider using read committed snapshot isolation for your reading queries