unable to see new/updated records
Arkadiy
I am trying to figure out what could cause the following issue.
Our app holds an OCCI connection, using it to obtain new/updated
records from a table using their timestamps. It runs the select query
once every 30 min for unlimited period of time (I do realize this is
not the best way of doing this. The new version will reconnect on
every cycle) .
We just noticed that the app stopped seeing the new records (the
SELECT returns nothing). Otherwise everything looks fine.
Could this be because the connection object eventually gets into a
wrong state?
Thanks for any help,
Arkadiy
hpuxrac
snip
Does the oracle database server still think your app is connected?
Is it possible that your app is generating an oracle error but you
have exception handling somewhere that is ignoring the ( perhaps )
failed connection?
Mladen Gogala
Does your app issue something like "BEGIN TRANSACTION"? I've had a Java
application that was doing that. The application was issuing the same
query over and over again, WITHIN THE SAME TRANSACTION. Transaction is the
basic unit of work in a RDBMS and there is something called "ACID rules".
If your transaction is started at connect time, you're bound to see only
the data that was committed at the time your transaction has begun.
That's the law. Try removing that "begin transaction" statement or, if
that's not possible, try putting in a "COMMIT" after each select.
Robert Klemme
> Does your app issue something like "BEGIN TRANSACTION"? I've had a Java
> application that was doing that. The application was issuing the same
> query over and over again, WITHIN THE SAME TRANSACTION. Transaction is the
> basic unit of work in a RDBMS and there is something called "ACID rules".
> If your transaction is started at connect time, you're bound to see only
> the data that was committed at the time your transaction has begun.
> That's the law. Try removing that "begin transaction" statement or, if
> that's not possible, try putting in a "COMMIT" after each select.
Also make sure that the data you want to see is actually committed by
whatever writes it to the DB.
Cheers
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
makni @yahoo.com GD
"Mladen Gogala" <n...@email.here.invalid> wrote in message
>> Our app holds an OCCI connection, using it to obtain new/updated records
>> from a table using their timestamps. It runs the select query once
>> every 30 min for unlimited period of time (I do realize this is not the
> Does your app issue something like "BEGIN TRANSACTION"? I've had a Java
> application that was doing that. The application was issuing the same
> query over and over again, WITHIN THE SAME TRANSACTION. Transaction is the
> basic unit of work in a RDBMS and there is something called "ACID rules".
> If your transaction is started at connect time, you're bound to see only
> the data that was committed at the time your transaction has begun.
> That's the law. Try removing that "begin transaction" statement or, if
That's the law only for serializable transactions, not for "normal"
transactions.
Default Oracle isolation level is read commited which makes both
nonrepeatable reads and phantom reads possible. This means that there is no
guarantee that the same SELECT run more than once within the same
transaction returns the same set of rows every time.
Regards
Mladen Gogala
> That's the law only for serializable transactions, not for "normal"
> transactions.
Nope. In Oracle RDBMS, transaction can see only the data that was
committed at the time when the transaction was started. That is always
the case. Serializable prevents phantom reads by imposing a shared lock
on the objects it accesses. Phantom read happens when records returned by
a query are modified (and committed) during the run time of the query so
that the rows returned by the query no longer exist in such form. In
other words, the result shows "phantoms". A shared lock on the underlying
objects will prevent them from being modified simultaneously with the
query and you will get a serializable session, with repeatable reads.
That will also do wonders for the concurrency of the applications.
GD
> On Tue, 01 Dec 2009 22:25:09 +0100, GD wrote:
>
>
>> That's the law only for serializable transactions, not for "normal"
>> transactions.
>
> Nope. In Oracle RDBMS, transaction can see only the data that was
> committed at the time when the transaction was started. That is always
> the case. Serializable prevents phantom reads by imposing a shared lock
This is not true. Please perform a simple test or consult your Concepts manual:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#CNCPT621
"Read committed
This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.
Because Oracle does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms."
Regards
Mladen Gogala
You are right. I was fully convinced that it was consistent up to the
point that transaction has started. I humbly apologize.
Vladimir M. Zakharychev
Are there indeed new records introduced (committed) since last query?
Does this very same query return results when executed outside of your
app? What's Oracle version (both server and client?) If you're on 11g,
this might be something related to OCI Client Result Cache feature
introduced in 11.1. It is said to be completely transparent to
applications and should handle data set changes automatically, but
this might not always be true due to defects in implementation...
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com