I've read the 2018 book 'Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners' by Dmitri Korotkevitch, but still I can't tell what the best choice is.
There's a 18-year-old MS-Access huge ERP application, which is now in my responsibility to 'upsize' to SQL Server. Up till now it's been an Access FE working with an Access BE.
The app is to be used simultaneously by 5 to 50 users, depending on the client. The are small ones out there and there are big ones as well. The company has been selling this app as a commercial off-the-shelf software.
The most critical point we have to think about is concurrency: Dozens of users are going to 'shoot' dozens of inserts each minute, others will update existing records from the same tables at the same time. It's a beehive full of hectic activity all day long. They all have to see a true state of the data in every SELECT statement they might be doing. They occasionally run some ad-hoc reports that are needed for further decision making along the workday. The application won't be split into OLTP and DWH in the foreseeable future. So the reports are here to stay.
I have some experience with converting small Access applications to work with SQL Server. I've always been using the default Read Committed Isolation Level, and I admit to have been a sinner, using 'WITH (NOLOCK)' in some of my SELECT statements. Now in this big project I know I can't get away with that; I have to deal with the concurrency issue.
My desperate questions are:
In this busy OLTP system should we use a PESSIMISTIC or OPTIMISTIC concurrency?
what should we do to eliminate the drawbacks of each?
In what way should we write our stored procedures in order to play gracefully in each of these methods?
I really appreciate any help from you. Thank you very much.