February 27, 2009 at 7:09 am
Hi all,
I have problem.
I am running a database for realtime production registration.
My problem is situated around 4 tables.
I record production data in 2 tables wich are related 1-n : LOADS & SUBLOADS
both tables are also in realtime archived (with a trigger) to archivetables which contain more detailed info about the operations : ARCHIVE_LOADS & ARCHIVE_SUBLOADS, and are not a part of a greater data structure. Basically, its the entire datamodel around the LOADS flattened to fit in one record. This makes sense for my application, so i want to keep this.
What happens is when i update a record in the LOADS table, the trigger performs an update on the ARCHIVE_SUBLOADS table which seems to create a table lock, that stalls all further insert/update actions on the LOADS table.
Unfortunately, there is a program that generates numbered reports that updates the loads table with the report numbers, and only commits that after the reports are actually generated (may take up to 15 seconds). This is done to ensure the report numbers are unique and there are no holes created.
In the meantime all other processes attempting to store LOAD data fail with deadlock exceptions.
Extra info : the LOADS and SUBLOADS (as well as the archive) tables are approx 20000 resp. 30000 rows large.
The update statement creating the problematic lock :
UPDATE [dbo].[ARCH_METERS_LOADS] SET ID_ARCH_LOAD = (SELECT MAX(ID_ARCH_LOAD) FROM ARCH_LOADS WHERE ID_LOAD = @ID_LOAD) WHERE ID_LOAD = @ID_LOAD
There are at most 3 records affected by this query. It corrects the reference from the subload to the parent load object.
Why does this thing lock my entire table ?
Regards
Tom
February 27, 2009 at 7:29 am
Writing it 'off' worked i guess.
Missing index caused a table scan, thus locking the entire table.
Problem fixed.
Thx for reading 😉
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply