×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 30-Jan-2012 07:37 , Charles Wilt wrote:
<<SNIP>>
So DB2 for i gurus, how does DB2 do it?
They are called "dirty reads" for a reason :-)
The situation is little different than a program which reads for no
lock to get a row that the program may decide to change, and then does
change that row. The program can either ensure that the row that was
read with no lock is really "the same", or just ignore the potential for
concurrency, thus risk corrupting the row.
The ridiculous results shown in the COUNT(*) in the referenced
article would be hard to reproduce with the DB2 for i SQL because the
DB2 for i performs row-locking rather than "page" locking; i.e. nothing
to do with SLS on IBM i nor the clustered indexes of other DBs, just a
result of a more granular locking for database I\O by using record-level
locking rather than the bucket approach of locking an entire "page" of
rows to update as few as just one row. And without a WHERE clause, the
counts are likely to be correct because the dataspace and index each
maintain the count, so visiting each page would not be required.
To avoid problems with dirty reads either allocate the data exclusive
to the reader or the reader and every updater must participate with an
appropriate level of isolation that will prevent dirty reads.
However I would still avoid using COUNT(*) in a SQL SELECT from "live
data" if the count result must relate directly to some specific row data
of interest; that is, I would instead, FETCH and perform my own count of
the specific rows. If just interested in grouping counts, I would
probably be content in the point-in-time result when querying live data.
Using a row-changed timestamp [in an index] and allowing only read of
committed data, illogical results could be eliminated by counting only
rows that have not been updated since the start of the query. A row
that might otherwise be double-counted could not be since a timestamp
for the since-changed would then preclude counting that row again. I
did not read anything about "snapshot isolation" but would presume that
could be a means to implement.?
Regards, Chuck
midrange.com
