0

I got a very weird problem with oracle today.

I setup a new server with xampp for developing, i activated mssql and oracle and everything was just fine until i tried to execute an update statement.

Every select, insert, etc is working fine with PHP 5.3.

I also can parse the statement and get a ressource id back, but when i try to execute the statement my whole site is not responding.

no error, nothing. just timeout until i restart the apache.

here the code... it's the test code, so there should be no problem at all.

$conn = oci_connect('***', '***', '***');

$query ="UPDATE CHAR*** SET TPOS = 14, ID = 5, DIFF = 'J' WHERE ***NR = '3092308' AND LA*** = '5'";
echo $query;
echo '<br>';
echo $stid = oci_parse($conn, $query);
oci_execute($stid, OCI_DEFAULT);    
oci_free_statement($stid2);

Any hints or ideas? :-( I already tried to reinstall the oracle instant client and another version. I am using 10g like our db at the moment.

best regards pad

Pad
  • 45
  • 3
  • 7
  • If you look in the database, is the session blocked? Perhaps you're trying to update a row that is locked by some other session? Can you `SELECT ... FOR UPDATE` the row in question? – Justin Cave Jun 13 '13 at 08:05
  • Perhaps you should call `oci_commit()` after a write-type query? – Narf Jun 13 '13 at 08:09

1 Answers1

3

The row may be locked by another session. If this is the case, your session will hang until the other transaction ends (commit/rollback).

You should do a SELECT FOR UPDATE NOWAIT before attempting to update a row (pessimistic locking):

  • If the row is locked, you will get an error and can return a message to the user that this record is currently being updated by another session. In most cases an explicit message is preferable to indefinite waiting.

  • If the row is available, you will make sure no session modifies its content until you commit (and thus you will prevent any form of lost update).

There are other reasons why a simple update may take a long time but they are less likely, for instance:

  • When you update an unindexed foreign key, Oracle needs to acquire a lock on the whole parent table for a short time. This may take a long time on a busy and/or large table.
  • There could be triggers on the table that perform additional work.

For further reading: pessimistic vs optimistic locking.

Community
  • 1
  • 1
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • yes you were right. i got the ORA-00054: resource busy and acquire with NOWAIT error, so some other statement (most likely mine from before with no commit) blocks the table – Pad Jun 13 '13 at 08:24
  • @pad maybe this is due to a previous attempt that has not commited and still hangs around idly. You can query `V$SESSION` to gather information and eventually kill the blocking transactions. The blocked session should be `ACTIVE` in this view and the blocking session should be showed in the `BLOCKING_SESSION` column. – Vincent Malgrat Jun 13 '13 at 08:30
  • thanks a lot. my problem is solved. i terminated the open session. :) – Pad Jun 13 '13 at 08:58