Best spray foam for old home in Climate zone 3A – GreenBuildingAdvisor

We will be finishing the attic in our 1930 house in Atlanta, GA next year. Currently it is a nonvented and partially conditioned space with insufficient foam batt insulation. The roof is older clay tiles but with ice and water shield installed (i.e. a “lift and relay” was done at some point in the past) and roofers have inspected and confirmed lots of life left. 

After the attic is finished it will remain nonvented and conditioned, and the roof assembly will remain the same. There is not space to add venting so this will be a “hot roof” assembly. I want to greatly improve the insulation as part of the project and would like to air seal it.

Should I use open cell foam, or closed cell foam? I read that open cell carries risk of moisture passing from living space into sheathing, and closed cell adds more risk if roof leaks (though Joe Lstibrek may disagree). Please help me decide the best way to insulate.