Deadlock question

patrick_j

I am getting random deadlocking when Substitution rules are running within 1 table such as, "Transaction (Process ID 77) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." The run then stops.

The most recent case was in table ClientEmployer. ID 09-0070 was updating PCode from dataset Postcodes +Prov +Town CND, while 09-0071 was updating Address from dataset Street Addresses.

Should I reduce to 1 Worker to avoid this?

I dont' see any obvious dependency between these rules unless any operation within the same table should be dependent on previous ones completing. Please advise.

PlantBasedSQL

Thanks @patrick_j - As of Data Masker v7 I would argue that all fields on a table that need substituting could feasibly be in the same substitution rule. The logic has been improved tremendously so that having slightly more columns per rule makes less difference and makes the rules easier to manage!

PlantBasedSQL

Hi @patrick_j thanks for your question! When you have multiple workers enabled and rules that are processing the same table it is common to see this behavior because they're trying to update the same rows, even if they are separate columns. The easiest way to handle this would be either to make rule 09-0071 dependent on -0070 OR to move -0071 to an entirely separate rule block, so that they don't run at the same time.
Very quick question though - is there any reason why you don't just have the SAME Substitution rule updating both the PCode and Address columns, rather than doing it in 2 individual rules?
Thank you very much - as always let me know if there's anything I can do to help!

patrick_j

Hi PBS. In the case that I've referred to, I don't think there is a reason why both fields couldn't be handled in the same rule. Where can I find some guidelines regarding when and when not to have multiple fields in one Substitution rule such as this?

patrick_j

Very good to know. I assumed the opposite and moved them all to be running separately so I'll change that on my next revision.