• mr_effe (4/17/2014)


    I think this solution doesn't help me. Because a switch partition leads to a Schema-M lock. And if all of my parallel processes wants a schema-m lock nothing works anymore.

    I already had this situation with my "truncate partition" function which uses switch partition to delete the table partition. It works fine if only one process is active. If i started the jobs in parallel all of them waits for each other. So I have already switched the logic from "truncate partition" to a delete logic.

    So I think this solution will not help me 🙁

    If you have N partitions, you can use N+2 jobs:- one which switches the partitions out into the new table and then kicks off the insert job for each partition, one per partition for the inserts, and one which polls to find finished jobs from the set handling the inserts and when it finds one switches the corresponding partition back and notes that it's dealt with that job so it won't poll for it again, and when there is nothing left to poll for terminates.

    That avoids the clashes on the schema mod lock.

    Obviously you need to deal with one of the insert jobs failing, but it's probably easier to deal with than it is in the case where the insert jobs are clashing on locks.

    Tom