In this busy OLTP system should we use a PESSIMISTIC or OPTIMISTIC concurrency?

Hello experts,
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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
There is no right or wrong or better or best method - it all depends on the scenario and what errors you meet.
Also, it is not clear if you actually have encountered concurrency issues, or if you are just preparing for the worst.

If you have code updating tables, you may benefit from my article:

Handle concurrent update conflicts in Access silently

But if it ain't broke, don't start fixing issues you haven't met.

As a side note, you may also have interest in another of my articles covering deployment:

Deploy and update a Microsoft Access application with one click

It works extremely well for our clients.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NNOAM1Author Commented:
Thank you Gustav. Are you suggesting that the normal scenario is to start with the default pessimistic isolation level provided by SQL Server, and only if we encounter concurrency problems we will consider applying optimistic isolation levels?
Gustav BrockCIOCommented:
Yes. Indeed as it is a huge system that - as I can read - is proven and tested in all corners.

Of course, use the SQL Server Migration Assistant for Access to initially build the SQL Server database, use the newest SQL Server native ODBC driver, and assign a small group of dedicated users to run a test system for a month or so. There will be things to handle, for example new bottlenecks, because an SQL Server is very different from an Access backend.
Then, when it runs with no errors, you can start optimising the system to reduce bottlenecks and improve performance. This should never be the first steps.
David ToddSenior Database AdministratorCommented:

Depending on the size of your database, there exists a lot of room for improved performance upsizing to SQL. But if the front end queries have been tuned for Access, then that won't necessarily be the same for SQL.

Something to watch out for: SQL Developer is free for development at present; But I imagine that most of your customers will use either SQL Express or SQL Standard. Be aware that Express and Standard optimise differently to Developer. That is, to head off negative performance reports you'll need to QA and performance test on your own copy of SQL Standard!

NNOAM1Author Commented:
Thank you Gustav and David!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.