Oracle 10gXE - readonly transaction

Terence · Post by **Terence** » 29.05.2006, 17:19

Hi,
i am using oracle 10gXE with driver oracle9i sucessfully to connect and execut select statements. But if executing insert statement i get following
error message:
"SQL Error: ORA-01456: INSERT/DELETE/UPDATE not allowed for a read only transaction"
The strange thing is that i sucessfully inserted into mysql,sqllite,posgresql and firebird tables with exactly same app/objects without problems before!
But however i noticed there was two types of query objects (readOnly and ZQuery), furthemore Zquery has a readonly property. So now im using ZQuery with readonly to false, but still have above mentioned porblem.

Any idea?
Tx for you assistance.

Post by **mdaems** » 30.05.2006, 09:10

Some guess. I'm not sure what settings you use, but did you set the transactionisolationlevel to tiReadCommitted? In that case the transaction is set to readonly in unit ZDbcOracle. If this is the case you could maybe try tiRepeatableRead. That should be a read/write transaction.
If this is not the case check the rights of the oracle user. (I don't think a user without update rights would automatically use readonly transactions, but one never knows)
Otherwise, try to debug ZEOS to see why/when the transaction is set to readonly.
Mark

Terence · Post by **Terence** » 30.05.2006, 09:49

Tx a lot, that was the cause, but i am still amazed that i had no problems on the other dbmas with setting "readcommitted".

pol · Post by **pol** » 30.05.2006, 11:26

Hm, as read commited is the default isolation level for Oracle, also for updating transactions, that doesn't sound too good. Has it always been this way?
Anyway, maybe it would be good to use a TZQuery with an

Code: Select all

alter session set isolation_level = read_committed

to re-set it to that after connecting.

Terence · Post by **Terence** » 30.05.2006, 12:42

Btw that the first time i here about commit type has something to do with write rights.. is that std behave?

I knew the transaction level just defined the reading process
like: in Read Committed isolation level, Dirty Reads are not possible, but Non-repeatable Reads and Phantoms are possible. In Repeatable Read isolation level, Dirty Reads and Non-repeatable Reads are not possible but Phantoms are. In Serializable, Dirty Reads, Non-repeatable Reads, and Phantoms are not possible.

..but not more.

Post by **mdaems** » 30.05.2006, 15:52

I've been looking what this transaction isolation stuff means.

As I understand it somebody tried to fit the non-standard oracle read-only transaction into zeoslib. Unfortunately this has been done by 'abusing' the tiReadCommitted Option, the default for Oracle. I didn't look at it very deep yet, but I think we should remove it from the coding as it's essentially wrong. Maybe we can use it for the 'readonly' property. Does anybody know when these read-only transactions are usefull?

Mark

Terence · Post by **Terence** » 30.05.2006, 16:49

As i understood you, except from oralce mostly? all DBMS have a special feature "readonly" query, which is "faked" by zeos in oracle using a tiReadCommitted. But furthemore tiReadCommitted in normal case (except in oracle) has nothing to do with write rights (read only)?

In this case, i would suggest to support the read only feature where it can be, and don't in oracle.
We already have the TransactIsolationLevel as property, whe shouldn't set this when setting property readonly. That will again lead to new threads of developers asking about, because they won't expect such a behave- like i wouldn' t.

So why do we have two types of queries?
TZQuery>TZAbstractDataSet>TZAbstractRODataset>TDataSet
TZReadOnlyQuery >TZAbstractRODataSet>TDataSet

But it seems that TZQuery also supports readonly feature what shall make TZReadOnlyQuery class unique.

Wouldn't it make more sense to have either or?
And if we decide to use the 2 class approach, using TZReadOnlyQuery for oracle driver simply shall result in exception "not supported feature for this dbms".

From my point of view that would be a clean solution,
what do you think?

Terence · Post by **Terence** » 30.05.2006, 17:32

Btw my question:
Does it has any disadvantages (performance, bandwidth, memory) to use
TZQuery or TZReadOnlyQuery with Insert/Update/Delete SQL statments?
Because there is a special comp called TUpdateSQL or even i could use IZPreparedStatement. There are so much possibilities and i already searched for an answer in doc, maybe you could point me out where i shall look?.

The article topic "Zeos 6.5.1 components properties and methods" in knowledge base sounded promising but since 12.12.2005, 16:39 there is still the message "Don't touch, I'll submit the article as I finis writing it.. Razz (expect to the end of the week) "

Well, zippo must have come to a pretty pass

Post by **mdaems** » 31.05.2006, 10:33

No Fabian,
I meant it's the other way round :Oracle is the only one who has a readonly transaction type. Don't know why. And I think somebody tried to use this by starting a readonly transaction if the IsolationLevel is set to 'read committed'.
If I had wanted to do that, I'd used 'read uncommitted', a level that's not supported by Oracle anyway. For compatibility of applications with other databases I would not recommend this solution either. Imagine an app written for mysql using 'uncommitted read' that should be ported to oracle --> same problem.
BTW, now tiNone sets the transaction mode to default for oracle, tiReadCommitted should do the same.
I'll modify in SVN testing version, unless somebody objects.

TUpdateSql is made to be connected to a TZQuery component. It provides a way to do 'alternative update processing', just read documentation for the Delphi UpdateSql component. It's similar.

The way I understand it : ZQuery is an extended ZAbstractR(ead)O(nly)Dataset component. So if you only need read access, you don't need the extensions to do updates on the retrieved data. I suppose this gives faster code. I think 'readonly' is more a feature to let you choose at runtime if you want to allow updates or not when using data aware components.

Mark

Post by **mdaems** » 31.05.2006, 22:42

Hi, I just changed this behaviour. Now tiNone and tiReadCommited behave the same way. The special Oracle ReadOnly Transaction isn't 'supported' anymore.
(Testing Branch, rev 54)

If somebody has an idea about 'reason' and 'why' we can always think about it again.

Mark

pol · Post by **pol** » 01.06.2006, 10:38

We are talking here about "transaction isolation level", and there is no such as read only with Oracle. It is a bug in the documentation. From Metalink:

Chapter 20 in the Oracle9i Database Concepts Release 2(9.2)
Part Number: A96524-01 contains the following section:

"You can set the isolation level of a transaction by using one of these
statements at the beginning of a transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY; "

If you attempt to set the isolation level to READ ONLY as per the
documentation, the following error is displayed:

SQL> SET TRANSACTION ISOLATION LEVEL READ ONLY;
SET TRANSACTION ISOLATION LEVEL READ ONLY
*
ERROR at line 1:
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }

The correct syntax is:

SQL> SET TRANSACTION READ ONLY;

Transaction set.

So nothing to worry about specialities with Oracle...

RÃ¼diger