Oracle time-travelling?

[Feenix566]

Sorry for the duplicate thread, I realized this forum would be more appropriate...

 

I've come across a situation where PHP and Oracle are apparently experiencing a time-dilation effect.

 

I'm writing code for user authentication. We track login sessions. First, it searches for open login sessions, and closes them. Then, it creates a new open login session. Here's pseudocode:

 

<?

$get_logins = OCIParse($DB, "select login_id from login where user_id = :user_id and logoutdate is null");

ocibindbyname($get_logins, ":user_id", $user_id);

if(!OCIExecute($get_logins))

  exit("failed to search for open logins");

while($login = oci_fetch_array($get_logins)){

  $update = OCIParse($DB, "update login set logoutdate = sysdate where login_id = :login_id");

  ocibindbyname($update, ":login_id", $login['LOGIN_ID']);

  echo "closing login {$login['LOGIN_ID']}\n";

  if(!OCIExecute($update))

      exit("failed to close login");

  OCIFreeStatement($update);

}

OCIFreeStatement($get_logins);

 

$insert = OCIParse($DB, "insert into login(user_id, logindate)values(:user_id,sysdate)returning login_id into :login_id");

ocibindbyname($insert, ":user_id", $user_id);

ocibindbyname($insert, ":login_id", $login_id, 32);

if(!OCIExecute($insert))

  exit("failed to create new login");

OCIFreeStatement($insert);

 

echo "created login $login_id";

?>

 

Now, this doesn't happen every time I run it, only about one in three times. It will actually update the login before it makes it! It prints out the debugging message saying it's updating the login, then it prints out the message saying it made the login, and the login_id's are the same number!!! So when the script is done running, the user doesn't have an open session, and subsequently is asked to log in again.

 

 

The only explanation I can think of is that PHP is skipping over the while loop because it's waiting for Oracle to return the results of the query. PHP must be executing the code after the while loop, then coming back and executing the loop on the results of the query, which unfortunately includes the record created by the code after the loop. Am I correct? Is there a way to avoid this?