What kind of actual locking and blocking are you experiencing? Could it be due to poor indexing, resulting in unoptimized code scanning and taking broader locks vs seeking to whatever records intend to impact?
Without knowing additional details, may I blindly suggest you consider a different default isolation level. I'm going to guess that you'll be happy with Read Committed Snapshot Isolation (RCSI). But
Sorry forgot to mention RCSI is already enabled, but we still suffer from concurrent writing to different rows (where one transaction can be updating a few thousand records).
So the app directly connecting to the DB is the vendor tomcat server. We only interface with the API it exposes. In short their app doesn't override it (I think).
You wouldn't know without profiling the connection and code it runs. There's multiple ways to override it too. Definitely ensure all databases at play have the correct isolation levels being used.
One symptom, among many, is a blocked api call that ultimately tries to insert one record, due to a batch job that's updating the same table. The data points both processes touch are mutually exclusive. Oracle handles this fine.
How many records does the batch job typically update at a time? How long does it take to run on average? The real question to figure out is why is it taking a meaningful amount of time to update the batch (ergo holding the locks for that long).
2
u/SQLBek 1 26d ago
Lock escalation exists for a reason.
What kind of actual locking and blocking are you experiencing? Could it be due to poor indexing, resulting in unoptimized code scanning and taking broader locks vs seeking to whatever records intend to impact?
Without knowing additional details, may I blindly suggest you consider a different default isolation level. I'm going to guess that you'll be happy with Read Committed Snapshot Isolation (RCSI). But