June 6, 2002 at 10:05 am
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
June 6, 2002 at 11:16 am
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