0
751

Written by

CJ H
Follow
Question CJ H · Nov 5, 2017

How to avoid dirty read

#Caché #SQL

I get two methods below: I would run both methods concurrently.

However, the "testRead" would always read the uncommitted results from "testInsert".

Anyway to avoid that? Thanks.

ClassMethod testInsert()
{
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE)
  &sql(insert into Test.Table(AttrA,AttrB,AttrC,AttrD) values(1,2,3,4))
  hang 15
  &sql(ROLLBACK)
}

ClassMethod testRead()
{
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
  &sql(select count(*) into :ans from Test.Table)
  &sql(COMMIT)
  w !,ans
}
Discussion (5)0
Log in or sign up to continue

Comments

Robert Cemper · Nov 5, 2017

being suspicious on any * do you see the same behavior for 

select count(AttrA) into :ans from Test.Table)
0
Robert Cemper  Nov 5, 2017 to Robert Cemper

That seems to be just a part of the problem

0
Robert Cemper · Nov 5, 2017

Found it:

Exceptions to READ COMMITTED  1 of some more

If you query contains an aggregate function, the aggregate result returns the current state of the data,
regardless of the specified ISOLATION LEVEL. Therefore, inserts and updates are in progress (and may subsequently be rolled back) are included in aggregate results. Deletes that are in progress (and may subsequently be rolled back) are not included in aggregate results. This is because an aggregate operation requires access to data from many rows of a table.

see:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY…

0
Kyle Baxter · Nov 6, 2017

As Robert C. said, the reason for this is because aggregate functions do not follow the rules of Read Committed mode.  Moreover, the way to tell if you have values or not is to check SQLCODE, not the answer.  For instance, even if you did:
&SQL(SELECT AttrA into :valA FROM Test."Table")
 

You could still find your value (1) in valA.  Your next line should ALWAYS be checking SQLCODE.  Without that check, you cannot be sure that the value in your variable is good.

0