Deadlock problems with MQT staging table

tsluijter · #1 (**permalink**) 01-27-11, 03:18

Hello,

We have an MQT table with a select of

Code:

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)?

Thanks in advance, Tinie.

Internet Services

Web Development

Digital Marketing

Consumer Tech