Jump to content


Photo
- - - - -

ORA-03123: operation would block


2 replies to this topic

#1 sean.graves

sean.graves

    Member

  • Members
  • PipPip
  • 15 posts
  • Gender:Male

Posted 13 July 2004 - 01:56 PM

ProIV 5.5r333 & mfc 518
Windows 2003 Server
Oracle 9i.2.0.3.0

We have a global update function that has 2 characteristics.
They are indipendent from each other ie. ls1 starts at 1 ends at 1

The characteristic 1 has two tables in lookup that both read one record.
On the good read of the last file we have a for loop that loops around an array of 25 elements.
eg FOR #I = 1 to 25

For each array element we are creating an SQL DYNAMIC DELETE statement followed by an SQL DYNAMIC UPDATE.
Both statements are against two different tables.

When #I = 1 for the first DELETE and the UPDATE all is ok.

When #I = 2 for the second DELETE we get a Proiv message box giving a "366 SQL Error".
In the trace file we see the following:
764:=========== OraSqlError: DBerr 3123=>-366: ORA-03123: operation would block

If we put a #OK = COMMIT() after the update the DELETE goes through successfully.

We can get this happen using the above SQL DYNAMIC and using native ProIV updates to do the delete.

:)

We have currently set the following ProIV Environment / Database flags:
SQL_CURSORS=AUTO
SQL_AGGREGATE_KEY=Y

Database Section has
[DATABASE - SQLDEFAULT]
FILETYPE=ORACLE
CONNECTION=USER/PASSWORD@DATABASE
PRODB_CHARSET=8
LOCKED_ROWS_RETURNED=Y

The TABLE which is being deleted has had:
the table ANALYZED for COMPUTE STATISTICS.
the index dropped and re-created.

Does anybody know why Oracle should give the "ORA-03123: operation would block"?

And if so how to go about fixing the problem?

Thanks for any advice.
Sean

#2 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 13 July 2004 - 05:05 PM

Sounds like the first operation is not yet completed (OCI_STILL_EXECUTING),


Is it possible to speed up comms between Pro & Oracle ?

I guess this would be awkward to code using Standard PRO-IV file delete and update
which might get around it and I wouldn't want to introduce a delay in the code so
perhaps BULK bind in a PL/SQL procedure would be worth checking out?
The content and views expressed in this message are those
of the poster and do not represent those of any organisation.

#3 sean.graves

sean.graves

    Member

  • Members
  • PipPip
  • 15 posts
  • Gender:Male

Posted 11 August 2004 - 09:14 AM

Thought I had better update this question.

Under the windows platform when using Oracle you should use the following two settings.

Pro4.ini
[Environment]
SQL_NOSIG=Y

[Database - SQL Default]
LOCKED_ROWS_RETURNED=Y

Whilst we were in our developmetn of just 50 records there was not a problem, but when our client of 50k to 2mil records then without the SQL_NOSIG=Y the system would timeout and then try the next SQL statement, hence the error ORA-03123.

ProIV have confirmed that these two flags should be on when using the Windows platform to stop timeouts.

Cheers for your help
Sean



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users