php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #36910 Cannot retrieve a newly inserted record after calling ibase_commit
Submitted: 2006-03-29 12:51 UTC Modified: 2007-10-17 07:57 UTC
From: arnoldvmartinez at yahoo dot com Assigned:
Status: Not a bug Package: InterBase related
PHP Version: 5.1.2 OS: Windows 2003
Private report: No CVE-ID: None
 [2006-03-29 12:51 UTC] arnoldvmartinez at yahoo dot com
Description:
------------
First, I run a SELECT command to retrieve records from a table. Then, I initiated an ibase_trans, inserted a record, and called ibase_commit. Next, I run another SELECT statement but I noticed that the newly-inserted record is not being returned. When I checked the database, however, the record is indeed added in the table.
    On the other hand, if i don't run the first SELECT request and initiated an ibase_trans, inserted a record and called ibase_commit, then the newly-created record is returned when I do a SELECT request. It seems like Firebird retrieves from its cache eventhough a transaction has just been committed. 


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-03-29 12:55 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc.

If possible, make the script source available online and provide
an URL to it here. Try to avoid embedding huge scripts into the report.


 [2006-03-29 13:08 UTC] arnoldvmartinez at yahoo dot com
$dbh = ibase_connect($host, $user, $password);
$stmt = 'SELECT * FROM Employees';
$sth = ibase_query($dbh, $stmt);
while ($row = ibase_fetch_object($sth)) {
       echo '<br>'.$row->EMPNAME;
}
$th=ibase_trans(IBASE_DEFAULT,$dbh);
$stmt='INSERT statement here...';
$sth = ibase_query($th, $stmt);
ibase_commit($th);
$stmt = 'SELECT * FROM Employees';
$sth = ibase_query($dbh, $stmt);
while ($row = ibase_fetch_object($sth)) {
       echo '<br>'.$row->EMPNAME;
}
/* new record not listed */
 [2006-03-29 13:41 UTC] tony2001@php.net
And why did you decide it's a problem of PHP?
Are you able to get the expected result using some other tool?
 [2006-04-06 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2006-04-27 07:26 UTC] kogut at kogumelo dot com dot br
I'm not the owner of this bug, but i?ve got the same problem.

In my situation, as opposed to what the manual says, changing "$sth=ibase_query($th,$stmt)" to "$sth=ibase_query($dbh,$stmt)" just after the "insert ... " statement and "ibase_commit($th)" to "ibase_commit($dbh)" makes it work for me. And if i change to "ibase_rollback($dbh)", it works fine, too.  I don?t know if it is a Php or a Firebird bug, but that?s weird.
 [2007-10-16 21:56 UTC] lars dot westermann at privat dot dk
This has to do with the transaction handling of Interbase/Firebird.

Taking the first example:

The first SELECT uses the default transaction (taking a "snapshot" of the database).
Then a NEW transaction is created (unseen by the default transaction)
A record is inserted, and the transaction is closed.
The next select STILL operates on the DEFAULT transaction, which was opened prior to the transaction, where the record was inserted - therefore the newly inserted record is not found.

The solution is to ALWAYS use explicit transactions - and NEVER use the default transaction.

You *might* want to fiddle with the transaction type (snapshot, read_commited etc.) but beware, that this can have a dramtic effect on performance on large databases.

Personally I always start a transaction - even for a single select.

This "bug" therefore isn't a bug, neither in PHP nor in Firebird.

/Lars
 [2007-10-17 03:01 UTC] arnoldvmartinez at yahoo dot com
I am surprised to receive email regarding the bug I posted about a year ago!!! It's good to see too that another guy identified the same problem.
Unfortunately, we never pushed Firebird in the project and switched to mySQL instead simply because it's hard to deal with the "unknowns". And if I would use Firebird DB, I probably would NOT use PHP.
Is it a bug or not a bug??? Obviously that's debatable. Is it a Firebird issue or a PHP issue? I believe it's PHP's, because the issue does not happen in other applications (like FlameRobin, Workbench, etc.). When you commit an INSERT, it is sanely logical to see the record in your subsequent SELECTs... if not... then it contradicts the very principle of transaction processing. Database transactions should be an ACID (Atomicity, Consistency, Isolation, Durability) transaction ... otherwise it cannot ensure data integrity in a networked environment, especially in web applications. You may see this link: http://en.wikipedia.org/wiki/ACID

Does Firebird support ACID transactions??? Absolutely!!! Otherwise no single company would use it!

Is it a "bug" or NOT a "bug"??? In my opinion it is a bug. SELECTs are READ operations... they are suppose to, well..., READ what's IN the database, NOT what's in the "snapshot" or in the "cache" or any other form of temporary storage intended to optimize execution of queries. First and foremost, SELECTs should return accurate data at its latest and greatest state. Returning it fast is just a secondary thing... a nice-to-have. 

You don't need to initiate a TRANSACTION to do SELECTs.. be it a single SELECT statement or a thousand SELECT statements. Why? You're NOT CHANGING anything in the database.. you are just READING it.  

TRANSACTIONs generally involve INSERTS, UPDATE and/or DELETE operations. They are intended to CHANGE (NOT READ) the database, that's why, they have to support ACID requirements. 

Do we need SELECTs INSIDE transaction blocks??? I don't see any reason why you would want to do a SELECT while INSIDE the transaction block.... I  ALWAYS try to put them OUTSIDE the transaction block. Embedding a SELECT statement is probably my LAST OPTION... that is, if I can't find any other feasible way to kick that SELECT out of the transaction block.

Knowing the difference between SELECTs and TRANSACTIONs is very vital. With due respect to Lars... I believe you don't NEED to have transaction blocks if you're merely doing SELECTs because they are just READ operations. When you do a select, you do not intend to ALTER any piece of information in the database. Now if you need to ALTER the data, then you are compelled to use TRANSACTIONS....ACID transactions, to ensure and protect the integrity of data.
 [2007-10-17 07:57 UTC] johannes@php.net
If there is an bug, then it's in Firebird, not PHP.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Tue Mar 31 20:01:24 2020 UTC