We have a table of values that has about 5 million entries in it. That table is pretty static, it's only updated every few weeks, but new records are inserted and old ones are modified during business hours.
We have a few different stored procedures that run all day and are updating records in other tables using the values that are in the large table. The queries usually run pretty quickly but occasionally one will end up running long, and then the database locks start and everything gets hung up until the first query finishes, or it deadlocks and throws and error and then everything starts running again.
Could I add this around the query so that the update wouldn't lock the table?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Update SQL here
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
(or am I totally missing an isolation level understanding?)
Since the large table is only updated every few weeks, and the potential for dirty reads is limited, and we have an overnight data integrity processes that's already in place to specifically look for any records that match to the table (due to late start/end date entries, etc), and it's not time critical to be 100% the first pass (can get picked up in the overnight scan without business implications).
Is that something that could work?