Isolation level and user defined database function

  • I have the following SQL statement which uses a regular database view (ACC_BCA_Balance_Calc_Vw) and a database function (acc_calcbalance). The database function runs a SQL query calculating a sum in a seperate table (table X) and returns it to the main query.

    My question is how the isolation level i working here. I want to be sure that no new rows or deletion of rows in table X will affect the main query as long as it runs. Do I need to put the isolation level to serilizable before executing the query?

    select INS_FK, PAC_FK, dbo.acc_calcbalance('B', PAC_FK, INS_FK, getDate(), NULL, getDate()) Balance

    from ACC_BCA_Balance_Calc_Vw

    group by INS_FK, PAC_FK

  • I think you woul have to use either

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    GO

    BEGIN TRAN

    select INS_FK, PAC_FK, dbo.acc_calcbalance('B', PAC_FK, INS_FK, getDate(), NULL, getDate()) Balance

    from ACC_BCA_Balance_Calc_Vw

    group by INS_FK, PAC_FK

    COMMIT

    OR

    select INS_FK, PAC_FK, dbo.acc_calcbalance('B', PAC_FK, INS_FK, getDate(), NULL, getDate()) Balance

    from ACC_BCA_Balance_Calc_Vw

    group by INS_FK, PAC_FK WITH (HOLDLOCK)

    But keep in mind that this will prevent inserts and updates while your query is running.

    If you want to do this and allow updates during your query you can pull the data into a temp table first and then run your query off of the temp table.

    The down side of the temp table is if you are preserving a large amount of data then it will significantly slow down you query.

    Hope this helps,

    Dan

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply