Oracle PHP update no response

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

Solution

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.