Tables locked...need input
-
I am working with an app which writes checks to pay invoices. When printing the checks, many of the tables used to store data are locked due to transactions we use for the ability to rollback the entire check run. The problem with this is that other users wanting to enter the system to create new records which also use some of these tables (distributions, etc) are locked out even if we are only printing one check. I have considered trying ROWLOCK, but that would involve many code changes to objects which we use throughout our system. We use ODBC to connect from a VB app. Any thoughts/input would be appreciated.
-
Not many choices, but here's my 2 bits:
Speed up the check printing process to decrease the amount of time the locks are held.
Have a performance Tuning Specialist come in and take a look at the database, they might recommend:
Hardware upgrades, nightly/weekly scheduled index defragging or the most extgreme resort, Table Denormalization to isolate the columns in contention.
I've been in places where the limit such processes(check printing) to non-peak hours like lunch or 5pm and that seemed to help.
Good Luck!
-
just decided to see if i give back to the SQL Server community, so please be gentle on my response.
Here is where i think you can start looking at with a little chance of changing the VB programs. Look at the TRANSACTION ISOLATION LEVEL and see what level the connection(s)for the "print check" process is set at.
at least be set at READ COMMITTED (default for most ODBC), and may want to consider lower to READ UNCOMMITTED but only do this for connections that can accept diry read or uncommited read.
If that doesn't work, then go back to see if any more index can be added for your read process.
Cheers.
slash
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply