Only take app lock out at beginning of transaction

[Charles-Gagnon]

Currently we call sp_getapplock at the beginning of every query we run. This shouldn't be necessary - the app locks we get are transaction level and so we only need to do that once at the beginning of a transaction. The benefit of this will mostly be to reduce the noise in the query logs - since the applock statement is a no-op after first getting it then it just clutters up the query logs (for example if an error happens).

Before doing this we need to verify that we can't get in a circumstance where the transaction is ended but queries can still execute, such as what happened here : #979 So ensuring that if an error ever occurs we rollback the transaction is critical - otherwise we could risk corruption of the state by running queries outside of a transaction (especially when that transaction was rolled back)

[mpaulcvna]

Any updates on this (sorry for the double-post)? We just ran into this and is causing major issues for us.

Thank you!