mORMot Open Source

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

TSQLDBOracleConnection and Oracle DBLink: ORA-01453

We found an unexpected behavior of the Oracle in the case of some tables mounted using DBLink. If we execute select statement from DBLink'ed table (or view) Oracle implicit start transaction as described in this article

So if after such select we try to start transaction manually using TSQLDBOracleConnection.StartTransaction we got a ORA-01453 error.

I propose to rewrite the TSQLDBOracleConnection.StartTransaction method like this and ignore ORA-01453:

procedure TSQLDBOracleConnection.StartTransaction;
var Log: ISynLog;
begin
  Log := SynDBLog.Enter(self);
  if TransactionCount>0 then
    raise ESQLDBOracle.CreateUTF8('Invalid %.StartTransaction: nested '+
      'transactions are not supported by the Oracle driver',[self]);
  try
    inherited StartTransaction;
    if fTrans=nil then
      raise ESQLDBOracle.CreateUTF8('Invalid %.StartTransaction call',[self]);
    // Oracle creates implicit transactions, and we'll handle AutoCommit in
    // TSQLDBOracleStatement.ExecutePrepared if TransactionCount=0
    OCI.Check(self,nil,OCI.TransStart(fContext,fError,0,OCI_DEFAULT),fError);
  except
    on E: Exception do begin
      // When we issue a SELECT statement across a database link, a transaction lock is placed on the undo segments
      // So we must ignore ORA-01453 error here
      // See http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_appdev002.htm
      if Pos( 'ORA-01453', E.Message ) > 0 then begin
       Log.Log(sllWarning, 'It seems that we use DBLink, and Oracle implicitly started transaction. ORA-01453 ignored');
      end else begin
        if fTransactionCount > 0 then
          dec(fTransactionCount);
        raise;
      end;
    end;
  end;
end;

Can I commit this patch? What do you think?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: TSQLDBOracleConnection and Oracle DBLink: ORA-01453

Why not put the Pos() test at Check() method level?
It is always better not to raise an Exception (which should stay exceptional), if the problem should just be a warning.
But it is great to have an explicit log entry in this case, as you proposed.

A workaround is reported here:

I've solved the problem by starting the transaction before the first select statement which accesses the dblink.
http://forums.devart.com/viewtopic.php?t=14237

Perhaps an option - at least to note in our source?

Since ORA-01453 may be a logic error (outside DBLINK scope), we may disable by default the Pos('ORA-01453') check, and define a dedicated option to ignore ORA-01453 on purpose.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: TSQLDBOracleConnection and Oracle DBLink: ORA-01453

On the OCI level this is exception...

I know a workaround by starting the transaction before the first select statement. But because I don't know is statement use DBLink'ed table or not, I must start trans before ANY select. And in this case I have huge performance penalty

I do not put a Pos() test at the Check() method because OCI.Check is executed everywhere, but I need to handle ORA-01453 only inside StartTransaction method.

But you are right - we can add option on the TSQLDBOracleConnectionProperties level. Something like IgnoreORA01453OnStartTransaction ?

Last edited by mpv (2016-01-18 12:03:33)

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: TSQLDBOracleConnection and Oracle DBLink: ORA-01453

I add property TSQLDBOracleConnectionProperties.IgnoreORA01453OnStartTransaction. See [25b257653b]