Unanswered: Deadlock problems with MQT staging table
We have an MQT table with a select of
SELECT <multiple columns>
FROM registration, registration_preference, opleiding, registration_preference_log, instelling, registration_preference_last_log
WHERE registration.id = registration_preference.id_registration
AND opleiding.id = registration_preference.id_education
AND registration_preference.id = registration_preference_last_log.id_registration_preference
AND registration_preference_log.id = registration_preference_last_log.id_last_log
AND instelling.id = opleiding.id_instelling
The MQT table is initially deferred and refresh deferred.
We also have a staging table attached to the MQT table and refresh the MQT incrementally every 3 minutes.
The problem is that we get a lot of deadlocks, because every update of one of the tables in the select the staging table will be locked. And there are a lot of concurrent updates.
Does someone know a way that only one row in the staging table will be locked and not the whole table (We know the staging table will be locked with a refresh, but this isn't the problem)?
Are there other solutions then a MQT table. We've tried a view, but then we have poor performance (the MQT has about 3 million records)?